Practical Business Intelligence with SQL Server 2005

Many data sources are simply a snapshot of the current state of some part of your enterprise. If you reload the dimension tables of your data warehouse directly from these sources, all reports and cubes appear as if the current state is the way things have been for all time. This may be alright if you are recording birthdays, but it can seriously mislead you if you are looking at something such as a customer's credit rating or which salesperson is (or was) responsible for a given territory.

When some of the attributes of a dimension record change over time, the dimension is called a slowly changing dimension (SCD). For example, customers or employees change their names, the cost of production for a product changes, or a salesperson may become responsible for a different marketing region. Date, on the other hand, is an example of a dimension that is not a SCD because it has a well-defined and universally accepted structure (unless you happen to be modeling events around the time of Caesar, when February 29 and 30 were "repurposed" to lengthen the months of July and August!).

In this chapter, we explore how to model slowly changing dimensions and show how to design the extraction, transformation, and loading (ETL) process to support them. We also examine Analysis Services options that can optimize the processing of SCDs. We also build a solution based on a problem drawn from the financial industry to illustrate the key techniques in working with SCDs.