OBIEE - Aggregate (Navigation|Table|Content)


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.

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.

Documentation / Reference

Powered by ComboStrap