Cube dimension hierarchy in which some levels are coming from the same DB Table RRS feed

  • Question

  • Hi Everyone,

    I a Dimension Place in my cube.

    This dimension contains the following hierarchy:

    1. Country - Area - Area Level 2 - Area Level 3 - Station

    The problem is that the Area, Area Level 2 and Area Level 3 come from the same dimension table, that is Dimension Area Table.

    In this table I have a Level column (if level 1 Area, if 2 Area level 2 and if 3 area level 3). And also there is a Column that indicates the Parent Area key for knowing with which Area level 1, Area level 2 or area level 3 has to be nested to.


    What would be the best approach to get the above?

    Any help is very welcome!


    Thanks and best regards,



    Monday, May 23, 2011 4:10 PM


  • From the sounds of it you have three tables that contain the data you want to display in the hierarchy that have relationships between each other.  In the middle you have a parent-child relationship table.  If that is the case to me it would seem that flattening out the middle table (so you would have a column for each area level instead of the self-referencing setup you currently have) would be the route to go so that you can setup the hierarchy with the other two tables as well.

    If you are looking for more answers or suggestions to this I would recommend placing this or moving to the Analysis Services forums - http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/threads.

    Dan English's BI Blog
    Wednesday, June 1, 2011 11:09 AM