Oracle BI EE 10.1.3.3.3/2 – 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.