Business Intelligence – Oracle

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

1. MEMBER_NAME
2. MEMBER_ALIAS
3. LEVEL_NUMBER
4. GEN_NUMBER
5. IS_EXPENSE
6. COMMENTS
7. RELATIONAL_DESCENDANTS
8. MEMBER_UNIQUE_NAME
9. ANCESTOR_NAME

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.

image

The intrinsic properties are typically referred directly in the repository as the External Name.

image

So, in order to refer the alias, lets create a custom physical cube column as shown below.

image

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.

image

Assign this new column to the Gen2, Year of the year dimension.

image

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.

image

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

image

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

image

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

image

Now, include this UDA in the Gen2, Region level of the Market Dimension.

image

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.

image

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.

About these ads

2 Responses to “Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Displaying Member Intrinsic & Custom Properties – Aliases and UDAs”

  1. [...] Go here to read the rest: Oracle BI EE 10.1.3.4.1 & Essbase Connectivity – Displaying Member … [...]

  2. Gonzalo said

    Hi Venkatakrishnan:

    Great post, I found it very very useful, but I am having a hard time trying to figure out how to control the drill down so I can see the member name. You mention “That can be manipulated by re-arranging the level keys accordingly” but I don’t get the idea. Can you help me please?

    Regards,
    Gonzalo

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 151 other followers

%d bloggers like this: