SQL · easy
What is the difference between a measure and a dimension, and why does it matter for a dashboard?
Asked in BI Developer interviews, in the SQL round.
Short answer
A dimension is what you slice by (date, region, product), a measure is what you aggregate (revenue, count, rate). Mixing them up (averaging an already-averaged rate, or summing a ratio) is the classic BI bug. A measure has to aggregate correctly at every grain the user can drill to.
| Measure | Dimension | |
|---|---|---|
| Is | A number you aggregate | An attribute you group or filter by |
| Examples | Revenue, orders, session length | Country, plan, device, date |
| Lives in | The fact table | The dimension table |
| On a chart | The axis with the numbers | The axis with the labels, or the legend |
| Why it matters | Summing a dimension key is meaningless | Grouping by a measure explodes the grain |
How to answer it
Definition
A measure is a number you aggregate. A dimension is an attribute you group or filter by. The table above sets them side by side; what follows is why the distinction has teeth on a dashboard.
Why it matters
Every chart is measures aggregated by dimensions. Get the split wrong and the chart is wrong in ways that look entirely plausible:
- Averaging an already averaged measure. The mean of five daily conversion rates is not the conversion rate for the week, unless every day had identical traffic.
- Summing a dimension key.
SUM(customer_id)is a number, and it means nothing. - Grouping by something with huge cardinality. Slicing by user id turns a bar chart into one bar per user.
- Double counting after a join. Joining a fact to a dimension with several matching rows repeats the fact and inflates the measure.
Example
-- wrong: an average of averages
SELECT week, AVG(daily_conversion_rate) FROM daily GROUP BY week;
-- right: rebuild the ratio at the week grain
SELECT week, SUM(conversions) * 1.0 / SUM(sessions) AS conversion_rate
FROM daily GROUP BY week;Additivity
Measures come in three kinds, and naming them is what separates a confident answer from a vague one:
- Fully additive: revenue, orders. Sum across any dimension.
- Semi-additive: account balance, headcount. Sum across regions, never across time.
- Non-additive: rates, ratios, distinct counts. Recompute from components rather than summing.
Distinct counts deserve their own warning: the distinct users for a week is not the sum of the daily distinct users, because a returning user is counted once for the week and several times in the daily sum.
Key points
- Measures aggregate, dimensions slice. Every chart is a choice of both.
- Ratios are not additive. Rebuild them at the grain you are showing.
- Distinct counts do not add up across time or segments.
- A join that fans out the fact table inflates every measure downstream.
FAQ
- Can a column be both? Yes. Price is a measure when averaged and a dimension when you group by price band.
- Where should this be enforced? In the semantic layer or the mart, so every dashboard shares one definition.
- Why do two dashboards disagree on active users? Usually a distinct count summed somewhere, or two different definitions of active.
The 30-second answer
A measure is what you aggregate, revenue or orders or session length. A dimension is what you slice by, date or country or plan. Facts hold the measures, dimensions hold the attributes, and every chart is measures aggregated by dimensions.
It matters because the failure is quiet rather than loud. Averaging an already averaged rate gives an average of averages, which is only correct if every bucket had identical volume. Distinct counts do not add across days, because a returning user is one user for the week and several in the daily sum. So I recompute ratios and distinct counts at the grain I am displaying rather than summing what was computed at a finer one.
In the interview
- What they are checking: whether you understand additivity, not whether you can define the two words.
- Common mistake: modeling a rate as a column and letting the tool sum or average it.