OBIEE - Aggregate (Navigation|Table|Content)
Oracle BI Server minimizes data retrieval time by selecting the most efficient data sources to satisfy user queries.
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.
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 |
Aggregation content tab:
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.