O'Reilly logo

Professional SQL Server™ 2005 Integration Services by Mike Murphy, Haidong Ji, Jason Gerard, Erik Veerman, Andy Leonard, Kathi Kellenberger, Douglas Hinson, Darren Green, Allan Mitchell, Brian Knight

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required