Protocol
Join historical work to the version that was valid then
Today's organization chart did not manage last year's sale.
When it fits
- A current master-data value silently changes the interpretation of old transactions.
When to avoid it
- Corrections and retroactive changes need agreed rules. Keeping versions is insufficient when their validity dates or source meaning are wrong.
Why it matters
When the question is historical, preserve or obtain effective-dated versions and match each event to the version valid at that time. A Type 2 slowly changing dimension is one implementation. Joining everything to the current row answers a different question.
Steps
- State whether the report needs current classification or historical classification.
- Use the relevant effective-time intervals or version keys.
- Check gaps, overlapping intervals and changes that occur exactly at a boundary.
An example
A customer moves to a new region in July. June sales keep their historical region when the report asks who served them at the time.
Check your result
A before-and-after test around one known change produces the intended historical attribution.
Keep this limit in mind
- Corrections and retroactive changes need agreed rules. Keeping versions is insufficient when their validity dates or source meaning are wrong.
Connected ideas
Useful withName what one row means before joining two tables
Evidence and sources
Supports
A Type 2 slowly changing dimension preserves versions with validity dates so historical facts can be associated with the relevant version.
Overlapping validity intervals, late corrections and inaccurate effective dates require explicit handling.
Understand star schema and the importance for Power BI · Slowly changing dimensions: Type 2