Dimensional modeling (Kimball)

Dimensional modeling (Kimball)

Design star schemas the way analytics teams use them: grain, facts, dimensions, history and conformance.

Outcome: You can design a star schema for a business process and defend every choice in review.

1. 1

   Declare the grain first

   Every fact table starts with one sentence: what does a single row mean?

   Build it in the Model Builder · 12 min · instant checks

2. 2

   Split a flat table into facts and dimensions

   Turn a wide export into a star schema: measures in the fact, descriptions in dimensions.

   Build it in the Model Builder · 15 min · instant checks

3. 3

   Keep history with a Type 2 dimension

   When a customer moves segment, should last year's sales move with them?

   Build it in the Model Builder · 12 min · instant checks

4. 4

   Build a date dimension and role-play it

   Replace raw dates with a calendar everyone shares, then use it twice on one fact.

   Build it in the Model Builder · 12 min · instant checks

5. 5

   Conform dimensions across facts

   Two fact tables, one product dimension: the trick that makes cross-process reporting work.

   Build it in the Model Builder · 15 min · instant checks

6. 6

   Factless facts and bridge tables

   Record events that have no measures, and attach many values of a dimension to one fact row without double counting.

   Build it in the Model Builder · 15 min · instant checks

7. 7

   Periodic and accumulating snapshot facts

   Pick the right fact type for balances over time and for processes that move through milestones.

   Build it in the Model Builder · 15 min · instant checks

8. 8

   Junk and degenerate dimensions

   Fold scattered low-cardinality flags into one junk dimension and keep transaction numbers on the fact.

   Build it in the Model Builder · 12 min · instant checks