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 an hierarchy based on Calendar Year, Quarter, Month and Day and the Fiscal Year, Quarter, Month and Day. BI EE can accomodate such possible multiple hierarchies within the same dimension. Lets 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 mutliple hierarchies i.e. all the hierarchies shoul 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 LevelPack Size CategoryProducts Sub-CategoryDIMENSION_KEY
i.e
Hierarchy1 = Total Level -> Pack Size -> Products -> Unique Dimension Key
Hierarchy2 = Total Level -> Category -> Sub-Category -> 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 lets start building the hierachy.
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.
Posts
