About Aggregate Tables

Aggregate tables are physical tables that store precomputed results from measures that have been aggregated over a set of dimensional attributes.

You must join the aggregate fact and dimension tables. See About Aggregate Table Joins.

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.

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 additional sources.

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 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 Analytics query engine first determines which sources have enough detail to answer the query. Out of these sources, the Oracle Analytics query engine 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 Logical Dimension Table to learn how to specify the number of elements at each level.