Oracle Essbase 11.1.1.3 and BI EE 10.1.3.4.1 – Displaying Member Properties – UDAs, Attributes and Alias Tables

Venkatakrishnan J -

Evaluate based MDX functions in BI EE currently do not support the display of quite a few member properties like Generation Number, Level Number, Alias Tables etc. These member properties can be very useful for reporting and sometimes these member properties drive the formatting of data, reporting layouts etc. Though it is not possible to use Evaluate based MDX functions, there is a work around that is available that can be used to display them in BI EE.

In order to understand the workaround, we would have to understand the physical structure of imported Essbase Cubes in the BI EE Physical layer. For example, lets consider the Sample->Basic cube that has been imported into BI EE. A cube (term for BI EE and database for Essbase) can consist of 4 objects. They are

  1. Hierarchy – This is actually a Dimension in Essbase. This will include all the dimensions including the measure dimensions
  2. Physical Levels – These are mapped as Generations in Essbase
  3. Level Attributes – These are actually the columns that would be used for reporting. By default the BI EE import, will produce one level attribute per Level
  4. Measures – These are actually the individual members of the Accounts dimension (this can be changed within BI EE)

The important aspect of Essbase reporting from BI EE is in understanding how Level attributes are treated. By default, BI EE would assign a level attribute as a level key. For example, if you look at the screenshot below

you would notice that, each level has a level attribute column of the same name. This attribute will in turn actually be imported as a level key for that level. This level key will by default produce only the Default Alias (if present for a member) or the member name itself. This is something that we cannot control as that has been hardcoded into the BI Server-Essbase MDX conversion dll. But what is possible though is we can add additional non-key attributes to each level. Each attribute can correspond to a member property. There are 2 kinds of properties that we can include as additional attributes. They are

  1. Intrinsic Member Properties
  2. Custom Member Properties

Intrinsic Member Properties are those properties that are available to every member and every Essbase outline. These are created by Essbase itself. The list of intrinsic member properties are given below

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

There are quite a few undocumented intrinsic properties (though they dont seem to work with BI EE) as well. Custom Member Properties are those that are defined by the end user. For example, UDAs, Attributes, Alias Tables etc are all considered as custom member properties. In order to display these properties in BI EE, they have to be added as custom level attributes to each and every level. For example, assume that we need to display whether a member has Ounces attribute in the Product dimension. In addition, we need to display the MEMBER_NAME instead of the alias for the Product dimension. In order to do this, right click on the Sample Cube and click on create new Physical Cube column.

Enter Gen1, Product – Ounces as the Column name. In the external name enter Ounces as shown below

Assign this new column to the Gen1, Product level. In the same way create another column called Gen1, Product Member Name as the column name and MEMBER_NAME as the external name.

Create the same set of attribute columns for every level in the Product dimension as shown below

The idea is to assign the member properties to each and every level and use them in reporting. So after creating the BMM for the above model, create a report containing the Category and the corresponding member properties as below. Also, create another report containing the Product SKU and the corresponding member properties

As you see, we have the assigned Ounces attribute for every product and the corresponding actual member names as well. This would work for all the levels. But the only problem is, if you have multiple levels along with their properties only the lowermost level member properties would be displayed correctly. For example, in the report below

The Category – Ounces and Category – Member Name are not correct. The main reason for this can be obtained by just looking at the MDX query.

With
  set [Product3]  as '[Product].Generations(3).members'
select
  { [Measures].[Profit],
    [Measures].[Sales]
  } on columns,
  NON EMPTY {{[Product3]}} properties ANCESTOR_NAMES, GEN_NUMBER, [Product].[MEMBER_NAME], [Product].[Ounces] on rows
from [Sample.Basic]

As you see the upper levels in the report are obtained through the ANCESTOR_NAMES property. What i would have ideally liked is to fire a separate query for the upper levels alone and then merge them together with this query. That would have ensured that member properties of the upper levels to be displayed correctly. There are a couple of workarounds possible to display the upper level properties as well. One is by achieving in-memory stitch joins within the BI Server and the other is by fragmentation. I will cover them in future blog entries.