Oracle BI EE 10.1.3.3.3/2 - Displaying UDA's of Essbase Dimension Members - A comparison with Hyperion Financial Reporting

Another common question that anyone might get while working on the BI EE – Essbase connectivity is to know a way for displaying UDA’s of Essbase dimension members. Essbase supports this concept called UDA’s wherein one can assign the same attribute to multiple dimension members so that one can do cross dimensional attribute analysis. For example, if you take the default Sample-> Basic database you would notice that the Market Dimension members would have some UDA’s assigned to it like Major Market, Small Market etc.

    

But if you import the database/cube to BI EE, you would notice that there is no provision for explicitly displaying UDA’s. So, in order to display all the members to a specific UDA, we would have to use the below EVALUATE function from within Answers.

CAST(EVALUATE('UDA(%1.Dimension,"Major Market")',Market.Region) as character(30))

    

    

Lets try drilling down on say East and see what happens.

    

It looks like BI Server is not drill happy whenever you use UDA’s. Also, one cannot have a report like the one shown below in BI EE(atleast in 10.1.3.3.3)

    

The major reason for the above errors that you see is due to the fact that BI EE is more of a relational reporting tool. It still is not Multi-Dimensional aware per se and it still tries to treat a multi dimensional data source in the form of rows and columns. But one good thing is that it is getting there and hopefully we should have full blown multi dimensional query capability in the coming releases. Now, lets try to achieve the same in Hyperion Financial Reporting.

As a first step lets try to create the above shown excel report. In order to do that we would need to have the query as shown below

    

Once this is done, choose the members for the Region and Accounts.

    

    

Now, insert a column just after the Region column. Make sure that it is a column of type formula.

    

And in the custom Text area enter the below formula (increment the row numbers as needed).

<<MemberProperty(current, 1, Market, UDA)>>

    

    

Now, if you look at the report one can get UDA’s as well as the member names. Now, what if we want all the member names for a specific UDA. In order to do that, just have sales and Market columns in your report. Then instead of adding the members add a function as shown below

    

    

The above will list all the members to a specific UDA (Major Market) similar to what BI EE did above. What if we want drill downs? Just enable auto expansion and we would get the drills automatically as shown below

    

There are quite a few member specific formatting that one can do with HFR. BI EE and BI Publisher should hopefully uptake these features in the coming releases.

About these ads