Business Intelligence – Oracle

Oracle BI EE – Reporting out of Multiple Levels in an Essbase Cube

Posted by Venkatakrishnan J on May 26, 2008

If you use Essbase or Oracle OLAP as a data source, the common reporting tool that the end users would most probably use is the corresponding Excel Add-ins. One of the major advantage of these excel add-in’s is that they have excellent connectivity to their correponding data sources. For example, if you take an Essbase cube (oversimplified for demonstration purposes) with a single dimension as shown below


the common reporting requirement is to have a report like the one shown below


This is one of the major advantages of Excel-add ins. One can treat any member from a dimension to be in the same level and can report on them. Now, lets try to import the above cube into BI EE and see what happens.


As you see, each level in an Essbase hierarchy is treated as a seperate column. Hence, in order to create reports like the one shown above, one way is to use a UNION ALL in the Answers and then combine all the different level columns into a single level. The other option to achieve the excel kind of reports as shown above is by leveraging the alternate hierarchy support option of Hyperion Essbase. That is, in the essbase outline, have all the members of your dimensional hierarchy in a flattened fashion (using the shared members method) in addition to the normal hierarchy as shown below.


As you se above, in addition to the normal hierarchy of the Geo dimension, we also have the flattened members of the dimension as shared members. Now go to BI EE and change the hierarchy type of the Geo dimension to “Unbalanced”.


Now, if you create a report in BI EE on the first level1 column, we should get all the members of the Geo dimension.

But the problem with this is that though the drill downs are enabled, the drills would not work since BI EE still treats them as level1 members and would try to apply a filter on level1. Lets look at an approach in a future blog entry wherein we can have the drills to work properly even if all the levels are flattened as shown above. So to summarize, if you have a requirement to report on all the levels (as a single level), the possible approaches are

1. Use UNION ALL and then combine data from multiple levels.
2. Use flattened alternate hierarchies from within Essbase.

4 Responses to “Oracle BI EE – Reporting out of Multiple Levels in an Essbase Cube”

  1. silw said

    You “flattened” essbase outline will produce wrong result for US member (‘+’ in shared members aggregation attributes)

  2. Venkatakrishnan J said

    Good Point Silw. I should have removed the aggregation (used ‘~’ instead of ‘+’).

  3. Darren Grogan said

    The unbalanced hierarchy is the best approach within OBI EE as it allows more flexibility with filtering. You still must include any filtered column in the request unless you hide it under column formatting. Your drill-downs will work if you have measures at each level.

  4. Raja said

    Hi Experts,

    Am a newbie to Answers,can you please help in solving this scenario.

    I need to combine 2 column members in a single column.Is is possible in OBIEE Answers.

    Like if 1,2 and 3 are one column member and A,B and C are 2nd column member…I need to have output in single colum like


Leave a 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: