OBIEE - Aggregate Navigation with level-based fragmentation

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.

The aggregate table is for OBI a fact table with a lower level of granularity.

Example :

  • 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 :

Each logical table source of a fact logical table needs to have an aggregation content defined. The aggregation content rule defines at what level of granularity the data is stored in this fact table.

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:

  • priority,
  • area,
  • subarea,
  • severity,
  • resolution,
  • 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.

If the aggregate table contain also dimensional information, they must also be declared as logical table sources for the logical dimension table. (In this case for instance, the table dimension “Service Request”). This logical dimension table will join to the logical FACTS table in the Business Model Diagram. The process is identical to that for fact tables, and when completed, the logical Service Request Dimension will look like this:

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 :

  • Priority,
  • Area,
  • Sub Area,
  • Severity,
  • Resolution,
  • 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.

The key insight is that, :
* 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.

dat/obiee/obis/fragmentation_level_based.txt · Last modified: 2017/09/13 16:15 by gerardnico