Steps 3-4: facts vs dimensions, a precise definition

Description

Foundations already gave you a first look at the difference between facts and dimensions, with a behavior test: if summing the column across many rows produces a number with business meaning, it's a fact measure; if you're describing something relatively stable that you use to group or filter, it's a dimension attribute. That test is still correct — this lesson doesn't contradict it, it makes it precise, with the exact vocabulary the Kimball Group uses and with a distinction foundations' "first look" didn't need yet: not every measure behaves the same way when you sum it.

Connection to the module. With lesson 5's grain already declared and verified, this lesson resolves steps 3 and 4 of Kimball's process for fact_orders: identifying its dimensions and its facts, with enough precision to carry the seven modules that follow.

An analogy: the patient chart and the lab result

In a doctor's office, a patient's chart has two kinds of information with completely different behaviors. There's the context: name, date of birth, blood type, known allergies — data describing who the patient is, relatively stable, that the doctor consults to make sense of any new result, but that they'd never sum across different patients (summing two blood types means nothing). And there are the measurements: blood pressure, glucose level, temperature — numbers that occur at a specific instant, from a specific visit, and that do make sense to aggregate in different ways: a patient's average glucose over the last year, the highest temperature recorded this week across the entire ER.

Kimball uses this exact same vocabulary, precisely, for a dimensional model: dimensions are the context (who, what, where, when, why, how — the six classic journalism questions, which the Kimball Group itself uses to describe them), and facts are the business process's measurements, captured at the exact instant the event occurred. This lesson applies that distinction, with the precise vocabulary, to fact_orders's seven columns.

Worked example: classifying fact_orders, column by column

With the grain already declared in lesson 5 — an order line — each column of fact_orders gets classified with a dual criterion: is it a numeric measure of that event, or is it the context describing where/what/when it happened?

# classify_columns.py
FACT_ORDERS_COLUMNS = [
    ("order_id",   "degenerate dimension", "Identifies the order, but has no table of its own -- lives inside the fact"),
    ("store_id",   "foreign key (FK)",     "Points to dim_store -- the 'where' of the event"),
    ("product_id", "foreign key (FK)",     "Points to dim_product -- the 'what' of the event"),
    ("quantity",   "measure",              "Additive: summing units across many rows makes sense"),
    ("unit_price", "captured measure",     "NOT additive: summing prices across rows has no business meaning"),
    ("revenue",    "measure",              "Additive: summing revenue across many rows makes sense"),
    ("order_ts",   "temporal context",     "The 'when' -- anchors the row in time, never summed"),
]

print("=== Precise classification of fact_orders (grain: an order line) ===\n")
for column, role, reason in FACT_ORDERS_COLUMNS:
    print(f"{column:12} | {role:22} | {reason}")

additive_measures = [c for c, role, _ in FACT_ORDERS_COLUMNS if role == "measure"]
print(f"\nFully additive measures: {additive_measures}")

What to expect. Running python3 classify_columns.py, the output is exactly this:

=== Precise classification of fact_orders (grain: an order line) ===

order_id     | degenerate dimension   | Identifies the order, but has no table of its own -- lives inside the fact
store_id     | foreign key (FK)       | Points to dim_store -- the 'where' of the event
product_id   | foreign key (FK)       | Points to dim_product -- the 'what' of the event
quantity     | measure                | Additive: summing units across many rows makes sense
unit_price   | captured measure       | NOT additive: summing prices across rows has no business meaning
revenue      | measure                | Additive: summing revenue across many rows makes sense
order_ts     | temporal context       | The 'when' -- anchors the row in time, never summed

Fully additive measures: ['quantity', 'revenue']

Now, the part that makes this classification more than a table of opinions: verify with a real query, over lesson 5's fact_orders, why unit_price is marked "not additive" while quantity and revenue are.

# additive_vs_not.py -- continues over lesson 5's con and fact_orders
print("=== Summing an ADDITIVE measure: revenue (has business meaning) ===")
print(con.sql("SELECT ROUND(SUM(revenue), 2) AS total_revenue FROM fact_orders"))

print("=== Summing quantity (also additive) ===")
print(con.sql("SELECT SUM(quantity) AS total_units FROM fact_orders"))

print("=== Summing unit_price directly (no business meaning) ===")
print(con.sql("SELECT ROUND(SUM(unit_price), 2) AS meaningless_sum FROM fact_orders"))

What to expect. Running python3 additive_vs_not.py, the output is exactly this:

=== Summing an ADDITIVE measure: revenue (has business meaning) ===
┌───────────────┐
│ total_revenue │
│    double     │
├───────────────┤
│        106.15 │
└───────────────┘

=== Summing quantity (also additive) ===
┌─────────────┐
│ total_units │
│   int128    │
├─────────────┤
│         102 │
└─────────────┘

=== Summing unit_price directly (no business meaning) ===
┌─────────────────┐
│ meaningless_sum │
│     double      │
├─────────────────┤
│           57.55 │
└─────────────────┘

106.15 in total revenue and 102 total units are numbers any manager would recognize and use — in fact, 106.15 is exactly the total revenue you already saw in foundations. But 57.55, the sum of all forty rows' unit_price, means absolutely nothing: it's not "the total price of anything," it's not a figure Kiosko's manager could use for any decision. It's the arithmetic sum of forty numbers that each describe a specific product's price at a specific instant — adding them mixes apples with oranges, even though SQL technically allows it without complaint.

Diagram: the three categories of additivity

┌──────────────────────────────────────────────────────────────────┐
│  ADDITIVE                                                           │
│  Can be summed across ANY dimension without losing meaning.        │
│  Example in Kiosko: quantity, revenue                                │
│                                                                       │
│  SEMI-ADDITIVE                                                      │
│  Can be summed across SOME dimensions, but not all.                 │
│  Kimball's classic example: a bank account balance can be summed     │
│  across different accounts (the bank's total balance), but NOT       │
│  across different days of the same account (summing Monday's         │
│  balance with Tuesday's doesn't give the real balance).             │
│                                                                       │
│  NOT ADDITIVE                                                       │
│  Never makes sense to sum, regardless of the dimension.              │
│  Example in Kiosko: unit_price. You can AVERAGE it or take the LAST  │
│  value, but summing it never produces a meaningful number.           │
└──────────────────────────────────────────────────────────────────┘

Going deeper: why unit_price lives in the fact, even though it isn't additive

If unit_price can't be summed meaningfully, why is it in fact_orders instead of in dim_product? The question is legitimate, and the answer is one of the most important decisions in all of dimensional modeling: unit_price describes the price at the exact moment of that specific sale, not the product's "current" price in general. Kiosko might sell P001 at 0.55 on a Monday and, if the price goes up the following week, at 0.60 the Monday after — each row of fact_orders needs to preserve the actual price charged at that instant, not today's catalog price.

This is exactly why Kimball calls unit_price a captured measure (sometimes "fact attribute"): it lives in the fact table, shaped like a number, but its job isn't to be summed — it's to preserve, row by row, a value that could change over time in the dimension that describes it (dim_product). Today, in Kiosko's fact_orders, unit_price doesn't change between orders of the same product — you verified this, without setting out to, in lesson 5's exercise — so the distinction feels almost theoretical. But this guide's module 4 — Slowly Changing Dimensions — builds exactly the scenario where a product's price does change mid-week, and there, the reason for capturing unit_price in every fact row, instead of just reading it from dim_product, becomes completely concrete: without that capture, you couldn't correctly calculate the historical revenue of orders that happened before the price change.

A vocabulary note, because you're going to find it in any serious reading on dimensional modeling: order_id, in this lesson's classification, was marked as a degenerate dimension — an identifier that functionally acts like a dimension (it groups, it identifies a complete transaction), but that has no table of its own: it lives directly as a text column inside fact_orders, with no dim_order to join against. This guide names the concept here, in passing, but develops it in depth — with its full justification and use cases — in module 7.

Common mistakes

Concluding that "captured measure" is the same as "additive measure." What happens: someone sees that unit_price is a number inside fact_orders, alongside quantity and revenue, and assumes all three can be treated the same way — summed, averaged, whatever's needed — without distinguishing their actual behavior. Why it happens: all three are numeric columns in the same table, and that visual similarity hides a real behavioral difference. How to spot it: before writing SUM() over any numeric column of a fact, ask yourself whether the result, summed across many rows, would produce a number with business meaning — the same test you already did in foundations, now applied more carefully. How to fix it: use this lesson's three-category diagram — quantity and revenue are additive (always sum); unit_price is a captured measure, not additive (average it, or take the last value, never sum it).

Thinking a degenerate dimension "should have its own table." What happens: someone, seeing order_id classified as a "degenerate dimension," tries to create a dim_order table with a single column (order_id) to "do it properly," as if every dimension needed its own table. Why it happens: the pattern "every dimension is its own table" is so common in dimensional modeling that it feels like a universal rule. How to spot it: if your dim_order has no descriptive attribute beyond order_id itself (nothing to group by, nothing to filter by other than the identifier itself), that table adds no real value — it only adds an unnecessary join. How to fix it: when an identifier has no attribute of its own beyond itself, the correct practice — with its own name in Kimball's vocabulary — is to leave it as a degenerate dimension inside the fact, exactly as order_id sits in fact_orders today. Module 7 revisits this in more depth.

Classifying order_ts as a measure, because "it's a number" (a timestamp). What happens: someone, seeing that order_ts can be represented internally as a number (seconds since a reference date), treats it as just another measure, a candidate for summing or averaging. Why it happens: technically, any date can be converted to a number, and it's easy to forget that "can be represented as a number" isn't the same as "makes sense to sum." How to spot it: ask yourself what summing two timestamps together would mean — the answer is "nothing with business meaning," the same signal you already used to rule out unit_price as an additive measure. How to fix it: order_ts is temporal context — the "when" of the event — not a measure. It's used to filter, sort, and, later in module 2, to join against dim_date — never to be summed directly.

Exercises

Exercise 1 — Classify three new dim_product columns. Using this lesson's precise vocabulary (not foundations' general test), classify these three dim_product columns: product_name, category, unit_cost. None of them is part of fact_orders — they're dimension attributes — but explain in one sentence each what type of attribute each one is.

Exercise 2 — Calculate the correct average of unit_price. You already saw that SUM(unit_price) makes no sense. Write the query that does have business meaning for unit_price: the average selling price per product, using AVG() instead of SUM().

print(con.sql("""
    SELECT product_id, ROUND(AVG(unit_price), 2) AS avg_price, COUNT(*) AS times_sold
    FROM fact_orders
    GROUP BY product_id
    ORDER BY product_id
"""))

┌────────────┬───────────┬────────────┐
│ product_id │ avg_price │ times_sold │
│  varchar   │  double   │   int64    │
├────────────┼───────────┼────────────┤
│ P001       │      0.55 │         16 │
│ P002       │       1.2 │         10 │
│ P003       │      0.75 │          7 │
│ P004       │       4.5 │          7 │
└────────────┴───────────┴────────────┘

Exercise 3 — Explain the difference between additive and non-additive with an example of your own, outside Kiosko. Using this lesson's diagram's semi-additive bank-balance example for inspiration, propose (no code, prose only) your own example of a non-additive measure in a context other than Kiosko or a bank.

Summary and next step

In this lesson you resolved steps 3 and 4 of Kimball's process for fact_orders, precisely: store_id and product_id are foreign keys to their dimensions; order_id is a degenerate dimension, with no table of its own; quantity and revenue are fully additive measures; unit_price is a captured measure, not additive, that exists in the fact to preserve the actual price of each specific sale, not to be summed; and order_ts is the temporal context that anchors each row in time. You verified with a real query, not just in theory, why summing unit_price produces a meaningless number (57.55) while summing revenue (106.15) doesn't.

Before moving on you should be able to: classify any new fact_orders column using this lesson's precise vocabulary (degenerate dimension, foreign key, additive measure, captured measure, temporal context); explain why unit_price lives in the fact even though it isn't additive; and name, unaided, the concept of "degenerate dimension" and which Kiosko column it applies to.

Lesson 7 steps back and asks, concretely, what changes in practice — not just in vocabulary — once the grain stops being an informal intuition and becomes an explicit, verified declaration.

Resources

• Kimball Group — "Star Schema / OLAP Cube" — the source that defines the vocabulary of measures and descriptive context ("who, what, where, when, why, and how") used in this lesson. kimballgroup.com/.../star-schema-olap-cube. In English.
• "The Data Warehouse Toolkit", 3rd edition (Kimball & Ross, Wiley) — the chapter on fact types develops the distinction between additive, semi-additive, and non-additive measures used in this lesson in detail. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• DuckDB — aggregate function documentation (SUM, AVG, COUNT), used in this lesson to verify additivity with real queries. duckdb.org/docs/current/sql/functions/aggregates. In English.