Protocol
Join historical work to the version that was valid then
Today's organization chart did not manage last year's sale.
Before you use it
- Use when
- A current master-data value silently changes the interpretation of old transactions.
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.
An idea to explore.
Try this idea.
Inspect the example, try the idea in your situation, then check your next move.
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.
Save and practise this idea
My learning
Read. Recall. Repeat.
Save this idea and return to it on any device.
Keep exploring
Connected ideas
You are readingJoin historical work to the version that was valid then
Ideas that link here
These ideas link to this one. Check their conditions and limits before use.
Useful with this ideaChoose the clock before assigning an event to a period
Evidence and sources
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
All sources (1)
Edition and review
Version 0.1.0
No review details are supplied for this edition.