Chapter 04 · Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts
Ratios, Percentages, Averages, Distinct Counts, and Other Non-Additive Metrics
Recompute AtlasMart ratios, percentages, averages, and distinct counts from atomic components instead of summing percentages or averaging pre-aggregated averages.
Learning outcomes
Ratios and distinct counts are often the metrics executives care about most, but they are also easy to corrupt during rollups. AtlasMart will retain numerators, denominators, and entity identity so every derived metric can be recomputed at the requested slice.
Recompute percentages from summed numerators and denominators instead of summing percentages.
Explain why average-of-averages is generally unsafe unless group weights are equal and intentional.
Compute distinct counts at the requested scope rather than summing lower-level distinct counts.
Reconcile gross margin, average order value, conversion rate, and unique customer count to atomic evidence.
Document denominator zero/null behavior and metric ownership.
The Chapter 03 sales fact remains one row per paid order line: 7 rows, 4 paid orders, 9 units, and 625 paid GMV. Chapter 04 does not change that grain. It adds explicitly documented measurement fixtures only where this chapter needs them: synthetic cost-at-sale evidence, a two-day inventory periodic snapshot, channel-session denominators, and promotion-eligibility coverage. Every new structure is reconciled back to the established source/control values before it is used.
The mandatory labs use Python's standard-library
sqlite3 module and synthetic local data. They are
semantic correctness labs, not performance benchmarks. Record
the actual Python and SQLite versions from your machine; query
speed, optimizer behavior, decimal representation, and
physical storage behavior are engine-specific.
1. Non-additive does not mean “cannot aggregate”
A non-additive metric cannot be meaningfully
summed across its dimensions. The safe pattern is usually to
retain additive components and calculate the ratio at query time
or in a governed semantic layer. AtlasMart’s gross margin
percentage is SUM(gross_profit) / SUM(revenue);
conversion rate is
SUM(paid_orders) / SUM(sessions). The percentage
itself is not an additive fact.
| Metric | Atomic components | Safe rollup |
|---|---|---|
| Gross margin % | gross profit, revenue | SUM profit / SUM revenue |
| Average paid order value | paid GMV, distinct paid orders | SUM revenue / COUNT DISTINCT order_id |
| Conversion rate | paid orders, sessions | SUM paid_orders / SUM sessions |
| Unique paid customers | customer identity at paid-order activity | COUNT DISTINCT customer_key at requested scope |
2. Gross margin: average the components, not the percentages
-- Chapter 04 migration: preserve the Chapter 03 order-line grain.ALTER TABLE fact_sales ADD COLUMN extended_cost NUMERIC;UPDATE fact_salesSET extended_cost = CASE product_key WHEN 201 THEN quantity * 30 -- P100 Keyboard synthetic cost-at-sale WHEN 202 THEN quantity * 10 -- P200 Mouse WHEN 203 THEN quantity * 140 -- P300 Monitor WHEN 204 THEN quantity * 60 -- P400 Dock ELSE 0END;SELECT SUM(extended_amount) AS revenue, SUM(extended_cost) AS cost, SUM(extended_amount - extended_cost) AS gross_profit, SUM(quantity) AS unitsFROM fact_sales;-- expected: 625 | 380 | 245 | 9SELECT 100.0 * AVG((extended_amount-extended_cost) / extended_amount) AS wrong_unweighted_line_margin_pct, 100.0 * SUM(extended_amount-extended_cost) / SUM(extended_amount) AS correct_margin_pctFROM fact_sales;-- wrong ≈ 44.2857%; correct = 39.2%
The line percentages have different revenue weights, so their
arithmetic average overweights small lines. Storing only
39.2% would also lose the ability to recompute
margin for a product, store, or new filter. Preserve revenue and
cost/profit components.
3. Average order value: average-of-daily-averages fails
WITH daily AS ( SELECT date_key, SUM(extended_amount) AS revenue, COUNT(DISTINCT order_id) AS orders, 1.0 * SUM(extended_amount) / COUNT(DISTINCT order_id) AS daily_aov FROM fact_sales GROUP BY date_key)SELECT AVG(daily_aov) AS wrong_equal_weight_daily_average, 1.0 * SUM(revenue) / SUM(orders) AS correct_overall_aovFROM daily;-- wrong ≈ 154.1667; correct = 156.25The daily averages represent groups of different sizes: September 18 has two paid orders while the other dates have one each. Overall AOV is a weighted ratio of total revenue to total distinct orders.
4. Conversion rate: numerator and denominator lineage
Sessions did not exist in the earlier warehouse contract, so the
lab adds a clearly labeled synthetic denominator fixture.
Paid-order counts in this table reconcile to the four paid
orders already present in fact_sales.
DROP TABLE IF EXISTS fact_channel_funnel_daily;CREATE TABLE fact_channel_funnel_daily ( date_key INTEGER NOT NULL REFERENCES dim_date(date_key), store_key INTEGER NOT NULL REFERENCES dim_store(store_key), sessions INTEGER NOT NULL CHECK (sessions >= 0), paid_orders INTEGER NOT NULL CHECK (paid_orders >= 0), PRIMARY KEY (date_key, store_key));-- Synthetic demand-denominator fixture; paid-order numerators reconcile to fact_sales.INSERT INTO fact_channel_funnel_daily VALUES(20260918,301,60,1),(20260918,302,30,1),(20260919,301,40,1),(20260919,303,20,0),(20260920,302,20,1);SELECT SUM(paid_orders) AS paid_orders, SUM(sessions) AS sessions, 100.0 * SUM(paid_orders) / NULLIF(SUM(sessions),0) AS conversion_pctFROM fact_channel_funnel_daily;-- 4 | 170 | 2.352941...%The denominator contract must define what counts as a session, bot filtering, time zone, attribution window, and zero-denominator behavior. SQL alone cannot settle those business definitions.
5. Distinct counts are set operations, not additive counts
SELECT date_key, COUNT(DISTINCT customer_key) AS daily_customersFROM fact_sales GROUP BY date_key ORDER BY date_key;-- 2, 1, 1: sum = 4SELECT COUNT(DISTINCT customer_key) AS global_paid_customersFROM fact_sales;-- 3Ada Retail appears on both September 18 and September 19. Summing daily distinct counts counts the same entity twice. For exact global distincts, evaluate the distinct set at the requested scope; approximate distinct algorithms are a separate physical-performance choice and must document their error contract.
6. Hands-on lab — prove four non-additive metrics
import sqlite3, mathconn=sqlite3.connect(":memory:")conn.executescript("PRAGMA foreign_keys = ON;\nDROP TABLE IF EXISTS fact_sales;\nDROP TABLE IF EXISTS dim_store;\nDROP TABLE IF EXISTS dim_product;\nDROP TABLE IF EXISTS dim_customer;\nDROP TABLE IF EXISTS dim_date;\nCREATE TABLE dim_date (\n date_key INTEGER PRIMARY KEY,\n full_date TEXT NOT NULL UNIQUE,\n calendar_year INTEGER NOT NULL,\n calendar_month INTEGER NOT NULL,\n day_of_month INTEGER NOT NULL,\n day_name TEXT NOT NULL\n);\nCREATE TABLE dim_customer (\n customer_key INTEGER PRIMARY KEY,\n customer_id TEXT NOT NULL UNIQUE,\n customer_name TEXT NOT NULL,\n segment TEXT NOT NULL,\n region TEXT NOT NULL\n);\nCREATE TABLE dim_product (\n product_key INTEGER PRIMARY KEY,\n product_id TEXT NOT NULL UNIQUE,\n product_name TEXT NOT NULL,\n category TEXT NOT NULL\n);\nCREATE TABLE dim_store (\n store_key INTEGER PRIMARY KEY,\n store_id TEXT NOT NULL UNIQUE,\n store_name TEXT NOT NULL,\n store_type TEXT NOT NULL\n);\nCREATE TABLE fact_sales (\n sales_key INTEGER PRIMARY KEY,\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key),\n product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n order_id TEXT NOT NULL,\n line_no INTEGER NOT NULL,\n quantity INTEGER NOT NULL CHECK (quantity > 0),\n unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0),\n source_batch_id TEXT NOT NULL,\n UNIQUE(order_id, line_no)\n);\nINSERT INTO dim_date VALUES\n(20260918,'2026-09-18',2026,9,18,'Friday'),\n(20260919,'2026-09-19',2026,9,19,'Saturday'),\n(20260920,'2026-09-20',2026,9,20,'Sunday');\nINSERT INTO dim_customer VALUES\n(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),\n(101,'C001','Ada Retail','SMB','North'),\n(102,'C002','Ben Home','Consumer','West'),\n(103,'C003','Cyra Labs','Enterprise','East'),\n(104,'C004','Dara Studio','Consumer','South');\nINSERT INTO dim_product VALUES\n(0,'__UNKNOWN__','Unknown product','Unknown'),\n(201,'P100','Keyboard','Accessories'),\n(202,'P200','Mouse','Accessories'),\n(203,'P300','Monitor','Displays'),\n(204,'P400','Dock','Accessories');\nINSERT INTO dim_store VALUES\n(0,'__UNKNOWN__','Unknown selling location','Unknown'),\n(301,'S-WEB','Web Store','Digital'),\n(302,'S-MOBILE','Mobile Store','Digital'),\n(303,'S-SALES','Assisted Sales','Assisted');\nINSERT INTO fact_sales VALUES\n(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),\n(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),\n(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),\n(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),\n(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),\n(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),\n(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');")conn.executescript('-- Chapter 04 migration: preserve the Chapter 03 order-line grain.\nALTER TABLE fact_sales ADD COLUMN extended_cost NUMERIC;\nUPDATE fact_sales\nSET extended_cost = CASE product_key\n WHEN 201 THEN quantity * 30 -- P100 Keyboard synthetic cost-at-sale\n WHEN 202 THEN quantity * 10 -- P200 Mouse\n WHEN 203 THEN quantity * 140 -- P300 Monitor\n WHEN 204 THEN quantity * 60 -- P400 Dock\n ELSE 0\nEND;\n\n')conn.executescript('DROP TABLE IF EXISTS fact_channel_funnel_daily;\nCREATE TABLE fact_channel_funnel_daily (\n date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n sessions INTEGER NOT NULL CHECK (sessions >= 0),\n paid_orders INTEGER NOT NULL CHECK (paid_orders >= 0),\n PRIMARY KEY (date_key, store_key)\n);\n-- Synthetic demand-denominator fixture; paid-order numerators reconcile to fact_sales.\nINSERT INTO fact_channel_funnel_daily VALUES\n(20260918,301,60,1),\n(20260918,302,30,1),\n(20260919,301,40,1),\n(20260919,303,20,0),\n(20260920,302,20,1);')revenue,cost=conn.execute("SELECT SUM(extended_amount),SUM(extended_cost) FROM fact_sales").fetchone()profit=revenue-costassert (revenue,cost,profit)==(625,380,245)margin=profit/revenueassert math.isclose(margin,0.392)aov=revenue/conn.execute("SELECT COUNT(DISTINCT order_id) FROM fact_sales").fetchone()[0]assert aov==156.25orders,sessions=conn.execute("SELECT SUM(paid_orders),SUM(sessions) FROM fact_channel_funnel_daily").fetchone()assert (orders,sessions)==(4,170)conversion=orders/sessionsassert math.isclose(conversion,4/170)global_customers=conn.execute("SELECT COUNT(DISTINCT customer_key) FROM fact_sales").fetchone()[0]daily_sum=sum(r[0] for r in conn.execute("SELECT COUNT(DISTINCT customer_key) FROM fact_sales GROUP BY date_key"))assert global_customers==3 and daily_sum==4print('margin=',margin,'aov=',aov,'conversion=',conversion)print('global distinct=',global_customers,'sum daily distinct=',daily_sum)print('non-additive assertions: PASS')7. Production judgment and bridge
A governed metric should expose enough lineage to recompute itself after filtering. Persisting a percentage without its numerator and denominator creates an opaque number that cannot be safely rolled up. Likewise, a cached distinct count is scoped to the dimensions and filters under which it was computed.
The next lesson covers a different case: sometimes the measurement is the occurrence or coverage relationship itself and there is no natural numeric fact at all.
Knowledge check
Check your understanding
- Why is gross margin percentage non-additive?
- Why does averaging daily AOVs produce the wrong overall AOV here?
- What is the correct global conversion rate in the fixture?
- Why does the sum of daily distinct customers equal 4 while the global distinct is 3?
- What should a metric contract say about a zero denominator?
Review the answers
1. The percentage is a ratio; summing or unweighted averaging ratios changes weighting. Recompute from summed profit and revenue.
2. The daily groups have unequal order counts. The overall ratio must weight each day by its denominator.
3. 4 paid orders divided by 170 sessions, approximately 2.3529%.
4. C001 appears on two dates, so lower-level distinct sets overlap.
5. It must define whether the result is NULL, zero, suppressed, or another governed state; do not rely on accidental engine behavior.
Summary and next step
AtlasMart can now recompute margin (39.2%), AOV (156.25), conversion (4/170), and global unique paid customers (3) from atomic components rather than aggregating already-derived numbers.
Next: Factless Fact Tables for Coverage, Eligibility, Attendance, Conditions, and Event Occurrence.
Authoritative references
- Kimball Group — Additive, Semi-Additive, and Non-Additive Facts — Definitions and the guidance to retain additive components for derived non-additive facts.
- Kimball Group — Fact Table Structure — Fact-table grain and measurement-event framing.
- SQLite — Aggregate Functions — Exact behavior of SUM, AVG, COUNT, and DISTINCT in the local execution harness.
- Python sqlite3 documentation — Standard-library API used by the deterministic labs.