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 :
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.
The Service Request star schema in a Datawarehouse is comprised of :
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:
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:
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:
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 :
The database have also a query rewrite mechanism which can go a little more far in term of functionality :
To know more : SQL Engine - Query Rewrite
In order to enable this, the OBI Server metadata needs to be constructed such that :
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.
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:
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 :
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:
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 :
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.
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.