Business Intelligence – Oracle

Oracle BI EE & Essbase Connectivity – Understanding MDX Queries and Reports Tuning – Level Based Aggregations and No Aggregations

Posted by Venkatakrishnan J on December 3, 2008

In the last blog entry we saw how the basic BI EE and Essbase connecitivity works. Now, lets try to understand this in detail further. Lets start with understanding how the aggregation works for Essbase data sources. To begin with we shall change the level of aggregation for the Actuals Measure in the Business Model layer.



Basically, we have assigned Actuals to Level 1 of both Year as well as Markets. Leave the aggregation to the default “Aggr_External”. So, our intention is that we should get the same value that we get at Level1 of Year for Actuals to all the other levels of the Year dimension. Now, lets go back to answers and create a simple report having Gen1,Year ,Gen2, Year and Actuals.



This works as expected. But typically this is not possible from Essbase alone. MDX cannot generate values which basically show different values than what is actually present inside Essbase. So, to find out how BI EE achieves this, lets take a look at the Physical query.



As you see, BI EE basically fires 2 different MDX queries and makes an in memory join to achieve the result. The MDX queries are provided below

set [Market1] as '[Market].Generations(1).members'
set [Year2] as '[Year].Generations(2).members'
set [Axis1Set] as 'crossjoin ({[Market1]},{[Year2]})'
select {} on columns,
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]


set [Market1] as '[Market].Generations(1).members'
set [Year1] as '[Year].Generations(1).members'
{ [Scenario].[Actual] } on columns,
NON EMPTY {crossjoin ({[Market1]},{[Year1]})}
properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

The first query above basically gets a cartesian product of Gen1, Market (remember we had set a level aggregation of Level1 for Market in Actuals) and Gen2, Year (this is what was chosen in our report). This query does not get any data. Instead it is just used as a temporary join placeholder. The output of the first query is shown below



The second query actually gets the value of Actuals for Gen1, Market and Gen1, Year (which are the levels for the Actuals measure). BI EE actually does go against the top level node rather than doing the aggregation in-memory. It is OLAP aware atleast in this regard. In the case of relational sources it would fire the group by to get to the top node value.



The result for the report is then obtained by making a cartesian product of the above 2 queries. This cartesian product is achieved in memory of BI Server. So, as much as possible try to avoid setting aggregation levels at the BMM layer. Leave them as defaults. If you are sure that your output results would be less, then use the level based aggregations.

As a next step let us try to understand what the default aggregation (Aggr_External) does. For that let us change the aggregation for Actual measure to none.



And lets test the same report as above (With Gen1 Year,Gen2, Year and Actuals measure).



This is very interesting as well. This basically provides data for all the intersections in your Essbase Cube. Try to avoid this as well whenever you are building your reports. The MDX will basically get every intersection that is possible using a cross join across all the dimensions. There are certain use cases wherein this might be useful. The MDX query is given below

set [Accounts4] as '[Accounts].Generations(4).members'
set [Market3] as '[Market].Generations(3).members'
set [Product3] as '[Product].Generations(3).members'
set [Year3] as '[Year].Generations(3).members'
set [Axis1Set] as 'crossjoin ({[Accounts4]},crossjoin ({[Market3]},crossjoin ({[Product3]},{[Year3]})))'
{[Scenario].[Actual]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Demo.Basic]

Now that we have understood how the level based aggregations work lets understand how we can use MDX functions using EVALUATE to achieve some complex reports. We shall see what MDX functions are supported and how they can be used in the next blog entry.


Leave a Reply

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

You are commenting using your 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: