Kimball's four-step process

Description

This lesson teaches the full method — business process, grain, dimensions, facts — over a small example that has nothing to do with Kiosko: a coffee shop. The reason for not using Kiosko yet is deliberate: you want to see the entire pattern, start to finish, in a case simple enough that no decision is questionable, before applying it to real data with its own complications. Lessons 4 and 5 pick up this exact same process, step by step, on fact_orders.

Connection to the module. This is the module's most conceptual lesson, and its most important one: if the four-step process isn't clear here, with an uncomplicated example, applying it to Kiosko in the following lessons is going to feel arbitrary instead of methodical.

An analogy: the recipe before the ingredients

A cook opening a new restaurant doesn't buy ingredients at random and then decide what dish to cook with what's on hand. They start backward: they decide which dish they're going to serve (the business process), then decide the exact portion a plate represents — is it an individual portion, a family portion, an appetizer? — (the grain), and only then do they know which ingredients to buy and in what quantity (the facts), and which sides or variants to offer (the dimensions: type of bread, size, with or without cheese).

If they reversed the order — buying ingredients before deciding the portion — they'd end up with a kitchen full of things that "could be useful for something," with no well-defined dish. Kimball's process applies that exact same discipline to designing a dimensional model: the sequence matters, and skipping a step — or doing them in the wrong order — produces the same chaos as buying ingredients without a recipe.

Worked example: the four steps, applied to a coffee shop

Before touching Kiosko, apply the full process to a different, much simpler business: a coffee shop with three employees (baristas) who prepare drinks for customers who pay on the spot.

Step 1 — Select the business process. The business process isn't "the coffee shop" in general — that's too broad to model in a single table — it's a specific, measurable event. For this coffee shop, the process chosen is selling a drink: every time a barista charges a customer for a drink, the event this model is going to record occurs.

Step 2 — Declare the grain. The question is: what, exactly, does a row represent? For selling a drink, the finest and most honest answer is: a row represents one drink sold, at a specific instant, prepared by a specific barista. It's not "a customer" (a customer can order three drinks in a single visit) nor "a day of sales" (that would already be an aggregate, not the original grain).

Step 3 — Identify the dimensions. With the grain already fixed ("a drink sold, at an instant, by a barista"), the dimensions are the context that answers who, what, when, and where: dim_drink (which drink — espresso, latte, cappuccino), dim_barista (who prepared it), dim_date (when).

Step 4 — Identify the facts. The numeric measures that make sense to sum across many rows: price (the price charged) and, if the coffee shop tracked it, prep_time_seconds (preparation time, summable to calculate a barista's total workload during a shift).

# coffee_shop_four_steps.py
BUSINESS_PROCESS = "Selling a drink"

GRAIN = "One row represents one drink sold, at a specific instant, prepared by a specific barista"

DIMENSIONS = ["dim_drink", "dim_barista", "dim_date"]

FACTS = ["price", "prep_time_seconds"]

print("=== Kimball's four steps, applied to a coffee shop ===\n")
print(f"Step 1 (business process): {BUSINESS_PROCESS}")
print(f"Step 2 (grain):            {GRAIN}")
print(f"Step 3 (dimensions):       {DIMENSIONS}")
print(f"Step 4 (facts):            {FACTS}")

# One example row, already at the correct grain
sale = {
    "drink_id": "D01",
    "barista_id": "B02",
    "sale_ts": "2026-08-03T08:15:00",
    "price": 3.50,
    "prep_time_seconds": 90,
}
print(f"\nA real row at this grain: {sale}")

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

=== Kimball's four steps, applied to a coffee shop ===

Step 1 (business process): Selling a drink
Step 2 (grain):            One row represents one drink sold, at a specific instant, prepared by a specific barista
Step 3 (dimensions):       ['dim_drink', 'dim_barista', 'dim_date']
Step 4 (facts):            ['price', 'prep_time_seconds']

A real row at this grain: {'drink_id': 'D01', 'barista_id': 'B02', 'sale_ts': '2026-08-03T08:15:00', 'price': 3.5, 'prep_time_seconds': 90}

Notice something you're going to see repeat, identical in structure, when you apply this same process to Kiosko in lessons 4 and 5: each dimension (dim_drink, dim_barista, dim_date) answers a different context question about the same row, and each fact (price, prep_time_seconds) is something that makes sense to sum across many sales. No column appears in both lists — a column is either a dimension or a fact, never both.

Diagram: the order you can't reverse

flowchart TD
    A["Step 1: Select the business process\n(which event you are measuring)"] --> B["Step 2: Declare the grain\n(what a row represents)"]
    B --> C["Step 3: Identify the dimensions\n(that row's context)"]
    C --> D["Step 4: Identify the facts\n(that row's numeric measures)"]

    B -.cannot be skipped.-> E["If you declare dimensions or facts\nwithout a fixed grain, every new column\nreopens the question 'a row of what?'"]

Going deeper: why reversing the order breaks the model

Imagine someone, in a hurry, starts at step 3 — "identify the dimensions" — without having declared the coffee shop's grain first. That person could reasonably propose dim_drink and dim_barista as dimensions... but they could also propose dim_customer_visit (one dimension per complete customer visit, which can include several drinks). Without the grain already fixed, neither proposal is objectively right or wrong — they depend entirely on whether the final grain is going to be "a drink" or "a complete visit." Every dimension added without a fixed grain forces you to re-ask "a row of what, exactly?", and that back-and-forth is, precisely, what produces an indecisive model: half one thing, half another, with nobody able to say with confidence what a row represents.

That's the concrete reason — not just an arbitrary convention — the process puts "declare the grain" at step 2, immediately after choosing the business process, and before touching a single dimension or fact. The grain is the constraint that makes every decision that follows have one correct answer, not several equally reasonable ones.

There's a second, even more practical reason: step 4's measures only make sense once you know at what level they're calculated. price as a measure of "one drink sold" is straightforward: that specific drink's price. But if the grain were "a complete visit" (several drinks), price would have to already be a pre-calculated sum — losing the detail of how much each individual drink cost. The grain doesn't just organize the model: it literally determines what each measure means.

Common mistakes

Choosing a business process that's too broad. What happens: someone, doing step 1, chooses something like "the coffee shop" or "the business's operations" instead of a specific, measurable event like "selling a drink." Why it happens: thinking in terms of "the whole business" feels more ambitious and important than thinking about a single type of event. How to spot it: if your "business process" can't be described as a verb with a specific moment it occurs at (sell, order, ship, log in), it's too broad to model in a single fact table. How to fix it: a real business has several processes — sales, purchases from suppliers, employee shifts — and each one gets modeled with its own fact table, each with its own grain. "The coffee shop" isn't a process; "selling a drink" is.

Declaring the grain using a column, not a sentence. What happens: someone, doing step 2, writes something like grain = "drink_id" — the name of a column, not a complete sentence describing what the row represents. Why it happens: thinking in terms of the table's "key" feels more technical and direct than writing a sentence in prose. How to spot it: if your grain declaration fits inside the name of a single column, you're probably describing a key, not the full grain — the grain needs to describe the complete business unit ("a drink sold, at an instant, by a barista"), not just an identifier. How to fix it: this lesson's worked example declares the grain as a complete sentence in GRAIN, not as a column name — always imitate that shape.

Mixing dimensions and facts in the same list, "because both describe the sale." What happens: someone, finishing steps 3 and 4, includes price in both DIMENSIONS and FACTS, reasoning that the price also "describes" the sale. Why it happens: it's easy to forget the behavior test — does it make sense to sum it? — and confuse "describes something about the row" with "is a dimension." How to spot it: if summing the column across many rows produces a number with business meaning ("the coffee shop's total revenue"), it's a fact, not a dimension — no exceptions, no matter how descriptive it feels. How to fix it: apply the two-question test you already saw in foundations (does it make sense to sum? does it describe something stable I use to group?) to each column, one at a time, before deciding which list it belongs in.

Exercises

Exercise 1 — Apply the four steps to a gym. A gym logs every time a member checks in with their card on entry. Apply Kimball's four steps to this process: (1) name the business process, (2) declare the grain as a complete sentence, (3) propose at least two dimensions, (4) propose at least one fact (numeric measure).

See solution

A reasonable answer:

1. Business process: a member's check-in at the gym.
2. Grain: a row represents one check-in by a specific member, at a specific instant, at a specific location.
3. Dimensions: dim_member (who), dim_gym_location (which location), dim_date (when).
4. Facts: visit_duration_minutes (if the gym tracks how long the member stayed, it's a measure summable across many check-ins — for example, to calculate a location's total usage time in a month). A check-in with no duration recorded could even have no numeric fact beyond an implicit "1 visit" counter — a legitimate case called a factless fact table, which you'll mention in passing in lesson 6.

Exercise 2 — Find the error in a badly declared grain. Someone proposes this grain for the gym: "a row represents a member's visits during the month". Explain in 2-3 sentences why this declaration, as written, is more aggregated than the finest available grain, and why that could be a problem.

See solution

"A member's visits during the month" is an aggregate — one row would summarize many check-ins into a single number (for example, "12 visits in August") — not the finest individual event the source system records (each specific check-in, with its own instant). Declaring the grain that way loses unrecoverable information: you couldn't answer "what time of day does this member visit most?" or "how many visits did location X have last Tuesday?" without going back to the raw data — exactly the same problem you already saw in foundations when the gold layer (aggregated) couldn't answer questions that only silver (fine grain) could. An aggregated grain can be a valid decision for a different, more summarized table, but it should never be the grain of the main fact table if the source system records something finer.

Exercise 3 — Explain the order in your own words. Without literally repeating the "going deeper" section, explain in 2-3 sentences why "identify the dimensions" (step 3) can't be done well without having completed "declare the grain" (step 2) first.

See solution

Dimensions answer context questions about a specific row ("which drink was it?", "which barista prepared it?"), but those questions only make sense if you already know exactly what "a row" is. If the grain hasn't been decided yet, any dimension you propose is, in reality, guessing among several possible grain definitions at once, and it's easy to end up with a dimension that makes sense for one grain ("a drink") but not for another ("a complete visit with several drinks"). Fixing the grain first eliminates that ambiguity: once it's decided, there's only one correct answer to "what context does this row need?"

Summary and next step

In this lesson you learned Ralph Kimball's complete four-step process — business process, grain, dimensions, facts — applied, start to finish, to a simple example that has nothing to do with Kiosko: selling a drink at a coffee shop. You saw why the steps' order isn't an arbitrary convention: each step depends, concretely, on the previous one already being resolved — reversing the order produces ambiguity, not just disorder.

Before moving on you should be able to: name the four steps in order, unaided; declare the grain of a new business process as a complete sentence, not as a column name; and explain, with an example of your own, why declaring dimensions before the grain produces ambiguity.

With the full process now clear on a simple example, lesson 4 applies step 1 — selecting the business process — directly to Kiosko: you're going to see that, even with only two data sources available (orders and events), choosing the right process isn't as obvious as it looks at first glance.

Resources

• Kimball Group — "Four-Step Dimensional Design Process" — the source that defines, in the exact order used in this lesson, the four steps applied here to the coffee shop. kimballgroup.com/.../four-4-step-design-process. In English.
• "The Data Warehouse Toolkit", 3rd edition (Kimball & Ross, Wiley) — the introduction chapter develops this same four-step process with retail examples, the conceptual foundation of this entire book. wiley.com/en-jp/The+Data+Warehouse+Toolkit. In English.
• Microsoft Learn — "Understand star schema and the importance for Power BI" — a practical, tool-neutral confirmation of why declaring the grain, dimensions, and facts precisely matters for any analytics consumer. learn.microsoft.com/en-us/power-bi/guidance/star-schema. In English.