Fact table grain and measure additivity are the two most technically precise concepts in dimensional modelling, and misunderstanding either produces silently incorrect query results. Grain defines exactly what constitutes one row in the fact table — the atomic level of measurement. Additivity defines whether a measure can be safely summed across a given dimension without producing a meaningless result. Both must be documented explicitly in schema design, not left to consumers to infer, because incorrect conclusions from misunderstood grain or additivity appear numerically plausible and are hard to detect.
Aggregate tables are pre-computed summaries of a lower-grain fact table, stored at a higher grain to improve query performance. A delivery-grain fact table has 1 billion rows; an over-grain aggregate has ~167 million rows; a match-grain aggregate has 74 rows per season. When a BI tool asks for 'total runs per team per season', the query engine can read the match-grain aggregate instead of the delivery fact table, reducing I/O by 99.99%. Modern data warehouses and BI semantic layers can auto-route queries to the appropriate aggregate level using aggregate awareness.
Analogy🏏Cricket
🏏 Think of it like cricket: OLTP is the IPL's live ticketing counter — it handles thousands of simultaneous seat reservations, each requiring a precise single-seat record update with immediate confirmation. Speed per transaction and data consistency under concurrent updates are everything. OLAP is the IPL's season statistics department — it runs complex analytical queries across every ball bowled in every match of every season to produce the published rankings, economy rates, and historical comparisons. No one books a seat through the statistics department, and no broadcaster calls the ticketing counter for Bumrah's career economy rate. The two workloads demand completely different systems. Just as the ticketing counter is built for speed and correctness on one seat at a time and would buckle if asked to tally a decade of attendance mid-sale, an OLTP row-store excels at single-record writes but chokes on full-table aggregation; and just as the statistics department pores over millions of past deliveries but would be hopeless at booking a live seat under contention, the OLAP columnar engine sweeps billions of rows yet is the wrong tool for a fast single-row update. The physical design of each — row-oriented for the counter, columnar for the stats desk — is what makes it superb at its own job and unfit for the other's.
🏏 Showing the Cricket analogy — a Cricket version isn’t available for this concept yet.