Essbase Two Ways

In the days of OBIEE 10g there was only one way for Essbase content to be presented to users.  The measures hierarchy was flattened and each level was presented as individual single measures.  But to be honest, we were all so happy to be able to integrate Essbase into the OBI repository, that we were willing to overlook the shortcomings...for a while.  The problem was that Essbase is MOLAP and OBIEE is, at it's core ROLAP.  This, effectively, eliminated one of the big advantages that Essbase offered...a measures hierarchy.

With the release of 11g, we are now offered two ways to present content to users.  We can still flatten the measures hierarchy or leave it in its original form.  Don't get me wrong, both methods have advantages, disadvantages and specific uses.  I want to show you how to have the best of both worlds in a single business model.

Start by importing your Essbase cube into the physical layer.  When you import the cube, it will be in the original form, with a measures dimension and one single fact.  The measures dimension holds the measures hierarchy and the single fact holds all measured data.

Once imported, we are going to duplicate the Essbase database that was just imported by right clicking on it and clicking duplicate.

 

Now rename the duplicate database.  I usually just add _FLAT to the end of the database name to identify the flattened measures.  Now we are going to flatten the measures in the newly renamed database.  Right click on the database table and click "Convert measures dimension to flat measures".  Now you should have two databases that look something like below.

 

 




Now drag the originally imported database to the BMM layer.  After this is complete, drag each hierarchy from the flattened database to the appropriate dimension table in the BMM layer except for the measures dimension because it now exists as individual measures.  Once this is complete, each dimension table should have two logical sources.

 





Next step is to create a new logical table and drag the flattened measures into it. 

 

Once completed, drag the business model to the presentation layer, check in your changes, do a consistency check and save.

Lets take a look at the results.  As you can see below, the measures take on two completely different form.

 

This new functionality really opens possibilities that make Essbase a legitimate data source for OBIEE.  Try creating calculated measures in the repository using the flattened measures we created or try horizontal federation by incorporating a relational source in the business model.

Until next time....