Business Intelligence – Oracle

Oracle BI EE and Essbase Connectivity – Understanding Aggregations – Part 1

Posted by Venkatakrishnan J on April 6, 2009

One of the least explored/documented part of the BI EE and Essbase Connectivity is the ability of BI EE to do aggregations at 3 different layers. Whenever Essbase is used as a data source in BI EE, there are 3 types of aggregations that are possible. All the 3 can produce different results and hence warrants a correct understanding of how they exactly work. I have seen quite a few customer cases wherein the report results are outright wrong due to wrong aggregations or due to lack of understanding of how BI EE treats Essbase. So, lets try to understand the 3 types of aggregation that BI EE and Essbase connectivity provides to the end user.

1. Aggregation by going directly against the aggregated intersections in Essbase.
2. Aggregation in Essbase by using MDX. Essbase would fire Aggregate/Sum functions to calculate the data from Level-0.
3. Aggregation at the BI EE layer

All the 3 above are possible in BI EE currently. Remember that by default, when you create the BMM for an Essbase cube by a simple drag and drop, the first type of aggregation would be used. There are certain caveats that one would have to understand while using this type of aggregation. As always, wherever possible try to include a dimension member from every dimension in the report. MDX as well as BI EE does not require us to qualify a report with all the dimensions. So, if you are creating a report with only a subset of all the dimensions in the outline, then you need to be aware of the fact that topmost dimension member for the other dimensions are implicitly assumed by the MDX query. For example, consider the outline shown below



As you see, this is a very simple outline containing 4 dimensions. Now lets create a simple report(assuming the default BMM created by the drag and drop option) as shown below


This report contains the topmost member of the channel and product dimension reporting against the Units measure. Since, we have not included the Time dimension in this report, we would typically assume that the report would be a sum of all the years data. Apparently that is not the case since MDX implicitly assumes the topmost node of the missing dimension. Since Time is a label only member, the value that you would see in the report would be of the year 1999 (first immediate child).


The MDX query for this report is given below

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

Now, since all the data that we requested in the report is present in Essbase, the MDX would go directly against the data source and extract the intersecting data values. But what if we want to get a sum of all the years. Then to achieve this include all the years as a filter in your report.



So basically, in the above report we are requesting data which does not even exist in Essbase. We only have the individual year values and not the summed up year values. So, If you look at the MDX now, you would notice that BI EE would have automatically fired an AGGREGATE function to retrieve the data back.

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], [Time].[2002], [Time].[2003], [Time].[2004]}'
member [Measures].[MS1] as 'AGGREGATE({[Time2]},Measures.[Units])', SOLVE_ORDER = 100
{ [Measures].[MS1] } on columns,
NON EMPTY {crossjoin ({[Channel1]},{[Product1]})} properties ANCESTOR_NAMES, GEN_NUMBER on rows

The question is how did BI EE determine that AGGREGATE function needs to be used here. What if we let the BI Server to do the aggregation? How do we make BI EE to generate SUM function based MDX instead of AGGREGATE? These are questions that would be answered in the next couple of blog entries.

In the same fashion, whenever you are creating a custom Evaluate MDX function based logical column, be aware of the intersecting dimension members while creating the report. This is absolutely critical for the data correctness in the report. We will go more into detail in the next couple of blog entries.


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

  1. […] concatenation 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: Logo

You are commenting using your account. Log Out /  Change )

Google photo

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

Connecting to %s

%d bloggers like this: