Business Intelligence – Oracle

Archive for April 13th, 2009

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

Posted by Venkatakrishnan J on April 13, 2009

Today i would basically summarize the observations that i had mentioned over the last couple of blog entries. As a generic rule, while designing BI EE reports with Essbase as the data source, it is important to consider the 2 points mentioned below

1. Physical layer aggregation and BMM layer aggregation both determine how the MDX is fired.
2. Each one of them should not be looked at in isolation or should not be changed in isolation without considering the end to end impact.

Lets look at the results of each and every combination one by one.

Physical Layer: Aggr_External and BMM: Aggr_External

1. If a report requests data that exists directly in Essbase, then the MDX generated would go only against those intersections.
2. If a report requests data that does not exist directly in Essbase ( can be retrieved through a custom aggregation), then the aggregation would be pushed back to Essbase. The MDX would only go against the necessary intersections to get the aggregated/calculated data. The Essbase aggregation would use the AGGREGATE function to calculate the summarized data.
3. This does not support relational database functions and hence everything would have to converted into MDX.

Physical Layer: Aggr_External and BMM: SUM

1. If a report requests data that exists directly in Essbase, then the MDX generated would go only against those intersections.
2. If a report requests data that does not exist directly in Essbase, then the MDX fired would be pushed against the Essbase layer. The major difference in this scenario and the one above is the fact that Aggregation is the fact that the aggregation in this case is done using the SUM function. So, this would generate wrong results if the outline contains Time Based properties like TBAverage, TBLast etc. This sometimes can vary based on the outlines.
3. This supports relational calculation like concatenation etc. But the aggregation would happen only at the BI Server layer. The MDX fired would go against all the level-0 intersections. So, this is generally not recommended. Also, the numbers produced might be wrong if you have lots of Label Only or Time Based properties in your outline.

Physical Layer: SUM and BMM: Aggr_External

1. This combination requires all the dimensions in the report layout.
2. This combination does not have any specific advantage over other combinations.
3. This always does a level-0 roll up using AGGREGATE function. This can be at times pretty slow and is not recommended unless you want the aggregation to happen dynamically all the time.

Physical Layer: SUM and BMM: SUM

1. This is exactly similar to Aggr_External aggregation set for both Physical Layer and BMM.
2. If a report requests data that exists in Essbase, then the MDX generated would go only against the necessary intersections.
3. If a report requests data that does not exist directly in Essbase, then the aggregation would be pushed back to Essbase. The MDX would go only those necessary intersections to calculate the aggregated/calculated data. The Essbase aggregation would use the SUM function to calculate the summarized data.
4. The major advantage of this is the fact that this supports relational calculations as well. When relational calculations are introducted then, the relational manipulations are done in BI Server memory. The MDX generated would go only against those necessary intersections rather than doing a level-0 dynamic sum.

Of course, some of the behavior above can change as new releases of BI EE come out. But currently the above summarizes the behavior of various aggregation combinations when introduced in the BMM and physical layer. There are other combinations that are possible as well. I have not covered them as they are not used that much.

Posted in All Posts, EPM, Hyperion Essbase, OBI EE Plus | 2 Comments »