DW Patterns: The good - type 3

Ok, today I will be continuing on from last week - this time talking about the first of the less-well-known SCD types:

Type 3: Add new attribute

See definition on Wikipedia

This is an interesting pattern - of all of the SCD patterns that actually retain attribute history, this is the only one that provides easy access to any attribute value other than the current value (i.e. type 1) or the as-at value (i.e. type 2). With type 3 we get the current value, as well as one or more immediately prior values. So, why would you want to use this?

The type 3 is great for transitions. Now, I'm not talking about transitions from one entity state to another - if you're wanting that, you're almost certainly better off with Type 2, 4 or 6 (coming in the next post). Those are great at managing the changes to entities as the react with your facts. What I mean is changes in business state; things like changes to business rules, changes to organisational structure and so on.

Type 3 is a good tool for evaluating the effectiveness of changes - you can compare, for example, the effectiveness of a new workload distribution method vs. the previous method, side-by-side. Once you've decided that the new method is better, the old doesn't matter - and when you're then assessing another new method later, you probably don't care what the old, worse method was - that history is unnecessary. You might even decide, once your evaluation period is over, that the field doesn't need to be Type 3 any more, and get rid of the "previous value" attribute altogether, which is fine.

This really comes into its own when the grain of a dimension becomes complicated - if it's a single key, then you can quite happily implement Type 3 over the top of a Type 2 or 6 by using a view; if that involves matching on multiple keys though it could become quite nasty.

It's also dead simple to implement - if your manager says "can you put together something that lets us compare the current method with the old method? Take your time, my meeting isn't for another two hours..." then Type 3 is probably a good option.

This series is continued with commentary on Type 4.