Business Intelligence – Oracle

Archive for December 5th, 2008

Oracle BI EE 10.1.3.4 & Essbase Connectivity – A Report Use Case

Posted by Venkatakrishnan J on December 5, 2008

While we are in the midst of our Essbase and BI EE connectivity article series, i got an email from Christian Berg (who used to frequent the BI EE forums when i was active before) asking whether a specific report was possible using MDX without using complex and complicated UNION requests. I thought it was worth publishing here just to emphasize the point that i made in the previous 4 articles. MDX is very powerful. Let us take a look at the report. We shall start with Demo Basic outline. I would not paste the exact outline and report screenshots that he sent. Rather i would rephrase that question so that everyone can try this out on the Demo Basic cube. In this case, we shall be using Scenario as our Measure Dimension.

       

       

The report specification goes like this “A report showing value of Actual, Budget and Variance (the measure dimension members) against all the products in Gen3, Products. Actual should show the value of Sales against each product (Accounts dimension member). Budget and Variance should show the value of Sales and Marketing (2 Accounts Members).” In a nutshell the report should look like this

       

Without Using MDX Functions this report is not possible out of the box. The only way is to use UNION requests and even then it would not produce the data in the correct format. So, lets use MDX to generate this report. As a first step pull in the Gen3, Product in our report. Once that is done, pull in the Actuals measure. In the column formula use the below EVALUATE function.

EVALUATE('(%1.dimension.currentmember,[Actual],[Sales]).value',Product."Gen3,Product")

       

This will ensure that we are getting the Actual for all the products with Sales as the Account.

       

Now, the next step is pretty tricky. All we need is a SUM([Product].currentmember,{[Sales],[Marketing]},[Actual]). But unfortunately, MDX would not accept the currentmember function within SUM. And we need to pass a column into EVALUATE. Else it would throw an error. In order to overcome this we shall be using the below evaluate function.

EVALUATE('(%1.dimension.currentmember,[Budget],[Sales]).value - (%2.dimension.currentmember,[Budget],[Sales]).value + SUM({[Sales],[Marketing]},[Budget])',Product."Gen3,Product",Product."Gen3,Product")

So, basically what this does is, we are passing Gen3, Product just to make the EVALUATE function work. The first minus will always result in a zero and that is our intention. The actual function should have the been the SUM part along. Similarly, use the below function for Variance

EVALUATE('(%1.dimension.currentmember,[Variance],[Sales]).value - (%2.dimension.currentmember,[Variance],[Sales]).value + SUM({[Sales],[Marketing]},[Variance])',Product."Gen3,Product",Product."Gen3,Product")

Now, if we look at the report, we would get the desired scenario values without any complicated UNION requests. But remember to disable the drills.

       

Advertisements

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 1 Comment »

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.

Posted in All Posts, Hyperion Essbase, OBI EE Plus | 1 Comment »