Set Up Aggregate Navigation by Creating Sources for Aggregated Fact Data
Aggregate tables store precomputed results from measures that have been aggregated over a set of dimensional attributes.
Each aggregate table column contains data at a given set of levels. For example, a monthly sales table might contain a precomputed sum of the revenue for each product in each store during each month. You configure this metadata in the Content tab of the Logical Table Source dialog.
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. If the sources at each level already exist, you don't need to create new ones.
For example, you might have a monthly sales fact table containing a precomputed sum of the revenue for each product in each store during each month. You need to have the following three other dimension sources, one for each of the logical dimension tables referenced in the example:
-
A source for the Product logical table with one of the following content specifications:
-
By logical level: ProductDimension.ProductLevel
-
By column: Product.Product_Name
-
-
A source for the Store logical table with one of the following content specifications:
-
By logical level: StoreDimension.StoreLevel
-
By column: Store.Store_Name
-
-
A source for the Time logical table with one of the following content specifications:
-
By logical level: TimeDimension.MonthLevel
-
By column: Time.Month
-
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's assumed to be the fastest. The most aggregated source is the one with the lowest multiplied number of elements. See Create Logical Levels in a Dimension to learn how to specify the number of elements at each level.