Something I wanted to post since a while and was just asked again today: when you import a cube with multiple measure hierarchies into an OBIEE rpd…what are the steps to swtich from using one measure hierarchy to another?

Let’s start with a very simple cube which was imported as uses “Account” as its default measure hierarchy:

In the structure you see the second measure hierarchy “Scenario” as one of the dimensions. I want to use “Scenario” as our measure dimension and hence our fact in the Business Model. The first thing to do is to clone the whole physical object “Sample Accounts”:

In step 2 I double-click the “Custom” physical cube table, to the hierarchy tab and open the “Account” hierarchy.

Note the dimension type currently is “Measure Dimension”. I change this to “Other” to transform “Account” into a normal dimension where OBIEE is concerned.

Now confirming with “Ok” shows us the effect of this change:

Now both our dimensions “Account” and “Scenario” are showing as hierarchy objects. The physical cube columns “Net Sales”, “Margin” etc. are useless now so I delete them. Next up is the change of “Scenario” into the new measure hierarchy and hence the fact. Again I go into the properties of the hierarchy object and change its dimension type to “Measure Dimension”:

That removes the “Scenario” dimension from the display in the physical layer:

What is missing now are the actual members inside the Scenario dimension. During import all the account members are created automatically as physical cube columns. A simple change of the dimension type doesn’t automatically create the corresponding cube columns, so I do it manually:

It’s actually very simple, you just need to take care to exactly match the MEMBER_NAME property or the ALIAS from Essbase. And make sure to follow the standard: if there’s an alias, use the alias otherwise use the member name. In my case I just quickly create two physical cube columns “Actual” and “Budget”:

And with that I’m done. I now have a new representation of my cube using the scenario dimension with its members as the measure dimension for analysis and the fact of the OBIEE Business Model:

Cheers,
C.