Aggregation table

What is an aggregation table?

An aggregation table is a summary table that stores data at a coarser grain than the detailed table behind it. Instead of one row per transaction, it holds one row per group, for example total revenue per product category per month. The totals are worked out once and stored, so a query that only needs the summary reads a handful of rows rather than scanning every transaction.

The whole point is speed. Ralph Kimball described aggregates as simple numeric rollups of atomic data built purely to make queries faster, and noted they can speed some queries by a factor of a hundred or even a thousand. If a fact table has two billion rows and a report only asks for monthly totals by region, an aggregation table with a few thousand rows answers it almost instantly.

Think of it as the running totals at the bottom of a spreadsheet. You could re-add every line each time someone asks for the total, or you could keep the total in a cell and read it straight off.

Grain is the whole design

An aggregation table is defined by its grain: what one row represents. You build one by rolling the detail up, either by dropping a dimension entirely or by rolling a dimension up to a higher level, such as day to month or product to category.

The catch is that a summary can only answer questions at or above its own grain. A table summarised to month cannot tell you what happened on a Tuesday, and one summarised to category cannot break out a single product. If a report needs finer detail than the aggregate holds, the query has to fall back to the detailed fact table.

Because of this, a table can carry several aggregations at different grains, each aimed at a common query pattern. Kimball's guidance is that each grain must live in its own physical table rather than being mixed into one.

Aggregation tables in Power BI

Power BI has this pattern built in. In a large model, you can keep the detailed fact table in DirectQuery mode against the source and add a smaller aggregation table in import mode, cached in memory. When a visual only needs columns and a grain the aggregation covers, the engine answers from the fast in-memory summary. When it needs finer detail, Power BI quietly falls back to the DirectQuery fact table.

A billion-row sales fact might have an import aggregation of monthly totals by product category and region holding only tens of thousands of rows. A dashboard showing monthly revenue by region hits that summary; a user drilling to a single day and product drops through to the detail. The switch happens automatically, so report authors and users never pick the table by hand.

Aggregation table versus materialized view

An aggregation table and a materialized view solve the same problem, storing a precomputed result so reads are cheap, and the difference is who keeps it current.

A materialized view is managed by the database: you declare the query once, and the engine handles the stored result and its refresh, sometimes automatically. A hand-built aggregation table is yours. You write the rollup into an ordinary table in your data pipeline, which gives you full control over when and how it refreshes, and hands you the job of keeping it in step with the detail. A silent staleness bug in a hand-built summary is also yours to catch.

What to watch out for with an aggregation table

Staleness is the real cost. A summary is only as current as its last rebuild. If the detail updates through the day but the aggregate refreshes overnight, the two disagree, and users notice when a drill-down does not match the headline number.

The totals must reconcile. An aggregation is only trustworthy if summing the detail gives the same answer. Averages, distinct counts, and other non-additive measures do not roll up by simple addition, so a naive rollup of them quietly produces wrong numbers.

More aggregates is not always better. Every extra summary is another table to build, refresh, and keep consistent. Aim aggregations at the query patterns that actually hurt rather than pre-summarising everything.

Match the grain to real reports. An aggregate at the wrong grain gets bypassed, so you pay to maintain it and still hit the detail table. Base the grain on how people actually slice the data.