Facts and dimensions.
WHAT A FACT TABLE HOLDS
Measurements of events: sales, payments, page views, sensor readings.
WHAT A DIMENSION TABLE HOLDS
Context describing those events: customer, product, date, location.
WHAT THE GRAIN IS
What one row of a fact table represents.
WHY IT MATTERS MOST
Declaring it precisely is the single most important modelling decision.
WHAT GOES WRONG WITHOUT IT
Rows at mixed levels, and totals that double-count.
WHAT A STAR SCHEMA IS
One fact table surrounded by dimension tables joined directly.
WHAT A SNOWFLAKE SCHEMA IS
The same, with dimensions normalised into further tables.
WHAT TO PREFER
Star, generally, for simplicity and query performance.
WHAT MEASURE TYPES EXIST
- Additive: summable across every dimension
- Semi-additive: summable across some, such as balances across accounts but not time
- Non-additive: ratios, which must be recomputed rather than summed
WHY THAT MATTERS
Summing a ratio produces a meaningless number, and it happens constantly.
WHAT TO DO WITH RATIOS
Store the numerator and denominator, and compute the ratio at query time.
WHAT TO BUILD FIRST
The fact table for the most important business process.
