Practical Business Intelligence with SQL Server 2005

Slowly changing dimensions occur in almost every application. Type 1 SCDs simply overwrite historical values. Type 2 SCDs preserve historical values, and you need to design the schema for SCDs to include a start and end date so the ETL process knows what time period each member is valid. Type 2 SCDs must be referenced using surrogate keys because the business key is no longer unique. If you have wide records, you can reduce storage costs by splitting the attributes into a single Type 1 SCD record and multiple Type 2 records so that the Type 1 attributes won't be repeated unnecessarily. Integration Services has a Slowly Changing Dimension Wizard that makes it easy to create the essential data flow transforms need to implement Type 1 or Type 2 dimensions.