Timespan Fact Tables
summary
A single fact table stores time ranges by adding valid_from/valid_to/is_current and a concatenated date range key, then a date range bridge explodes those ranges to individual date keys so a date dimension can filter facts. A 5‑day bank example uses only three fact rows that produce 4,222 bridge rows, renders correctly in Power BI, and simplifies orchestration to one stored procedure; tradeoffs are heavier upserts, longer ETL, and managing a many‑to‑many relationship with filter direction.
AI-written summary · about
chapters (8)
- 0:00Introduction and agenda
- 1:01Speaker background
- 2:18Storing history in facts
- 5:26Periodic snapshotting
- 8:15Time span fact tables
- 11:20Bridge table implementation
- 14:27Power BI demo
- 16:15Conclusions and takeaways
related talks
16mGood Data Modeling & Effective Communication in Power BI
41mThe Unified Star Schema
59mMastering Time-Based Analysis in DAX
48mDon't be scared with Hybrid, they are only tables…
01h 03mTemporal Database Demo
42mAccessible Time Series data
29mCatching Waves with Time-Series Data
51mPower BI Composite and Hybrid Models