An Interesting Case of An Essbase Dimension and its Attributes in RDBMS

Being a BI Consultant, I often browse through a number of BI forums for a quick glance on the things going on and to offer some peer help. I have recently come across an interesting case on oracle OBIEE forums, where there was a dimension in Essbase but its attributes are in a relational database and the numbers have to be reported using the attributes too apart from the dimension members. I have given it a thought, and it certainly seemed to be a good candidate to be in my blog.
So here it goes,

Essbase Outline:
My Essbase outline is something like this (Pretty simple, huh!!!), with a Store dimension (4 Generations : Store -> Country -> City -> Location) and a Sales Account Dimension.

Attributes:
Here is my dimensional attributes (Area, Staff at location level) table in relational database.

Requirement:
The requirement is pretty simple, to report sales by store location (Gen4), with the store’s attributes too.

My Approach:
I have given a thought on this requirement and the first thing that striked my mind was the concept of horizontal federation. I have given it a try, but I do not get the sales numbers correct, for which I later kind of realized that horizontal federation might not apply here at all just because the dimension is not totally conformed. So, my other approach was to lookup for attributes in the relational table based on store location.So here is how I designed

Bring as is the Essbase Cube into the physical and business layers as usual. 

Next, bring in the relational table as a lookup table into the business with Location as the business key.

Now, Create an attribute in the Essbase’s Store dimension called ‘Area’ in BMM with the formula as

Lookup(DENSE   “Sales”.”DIM_STORE”.”AREA” ,  “Sales”.”Store”.”Gen4,Store”) 

Once you have been through these steps, you are all set and here is what the report looks like (Ohh, yes, the screen capture is a bit weird.. There was some issue with 11g .6.2 BP1 VM with this setup, so I had to set this up on a different system with 11g 6.8 and take a cam shot !!)

I hope you found it useful and till I catch up again, Adios.

Advertisements

One thought on “An Interesting Case of An Essbase Dimension and its Attributes in RDBMS

  1. Anonymous says:

    Have similar business case. Able to get my Gen4,Country and Area to display, but when I add TotSales, see the message:

    Message returned from OBIS.
    [nQSError: 43119] Query Failed: [nQSError: 46008] Internal error: File server\Utility\Server\DataType\SUKeyCompare.cpp, line 854. (HY000)

    but i figured it out 🙂

    had to add foreign key to my attribute table.

    works as designed. thanks for the hint

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 )

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