Changing Dimensions

When designing a BI solution, consider the impact of changing data. For example, what happens if you add a new product, or if you change the name of a product or the category to which the product is assigned? How would you handle a customer who is assigned to a different sales territory? Here, you need to make a business decision about how to manage this change in the data warehouse. Do you insert the customer's previous sales into the new territory? Do you track previous sales at their original value and future sales at the new value? Or, do you track at a high level instead? The way that the business needs to respond to these changes will determine the techniques you use to manage these changes in a data warehouse. You might overwrite data in existing records (and thereby lose history), or you might flag the original record as inactive and create a new record that contains the current data (and, consequently, have both the original value as well as the new value). Integration Services includes a special task for managing changing dimensions to meet your specific business requirements.