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.
