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 do not 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 is assumed to be the fastest. The most aggregated source is the one with the lowest multiplied number of elements. See Creating Logical Levels in a Dimension for more information about specifying the number of elements at each level.