Business Intelligence – Oracle

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.


AddThis social bookmarking image button

Advertisements

2 Responses to “Oracle BI EE 10.1.3.3.2 – Handling Ragged Hierarchies in Hyperion Essbase”

  1. Sowjanya said

    Venkat,

    How would you pull aliases from the essbase(description of the product) into the physical layer. It doesn’t automatically pull when I tried.

    Thanks
    Sowjanya

  2. […] Tab!!!Oracle BI EE 10.1.3.3/2 – Between Prompts for Date Columns – Using Presentation VariablesOracle BI EE 10.1.3.3.2 – Handling Ragged Hierarchies in Hyperion EssbaseOracle BI EE 10.1.3.3/2 – Dynamically varying Colums in Sub-Prompts – Passing Presentation Variables […]

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

 
%d bloggers like this: