Business Intelligence – Oracle

Oracle BI EE – One Dimension – Multiple Hierarchies

Posted by Venkatakrishnan J on November 30, 2007

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 Level

   Pack Size                  Category

   Products                    Sub-Category



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 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.



4 Responses to “Oracle BI EE – One Dimension – Multiple Hierarchies”

  1. caimino said

    Very useful!!

    Thanks a lot!

  2. Ranjay said

    This information was very helpful. I was struggling a bit in implementing these.

    Keep up the good work.


  3. Ishaq said

    Real Helper, Thanks a lot



  4. Tori said

    This is really interesting — thanks for the explanation. I have a follow-up question…

    I can’t figure out, once you have the multiple hierarchies, how to use them. In Answers it just drills through one of the hierarchies.

    In other words, once you’ve done the work described, what’s next?

    Thanks so much for this great resource!

    — Tori

Leave a Reply to caimino Cancel reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: