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.
Here is my dimensional attributes (Area, Staff at location level) table in relational database.
The requirement is pretty simple, to report sales by store location (Gen4), with the store’s attributes too.
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.