Lesson 4 of 11 · 22 min
Working with facts and dimensions
By the end of this lesson you can tell which measures can be summed across which dimensions, recompute non-additive measures at every grain, and use a dimension as the driving table so that periods without facts still appear with zero.
Three kinds of measures
Not every number in a fact table can be added up in every direction.
- Additive measures can be summed across every dimension:
quantity,net_amount,spend_amount. Net sales by month add up to net sales for the year, and by channel to the total. - Semi-additive measures can be summed across some dimensions but not across time. A subscription business’s MRR on 31 March can be summed over customers, but MRR on 31 January plus 28 February plus 31 March is meaningless. Stock levels and account balances behave the same way; across time you take the value at the end of the period, or an average.
- Non-additive measures cannot be summed at all: ratios (margin %, conversion rate) and distinct counts. A customer who bought in January and in February is one customer in the quarter, not two.
For non-additive measures the rule is simple: recompute them from the base rows at every grain you report. Never sum or average them from a finer grain.
This lesson, its 4 graded exercises and every other Members lesson in SQL Quest come with a Formula Quest membership that includes SQL (Single Quest with SQL selected, Quest Trio including SQL, or Quest Unlimited), or with SQL access from your organization.
Free to everyone: all of SQL Foundations and Retrieving and Filtering Data, the first lesson of every track, the playground, the dataset library and the SQL reference. See free lessons