“As Is” vs “As Was” Reporting Using Type 2 Dimensions

The other week I was working for a client who was putting together a type 2 slowly changing dimension (a.k.a. 'SCD2') to hold details of their customers. A customer could (for example) move over time, and the type 2 dimension tracked all changes to their postcode over time. When a transaction was recorded in the fact table, the fact row pointed to the correct version of the customer record, that had their actual postcode at the time of the transaction.

The problem we had was that, although they wanted to record history in this way, they also had a big requirement for "as is", as well as "as was" reporting, in that they also wanted to analyse their transactions, regardless of time, by the customers current postcode, on which several bits of demographic information was based. An SCD2 dimension is great for bringing back their address at the time the transaction was made (you join from the fact table to the version of the customer's dimension record that was valid at the time of the transaction), but it's a bit more difficult if what you actually want is their *current* postcode, which you'll need to obtain by referring to the most current dimension record for the customer. At best, it's a complicated bit of SQL involving a subquery, and at best, your query tool of choice won't be flexible enough to do this for you.

Of course, this issue about "as is" vs. "as was" reporting must be fairly common, and I remembered reading something by Ralph Kimball about this very subject. Sure enough, I had a look through some old Ralph Kimball design tips, and came up with design tip #15 from the Design Tips 2000 Archive, entitled "Combining SCD Techniques". The relevant bit for this problem reads:

"In my experience, data warehouse teams are often asked to preserve historical attributes, while also supporting the ability to report historical performance data according to the current attribute values. None of the standard SCD techniques enable this requirement independently. However, by combining techniques, you can elegantly provide this capability in your dimensional models.

We