Fact Types by Additivity
Fact tables can be classified based on how they behave when aggregated across dimensions:
Additive Facts
Additive facts can be safely summed across all dimensions. They are the easiest facts to aggregate.
For example, sales_amount can be summed across products, customers, stores and time.
| sale_date | product | store | sales_amount |
|---|---|---|---|
| Jan 1 | Laptop | Store A | $1,000 |
| Jan 1 | Phone | Store A | $500 |
| Jan 2 | Laptop | Store B | $1,200 |
| Jan 2 | Phone | Store B | $700 |
The total sales amount can be calculated across any combination of these dimensions.
Semi-Additive Facts
Semi-additive facts can be summed across some dimensions but not others. This commonly occurs with measurements that represent a point-in-time state, where summing across time would produce a meaningless result.
For example, an account balance can be summed across customers on a given day, but adding account balances across multiple days is not meaningful.
| snapshot_date | customer | account_balance |
|---|---|---|
| Jan 1 | Alice | $1,000 |
| Jan 1 | Bob | $2,000 |
| Jan 2 | Alice | $1,500 |
| Jan 2 | Bob | $2,500 |
On Jan 1, the total balance is $3,000. However, summing Jan 1 and Jan 2 balances to get $7,000 would be meaningless.
Non-Additive Facts
Non-additive facts cannot be meaningfully summed across dimensions. When aggregated, they usually must be recalculated from their underlying additive or semi-additive components.
A good approach is to always store the additive building blocks of these non-additive facts in your database. When summarizing data, you add those basic components together first and use the combined totals to calculate the final non-additive fact.
Common examples include percentages, ratios and unit prices. Consider profit margin across two stores:
| store | revenue | profit | profit_margin |
|---|---|---|---|
| North | $10,000 | $2,000 | 20% |
| South | $1,000 | $500 | 50% |
Summing the margins gives 70%, which is meaningless. The correct total margin comes from summing the additive components first: $2,500 profit ÷ $11,000 revenue = 22.7%.
Derived Measures (e.g., Year-to-Date)
Business users often request derived or cumulative values like year-to-date (YTD) directly in a fact table. It is hard to argue against a single request, but YTD requests can easily morph into "YTD at the close of the fiscal period" or "fiscal period to date".
A more reliable, extensible way to handle these assorted requests is to calculate the YTD metrics dynamically in the BI applications or OLAP cube rather than storing them as physical facts.
| date_key | product_key | daily_sales_amount |
|---|---|---|
| Jan 1 | Laptop | $1,000 |
| Jan 2 | Laptop | $1,500 |
TIP
Rely on your BI tool's analytical functions (e.g., window functions in SQL, or time intelligence in DAX) to compute the YTD rollups based on atomic additive facts like daily_sales_amount.