Chapter 04 · Fact Table Design: Additive, Semi-Additive, Non-Additive Measures, and Factless Facts

Factless Fact Tables for Coverage, Eligibility, Attendance, Conditions, and Event Occurrence

Use a factless promotion-eligibility table to model coverage and answer what was eligible but did not sell without inventing a numeric fact.

Intermediate → Advanced105–125 minutesFactless coverage labPython stdlib sqlite3 · local/syntheticLast reviewed: September 2026

Learning outcomes

Not every useful business event produces a dollar amount or quantity. AtlasMart needs to know which product/store/date combinations were eligible for a promotion—even when no sale occurred. The relationship itself is the measurable event or coverage state.

Define event and coverage factless fact tables and their grain.

Model promotion eligibility without inventing a meaningless numeric measure.

Count factless rows safely when the row grain is unique and understood.

Combine coverage with sales activity to answer “what did not happen?” questions.

Avoid inner-join logic that erases eligible-but-unsold combinations.

Chapter 04 continuity contract

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.

Execution scope

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. A fact table can be useful with no numeric fact

A factless fact table records the intersection of dimensions for an event or coverage condition. In an attendance system, a row may mean “this student attended this class on this date.” In AtlasMart, a row can mean “this promotion covered this product at this selling location on this date.” The row is meaningful because its grain is explicit; no fake amount=0 is required.

Pattern	Row meaning	Typical question
Event factless fact	An event occurred at the intersection of dimensions	Which customers received a campaign contact?
Coverage factless fact	A combination was eligible/possible/covered	Which eligible product-store-day combinations had no sale?
Numeric transaction fact	A measurement event produced quantities/amounts	How much revenue or how many units sold?

2. Build promotion eligibility coverage

The grain is one row per promotion × product × store × date that is eligible. The primary key enforces that one coverage statement cannot be duplicated within the same promotion definition.

promotion_coverage.sql

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');DROP TABLE IF EXISTS fact_promotion_eligibility;DROP TABLE IF EXISTS dim_promotion;CREATE TABLE dim_promotion (  promotion_key INTEGER PRIMARY KEY,  promotion_id TEXT NOT NULL UNIQUE,  promotion_name TEXT NOT NULL);CREATE TABLE fact_promotion_eligibility (  date_key INTEGER NOT NULL REFERENCES dim_date(date_key),  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),  store_key INTEGER NOT NULL REFERENCES dim_store(store_key),  promotion_key INTEGER NOT NULL REFERENCES dim_promotion(promotion_key),  source_batch_id TEXT NOT NULL,  PRIMARY KEY (date_key, product_key, store_key, promotion_key));INSERT INTO dim_promotion VALUES (401,'PROMO-ACCESSORY','Accessory Boost');INSERT INTO fact_promotion_eligibility VALUES(20260918,201,301,401,'PROMO-COVERAGE-001'),(20260918,202,301,401,'PROMO-COVERAGE-001'),(20260918,201,302,401,'PROMO-COVERAGE-001'),(20260919,204,301,401,'PROMO-COVERAGE-001'),(20260919,202,301,401,'PROMO-COVERAGE-001'),(20260920,201,302,401,'PROMO-COVERAGE-001'),(20260920,202,302,401,'PROMO-COVERAGE-001'),(20260920,204,302,401,'PROMO-COVERAGE-001');

There are eight eligible combinations. Six have matching paid-sales activity in the Chapter 03 sales fact and two do not.

3. Controlled failure — inner join answers only “what happened”

SQL · wrong question shape

SELECT COUNT(*) AS eligible_combinations_that_soldFROM fact_promotion_eligibility eJOIN fact_sales s  ON s.date_key=e.date_key AND s.product_key=e.product_key AND s.store_key=e.store_key;-- 6-- If you start here, the two eligible combinations with no sale are invisible.

An inner join cannot return the missing side of a relationship. It is not “wrong SQL”; it answers a different question. To find eligible combinations that did not sell, preserve coverage rows and anti-join against activity.

SQL · coverage minus activity

SELECT d.full_date, p.product_name, st.store_nameFROM fact_promotion_eligibility eJOIN dim_date d ON d.date_key=e.date_keyJOIN dim_product p ON p.product_key=e.product_keyJOIN dim_store st ON st.store_key=e.store_keyLEFT JOIN fact_sales s  ON s.date_key=e.date_key AND s.product_key=e.product_key AND s.store_key=e.store_keyWHERE s.sales_key IS NULLORDER BY d.full_date,p.product_name,st.store_name;-- 2026-09-18 | Keyboard | Mobile Store-- 2026-09-20 | Mouse    | Mobile Store

4. Counting rows can be a derived measure

A factless table has no stored numeric fact, but COUNT(*) is often a useful derived measure because each row represents one unique event or coverage statement. That count is trustworthy only if the grain is enforced. Duplicating a coverage row would inflate eligibility counts, so the composite primary key is part of the metric contract.

Do not add an eligible_count=1 column merely to make the table “look like a fact table.” It repeats information already encoded by row existence and can invite inconsistent values.

5. Hands-on lab — prove coverage and non-occurrence

verify_factless.py

import sqlite3conn=sqlite3.connect(":memory:")conn.execute("PRAGMA foreign_keys=ON")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("DROP TABLE IF EXISTS fact_promotion_eligibility;\nDROP TABLE IF EXISTS dim_promotion;\nCREATE TABLE dim_promotion (\n  promotion_key INTEGER PRIMARY KEY,\n  promotion_id TEXT NOT NULL UNIQUE,\n  promotion_name TEXT NOT NULL\n);\nCREATE TABLE fact_promotion_eligibility (\n  date_key INTEGER NOT NULL REFERENCES dim_date(date_key),\n  product_key INTEGER NOT NULL REFERENCES dim_product(product_key),\n  store_key INTEGER NOT NULL REFERENCES dim_store(store_key),\n  promotion_key INTEGER NOT NULL REFERENCES dim_promotion(promotion_key),\n  source_batch_id TEXT NOT NULL,\n  PRIMARY KEY (date_key, product_key, store_key, promotion_key)\n);\nINSERT INTO dim_promotion VALUES (401,'PROMO-ACCESSORY','Accessory Boost');\nINSERT INTO fact_promotion_eligibility VALUES\n(20260918,201,301,401,'PROMO-COVERAGE-001'),\n(20260918,202,301,401,'PROMO-COVERAGE-001'),\n(20260918,201,302,401,'PROMO-COVERAGE-001'),\n(20260919,204,301,401,'PROMO-COVERAGE-001'),\n(20260919,202,301,401,'PROMO-COVERAGE-001'),\n(20260920,201,302,401,'PROMO-COVERAGE-001'),\n(20260920,202,302,401,'PROMO-COVERAGE-001'),\n(20260920,204,302,401,'PROMO-COVERAGE-001');")coverage=conn.execute("SELECT COUNT(*) FROM fact_promotion_eligibility").fetchone()[0]matched=conn.execute("""SELECT COUNT(*)FROM fact_promotion_eligibility e JOIN fact_sales sON s.date_key=e.date_key AND s.product_key=e.product_key AND s.store_key=e.store_key""").fetchone()[0]misses=conn.execute("""SELECT COUNT(*)FROM fact_promotion_eligibility e LEFT JOIN fact_sales sON s.date_key=e.date_key AND s.product_key=e.product_key AND s.store_key=e.store_keyWHERE s.sales_key IS NULL""").fetchone()[0]assert (coverage,matched,misses)==(8,6,2)assert matched+misses==coverageprint('coverage, sold, no-sale =',coverage,matched,misses)print('factless reconciliation: PASS')

6. Event, attendance, conditions, and eligibility are the same modeling idea

The title names several domains because the technique is general. Attendance records a person/course/date intersection; eligibility records a customer/product/program/time intersection; conditions can record a rule or state applying to an entity at a time; event occurrence records the co-occurrence of dimensions. The design test is always the same: can you state exactly what one row means without inventing a numeric measurement?

7. Production judgment and bridge

Factless facts are particularly valuable for denominator and “what did not happen?” analysis, but they can become large because coverage often enumerates possibilities. In production, validate whether explicit coverage rows, interval rules, a rule engine, or another representation is the right physical choice. The dimensional semantics should remain explicit either way.

The next lesson consolidates the chapter into a metric catalog that records aggregation class, components, grain, and prohibited operations.

Knowledge check

Check your understanding

What does one fact_promotion_eligibility row mean?
Why does the table not need eligible_count=1?
Why does an inner join hide two important rows?
How many coverage rows and no-sale rows are in the fixture?
What protects COUNT(*) from duplicate eligibility statements?

Review the answers

1. One promotion-product-store-date combination is eligible under the synthetic promotion coverage contract.

2. Row existence already represents one coverage fact; the constant would duplicate that information.

3. An inner join retains only combinations with matching sales, so eligible combinations with no activity disappear.

4. Eight coverage rows, of which two have no matching paid-sale activity.

5. The composite primary key that enforces the declared coverage grain.

Summary and next step

AtlasMart now models promotion coverage without invented measures and can prove the decomposition 8 eligible = 6 sold + 2 no-sale combinations.

Next: Audit a Metric Catalog for Double Counting, Incorrect Grain, and Unsafe Aggregation.

Authoritative references

Kimball Group — Factless Fact Tables — Event and coverage factless-fact patterns, including analysis of what did not happen.

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.

Keep knowledge open

Help the academy stay free and grow.

If these tutorials save you time, a small donation supports new lessons, technical review, diagrams, examples, and long-term maintenance.

ETHEthereum / ERC-20 only

0x716c4Ab160C4B66F31a28AE2448BfF68fc3a2ef0

Send only Ethereum or ERC-20 compatible assets to this address.