Chapter 04 · Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts
Additive Measures and Safe Summation Across Dimensions
Classify AtlasMart transaction measures by additivity, extend the Chapter 03 sales fact with atomic cost evidence, and prove which sums remain valid across dimensional slices.
Learning outcomes
AtlasMart can already answer paid-sales questions at order-line grain, but a fact table becomes dangerous when every numeric column is treated as summable. This lesson makes additivity an explicit metric contract: a measure is safe to sum only across dimensions for which repeated rows still represent disjoint pieces of the same quantity.
Define additive, semi-additive, and non-additive behavior in terms of a fact table’s declared grain and dimensions.
Identify transaction measures that can be safely summed across date, customer, product, and store.
Extend the sales fact with additive cost evidence without changing the order-line grain.
Reconcile revenue, cost, gross profit, and units to deterministic Chapter 03 controls.
Reject numeric columns whose arithmetic does not represent a meaningful business accumulation.
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. Additivity is a semantic property
An additive measure can be summed across every
dimension associated with its fact table without changing its
business meaning. AtlasMart’s extended_amount and
quantity are additive at paid order-line grain
because each row contributes a disjoint amount and unit count to
a larger sales population. The rule is about the measurement
event, not the SQL data type: an integer key is numeric but is
not a measure, and a unit price is numeric but summing prices
across unrelated lines is not a meaningful sales metric.
| Column / metric | Role at order-line grain | Safe SUM across date/customer/product/store? |
|---|---|---|
| quantity | Atomic units sold | Yes |
| extended_amount | Atomic line revenue | Yes |
| extended_cost | Atomic line cost introduced below | Yes |
| gross_profit = revenue − cost | Derived from additive components | Yes, when computed per line or from summed components |
| unit_price | Rate/price attached to a line | No — sum of prices is not a business total |
| customer_key / product_key | Dimension foreign key | No — identifier, not a measure |
2. Extend the fact without changing its grain
Margin requires cost evidence. Chapters 01–03 did not claim to have a historical cost source, so Chapter 04 makes the extension explicit instead of silently inventing a column. For this synthetic lab only, AtlasMart assigns a deterministic cost-at-sale to each product. In a production warehouse, the authoritative source and effective-time policy for cost would be a governed requirement.
PRAGMA foreign_keys = ON;DROP TABLE IF EXISTS fact_sales;DROP TABLE IF EXISTS dim_store;DROP TABLE IF EXISTS dim_product;DROP TABLE IF EXISTS dim_customer;DROP TABLE IF EXISTS dim_date;CREATE TABLE dim_date ( date_key INTEGER PRIMARY KEY, full_date TEXT NOT NULL UNIQUE, calendar_year INTEGER NOT NULL, calendar_month INTEGER NOT NULL, day_of_month INTEGER NOT NULL, day_name TEXT NOT NULL);CREATE TABLE dim_customer ( customer_key INTEGER PRIMARY KEY, customer_id TEXT NOT NULL UNIQUE, customer_name TEXT NOT NULL, segment TEXT NOT NULL, region TEXT NOT NULL);CREATE TABLE dim_product ( product_key INTEGER PRIMARY KEY, product_id TEXT NOT NULL UNIQUE, product_name TEXT NOT NULL, category TEXT NOT NULL);CREATE TABLE dim_store ( store_key INTEGER PRIMARY KEY, store_id TEXT NOT NULL UNIQUE, store_name TEXT NOT NULL, store_type TEXT NOT NULL);CREATE TABLE fact_sales ( sales_key INTEGER PRIMARY KEY, date_key INTEGER NOT NULL REFERENCES dim_date(date_key), customer_key INTEGER NOT NULL REFERENCES dim_customer(customer_key), product_key INTEGER NOT NULL REFERENCES dim_product(product_key), store_key INTEGER NOT NULL REFERENCES dim_store(store_key), order_id TEXT NOT NULL, line_no INTEGER NOT NULL, quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price NUMERIC NOT NULL CHECK (unit_price >= 0), extended_amount NUMERIC NOT NULL CHECK (extended_amount >= 0), source_batch_id TEXT NOT NULL, UNIQUE(order_id, line_no));INSERT INTO dim_date VALUES(20260918,'2026-09-18',2026,9,18,'Friday'),(20260919,'2026-09-19',2026,9,19,'Saturday'),(20260920,'2026-09-20',2026,9,20,'Sunday');INSERT INTO dim_customer VALUES(0,'__UNKNOWN__','Unknown customer','Unknown','Unknown'),(101,'C001','Ada Retail','SMB','North'),(102,'C002','Ben Home','Consumer','West'),(103,'C003','Cyra Labs','Enterprise','East'),(104,'C004','Dara Studio','Consumer','South');INSERT INTO dim_product VALUES(0,'__UNKNOWN__','Unknown product','Unknown'),(201,'P100','Keyboard','Accessories'),(202,'P200','Mouse','Accessories'),(203,'P300','Monitor','Displays'),(204,'P400','Dock','Accessories');INSERT INTO dim_store VALUES(0,'__UNKNOWN__','Unknown selling location','Unknown'),(301,'S-WEB','Web Store','Digital'),(302,'S-MOBILE','Mobile Store','Digital'),(303,'S-SALES','Assisted Sales','Assisted');INSERT INTO fact_sales VALUES(1,20260918,101,201,301,'O1001',1,2,50,100,'BATCH-CH03-001'),(2,20260918,101,202,301,'O1001',2,1,25,25,'BATCH-CH03-001'),(3,20260918,102,203,302,'O1002',1,1,200,200,'BATCH-CH03-001'),(4,20260919,101,204,301,'O1003',1,1,100,100,'BATCH-CH03-001'),(5,20260919,101,202,301,'O1003',2,2,25,50,'BATCH-CH03-001'),(6,20260920,104,201,302,'O1005',1,1,50,50,'BATCH-CH03-001'),(7,20260920,104,204,302,'O1005',2,1,100,100,'BATCH-CH03-001');-- 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 | 9
The expected controls are revenue 625, cost
380, gross profit 245, and
units 9. The migration preserves the existing
seven rows and the (order_id, line_no) uniqueness
contract.
3. Prove that dimensional slicing preserves additive totals
SELECT p.category, SUM(f.extended_amount) AS revenue, SUM(f.extended_cost) AS cost, SUM(f.extended_amount - f.extended_cost) AS gross_profit, SUM(f.quantity) AS unitsFROM fact_sales AS fJOIN dim_product AS p ON p.product_key = f.product_keyGROUP BY p.categoryORDER BY p.category;Accessories contribute 425 revenue, 240 cost, 185 profit, and 8 units; Displays contribute 200 revenue, 140 cost, 60 profit, and 1 unit. Adding those category subtotals returns the unsliced controls. This is the defining behavior you want from additive measures.
4. Controlled failure — “it is numeric, so SUM it”
A common modeling error is to expose
unit_price next to additive measures and assume
every BI aggregation should be SUM. On this fixture,
SUM(unit_price) returns 550. That number has no
stable sales interpretation: duplicating a line because of a
join would change it, and a two-unit line still contributes only
one price value.
-- Wrong business metric: arithmetic is legal, semantics are not.SELECT SUM(unit_price) AS meaningless_price_sum FROM fact_sales;-- If the question is revenue, use the additive extended amount.SELECT SUM(extended_amount) AS paid_gmv FROM fact_sales;-- 625-- If the question is quantity-weighted average selling price, retain components.SELECT 1.0 * SUM(extended_amount) / SUM(quantity) AS weighted_unit_priceFROM fact_sales;-- 69.444444...The repair is not “use AVG instead of SUM” by reflex. First name the business question, then derive the metric from atomic components whose grain is known.
5. Hands-on lab — assert additive controls
import sqlite3, sysconn = 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')row = conn.execute("""SELECT COUNT(*), COUNT(DISTINCT order_id),SUM(quantity), SUM(extended_amount), SUM(extended_cost),SUM(extended_amount-extended_cost) FROM fact_sales""").fetchone()print("Python", sys.version.split()[0], "SQLite", sqlite3.sqlite_version)print("rows, orders, units, revenue, cost, profit =", row)assert row == (7, 4, 9, 625, 380, 245)by_category = conn.execute("""SELECT p.category, SUM(f.extended_amount),SUM(f.extended_cost), SUM(f.extended_amount-f.extended_cost), SUM(f.quantity)FROM fact_sales f JOIN dim_product p ON p.product_key=f.product_keyGROUP BY p.category ORDER BY p.category""").fetchall()assert by_category == [('Accessories',425,240,185,8),('Displays',200,140,60,1)]assert sum(r[1] for r in by_category) == 625print("additive reconciliation: PASS")Cleanup/reset: the database is in memory; exiting Python destroys it. If you save it to a file, delete only that disposable lab file.
6. Production judgment and bridge
Publish aggregation behavior alongside metric definitions. Additive measures are flexible because regrouping does not require a special time or denominator rule, but they still depend on correct grain and join cardinality. A one-to-many join can multiply even a perfectly additive measure, so later bridge chapters still require reconciliation controls.
The next lesson uses inventory to show the most common exception: a balance can be additive across products at one snapshot but unsafe to sum across time.
Knowledge check
Check your understanding
- Why is extended_amount additive but unit_price is not?
- What Chapter 04 change was made to support margin?
- What must remain unchanged when adding extended_cost?
- Why is gross profit additive in this fixture?
- Does additivity protect a measure from a row-multiplying join?
Review the answers
1. Each extended amount is a disjoint contribution at the declared order-line grain; unit price is a rate attached to a row, not an accumulated quantity.
2. A documented synthetic cost-at-sale fixture adds extended_cost to each existing order line.
3. The one-row-per-paid-order-line grain, keys, seven-row population, and prior revenue/unit controls remain unchanged.
4. It is the difference of two additive components—revenue and cost—at the same grain, so summed profit equals summed revenue minus summed cost.
5. No. Additivity describes valid aggregation across dimensions when rows are correct; a one-to-many join can still duplicate measurement rows.
Summary and next step
AtlasMart now has an explicit additive core: 625 revenue, 380 cost, 245 gross profit, and 9 units. The arithmetic is useful because every component is tied to the same atomic sales event.
Next: Semi-Additive Balances/Snapshots: Why Time Often Requires Different Aggregation.
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.