Oracle BI EE 10.1.3.3.2 – Handling Ragged Hierarchies in Hyperion Essbase
Posted by Venkatakrishnan J on April 9, 2008
I am currently at a client location wherein we are testing out the BI EE and Hyperion Essbase connectivity. One of the questions that i got from the client was how does BI EE handle the ragged hierarchies of Hyperion Essbase. This was a pretty interesting question considering the fact that BI EE in itself cannot handle ragged hierarchies by default. Hence, i thought of testing this out with a simple Essbase cube. Lets start with a simple cube containing the following dimensions
1. Product Dimension (This would contain a simple ragged hierarchy).
2. Channel Dimension
3. Account Dimension
Hierarchies in the above dimensions is given below
So, the first step is to create a OLAP Model using Hyperion Integration Services. The model would look something like the one shown below
Ensure that you have created the account dimension. Save the OLAP Model. Now, create a metaoutline as shown below.
Once this is done, load the cube.
Once this is done, import this cube using BI EE.
Once imported, you will notice that BI EE would have imported the ragged product hierarchy as though it is a normal hierarchy.
Now, drag and drop this data source into the BM and Presentation layers. Then create a simple report out of this cube.
If you see above, i have included the first 2 levels of the hierarchy in the report. If you click on any of the Categories like Photo, you would get the corresponding sub-categories.
But if you click on any Product name which has no third level then it will give you no data which should not be the case.
Also, if you create a simple report containing all the levels, then you would see that all values which have all the 4 levels i.e all product names would be filtered which should not be the case again.
Well, one of the main reasons why the above happens is that BI EE thinks that all hierarchies are balanced by default. Hence when you pull in all the columns it removes the unbalanced columns out of the report. In order to circumvent this, go to the Administrator and change the Hierarchy type to “Unbalanced” in the physical layer.
Now, if you go back and check the report, you would be able to even drill on the product names. But the lower levels would all be null as expected.
Very nice indeed.