Business Intelligence – Oracle

Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 2

Posted by Venkatakrishnan J on April 7, 2009

Yesterday we saw the 3 types of aggregations that are possible currently in BI EE (while using Essbase as a data source). We had discussed the first type of aggregation which is the default. Now, today we shall go more into details of how BI EE generates the MDX and how the aggregations set in the BMM and the physical layer affect the MDX generated. As you might have already noticed, both BMM layer and physical layer level aggregations can be specified for Essbase data sources. For simplicity and for easy understanding we shall take only 3 types of aggregation in both the BMM and the physical layer. The list is given in the below table

BMM Aggr_External
BMM SUM
BMM None
Physical Layer Aggr_External
Physical Layer SUM
Physical Layer None

The important part to note here is the fact that different combinations of aggregations set in the BMM and the physical layer can produce different MDX queries. Let us start with the first combination shown below (we shall use the same report that we had used yesterday – One wherein data is directly available in Essbase and the other wherein its not available in Essbase).

BMM: Aggr_External and Physical Layer: SUM

       

       

You would notice that this would produce an error due to the wrong MDX being generated. Thats because whenever in physical layer, if the aggregation is set other than Aggr_External, the reports that we create should have a dimension member from every dimension. Though this has produced an error, lets take a look at the MDX.

With set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
member [Measures].[MS1] as 'AGGREGATE(crossjoin (Descendants([Channel].currentmember,[Channel].Generations(3)),crossjoin (Descendants([Product].currentmember,[Product].Generations(5)),{[Time4]})),Measures.[Units])', SOLVE_ORDER = 100
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

Now, lets go back to the report and add Gen2, Year to bypass the above error.

       

The MDX generated is given below

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '[Time].Generations(2).members'
member [Measures].[MS1] as
'AGGREGATE(crossjoin (Descendants([Channel].currentmember,[Channel].Generations(3)),crossjoin (Descendants([Product].currentmember,[Product].Generations(5)),
Descendants([Time].currentmember,[Time].Generations(4)))),Measures.[Units])', SOLVE_ORDER = 100
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},crossjoin ({[Product1]},{[Time2]}))} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, due to the aggregation property (SUM) in the physical layer, the MDX generated does a level-0 dynamic aggregation using the AGGREGATE function (though the intersection for the above members actually exist in Essbase). So, what this means is that, you do not necessarily have to always have aggregated data in Essbase. Now, as a next step lets change the report to produce a report for which data does not even exist inside Essbase.

       

       

Even this has produced an error. So, in effect what this means is with a combination of BMM: Aggr_External and Physical Layer: SUM it is not possible to do in memory or custom aggregations on Essbase. But by default in the report if we have generations from every dimension then a level-0 roll up happens using the AGGREGATE function.

BMM: SUM and Physical Layer: Aggr_External

To test this combination lets first create the simple report without the Time dimension and then we shall look at the MDX query.

       

       

The MDX query generated would be

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
select
{ [Measures].[Units] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, this behaves like the first case discussed above. If we are requesting a report for which data already exists in Essbase, then the MDX generated would go directly against those intersections. Now, lets add a year filter to generate a report for which data does not exist in Essbase (can be obtained only through calculations/aggregation).

       

       

Now, if you at the MDX below you would notice that the aggregation is not pushed back to Essbase. There is no AGGREGATE function. But there is a SUM function. So even in this case the aggregation is pushed back to Essbase. But if you have Time Based Properties in the outline like TBLast, TBAverage etc, then this would generate wrong results. You need to be extremely careful in such cases as the numbers reported might be wrong.

With
set [Channel1] as '[Channel].Generations(1).members'
set [Product1] as '[Product].Generations(1).members'
set [Time2] as '{[Time].[1998], [Time].[1999], [Time].[2000], [Time].[2001]}'
member [Measures].[MS1] as 'SUM({[Time2]},Measures.[Units])'
select
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

Now the question is what is the difference between this and the default setting. What difference does SUM aggregation on the BMM layer make to the report. There are 2 main differences (one is noted above).

1. When a report requests some data that is not there in Essbase (which would have to be calculated on the fly) then in this case the aggregation is pushed back to Essbase but with the SUM function. In the default case (both aggregations set to Aggr_External) the aggregation is pushed back to Essbase with AGGREGATE function.
2. The other difference is that when we set SUM as the aggregation in the BMM layer, then in Answers we can use relational functions to manipulate the data. For example, in the report above, lets assume we need to do a relational calculation like concatenation to the dimension members in the report. So, all we need to do is to append “Test” to the Product dimension as shown below.

       

       

As you see the number above is outright different than the actual numbers. The reason for this is the MDX generated.

With
set [Channel3] as '[Channel].Generations(3).members'
set [Product5] as '[Product].Generations(5).members'
set [Time4] as '[Time].Generations(4).members'
set [Axis1Set] as 'crossjoin ({[Channel3]},crossjoin ({[Product5]},{[Time4]}))'
select
{[Measures].[Units]} on columns,
NON EMPTY {[Axis1Set]} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from
[Global.Global]

As you see, just because of the relational calculation, BI EE has generated an MDX that goes against Level-0 members. If you fire this directly in Essbase you would notice all Non Empty tupules being generated as shown below.

       

So what this means is BI Server itself does the aggregation. Again, one would have to be extremely careful while using this as this would turn out to be very slow depending on the amount of data getting extracted. Not many people are aware of these minute differences and this is where i believe understanding Essbase and the BI EE RPD would help a lot while creating Essbase specific BI EE reports. I would document(in the form of a tabular chart) various aggregation combinations in the coming blog entries so that everyone can refer to that while using Essbase as a data source.

About these ads

One Response to “Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 2”

  1. […] from Answers. For more details on each of these aggregations check my blog entries here, here, here and […]

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

 
Follow

Get every new post delivered to your Inbox.

Join 158 other followers

%d bloggers like this: