OBIEE - Aggregate (Navigation|Table|Content)
About
The aggregate navigation capability of the Oracle BI Server allows queries to use the information stored in aggregate tables automatically.
When users request information at a high “grain” of aggregation, the Oracle BI Server can use the pre-aggregated sources instead of requiring the database to add up the detail at report time.
Oracle BI Server minimizes data retrieval time by selecting the most efficient data sources to satisfy user queries.
Articles Related
How an aggregate table is chosen ?
At query time, the Oracle BI Server first determines which sources have enough detail to answer the query.
Out of these sources, the Oracle BI Server chooses the most aggregated source to answer the query, because it is assumed to be the fastest.
The most aggregated source is the one with the lowest multiplied number of elements.
Model
When you create a logical table source for an aggregate fact table, you should create corresponding logical dimension table sources at the same levels of aggregation. You need to have at least one logical dimension table source for each level of aggregation.
For example, you might have a monthly sales fact table containing:
- a precomputed sum of the revenue (aggregated fact)
- for each product (dimension product)
- in each store (dimension store)
- during each month. (dimension time)
You need to have the following logical table source with the following aggregation content
| Logical Table Name | Logical Table Source Name | Model Aggregation content by logical level | or Model Aggregation content by logical column |
|---|---|---|---|
| Dimension Product | Aggregated Product | ProductDimension.ProductLevel | Product.Product_Name |
| Dimension Store | Aggregated Store | StoreDimension.StoreLevel | Store.Store_Name |
| Dimension Time | Aggregated Time | TimeDimension.MonthLevel | Time.Month |
| Fact Table | Aggregated Fact | ProductDimension.ProductLevel StoreDimension.StoreLevel TimeDimension.MonthLevel | Product.Product_Name Store.Store_Name Time.Month |
Aggregation content tab:
Support
A logical Column with a functional dependency association on a more detailed level
Business Model myBusinessModel:
[nQSError: 15011]
The dimension table source "Dimension Logical Table Source" has an aggregate content specification
that specifies the level "Logical Column".
But the source mapping contains column "Logical Column" with a functional dependency association
on a more detailed level "Level".
[nQSError: 15001]
Could not load navigation space for subject area myBusinessModel
If a logical column is already a part of a level, you can not use it as aggregation content rule, you have to specify its level.