Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Additive, semi-additive and non-additive measures

Data modeling · Fact Table Design

Additive, semi-additive and non-additive measures

Mediumdata-modeling-37
measuresadditive-factssemi-additivebi-reporting

Question

What are additive, semi-additive and non-additive facts? Give examples.

Solution

Measures in a fact table fall into three categories based on whether they can be meaningfully summed across dimensions. Additive facts sum across all dimensions, semi-additive facts sum across some dimensions but not time, and non-additive facts cannot be summed across any dimension.

The three measure types

Dimensional modeling classifies numeric fields by their aggregation behavior:

  • Additive facts: Sales revenue and quantity sold in fact_sales. You can sum revenue across products, stores, customer demographics, and calendar quarters. The resulting total is always mathematically valid.
  • Semi-additive facts: Account balance in fact_daily_balances or inventory count in fact_inventory_snapshot. You can sum account balances across all customers on December 31 to find total branch deposits. However, summing balances across 365 days yields a meaningless number. For the time dimension, reports must take the ending balance or calculate an average.
  • Non-additive facts: Unit price, discount percentages, and gross margin ratios. Summing percentage values across rows generates corrupted numbers that distort business analysis.

Handling non-additive metrics in BI

Business intelligence tools push aggregations down to the warehouse using SQL. If an analyst defines a dashboard card with SUM(profit_margin_pct), the calculated result is completely invalid. To prevent this, always store the atomic additive components in the fact table instead:

Store in fact table:
profit_amount     (additive)
revenue_amount    (additive)

Calculate in semantic layer:
gross_margin = SUM(profit_amount) / SUM(revenue_amount)

A simple explanation of semantic calculations:

  • The BI tool or semantic layer sums the numerators and denominators independently across whatever slice the user selects.
  • It then computes the ratio on the aggregated numbers, guaranteeing mathematical accuracy at any grain.

Design takeaway

Always prefer storing additive building blocks in base fact tables whenever possible. When semi-additive facts are necessary, document the time aggregation rule explicitly so downstream dashboard queries do not accidentally sum across dates.

PreviousNext