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 lets the server answer a summary question from a summary table, automatically, with no change to any report.
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
1
The setup.
logical fact "Sales"
LTS "Detail" -> F_SALES_DAILY
content: Day level, Product level
LTS "Monthly" -> F_SALES_MONTHLY
content: Month level, Product Category level2
Content levels are the declaration. Each LTS states the grain it holds, in terms of the dimension
hierarchies.
3
How the choice is made. The server works out the grain the query needs, then picks the coarsest LTS
that can still satisfy it.
4
query: revenue by month, by product category
-> the Monthly LTS is coarse enough. Use it.
(perhaps 10,000 rows)5
query: revenue by day, by product
-> the Monthly LTS cannot supply days or individual products.
Fall back to Detail. (perhaps 400 million rows)6
That is the whole feature. The report author asked the same question both times, and the difference is four
orders of magnitude in what gets read.
7
The worked example interviewers use. Given a detail table, a monthly aggregate and a quarterly
aggregate, which serves "quarterly revenue by product category"?
8
The quarterly one — provided it holds product category. Trace the content levels: quarterly is coarsest, and
if its grain covers everything the query asks for, it wins.
9
If the quarterly aggregate is by region only, it cannot answer a product category question, and the monthly
one is used instead.
10
Two prerequisites, and both are commonly missing.Hierarchies must be properly defined, with levels matching the aggregates' grain. Content levels are
expressed in terms of those levels, so without a hierarchy there is nothing to declare.
11
The aggregate must actually agree with the detail. The server trusts the declaration. If the ETL
populating the monthly table is a day behind, or filters differently, users get different numbers depending
on which grain they asked for — and it looks like the data is wrong rather than the model.
12
Cache timing matters too. Purge the cache after the aggregates refresh, not before, or cached results
from the old aggregate outlive the data.
13
What the interviewer is probing.1. "What is aggregate navigation?" Probing: the central feature. Stalls: "Caching." *Moves
up:* the server picking the smallest table that can answer a query, so a yearly summary hits the
aggregate rather than the detail fact.
14
2. "What has to be configured for it to work?" Probing: the mechanics. Stalls: "Nothing."
Moves up: the aggregate as another logical table source with its content levels set correctly —
without them the server cannot know what it holds.
15
3. "What is the maintenance cost?" Probing: the trade. Stalls: "None." Moves up:
aggregates must be refreshed in step with the detail, or users get different answers depending on
which source was chosen.
16
4. "How do you decide which aggregates to build?" Probing: the method. Stalls: "Build them
all." Moves up: from usage tracking — the common grain combinations, not every possible one, since
each adds ETL and storage.