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.

This distinction sits at the exact intersection of warehouse design and RPD/BI Server configuration, which is why interviewers like it as a bridging question between the ETL and RPD topic areas — it's also a very common source of real production bugs when overlooked.

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 measures can be safely summed across every dimension in a fact table — revenue, units sold, and cost are the classic examples, since "total revenue across all regions and all months" is simply the sum of every individual row's revenue, no matter how you slice it. This is the easy, default case and most fact table measures fall here.

Additive measures

Semi-additive measures can be summed across *most* dimensions but not across time — account balances and inventory snapshot levels are the textbook examples: summing a bank account's balance across all twelve months of the year produces a meaningless number (you don't have thirteen months' worth of money), but summing that same balance across all customers *at a single point in time* is perfectly valid. These require special aggregation rules — typically LAST (ending balance), AVG (average balance), FIRST, or MAX/MIN over the time dimension specifically, while still allowing normal SUM across every other dimension.

Semi-additive measuresaccount balancesinventory snapshot levelsover the time dimension specificallyLAST

Non-additive measures cannot be meaningfully summed across *any* dimension — ratios, percentages, and unit prices are the common examples, since averaging a set of already-averaged percentages (an "average of averages") produces a statistically meaningless result; these must instead be recalculated from their underlying additive components at whatever grain is being reported (e.g., recompute SUM(revenue) / SUM(units) rather than AVG(unit_price)).

Non-additive measuresSUM(revenue) / SUM(units)AVG(unit_price)

The RPD-side consequence, tying directly back to the RPD topics: this is exactly why a logical column's default aggregation rule in the Business Model layer matters so much — setting a semi-additive balance column's default aggregation to SUM (instead of the correct time-aware rule) produces technically-valid-looking but business-nonsensical numbers the moment someone builds a report that spans multiple time periods, and this class of bug is notoriously easy to miss in testing if nobody happens to build a multi-period report during QA.

default aggregation ruleSUM