Facts by Additivity
Fact measures 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%.
Best Practice: Derived Measures
Business users often request derived or cumulative measures like year-to-date (YTD) directly in a fact table. While this seems convenient, these requests can quickly become too complex to manage. For example, a simple YTD request might expand to include "YTD at the close of the fiscal period", "fiscal period to date" etc.
NOTE
Fiscal means related to financial matters. A fiscal year is the custom 12-month period a company uses for accounting and financial reporting, rather than the standard January to December calendar.
A more reliable way to handle these requests is to store only the basic additive facts in your database. You can then calculate metrics like YTD dynamically in your reporting applications.
TIP
Use SQL window functions or BI features such as DAX time-intelligence functions to calculate measures. Persist calculated measures only when a specific performance or reporting requirement justifies it.