Business Intelligence – Oracle

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.

About these ads

2 Responses to “Oracle BI EE 10.1.3.4 and Essbase Connectivity – Understanding Aggregations – Part 4”

  1. […] Import the Demo Basic cube into the repository and create the BMM and presentation layers by drag and drop. Change the physical and the BMM layer aggregations(of the all the measures for which you want to enable writebacks) to SUM instead of Aggr_External. The main reason for doing this is to ensure that we can use string manipulation functions like concatenation from Answers. For more details on each of these aggregations check my blog entries here, here, here and here. […]

  2. Sunil Epari said

    Great Article. I do have a question on. I am building a Federated query using essbase and obiee, basically rendering information of different aggregation for the same dimensions from either OBIEE DW source or the Essbase source. I am able to configure for all the conformed dimensions. The only thing I am not sure how to configure is when an essbase dimension uses alternate hierarchies with shared members. Just using the alternate hierarchies and essbase shared members is possible, but clubbing this information with the OBIEE dimensions is where I am lost. Any input is greatly appreciated.

    Sorry for the detailed message.

    Thanks

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: