Star Schema from Scratch: Facts, Dimensions and Grain
Nine order lines, one fact table, three reports, and every report adds up to 64.
Video #256 ยท Data modelling
Steps in the video
- 0:00 Nine order lines, one fact table, three reports that each add up to 64.
- 0:35 The idea
- 1:08 Setup
- 1:33 Dimensions, grain and the fact table
- 2:17 Loading the dimensions
- 2:45 Loading the facts
- 3:03 Three reports, one pattern
- 3:28 Checking the grain
- 3:45 Run the build
- 4:32 What this does not show
- 5:11 Mistakes to avoid
- 5:44 My take and next steps
- 6:10 Recap
Files
- reports.py (15 lines)
- run.py (28 lines)
- source.py (23 lines)
- star.py (65 lines)
Tested with
Python 3.12.15
What this does not show
Nine made-up order lines in SQLite; it does not measure speed or cover slowly changing dimensions.
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/additive-semi-additive-non-additive-fact/
- 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/star-schema-olap-cube/
- https://www.sqlite.org/foreignkeys.html
Learning IT by Example.