The Impact of Slowly Changing Dimensions
- data warehouses typically update on a scheduled basis
- often hold the entire history of data for reporting and business intelligence projects
- how data changes plays a major role in how the data is stored
- so designed based on the slowly changing dimensions
Example
- use an example to demonstrate how updates are made to dimension data for each type of slowly changing dimensions
- scenario
- company that sold a popular bike with the product name “Red Valley Bike” from January 200 to 2010
- in 2011, company decided to rename the product “Vibrant Valley Bike”
Type 1 Slowly Changing Dimensions
- overwrites the existing value (Red Valley Bike) with the new value (Vibrant Valley Bike)
- Every data set we generate from any point in time will now have the most current name applied
- previous product name will not be retained
- preferred if
- only the most current product name is needed
- the former name of the bike is no longer relevant

- when reporting on this table, establish a join from the product ID as a primary key to the product ID as a foreign key in the sales table
- key did not change, but value did
- new sales records will only reflect the current information for that product name
Type 2 Slowly Changing Dimensions
- allows us to create reports showing the former product name even though the bike is now named “Vibrant Valley Bike”
- Type 2 is preferred if it’s important to have a record of historical data for the product name
- retains the record with the original product name and creates a record with the new product name
- providing the most complete history of the product name
- records include start and end dates for each name of the bike

- means the data warehouse holds both the historical information as well as the current information
- provides you with the most detailed history for reporting
- allows you to leverage the dates of the change to provide accurate information at that time in any of your reporting
Type 3 Slowly Changing Dimensions
- a current value column would be created with the current product name,
- while the original column is retained and shows the old product name
- Unlike with Type 2, there is only a single record for the product using Type 3
- Type 3 only maintains the last two changes made to the data
- if name changed a third time, then the last 2 would be retained and not the original

- a new column is added showing the previous name
- if the bike is renamed again,
- this update would only capture the new product name and previous name
- original product name would be lost
- lose some history for data in this field when it changes