Fact Table Design: Additive, Semi-Additive & Non-Additive Measures

Explain the difference between additive, semi-additive, and non-additive measures and why this distinction determines correct aggregation logic in a warehouse and RPD.

Whether a measure can be summed depends on which dimension you are summing across. Getting this wrong is a common production bug.

Revenue is like rainfall — total rainfall over a year is just the sum of every day's rainfall, additive in every direction. A bank balance is like a fuel gauge reading — summing your car's fuel gauge readings across every day of the month gives a meaningless number, but the average or ending reading is meaningful. A unit price is like a speed limit sign — averaging the speed limits of every road you drove on tells you nothing useful about your actual average driving speed, which you'd instead compute from total distance divided by total time.

Key Concepts

Additive: sum across everything.

    revenue, quantity, cost

Total revenue across all regions and all months is the sum of the rows. Most measures are this, and it is the default assumption.

Semi-additive: sum across some dimensions, not time.

    account balance, inventory on hand, headcount

    balance by branch, on 31 March:  sum the branches   -> correct
    balance across January to March: sum the months     -> wrong

Adding January's balance to February's gives a number that means nothing. The correct aggregation over time is the last value, or sometimes the average.

That is what the RPD's aggregation rule is for.

    SUM over every dimension
    LAST over Time

The BI Server then sums across branches and takes the closing value across months, from one measure definition.

Without it, a balance sheet sums balances over time and produces a figure three times too large — and it looks plausible, which is why it survives.

Non-additive: cannot be summed at all.

    ratios, percentages, unit prices, margin %

    margin % by region: 20%, 30%, 25%
    summed:   75%       -> meaningless
    averaged: 25%       -> also wrong, unless the regions are equal

The correct handling is to store the components, not the ratio:

    store   revenue and cost
    compute margin % = SUM(revenue - cost) / SUM(revenue)

The calculation happens after aggregation, at whatever level is displayed, and it is right at every level.

Storing the percentage in the fact table and summing or averaging it is the defect, and it is very common because the percentage is what the business asked for.

Which gives the design rule. Fact tables hold additive components. Ratios are calculated in the model, not stored — so they can be recomputed at every grain rather than aggregated incorrectly.

What the interviewer is probing.1. "Give an example of each type." Probing: the fundamentals. Stalls: "Numbers." Moves up: sales amount is additive, an account balance is semi-additive (not over time), and a ratio or percentage is non-additive.

2. "How do you handle a semi-additive measure?" Probing: the implementation. Stalls: "Sum it." Moves up: a different rule over the time dimension — last or average — declared in the model so every tool aggregates it the same way.

3. "Why should a ratio not be stored as a measure?" Probing: the insight. Stalls: "It should." Moves up: summing or averaging ratios gives the wrong answer — store numerator and denominator and compute the ratio at query time.

4. "What is a factless fact table?" Probing: the breadth. Stalls: "An empty table." *Moves up:* one recording that an event or relationship occurred with no measure, which is how you count attendance or coverage.