OBIEE - Aggregate Navigation with level-based fragmentation
Table of Contents
1 - Purpose
OBI Server features have the ability to retrieve queries from an aggregate table defined through level of the hierarchy instead of the fact table. This ability is a part of the aggregate navigation feature.
The users see then a single view of the data, and if they ask for information that is precomputed in aggregate tables, the server automatically uses those aggregates to retrieve the data.
- Order Header is an aggregate table of the detail line Order. If you have to count the number of order, it's more light to perform it against the order header than against the detail line.
- Summary table, made by a group by sql statement.
In a other way, you will have one logical fact table with two logical table source :
- the aggregate table define on the level of the aggregate.
2 - Articles Related
3 - The environment : the Service Request star schema
The Service Request star schema in a Datawarehouse is comprised of :
- a central fact table, W_SRVREQ_F (By definition, W_SRVREQ_F, is constructed at the lowest level of granularity, which is the individual SR)
- and a number of associated dimensions, including Product (W_PRODUCT_D), Region (W_REGION_D), Service Request (W_SRVREQ_D) etc.
Thus, to obtain the number of SRs, the most basic fact from this table, one could execute a query of COUNT(W_SRVREQ_F.SR_WID) the data model for this star looks like this:
4 - Usage Pattern Observation to define the aggregate table
Typically, observations of the usage patterns of the user community suggest that a majority of their Service Request-related queries are not at the detail grain, but are aggregated along specific dimensions (these will appear in the SQL GROUP BY statements). These are:
- and source.
Thus, an aggregate table is constructed which compresses the basic metric, number of SRs, along all of these dimensions. This table is W_SRVREQ_A, and it has these columns:
5 - The query rewrite
Typically, when a user wants to query across the dimensions of priority, area, subarea, severity, resolution, and source, it will be far faster to give this information from the aggregate table than to sum up all of the rows from the fact table.
For queries :
- along the aggregated dimensions, it should navigate to the aggregate table (W_SRVREQ_A)
- at the detail level, it should navigate to the lowest level fact table (W_SRVREQ_F)
The database have also a query rewrite mechanism which can go a little more far in term of functionality :
- Join Back Rewrite
- Join Back and Rollup
To know more : SQL Engine - Query Rewrite
6 - Design the Business Model to use the aggregate table
In order to enable this, the OBI Server metadata needs to be constructed such that :
- the two physical table (aggregate and fact table) represent one logical fact table.
- each level of the physical tables (Aggregate and Fact Table) are defined
6.1 - Two physical table sources for one the logical table
Once the physical fact tables are constructed, one at the detail level and one at the aggregate level, they need to be added to the same logical fact source individually. To do so, create a logical fact table, and then drag each physical fact table from the physical layer on top of the logical fact table. When done correctly, it will look similar to the following:
Note that both physical fact sources now appear under the logical FACTS table.
Note that both physical fact sources now appear under the logical “Service Requests” dimension table.
6.2 - Declaration of the grain of each logical table source
The next step is to declare the grain of each logical table source.
First, a hierarchy must be created that defines exactly what keys define the grain of each level. The SR hierarchy consists of three levels:
- A grand total,
- an aggregate level that we'll call SR Attrs,
- and the Detail level.
All levels except the grand total level must have keys that uniquely identify rows at that level.
For our SR Attrs level, the keys we'll use are :
- Sub Area,
- and Source.
These are simply the logical columns that correspond to the physical columns of the same name. Then, to define the Detail Level, we'll use the Service Request Number and Row ID. Our completed hierarchy will look like this:
Once this hierarchy is constructed, we can now declare the grain of the two logical table sources in the FACTS table. Double-click on Logical Table Source (W_SRVREQ_A), navigate to the Content Tab, and specify the Aggregation Content for the Service Request Dimension as “SR Attrs”. It will then look like this:
Then, do the same for the logical table source for W_SRVREQ_F, except set it to the “Service Request Detail” level as follows:
6.3 - Two physical columns sources for one logical column
The final step involves building the logical columns derived from the logical table sources.
* to ensure aggregate navigation, a single logical column will have multiple physical column table sources (one for the fact and one for the aggregate).
* OBI Server will know which physical column to choose according to the granularity of the relevant table sources : aggregated table or detail fact table.
Thus, we can create for instance a measure “# of SRs” that will have :
- two logical column sources: fact table (W_SRVREQ_F) and aggregate (W_SRVREQ_A).
- a formula COUNT(distinct W_SRVREQ_F.SR_WID).
The formula for “# of SRs” at any of the levels specified in the hierarchy above, however, will be derived from W_SRVREQ_A.NUM_SR. The logical column “# of SRs” thus looks like this:
Repeat the configuration of each column in the aggregate table.
7 - Conclusion / Principles / Reference
Each logical dimension table will have multiple table sources at each level of granularity and a hierarchy which specifies the unique keys for each level. The central FACTS table will also have all of the logical fact sources from each level of aggregation and these sources will need to have the grain of each fact source declared as well.