Aggregation in OBIEE

OBIEE as a reporting tool provides tremendous capabilities to do analytical reporting. Many new functions and decision features helps any user to do different reporting using OBIEE. For any analytical reporting anyone would accept that aggregation is the most basic and important tool. And so OBIEE provides amazing capabilities for doing different types of aggregation.

In this article we would see places where OBIEE provides features and capability is apply aggregation rules.

1.       The first and the most basic is the Aggregation tab in logical column of any logical table in BMM layer of RPD. In the figure below the area highlighted in red is used to set the default aggregation rule for any logical table column.

2.       The aggregation tab is used to specify default aggregation rules that would get applied when any user tries to query this column with other dimensions. OBIEE also provides a feature to specify different aggregation rules to be applied for different dimension table. To use this feature, one needs to check the “Based on dimensions” option as highlighted in green in above figure and specify different aggregation formula for different dimension.

If nothing is specified for any dimension then the aggregation rule as specified in the “Other” section gets applied in the physical query.

3.       The second feature that OBIEE provides related to aggregation is the concept of level based measure in any fact table. A level based measure in OBIEE is a measure in any fact table that is always pinned to a certain level of dimension table. Suppose there is a requirement where you have to show the monthly as well as yearly sales of products as two columns of a report. To do this one has to sum up sales column on monthly as well as yearly basis. To achieve this is SQL, you have to fire two queries and then join them together to get the final result.

SELECT time.cal_year,

        time.cal_period,

        SUM(sales.amount)

FROM sales,

       Time

WHERE sales.time_id=time.time_id

GROUP BY time.cal_year,

       time.cal_period;

And,

SELECT time.cal_year,

        SUM(sales.amount)

FROM sales,

       Time

WHERE sales.time_id=time.time_id

GROUP BY time.cal_year;

And then join the two result sets based on the cal_year column to get the monthly as well as yearly sales figure. However in OBIEE one can achieve this only when we tell OBIEE that one of the measure needs to be calculated at year level and one at monthly level separately and then it has to stitch the tow result sets together to get the final result. This can be done in OBIEE by creating a level based measure which is pinned at year level of time dimension hierarchy.

As before, we would consider a typical sales demo model. Here we would create a logical column amount_year in sales logical table and map it to sames.amount column and set the default aggregation rule as SUM as specified in step 2. Then we would double click on the sales.amount_year column in the BMM layer and go to Levels tab. This tab would be used to define the level of this measure.

As we have to pin the measure only related to Time_DDim, So we would specify the level for Time dimension as year level.

Now when a user queries columns as time.cal_year, time.cal_month, sales.amount and sales.amount_year column in OBIEE, the report produced would provide yearly as well as monthly sum of sales. Here one thing to mention is that if no level is specified for any dimension in the levels tab then it is considered as detail level for remaining dimensions. In the above example for location and product dimension the level pinned with amount_year column is detail level.

One of the most important and intriguing concept in OBIEE related to aggregation concept is the feature of aggregate navigation and fragmentation. In the next blog we would explore the concept of aggregate navigation.