Chapter 19 · Materialized Views, Aggregate Tables, Cubes, and Precomputation
OLAP Cubes/Pre-Aggregation, Sparse Dimensional Combinations, and Modern Semantic Acceleration
Explain cube-like pre-aggregation and semantic acceleration without materializing a combinatorial universe, using AtlasMart sparsity evidence and workload-driven aggregate selection.
Chapter 19 begins from the Chapter 18 accepted current-state
controls:
9 paid lines, 7 orders, 11 units, 740 USD GMV, 450 USD cost,
and 290 USD gross profit. The atomic grain remains
one current paid order line. Precomputation
never replaces or rewrites that atomic fact. Later in the lab,
source sequence 207 deliberately introduces one new
paid line (O1010/1, 80 USD GMV, 45 USD cost) so the
base truth becomes
10 lines, 8 orders, 12 units, 820 USD GMV, 495 USD cost, 325
USD gross profit. That change is an explicit Chapter 19 fixture extension used
to demonstrate stale acceleration and refresh.
Mandatory runtime: Python standard library plus
SQLite; generation evidence used Python 3.13.5 and SQLite
3.46.1. Environment: local
filesystem/in-process database; no managed service or paid
feature. Storage: SQLite tables; because SQLite
has ordinary views but no native materialized-view command,
agg_daily_product is an explicitly managed
materialized equivalent table.
Time: AtlasMart business dates are UTC dates
and refresh timestamps are UTC ISO-8601 strings.
Keys: atomic sales key is
(order_id,line_no); aggregate key is
(order_date,product_id).
History: this chapter does not change SCD
rules; it accelerates the current paid-sales fact.
Security: synthetic identities only.
Benchmark cache: two warm-ups and seven
measured executions; OS cache is not flushed.
Non-guarantee: local timings do not predict
cloud latency or billing.
Learning outcomes
Explain cube/pre-aggregation as multiple grouping levels, not a requirement to materialize every combination.
Quantify grouping-set growth and AtlasMart sparsity.
Select useful pre-aggregations from query workload rather than dimension-count folklore.
Keep non-additive metrics and security semantics correct across accelerated routes.
1. Cube means multiple useful grouping levels, not “precompute the universe”
An OLAP cube or cube-like pre-aggregation
organizes measures across multiple dimensional grouping levels
so common rollups can be answered without repeatedly scanning
atomic facts. SQL CUBE expresses all subsets of
listed grouping elements; with n independent dimensions
that is 2^n grouping sets before considering each
dimension’s member cardinality. The logical idea is valuable;
blindly persisting every grouping result is usually not.
2. The combinatorics are observable
# Three dimensions have 2**3 = 8 grouping sets in a full cube.dimensions = 3print(2 ** dimensions)# AtlasMart after the seq-207 insert:observed_leaf_combinations = 10dense_leaf_space = 5 * 4 * 3 # dates * products * channelssparsity = 1 - observed_leaf_combinations / dense_leaf_spaceprint(round(sparsity * 100, 2), "% sparse")assert 2 ** dimensions == 8assert round(sparsity * 100, 2) == 83.33After the sequence-207 insert, AtlasMart has five business dates, four products, and three channels in this tiny current fixture. A dense leaf space would contain 60 date×product×channel combinations, but only 10 are observed: 83.33% of the dense leaf space is empty. Real warehouses commonly have far larger dimensions and stronger sparsity. Materializing every possible member combination wastes storage and refresh work even before higher grouping levels are counted.
3. Sparse combinations change both storage and semantics
“Sparse” means most mathematically possible dimension-member combinations never occur. A cube implementation may store only observed combinations, compress sparse structures, or calculate some levels on demand. Exact strategy is engine-specific. The dimensional model still needs to distinguish zero activity, not applicable, unknown, and missing data; an absent cube cell is not automatically a business zero.
A finance report may require explicit zero rows for planned-but-no-sales combinations, while a sales cube built only from observed facts contains no such cells. That requirement may need a coverage/factless structure or controlled densification rather than treating sparse absence as zero.
4. Choose pre-aggregations from workload evidence
Suppose 80% of certified dashboard queries ask for date×product GMV, 15% ask for date×channel, and 5% are exploratory. Candidate accelerators should be evaluated on measured query frequency, work saved, refresh cost, storage, freshness, and coverage. A full five-dimension cube with 32 grouping sets is not automatically better than two targeted aggregates.
| Candidate | Coverage | Risk/cost | Typical decision |
|---|---|---|---|
| Date × product | High for merchandising dashboards | Low dimension count; simple reconciliation | Strong candidate if workload confirms |
| Date × channel | Useful for acquisition/operations | Separate refresh object | Candidate if repeated often |
| Date × product × customer × channel × campaign | Broad theoretical coverage | High cardinality, sparse, expensive refresh | Reject unless evidence is unusually strong |
5. Modern semantic acceleration and aggregate navigation
A semantic/query layer can select a precomputed object when the requested dimensions, filters, metric version, security scope, and freshness are all compatible; otherwise it falls back to a lower-grain source. This is aggregate navigation in mechanism: choose an eligible accelerator without making business users manually know every physical table.
The navigator must never route an exact-distinct-order query to an aggregate whose order counts are only group-local, nor route a customer-restricted query to an object that lacks a security-compatible customer dimension. Speed does not override semantics or policy.
6. Controlled failure: “build every cube combination”
The failure has three forms: storage explosion, refresh explosion, and semantic explosion. Every persisted level needs lineage, freshness, tests, permissions, and compatibility rules. Some measures such as distinct counts or ratios need special rollup logic. A cube with many fast but ambiguous measures is worse than a smaller acceleration set with explicit contracts.
7. Production judgment
- Track real query shapes and frequency before selecting levels.
- Estimate member cardinality and sparsity from current and growth distributions.
- Keep atomic/base facts available for unsupported slices and audit.
- Classify metric aggregation semantics per level.
- Test empty-vs-zero behavior.
- Apply the same security purpose and row/column restrictions as the governed semantic request.
- Measure refresh amplification when dimensions or metric contracts change.
- Retire unused aggregates; precomputation is operational inventory, not permanent architecture.
8. Bridge to the acceleration contract
Lesson 5 turns the chapter’s rules into one acceptance contract: what may route to the accelerator, how fresh it must be, what it represents, how it reconciles, who owns it, and how to recover when it is stale or wrong.
Knowledge check
Check your understanding
- How many grouping sets does a full cube of three dimensions define?
- Why is AtlasMart’s 83.33% sparsity relevant?
- What conditions must aggregate navigation check?
- Why can an absent cube cell differ from zero?
Review the answers
1. Eight (2^3).
2. It shows dense materialization would allocate work/storage to many combinations with no observed facts.
3. Dimension/filter coverage, metric version/semantics, freshness, and security eligibility.
4. Absence can mean no event, not-applicable, unknown, or incomplete ingestion rather than measured zero.
Authoritative references
- Kimball Group — Aggregate Fact Tables or CubesNamed dimensional technique for numeric rollups that accelerate queries while retaining atomic facts and conformed dimensional semantics.
- Kimball Group — Fact TablesBackground for declaring grain first and retaining atomic facts as the expressive foundation beneath higher-grain aggregates.
- PostgreSQL 18 — Materialized ViewsCurrent official example of persisted query results that may be faster to read but are not inherently current. PostgreSQL is a reference, not a prerequisite for the local lab.
- PostgreSQL 18 — REFRESH MATERIALIZED VIEWCurrent official full-refresh semantics and concurrency conditions; exact behavior is PostgreSQL-specific and must not be generalized to all engines.
-
PostgreSQL 18 — GROUPING SETS, CUBE, and ROLLUPOfficial SQL example for cube-like grouping semantics and
the power-set nature of
CUBE. - SQLite — CREATE VIEWOfficial ordinary-view semantics. The mandatory lab deliberately uses a table plus explicit refresh metadata instead of pretending SQLite provides native materialized views.
- SQLite — EXPLAIN QUERY PLANOfficial plan evidence used in the local benchmark. Plan output is diagnostic and engine/version dependent.
- Python — sqlite3Standard-library interface used by the no-cost local lab.