Business Intelligence – Oracle

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

Posted by Venkatakrishnan J on December 4, 2008

In the 2 blog entries before here and here we saw the basics of BI EE and Essbase connectivity. In this blog entry we shall see more complex reporting using the EVALUATE function. Wherever possible try using this atleast in this release. This would be made more easier in the coming releases like 11g. But untill then we need to use these approaches. But the advantage of using this approach is that we would have more control on the report. The MDX functions which can be called through EVALUATE can be classified into 2 types. They are

1. Functions that return data (numeric values or measure values) – An example for this would be SUM, RANK etc (I would refer them as Column level MDX Functions)
2. Functions that returns members, sets or tupules – An example for this would be UDA etc (I would refer them as row level MDX functions as they determine the row level members)

Both of these are supported currently. But before we move further we need to understand what the above 2 mean and how they pan out in terms of the MDX queries. Also there are certain limitation in the extent of the usage of these functions. We shall understand each one of them as move forward.

The above screenshot is nothing but a MDX query containing both the types of MDX functions mentioned above. Currently in BI EE both the above function types are supported only for certain dimension types.

1. Column Level MDX Functions – These are only supported on an Essbase Accounts dimension (even if you switch the Measure Dimension within BI EE, this would work only on the Accounts Dimension within the outline). Multiple Column Level MDX Functions are supported.
2. Row Level MDX Functions – These are supported on all the dimensions. But only one Row-Level MDX function per report is supported

With that brief introduction, lets start with a typical requirement which usually comes up in multidimensional reporting. We shall use the same Demo-> Basic Cube. If you had followed my previous blog entries then revert back the aggregation on the Actuals measure to Aggr_External. The requirement is given below

A report showing all the members of Products dimension, Generation 2 of Markets and their corresponding Sales. Basically multi level reporting.

Without the knowledge of MDX Functions, the only way to achieve this is by using UNION requests from within Answers. One of the main reasons for that is BI EE treats each and every level within a dimension as a seperate column. In our report above, we need all the level members for the Products dimension, only one column(Generation 2 of Markets) and their corresponding intersection values for Sales. But understanding of MDX functions makes the task a lot easier. Lets go to answers and pull up the 3 columns as shown below.

Now in the Products column, enter the below formula.

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")

As you see, this is nothing but a row level formula since we are generating sets of members. Anyone with a knowledge of Essbase would appreciate this fact since from an Essbase standpoint we just need to provide valid combinations of members to get at the intersections. Once this is done lets look at the report and the outline of the products dimension.

As you see, we have got the data for all the products, Gen2 of Markets and their corresponding sales. But one would have to disable drills on this EVALUATE column since the drills would not work. The reason for this is the fact that, BI EE still thinks it is at a certain level and would try to fire MDX queries accordingly when we do the drills. So, as a thumb rule, disable the default drills whenever EVALUATE is used. If drills are required use navigate instead. Also, there are certain drawbacks which we shall see below.

Lets try to use the same evaluate function for more than one dimension (for example products as well as markets). So basically, we want a Sales report for all intersections of the Products and Markets dimensions. The evaluate functions are given below

EVALUATE('%1.DIMENSION.MEMBERS',Product."Gen1,Product")
EVALUATE('%1.DIMENSION.MEMBERS',Market."Gen2,Market")

But this would throw an error shown below.

Lets analyze this further. If you look at the error above, this is nothing but an essbase specific error. Which is strange since our evaluate functions are correct. So, lets take a look at the MDX query that is fired back to Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

If you look at the query above, the same set for Evaluate0 is passed twice to the query. This i believe is a product bug when it generates the MDX queries. Now, lets remove one of the Evaluate0 sets in the above MDX query and fire this from Essbase.

With
set [Evaluate0] as '{[Product].Generations(2).DIMENSION.MEMBERS }'
set [Evaluate1] as '{[Market].Generations(1).DIMENSION.MEMBERS }'
select
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Evaluate0]},{[Evaluate1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Demo.Basic]

As you see, this is clearly a product bug since if we remove one of the Evaluate0 sets (which should actually have been the MDX query for our report above). This is such a shame as the above had such a potential to be used for more complex reporting. But this is something that has to be kept in mind while doing BI EE and Essbase Reporting. Multiple Row-Level MDX Functions are not supported.

Advertisements

2 Responses to “Oracle BI EE 10.1.3.4 & Essbase Connectivity – EVALUATE and MDX Functions – Multi Level Reporting”

  1. […] the fundamental difference between EVALUATE and EVALUATE_AGGR. If you had looked at my blog entries here and here, i would shown you 2 variations of using EVALUATE. The former shows how to use EVALUATE […]

  2. 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: