Protocol

Join historical work to the version that was valid then

Today's organization chart did not manage last year's sale.

About 1 min read

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.

Keep reading

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

  1. State whether the report needs current classification or historical classification.
  2. Use the relevant effective-time intervals or version keys.
  3. 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.

Use email
Keep exploring

Connected ideas

You are readingJoin historical work to the version that was valid then

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

All sources (1)
Edition and review

Version 0.1.0

No review details are supplied for this edition.

Read this idea as JSON · Release details