Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Displaying Member Intrinsic & Custom Properties – Aliases and UDAs
Posted by Venkatakrishnan J on May 31, 2009
One of the common questions asked in the existing BI EE to Essbase connectivity is the ability to display member names in a report instead of the alias names. Currently BI EE does not support displaying the member names(if default alias exist already) in a direct way. So, lets take a look at an approach today which basically extends on the method described here in the modeling guide. Actually,. the method described there is correct but the explanation is partly wrong(in a different context it is right though). It is described there that Member Names are supported for only Level-0 members. It is actually supported for all the generations. But when a drill happens the alias names will return. Lets look at why this happens and of course understand the capability of BI EE in displaying other member intrinsic properties.
The most common question that generally comes up with regard to member names is, why not EVALUATE can be used to display them directly. The main reason why this is not possible is, Member Name is an intrinsic property of a member. There are no MDX functions which can actually be used to display a member name. An essbase member has many intrinsic properties. The intrinsic properties are listed below
Apart from the above there are other properties like custom UDA properties etc. Now lets take the example of the Demo Basic cube. If you look at the outline of this cube, it has aliases at the Time dimension level and also for the quarters(not for the level-0 though). Now, our aim is that, whenever we include these levels in the report, the report should automatically display the member names instead of the alias names.
The intrinsic properties are typically referred directly in the repository as the External Name.
So, in order to refer the alias, lets create a custom physical cube column as shown below.
The name for the column can be anything. Basically we are creating an Member Name column for the level that has aliases. In our case it is the quarter level.
Assign this new column to the Gen2, Year of the year dimension.
Also, make this column to be part of the BMM and the presentation layer. Now, if you create a report on this column and a measure, you would get a report only with the member names for the Quarter Level.
But the problem with this approach is that, once you drill on this column, the drills would return. That can be manipulated by re-arranging the level keys accordingly. The main reason why i introduced this was to show a very good feature that can be derived using this approach. In many cases, you would have a report containing 2 or 3 dimension levels and a fact measure. In the same report, you might want to know which dimension member has a specific UDA assigned. For example, consider the outline below
If you look at the Market dimension, the 2nd generation has multiple UDAs assigned. Assume that we have a report like the one shown below
In the above report, we need one more column stating whether that member has a “Major Market” UDA assigned or not. This is not possible using MDX as the UDA based MDX functions will filter the members based on a UDA (which is not our requirement). So, to start with lets create a custom Physical cube column. In the External name of the column enter Major Market as the External Name. UDA is a custom property of a member. Hence it can be referenced directly as an External name as shown below
Now, include this UDA in the Gen2, Region level of the Market Dimension.
Once this extra column is included, just include this in the BMM and the presentation layer. Now in the above report, pull in this column. This would basically show all the members having the Major Market UDA as 1. Remaining members that do not have this UDA assigned will be shown as 0.
The above technique can be used for other solving other interesting reporting requirements as well. Its heartening to see every aspect of MDX being available in some form or other for customization.