Oracle BI EE 10.1.3.3.3/2 – Hyperion Essbase Connectivity – Handling Measures and Account Dimension
Posted by Venkatakrishnan J on May 12, 2008
I was working with the BI EE and Essbase connectivity today and came across another important feature that would let an user to analyze any dimension members as Type 1 or Type 2. If you had gone through my previous blog entry here, i would have briefly explained what Type 1 and Type 2 actually mean. By default, when you import an Essbase cube, all the measures within an Account dimension would be imported as flat-list (with no hierarchy). This kind of an import is called as Type 1. For example, consider that we have a cube/database containing 2 dimensions LOB and YEAR. And we have a third dimension ACCOUNT containing the AMOUNT measure, then the TYPE 1 Essbase cube would be treated as shown below
DIMENSION LOB DIMENSION YEAR AMOUNT
As you see, in Type 1 we are losing out on the measure hierarchy if any. One of the very good advantages of Essbase is that it treats all the dimensions(including an ACCOUNT dimension) as the same i.e one can use any dimension as a scenario dimension from a reporting standpoint. Now, the question is how do we leverage that from within BI EE. i.e Basically we would like to use the months in the YEAR dimension as a scenario dimension and keep the account dimension intact. If you are not sure what i mean, below screenshot is what i need. The idea is to have the months in the reporting as a column header for all the analysis that i would be doing. In such a case, it is as though we are having as many AMOUNT measures as the number of MONTHS.
In order to achieve the above, there are a couple of changes that we would have to do in the physical layer of the Admin tool. Double click on the cube and navigate to hierarchies. Double click on the Accounts Dimension and change the dimension type to Other (instead of Measure Dimension).
Similarly, change the Year Dimension type from Other to Measure Dimension.
This will flatten out the Year Dimension. Now, remove the AMOUNT measure column from the cube and manually create 3 columns (each matching the attribute value names of the dimension members) as shown below
Now, recreate the BM and physical layer. Now you would be able to create reports as shown above in the 2nd screenshot
The above should give you an idea of how to go about converting your hierarchical members into flattened measure columns.