Chapter 1 of 7 · 4 practices · 3 min · 3-question quiz
Start from the business process and the grain
Every good dimensional model starts with two decisions: which business process you are modeling and what one row means. These practices make both explicit.
Start: Design each star in four steps: process, grain, dimensions, facts4 practices in this chapter
- 1Design each star in four steps: process, grain, dimensions, factsThe Kimball Group describes dimensional design as four key decisions, made in order: Select the business process: an operational activity such as taking orders, shipping, or registering a student…
- 2Declare the grain in one sentence and keep every row at that grainWrite the grain as a sentence before any SQL: "one row per order line", "one row per product per day". Microsoft's fact table guidance explains that the values of the dimension keys set the…
- 3Prefer the most detailed grain the source data supportsDetailed facts can always be rolled up; summarised facts cannot be split back down. Microsoft's fact table guidance notes that while you can store facts at a higher granularity, it is not easy to…
- 4Test the grain with a uniqueness check on its key columnsA declared grain is a claim you can test. If the grain is one row per order line, the combination of
order_idandline_numbermust be unique.
Check yourself on this chapter
3 questions, right here once you are signed in. Your progress is saved as you read.