One Dimension – Multiple Hierarchies

There was a question yesterday on the forums regarding the usage/availability of Multiple Hierarchies for a single dimension. I thought i would blog about it since there is very little documentation on this. Now let us take an example and try to understand what multiple hierarchies are and where they are used. Classic example for this would be the Time Dimension. In time dimension we can have a hierarchy based on Calendar Year, Quarter, Month and Day and the Fiscal Year, Quarter, Month and Day. BI EE can accommodate such possible multiple hierarchies within the same dimension. Let’s try to build one for a Product Dimension. There are certain ground rules for building multiple hierarchies for the same dimension.

1.   There must be a unique key for the dimension at the lowest level.

2.   This unique key must be shared by all the multiple hierarchies i.e. all the hierarchies should have their last levels as a level with the unique key.

3.   All the hierarchies should roll up to the top Total Level. And Top Level attributes should not have a Logical Key defined.

In our case, we have a unique key called DIMENSION_KEY which would form the last level. Our aim is to build 2 hierarchies like this

               Total Level
   Pack Size                  Category
   Products                    Sub-Category
               DIMENSION_KEY
i.e
Hierarchy1 = Total Level -> Pack Size -> Products -> Unique Dimension Key
Hierarchy2 = Total Level -> Category -> Sub-Category -> Unique Dimension Key 
As you see above DIMENSION_KEY is a shared level by both Products and sub-category. Now let’s start building the hierarchy.

Once we have reached till the 3 level for both the hierarchies, create a child level for the level Sub-Category. The child level would be DIMENSION_KEY.

After this create a Shared Child Level for the Products level and use the DIMENSION_KEY for this shared level.

Now we have basically created a dimension containing 2 hierarchies. Just test it out to see whether you get the proper drill downs.