Fact Table Types: Transaction, Snapshot, Accumulating
The same nine order events become three fact tables, and the shipped orders took 2 days on average.
Video #258 ยท Data modelling
Steps in the video
Files
- facts.py (50 lines)
- run.py (44 lines)
- source.py (15 lines)
Tested with
Python 3.12.15
What this does not show
Nine made-up events in SQLite; the snapshot is computed from the events, the averages skip open orders, and no dimension tables are shown.
Sources
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/accumulating-snapshot-fact-table/
- 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/grain/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/lag-duration-fact/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/periodic-snapshot-fact-table/
- https://www.kimballgroup.com/data-warehouse-business-intelligence-resources/kimball-techniques/dimensional-modeling-techniques/transaction-fact-table/
- https://www.sqlite.org/lang_datefunc.html
Learning IT by Example.