Starting from the last post, where we
stopped about aggregation features in OBIEE. Today we will be discussing about
aggregate navigation.To reap the benefit of pre aggregated fact
tables OBIEE provides a way so that OBIEE automatically decides on itself which
table to use for generating a report based on the aggregation level of the
query.
To understand the concept of aggregate
navigation, let us consider a situation where we have revenue calculated at
detail day level (Revenue) and one aggregated at month level(Revenue Agg). We would follow the below mentioned steps to
activate aggregate navigation.
1.
First step is to import both the tables to
physical layer of RPD.
2.
Now in the BMM layer, we would create a logical
table Revenue.
3.
In the logical table “Revenue”, add a logical
table source Revenue. This can be done by clicking on Revenue table in physical
layer and dragging and dropping it on the “Revenue” logical table.
4.
On double clicking on LTS Revenue, we will see
only Revenue LTS in the Map to these tables area showing that the columns of
this logical table maps to only Revenue LTS. Now drag the physical table Revenue_Agg from
physical layer and drop it on logical table Revenue. After this we could see
the LTS Revenue as well as Revenue_Agg in the logical table sources for logical
table Revenue.
5.
Now as these tables in database are at different
granular level hence we need to tell OBIEE that these tables are at different
levels of granularity. To do this we first click on Revenue LTS and move to
Content tab.
6.
Now if we do not specify any levels for
different dimensions to which this table is related (Location, Product and Time
here), then the default level of aggregation would be detail level for each
dimensional hierarchy. However to specify different levels for time dimension
on which Revenue tables are aggregated we need to specifically specify the
level of aggregation in each LTS.
To do this in LTS Revenue content tab, we would specify detail level for
time dimension.
Since we
are not specifying any level for other dimensions, so the default now would be
considered as total level for other dimensions. This is contrasting to when no
level is specified for any of the dimensions in which case the default level is
detail level.
So to get the correct functionality we would specify detail level for
other dimensions too in Revenue LTS.
Now for the other LTS Revenue_Agg, specify period level as time dimension
and detail level for other dimensions.
Now
when a report is created with column from time dimension as period then Revenue_Agg
is queried while if we have column which is lower than period level then Revenue
table is queried.
