Explain Aggregate Tables & Aggregate Navigation in Oracle BI

14 / 55

RPDadvanced

Aggregate Tables & Aggregate Navigation

Explain how aggregate tables are registered in the RPD so the BI Server automatically routes queries to the smallest sufficient grain.

Aggregate navigation is one of the most valuable — and most tested — performance features in the RPD. It lets a data warehouse team pre-compute summary tables at coarser grains (say, monthly-by-region instead of daily-by-store) and have the BI Server transparently choose the aggregate when a query doesn't need finer detail, without any change to the reports themselves.

It's like keeping a jar of pre-mixed pancake batter in the fridge for a quick weekend breakfast, but still having flour, eggs, and milk on hand in the pantry for when a recipe needs something the pre-mix can't provide — you reach for the coarser, ready-made option whenever it's sufficient.

Key Concepts

Mechanically, this is built on the multiple-LTS pattern discussed in logical modeling: you add an additional logical table source pointing at the aggregate physical table, and critically, you set its content level — the specific level within each relevant dimension's hierarchy that the aggregate represents (e.g., Month level of the Time hierarchy, Category level of the Product hierarchy). The BI Server's navigator compares the requested query's grain against each LTS's declared content level and picks the *coarsest* LTS that can still fully satisfy the request — using a smaller aggregate whenever possible, and only falling back to the detailed fact when the query needs finer granularity than any aggregate provides.

content level

For this to work correctly, dimensional hierarchies must be properly defined with levels matching the grain of each aggregate table, and the aggregate table's row count/content should be verifiably consistent with the detail table (usually maintained by the same ETL/refresh process, ideally with cache purges timed to follow aggregate table refreshes).

dimensional hierarchies must be properly defined

Interviewers often ask you to reason through a concrete scenario: given a fact table, a monthly aggregate, and a quarterly aggregate, which one does a query asking for "quarterly revenue by product category" use? The correct answer requires tracing content levels: it should use the coarsest table whose grain still satisfies the query — here, the quarterly aggregate, assuming product category grain is available there.