Declare the Grain: The Mistake That Breaks Reports
A daily sales report said 108 pounds when the shop sold 54, and one rule fixed it.
Video #257 ยท Data modelling
Steps in the video
- 0:00 A report said 108. The shop sold 54.
- 0:30 The idea
- 1:06 Setup
- 1:27 Two levels in the source
- 1:52 Mistake one: two grains in one table
- 2:17 Mistake two: a header fact on every line
- 2:41 The fix: one grain per table
- 3:09 Checking against the source
- 3:24 Run the mistakes
- 4:30 What this does not show
- 5:05 Mistakes to avoid
- 5:31 My take and next steps
- 5:59 Recap
Files
- fixed.py (24 lines)
- mixed.py (30 lines)
- run.py (54 lines)
- source.py (17 lines)
Tested with
Python 3.12.15
What this does not show
Four made-up orders in SQLite; it shows two grain mistakes and one fix, not allocation or dimension tables.
Sources
- https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/allocated-fact/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/fact-table-structure/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/four-4-step-design-process/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/grain/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/header-line-fact-table/
Learning IT by Example.