Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 & Essbase Connectivity – EVALUATE and MDX Functions – Multi Level Reporting – Continued

Posted by Venkatakrishnan J on December 5, 2008

Yesterday we saw the 2 types of MDX functions that are fired by BI EE via the EVALUATE function. The important point to know while using these functions is in understanding when a function is treated as a Row Level MDX function and a Column Level MDX function. In order to understand that lets start with a simple report as shown below

As a generic rule, any EVALUATE when used against the measure dimension would be treated as a ROW Level MDX Function. There are some exceptions to this rule. But for now lets not go into those exceptions. And any EVALUATE against any other dimension (non-measure dimension) would be treated as a Column Level MDX Function. In order to make a column level MDX function to be treated as a Row Level MDX Function, the dimension should also be included in the report. To understand this lets start with the above report. Now in the above report, our aim is to make the 2 column in the report as a EVALUATE Column Level Function. So, basically we want 3 members (in multiple levels. For eg we need Market, West and Dallas in the report) in the Market dimension to be displayed. So, lets change the formula of the second column and include the below EVALUATE function

EVALUATE('Intersect(%1.dimension.members,{[East],[Market],[Dallas]})',Market."Gen2,Market")

As you see, we have got a report that we needed. Lets take a look at the MDX Query

With
set [Year2] as '[Year].Generations(2).members'
set [Evaluate0] as '{Intersect([Market].Generations(2).dimension.members,{[East],[Market],[Dallas]}) }'
select
{
[Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Year2]},{[Evaluate0]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see, the EVALUATE has gone in as a column level MDX function. Now, since multiple column level MDX functions are not supported now (due to a probable bug), lets remove the evaluate from the above report. Now, lets see a version of the EVALUATE function wherein EVALUATE against the same Markets dimension would be treated as a ROW Level MDX Function. For example Rank. Lets add one more column and in the column formula use the below EVALUATE function.

EVALUATE('Rank(%1.dimension.currentmember,%1.dimension.members)',Market."Gen2,Market")

As you see, we have got the Rank of each member in the outline. Now, lets take a look at the MDX Query

With
set [Market2] as '[Market].Generations(2).members'
set [Year2] as '[Year].Generations(2).members'
member [Scenario].[MS1] as 'Rank([Market].Generations(2).dimension.currentmember,[Market].Generations(2).dimension.members)'
select
{ [Scenario].[Actual], [Scenario].[MS1] } on columns,
NON EMPTY {crossjoin ({[Market2]},{[Year2]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

As you see the MDX Function has got passed as a Row Level MDX function. A Row Level MDX function would be passed only when the source dimension exist in the report as a seperate column. i.e in our above report, we need to have the Gen2, Market Column to generate a rank. Else it would not be generated. In the next blog entry let us look at a sample use case of these MDX functions.

Advertisements

One Response to “Oracle BI EE 10.1.3.4 & Essbase Connectivity – EVALUATE and MDX Functions – Multi Level Reporting – Continued”

  1. nice…

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

 
%d bloggers like this: