Metric Types and Additivity

Concepts: dmMetricAdditivity

Not Every Metric Can Be Pre-Aggregated This is the most important concept in pre-aggregation. Some metrics can be safely SUMmed across any dimension. Others cannot. If you pre-aggregate a non-additive metric, the numbers will be mathematically wrong and nobody will notice until someone reconciles against the source. The Average-of-Averages Trap You pre-compute average order value (AOV) per day: Monday = $50, Tuesday = $100. What is the weekly AOV? NOT ($50 + $100) / 2 = $75. If Monday had 1,000 orders and Tuesday had 10 orders, the true AOV is ($50,000 + $1,000) / 1,010 = $50.50. Averaging the daily averages gives $75, which is 48% wrong. The fix: never store ratios or averages in summary tables. Store the additive components: SUM(revenue) and COUNT(orders). Compute the ratio at query time