08 Dec 2009 Implementing Multiple Hierarchies in a Dimension Mapped to Essbase with Oracle BI

If you are using an Essbase data source, you may be interested in learning more about the relationship between Essbase cubes and your Oracle BI EE platform.

It is quite common to find a dimension with multiple hierarchies in a BI project. This is managed in Oracle BI by creating a Dimension object with multiple child levels at a point that converges into the same leaf level.

This looks quite easy when the physical data source is a relational database. However, when the physical data source is an Essbase cube, the process is a bit more complex.

Let’s assume that we have a Dimension with the following attributes organised into two hierarchies:

Sample Account Dimension

When implementing this dimension in Essbase, the outline will look something like this:

Sample Essbase Outline

Dimension challenges

1. The Dimension object in Oracle BI will require an extra level containing “Generation 2”, where the hierarchy name (“Type Hierarchy” or “Location Hierarchy”) is indicated. This means that the account level actually contains the number of accounts multiplied by the number of hierarchies that have been defined.
2. Another issue is that every Logical Column defined under Hierarchy will share the same physical cube column. In this case, “Type” and “Country” are both mapped to “Generation 3”.

Sample Generation levels

Solutions for implementing multiple hierarchies

In order to resolve the two issues above, a Logical Table Source is required for each hierarchy. In addition, you will need to force a filter on each “Generation 2” hierarchy (in this case, ‘Type Hierarchy’ and ‘Location Hierarchy’).

This way, when querying for the accounts in California, we will be forcing the filter “Generation 2” = ‘Location Hierarchy’. The limitation here is that we will not be able to have queries combining objects from multiple hierarchies, as that would add two filters using different values in “Generation 2”. A solution to this problem is to duplicate the account dimension (having multiple dimensions with only one hierarchy). However that would increase the size of the Essbase cube and decrease the query performance.

As you can see, working with shared members in a dimension with multiple hierarchies mapped to an Essbase cube creates some technical issues that need to be assessed at design time. Your decision is a tradeoff and will depend on your business requirements and your technical environment.