Author
Ralph Kimball, Margy Ross
2 reading cards from 1 book · 2013.
2 cards
The Data Warehouse Toolkit · 2013
Type 1 overwrites, type 2 adds a row with a period, type 3 keeps a column with the previous value.
A customer moves from one city to another. What do you do with their row in the Customer dimension? Type 1: overwrite. Simple, no history — and the 2023 sales suddenly show up in the new city, so old reports change. Good for fixing mistakes and for attributes with no historical value. Type 2: add a new row, with a new surrogate key, valid_from and valid_to, and a flag for the current one. Old facts stay tied to the version of the time, new facts to the new version; the 2023 report stays exactly as it was. Type 3: keep a "previous value" column — a single step of history, useful when sales territories are realigned and you want both cuts at once. The choice is per attribute, not per table: the same dimension can have type 1 and type 2 columns. Two traps. First: joining facts on the natural key instead of the surrogate, which multiplies rows by the number of versions the customer has. Second: a type 1 "update" run by mistake on a type 2 dimension, which rewrites every version at once and erases the history without a single error.
“Slowly changing dimension type 2 changes add a new row in the dimension with the updated attribute values. This requires generalizing the primary key of the dimension beyond the natural or durable key because there will potentially be multiple rows describing each member.”
The Data Warehouse Toolkit · 2013
The grain is a sentence: "one row = one receipt line, in one store, on one day".
Kimball's four steps come in that order for a reason. The business process is something that happens — a sale, a payment, a shipment — not a department. The grain is the sentence that says what one row of the fact table stands for: "one receipt line, in one store, on one day". Only then come the dimensions — who, what, where, when, the descriptive context — and the facts, the numeric measures that can be added up at the chosen grain. The result is the star schema: a narrow, long fact table with foreign keys to wide, denormalised dimensions, each carrying a surrogate key (an integer minted by the warehouse, not the code from the source). The classic mistake is to mix grains — order lines and order totals in the same table — and get doubled sums in the first report. The second mistake is three slightly different "Customer" dimensions; conformed dimensions, shared by every process, are what make two reports comparable.
“Select the business process. Declare the grain. Identify the dimensions. Identify the facts.”