A number of OLTP systems store dimension data in SCD2-like tables in order to retain all the revisions whenever the dimension information changes. In certain situations, you may come across a need to join two or more SCD tables, while keeping all the versions information intact. Sound tricky? Not really.
Tag: scd 2
Efficient data, part 6: Versioning changes
This installment in the series on efficient data is on versioning changes in a table. The article is a re-post of a post I wrote in september on compressing slowly changing dimensions, although the concept does not only apply to dimensions – it can be used pretty much on any data that changes over time.
The idea is to “compress” a versioned table, so instead of just adding a date column for each version, you can compress multiple, sequential versions into a single row with a “from” date and a “to” date. This can significantly compress the size of the table.
Slowly changing dimensions (part 3)
In the third installment of the series on slowly changing dimensions, we’re going to tackle the question of how to manage accumulated fact aggregates in a solution that uses SCD 2 dimensions. While SCD 2 dimensions solve a lot of problems with slowly changing dimensions, accumulated values can still make a mess of the aggregate data.
Slowly changing dimensions (part 2)
In this second installment of the Slowly Changing Dimensions series (see part one here), we’ll take a look at how to practically create a slowly changing dimension table using T-SQL.
Slowly changing dimensions (part 1)
This is the first article in a series that will describe what slowly changing dimensions (SCD for short) are, how they work, and why you might need to take them into account in your database or datawarehouse solution.