locked
Hierarchy across multiple dimensions RRS feed

  • Question

  • Hi,

    Is there an option to define hierarhies across more than 1 dimension in the SSAS cube? When I double click on a dimension name in the SSAS cube, only 2 dimension gets selected and I am able to define the hierarchy between columns in the same dimension. In case I want to define the hierarchy across multiple dimensions, how can I achieve this? Please help.
    Monday, December 14, 2009 12:11 PM

Answers

  • First of all, i never tried it!

    But maybe it works when you define a snowflake schema. Which means your dimensions which you want to put into a "multidimensional" hierarchy refere to each following dimension.

    Dim1 -> Dim2 -> Dim3

    So you have to put in each dimension a relation (by using a foreign key) to the corresponding dimension.
    Then you can refere each dimensions.

    Further information you find here:
    http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/dd633c5e-fdb4-449b-a445-c13abcd6db24/
    Sven
    • Proposed as answer by Raymond-Lee Wednesday, December 16, 2009 8:29 AM
    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Monday, December 14, 2009 12:31 PM
  • Hi,

    You can simply use the attribute herarchies from each dimension, like gender from the customer and leaf level product attribute.

    HTH
    Thomas Ivarsson
    • Proposed as answer by Raymond-Lee Wednesday, December 16, 2009 8:29 AM
    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Monday, December 14, 2009 12:33 PM
  • You can create user-defined hierarchy across multiple dimension if there are relationships between the dimension tables.

     

    Double click the dimension in BIDS -> In data source view pane, right click anywhere, select the option ‘show all tables’ -> right click one column from another dimension table, select the option ‘new attribute from column’ -> create hierarchy base on this attribute. A snowflake schema as Sven said.

     



    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Wednesday, December 16, 2009 8:36 AM

All replies

  •   You can do this by creating new dimension with the help of named query by selecting respective columns from two or more base tables (not actual dimensions in cube)
    Lakshman
    Monday, December 14, 2009 12:27 PM
  • First of all, i never tried it!

    But maybe it works when you define a snowflake schema. Which means your dimensions which you want to put into a "multidimensional" hierarchy refere to each following dimension.

    Dim1 -> Dim2 -> Dim3

    So you have to put in each dimension a relation (by using a foreign key) to the corresponding dimension.
    Then you can refere each dimensions.

    Further information you find here:
    http://social.msdn.microsoft.com/forums/en-US/sqlanalysisservices/thread/dd633c5e-fdb4-449b-a445-c13abcd6db24/
    Sven
    • Proposed as answer by Raymond-Lee Wednesday, December 16, 2009 8:29 AM
    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Monday, December 14, 2009 12:31 PM
  • Hi,

    You can simply use the attribute herarchies from each dimension, like gender from the customer and leaf level product attribute.

    HTH
    Thomas Ivarsson
    • Proposed as answer by Raymond-Lee Wednesday, December 16, 2009 8:29 AM
    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Monday, December 14, 2009 12:33 PM
  • You can create user-defined hierarchy across multiple dimension if there are relationships between the dimension tables.

     

    Double click the dimension in BIDS -> In data source view pane, right click anywhere, select the option ‘show all tables’ -> right click one column from another dimension table, select the option ‘new attribute from column’ -> create hierarchy base on this attribute. A snowflake schema as Sven said.

     



    Hope this helps,

    Raymond

    • Marked as answer by Raymond-Lee Friday, December 25, 2009 9:13 AM
    Wednesday, December 16, 2009 8:36 AM