An independent study reference written by Dr Phuc V. Nguyen. It is not official subject material — for assessment requirements always follow your subject outline and vUWS.

Data Foundationsintermediate

Measures and dimensions

Q: A team wants conversion rate to be correct at store, region and national level with no special handling. What should the fact table store?

Conversions and visits as two additive measures, with the rate computed at query time. Rates do not sum. Storing the two additive components lets any level of the report sum them first and divide once, which reproduces the pooled rate everywhere and needs no per-level logic. A weighted average can also recover the pooled rate, and only if the weight is the denominator itself; weighting by a store count instead gives small and large stores the same influence and misses the target again.

Q: Which of these can be summed across the days of a month to give a figure in the same unit, meaning the same thing it means on a single day?

Units sold. Units sold is a flow, so each day contributes new events and the month is the sum of the days, still counted in units sold. Inventory, subscribers and cash are stocks measured at a point in time. Adding a stock across days is not meaningless, and what it produces is an exposure in unit-days, such as inventory-days or dollar-days, which is a different quantity in a different unit. It cannot stand in for the month's inventory, subscriber count or cash balance, which is why a closing or an average value is reported instead.

A measure is a number you aggregate. A dimension is an attribute you slice by. The working test is whether adding the values produces something meaningful: sales amount passes, postcode does not. Measures then divide by how they add up. Additive measures sum across every dimension. Semi-additive measures, such as an account balance or stock on hand, sum across products and stores and not across time, where a closing or average value is used instead. Non-additive measures, above all ratios and percentages, cannot be summed, and averaging them across groups is only correct when each rate is weighted by its own denominator, so the dependable design recomputes them from their components at whatever level is being reported.

Why it matters

Measures are the things on the scoreboard. Dimensions are the ways of cutting the scoreboard up: by team, by month, by venue. The trap is ratios. If one store converts ten visitors out of a hundred and another converts one out of nine hundred, averaging ten per cent and about a tenth of a per cent gives roughly five per cent, which is nowhere near the truth. The real combined rate is eleven conversions out of a thousand visitors.

Before you read on — recall

A team wants conversion rate to be correct at store, region and national level with no special handling. What should the fact table store?

Formulas

Combining a ratio across groups

R_{\text{total}} = \dfrac{\sum_{g} n_g}{\sum_{g} d_g} \quad \ne \quad \frac{1}{G}\sum_{g} \frac{n_g}{d_g}

Add the numerators, add the denominators, then divide once. Store A converts

n=10

d=100

visits and store B converts one of 900. The pooled rate is eleven in a thousand, or 1.1 per cent. The unweighted average of 10 per cent and about 0.11 per cent is roughly 5.1 per cent, nearly five times too high, because it hands the tiny store equal weight. Weighting each store rate by its own visits does reproduce the pooled figure exactly, so it is the unweighted average that fails rather than averaging as such.

Semi-additive measures over time

B_{\text{period}} = B_{t_{\text{end}}} \quad \text{not} \quad \sum_{t} B_t

A balance or an inventory level is a stock, not a flow. Summing daily closing balances across a month gives a figure roughly thirty times too large. Sum a stock across stores and products, and take the closing value, or an average, across time.

Worked examples

Scenario

A dashboard shows average conversion rate by region, computed by averaging the store-level rates inside each region. A regional manager finds it does not match the rate she calculates from the raw totals.

Solution

The dashboard is averaging ratios. A region with one enormous store and nine small ones gives the small stores nine tenths of the weight, which is not how customers actually arrive. The fix is to hold conversions and visits as two additive measures in the fact table and compute the ratio at query time from the sums. The same measure is then correct at store, region and national level with no special handling, which is the general rule for any rate, share or percentage.

Scenario

A finance report sums daily closing cash balances to produce a monthly cash figure, and the number looks implausibly large.

Solution

Cash on hand is a stock measured at a point in time, so thirty daily balances of about two hundred thousand dollars sum to roughly six million dollars of cash the business never held. The monthly figure should be the closing balance on the last day, or an average daily balance if the question is how much cash was typically held. The measure sums perfectly well across branches, which is what makes semi-additive measures easy to get wrong. They behave normally in every direction except time.

Common mistakes

✗A percentage stored in the fact table is just another measure. Percentages cannot be added, and averaging them across rows is correct only when each one is weighted by its own denominator, so storing the numerator and denominator separately and dividing at query time is the design that stays correct at every level without special handling.
✗Anything numeric is a measure. Postcodes, product codes and years are numbers you group by rather than add, so they are dimension attributes despite their type.
✗Averaging group rates without weights is a reasonable approximation of the overall rate. An unweighted average equals the pooled rate only when every group has the same denominator, and the gap widens as the group sizes diverge; weighting each rate by its own denominator recovers the pooled rate exactly.
✗Semi-additive measures are a rare edge case. Balances, headcount, inventory and subscriber counts are all stocks, and they turn up in almost every business area.

Revision bullets

•Measure: a number you aggregate. Dimension: an attribute you slice by
•Additive measures sum across every dimension
•Semi-additive measures (balances, inventory, headcount) do not sum across time
•Ratios are non-additive: store numerator and denominator, divide at query time
•An unweighted average of group rates equals the pooled rate only when denominators are equal

Quick check

A team wants conversion rate to be correct at store, region and national level with no special handling. What should the fact table store?

Which of these can be summed across the days of a month to give a figure in the same unit, meaning the same thing it means on a single day?

Connected topics

Data Warehouse Metadata Star Schema KPI Design Visual Variables Choosing a Chart Dashboards Visual Mistakes

More in Data Foundations

What Big Data Means Forms of Data ETL and ELT Cloud for Analytics Fitness for Use Quality Dimensions Why Data Goes Bad Data Cleansing

Sources

Kimball & Ross (2013)
Kimball, R., & Ross, M. The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling, 3rd ed. Wiley, 2013.
Sets out additive, semi-additive and non-additive facts and the rule that ratios are computed from stored components.
Simpson (1951)
Simpson, E. H. "The Interpretation of Interaction in Contingency Tables." Journal of the Royal Statistical Society, Series B, 13(2), 238-241, 1951.
Shows that a comparison made within groups can reverse when the groups are pooled, which is why a rate must be recomputed from its components rather than averaged.

How to cite this page

Dr. Phil's Quant Lab. (2026). Measures and dimensions. Derivatives Atlas. https://phucnguyenvan.com/concept/ba-measures-dimensions