What are the four steps of Kimball's dimensional design process, in order?

## Why a fixed sequence at all Dimensional design is not a free-form modelling exercise; it is four decisions taken in a specific order, because each one constrains the next. Kimball's sequence is: choose the business process, declare the grain, identify the dimensions, identify the facts. Follow it and the model is internally consistent by construction. Skip a step or reorder it, and the defects show up months later as reports that disagree. ## Step 1 — Select the business process A business process is a real operational activity that produces measurements: a point-of-sale ticket being rung up, an order line being placed, a carton shipping, an invoice being paid, a claim being adjudicated, a support ticket being closed. It is not a department ("Finance"), not a report ("the weekly exec dashboard"), and not a data source ("the Salesforce export"). The distinction matters because processes are stable while reports and org charts churn. Model the process once and the reports that people ask for next quarter are usually already answerable. Model the report, and the next question requires a new table. ## Step 2 — Declare the grain The grain is a plain-language statement of what exactly one row of the fact table represents: "one row per product scanned on one POS ticket", "one row per policy per month-end", "one row per shipment carton". It is a sentence a business person can confirm or reject, and it is fixed before any column is chosen. The default is the **atomic** grain — the lowest level of detail the process actually captures — because dimensionality is richest there and no other grain can be reconstructed from a summary. ## Step 3 — Identify the dimensions With the grain fixed, dimensions become a checkable question rather than a matter of taste: for a single row at this grain, does this descriptor take exactly one value? For "one row per POS ticket line", the date, store, product, cashier and promotion each take one value, so each qualifies. "Salesperson on the account" may take several values for one row, which means it does not attach directly at this grain and needs a different treatment. This is the step that most obviously depends on step two. Change the grain from ticket line to daily store total and the product dimension stops qualifying, because a day's total spans many products. ## Step 4 — Identify the facts Finally, list the numeric measurements produced at that grain: quantity, extended amount, discount, cost. The test is symmetrical to the dimension test — is this value genuinely measured at this grain, or does it belong to a coarser level? An order-level shipping charge is not a line-level measurement, and forcing it onto line rows is the classic way to create a table that double-counts. ## A worked pass ```text Process : retail point-of-sale transactions Grain : one row per product scanned on one POS ticket Dimensions : date, store, product, cashier, promotion, payment method Facts : quantity, unit price at sale, extended amount, discount amount ``` Every later decision in the model — which surrogate keys the fact carries, which summaries can be derived, which questions the mart can answer — follows from those four lines. ## How the order fails in practice The most common inversion is starting from the facts: someone lists the measures a dashboard needs, gathers whatever columns produce them, and only afterwards asks what a row means. The result is a table whose rows mix levels — some per line, some per order, some per day — so no aggregate is safe without knowing which rows to exclude. The second common inversion is starting from the source system: one table per source extract, grain inherited by accident from whatever the extract happened to produce. That guarantees the model changes shape whenever the source does. The third is starting from the report. Reports are the *test* of a dimensional model, not its blueprint; a model built to answer exactly one report answers exactly one report. ## What the four steps do not decide The sequence deliberately says nothing about physical layout, how history is kept on dimension rows, how keys are generated, or which technology stores the result. Those are downstream decisions, and every one of them is easier once you can state the process and grain in two sentences. If you cannot, the model is not ready to be built regardless of how good the platform is. ## What an interviewer listens for They want the four steps named in order, and they want to hear *why* grain sits at position two rather than last. A candidate who recites the list but then designs a table by picking columns first has not internalised it — and interviewers frequently follow the recitation with a small design exercise to check exactly that.