Professional SQL Server™ 2005 Integration Services

6.13. Slowly Changing Dimension

The Slowly Changing Dimension (SCD) transform provides a great head start in helping to solve a common, classic changing-dimension problem that occurs in the outer edge of your data model—the dimension or lookup tables. The changing-dimension issue in online transaction and analytical processing database designs is too big to cover in this chapter, but a little background may be necessary to help you understand the value of service the SCD transformation provides.

A dimension table contains a set of discrete values with a description and often other measurable attributes such as price, weight, or sales territory. The classic problem is what to do in your dimension data when an attribute in a row changes, particularly when you are loading data automatically through an ETL process. Take for example something as simple as a product defined in AdventureWorks (see Figure 6-36).

Figure 6.36. Figure 6-36

Typically in an On-Line Transaction Processing system (OLTP) you'd store one row for the product. If the price of the product changes from $10.00 to $15.00, the field StandardCost gets updated to $15.00. This accomplishes the mission of providing the answer to the question "How much does it cost now?" but you lose the historical perspective about the increases in the price of product. To solve this problem, you have really three basic options. The key ...