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