“As Is” vs “As Was” Reporting Using Type 2 Dimensions
August 30th, 2004 by Mark Rittman
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
February 19th, 2007 at 1:22 am
I have a question regarding Type 2.
In Type 2 when any attribute changes we add new row.
For fact table, What we did was that we updated fact table to update Customer dimensions surrogate key for existing records to the surrogate key of newest row of updated record but now all my transactions point to newest Customer record. Is that a correct approach?
If not then what can I do ? B’cos if I do not update the fact table’s previous records then how do I get roll ups for a particular customer ?