Data Modelling Essentials

Not this time. The 4 steps are: select the business process, declare the grain, identify the dimensions, identify the facts. The grain comes before dimensions and facts because every one of them must fit it.

Not this time. The grain says exactly what one fact table row represents, such as one line on a receipt. Atomic grain is the lowest level the process captures, and different grains must not be mixed in the same fact table.

Not this time. An accumulating snapshot has one row per order line, inserted when the line is created and updated as each milestone happens, with a date key for each milestone and lag measures. It is the only one of the 3 kinds whose rows are updated.

Not this time. A balance is semi-additive: it can be summed across every dimension except time. Over days, use the last value or an average instead. Ratios are non-additive: store their parts and divide at the end.

Not this time. When changes are tracked over time, one customer ID has several dimension rows, so it cannot be the primary key. Natural keys from several sources may also clash. Kimball recommends integer surrogate keys assigned in sequence; the date dimension can be an exception.

Not this time. Type 2 adds a new row with the new values and a new surrogate key, so earlier facts still point to the old row. It needs at least a row effective date, a row expiration date and a current row indicator.

Not this time. Conformed dimensions have the same column names and contents wherever they are used. Grouping both fact tables by a conformed attribute lines up the results on the same rows, which is a drill-across report.

Not this time. Once each line carries the invoice's dimension keys, the invoice has nothing left but its number. That number stays in the fact table as a degenerate dimension, with no dimension table behind it.

Not this time. Put the low-cardinality flags together in one junk dimension, holding only the combinations that actually occur in the data rather than every possible combination.

Not this time. A factless fact table records dimensions coming together at a point in time, here day, student, teacher, location and class, with no numeric fact. A coverage table of every possible attendance minus this activity shows who did not attend.

Not this time. A dimension with several values at the grain is attached through a group key to a bridge table, which has one row for each diagnosis in the group.

Not this time. A snowflaked dimension holds the same information as the flattened one, but it is harder for business users to understand and use, and it can slow queries.