Data Modeling

A measure is not simply additive or nonadditive in the abstract. The useful statement is dimension-specific: this measure may be summed across product and store, but not across date; or this rate must be recomputed from its numerator and denominator after filtering. That sentence is what keeps fact tables, semantic layers, and dashboards from silently producing plausible but wrong totals.

Why summability must name the dimensions

Practitioners often label a field as additive, semi-additive, or nonadditive and stop there. That is not enough. The operational question is more precise: which dimensions permit summation for this measure at this grain?

A measure can behave differently depending on the dimension being rolled up. Sales amount is usually safe to sum across date, product, store, promotion, and customer. End-of-day inventory units are usually safe to sum across product and store for one day, but not across multiple dates. Gross margin rate is not safe to sum across product, store, or date; it should be recalculated from gross margin amount divided by net sales amount after the filters are applied.

This is why measure definitions should include a summability statement. The statement belongs close to the fact table design, not only in a dashboard note after confusion appears.

Practical rule

Do not ask only whether a measure is additive. Ask which dimensions permit summation and which require a different rollup rule.

The three classes in plain English

The classic dimensional modeling distinction is simple, but it is easy to misuse if the fact table grain is not clear.

  • Additive measures can be summed across all dimensions that apply to the fact table. Examples often include order quantity, sales amount, cost amount, and discount amount.
  • Semi-additive measures can be summed across some dimensions, but not all. Balance and snapshot measures are common examples. Inventory on hand can be summed across products for one date, but summing the same product over seven dates usually creates a meaningless total unless the intended metric is unit-days.
  • Nonadditive measures cannot be summed usefully across dimensions. Ratios, percentages, averages, and distinct counts often fall here. The usual repair is to store or expose additive components, then recompute the final measure at query time.

The classification depends on both the business meaning and the declared grain. A field called customer_count might be additive if each row represents mutually exclusive customers in a period, but nonadditive if the same customer can appear in many product, channel, or date groups.

Hypothetical dimensional schema

Consider a retailer with two fact tables and shared conformed dimensions. This is a hypothetical example used to make the summability rules concrete.

  • dim_date: one row per calendar date.
  • dim_product: one row per sellable product.
  • dim_store: one row per physical store.
  • dim_promotion: one row per promotion, where applicable.
  • fact_sales_day: grain is one row per date, product, store, and promotion combination with sales activity.
  • fact_inventory_snapshot_day: grain is one row per date, product, and store at the end of the day.

The sales fact and the inventory snapshot fact share date, product, and store, but they do not mean the same thing. Sales rows record activity during a day. Inventory snapshot rows record a state at a point in time. That difference is what drives the summability rules.

Worked example: reconciling sales, inventory, and margin

Assume product P1 at store A has the following two days of activity.

  • January 1: units sold 10, net sales amount 100, gross margin amount 25, end-of-day inventory units 90.
  • January 2: units sold 5, net sales amount 60, gross margin amount 12, end-of-day inventory units 85.

For the two-day period, units sold are 15 and net sales amount is 160. Those are additive activity measures. Gross margin amount is 37, also additive. Gross margin rate is not 25 plus 20, and it is not necessarily the unweighted average of the two daily rates. It should be recomputed as 37 divided by 160, which is 23.125 percent.

Inventory is different. The end-of-day inventory values 90 and 85 should not be summed to say the store had 175 units on hand. If the business asks for ending inventory for the period, the answer is 85. If it asks for average daily inventory, the answer is 87.5 using this simple two-day example. If it asks for unit-days, then 175 may be meaningful, but that is a different measure and should be named accordingly.

Warning

A number can be mathematically summed and still be analytically wrong. Snapshot values and rates often produce totals that look precise but do not represent the business question.

How to write a summability statement

A useful measure definition should state the permitted rollups explicitly. Use a short sentence that names the measure, the fact grain, the dimensions that allow summation, the dimensions that do not, and the correct alternative.

A practical template is:

At grain X, measure Y may be summed across dimensions A, B, and C. It must not be summed across dimension D. For D, use rule R.

For example:

  • Net sales amount: at the daily product-store-promotion sales grain, net sales amount may be summed across date, product, store, and promotion.
  • End-of-day inventory units: at the daily product-store inventory snapshot grain, inventory units may be summed across product and store for a single date. They must not be summed across multiple dates. Across date, use ending balance, average balance, minimum, maximum, or another named period rule.
  • Gross margin rate: do not sum margin rate across date, product, store, or promotion. Recompute it as gross margin amount divided by net sales amount after applying the desired filters.
Modeling checkpoint

If a measure definition cannot say how it behaves across date, product, customer, geography, and other core dimensions, it is not ready for broad self-service use.

Measure-by-measure rules for the example

The table below states the summability contract for the hypothetical model. The important habit is not the exact wording. The important habit is that every measure gets an explicit answer for every relevant dimension.

When a dimension does not apply to a fact table, mark it as not applicable instead of pretending the measure rolls up through it. For example, the inventory snapshot fact in this example has no promotion dimension, so promotion is not part of its summability contract.

Measure Fact grain Date Product Store Promotion Correct rollup rule
Units sold Date-product-store-promotion sales activity Yes Yes Yes Yes Sum units sold across all applicable dimensions.
Net sales amount Date-product-store-promotion sales activity Yes Yes Yes Yes Sum net sales amount across all applicable dimensions.
Gross margin amount Date-product-store-promotion sales activity Yes Yes Yes Yes Sum gross margin amount across all applicable dimensions.
Gross margin rate Derived from sales components No No No No Recompute as gross margin amount divided by net sales amount after filtering.
End-of-day inventory units Date-product-store end-of-day snapshot No across multiple dates; yes within one selected date Yes Yes Not applicable Across date, use a named rule such as ending balance, average daily balance, minimum, or maximum.
End-of-day inventory value Date-product-store end-of-day snapshot No across multiple dates; yes within one selected date Yes Yes Not applicable Across date, use a named rule such as ending value or average daily value.
Average daily inventory units Period-product-store derived measure Depends on the defined period Yes if periods are aligned Yes if periods are aligned Not applicable Compute from daily inventory units over the selected period; do not sum precomputed averages across unequal groups.

Common failure modes

Most summability errors are not caused by people misunderstanding arithmetic. They are caused by models that hide the grain or dashboards that let users combine measures without revealing the rollup rule.

  • Summing snapshots over time: inventory, account balance, headcount, backlog, open tickets, and pipeline value are common examples. Summing daily values usually inflates the apparent amount.
  • Averaging averages: average order value, average handle time, and average price need their underlying numerator and denominator unless each group has equal weight and that weighting is intended.
  • Summing percentages: conversion rate, margin rate, cancellation rate, utilization rate, and fill rate should usually be recomputed from components.
  • Counting distinct entities across overlapping groups: distinct customers by product cannot usually be summed to get distinct customers overall because one customer can buy multiple products.
  • Mixing transaction facts and periodic snapshots: activity during a period and state at the end of a period answer different business questions.

Decision checklist before publishing a measure

Before a measure is published into a semantic layer, BI model, spreadsheet export, or metric catalog, answer these questions.

  1. What is the grain of the fact table? If the grain is unclear, summability cannot be evaluated reliably.
  2. Is the measure an event amount, a state, or a derived calculation? Event amounts are often additive. States are often semi-additive. Derived ratios are often nonadditive.
  3. Can the same real-world thing appear in multiple groups? If yes, counts and distinct counts may not be additive across those groups.
  4. Which dimensions are valid rollup paths? Name date, product, store, customer, account, region, channel, promotion, or any other relevant dimension explicitly.
  5. What should happen across time? Sum, ending balance, beginning balance, average, minimum, maximum, last non-null value, and period-to-date are different rules.
  6. Are additive components available? For a ratio, store or expose the numerator and denominator so the ratio can be recomputed at the requested level.
  7. What should the dashboard do by default? Default aggregation should match the measure type. Do not let a BI tool guess that a rate should be summed.

Operator rules for durable metric definitions

A few rules make additive, semi-additive, and nonadditive measures much easier to govern.

  • Classify measures after declaring the grain. The same field name can behave differently at a different grain.
  • Prefer additive base measures. Store amounts, counts, numerators, and denominators where possible. Derive ratios at query time.
  • Name period rules directly. Use names like ending inventory, average daily inventory, and unit-days rather than one vague field called inventory.
  • Do not rely on dashboard formatting to fix model ambiguity. A percent sign does not prevent a field from being summed incorrectly.
  • Document non-summable dimensions in the metric contract. The absence of a rule is usually interpreted as permission to aggregate.

The goal is not to make every metric complicated. The goal is to remove silent ambiguity before the measure is reused by people who were not in the original modeling discussion.

Key takeaways

  • Additive, semi-additive, and nonadditive measures should be defined by dimension, not only by label.
  • A measure is additive when it can be summed across all relevant dimensions at its declared grain.
  • Semi-additive measures, such as inventory or balances, usually allow summation across some dimensions but require special handling across time.
  • Nonadditive measures, such as rates and many averages, should usually be recomputed from additive components.
  • A clear summability statement prevents dashboards and semantic layers from turning valid row-level data into invalid totals.

Next step

Pick one important fact table and list its ten most-used measures. For each measure, write one summability statement that names the fact grain, the dimensions where summation is allowed, the dimensions where it is not allowed, and the correct alternative rollup rule.

Recommended next reads