Slowly Changing Dimensions in SQL Server 2005

By : Dinesh Asanka Apr 20, 2008

Printer friendly

Introduction
Slowly changing dimensions (SCD) are very useful in data warehouses. Even though they are useful, it is very difficult to implement slowly changing dimensions with most of ETL tools. In case of SQL Server 2000 it was difficult to implement SCD's as there was no direct way of implimenting them.  As you are aware there were numerous changes in SQL Server 2005 including the introduction of a SCD data flow task. This article explores the options available in the SQL Server 2005 for SCD's.

Requirements
You need to have installed SQL Server 2005 with Business Intelligence Development Studio (BIDS).  It will also be beneficial if you know how to create simple SQL Server Integration Services (SSIS) packages. However, as usual I will elaborate on how to develop them when and where ever possible.

What is SCD?
The "Slowly Changing Dimension" problem is a common one particular to data warehousing. In a nutshell, this applies to cases where the attribute for a record varies over time. Let us take this hypothetical example. Let us say that we have a fact table that contains customersk. The Fact table is linked to dimcustomer and there is the following relationship between these two tables.



This dimcustomer table data is taken from the OLTP database which has the following table structures and relationships.

The data for these three tables should be stored in the dimcustomer table in the data warehouse. As you aware Material Status and Region can be changed. However, these will not change frequently. Therefore, this is considered to be a slowly changing dimension or SCD.