Entity Relational Modeling
vs.
Dimensional Modeling

Slowly Changing Dimensions

• Dimensions that change over time are called Slowly Changing Dimensions.
  
  
• For instance, a product price changes over time; People change their names for some reason; Country and State names may change over time. These are a few examples of Slowly Changing Dimensions since some changes are happening to them over a period of time.

Handling : Slowly Changing Dimensions categorized

• Type 1: Overwriting the old values. In the year 2005, if the price of the product changes to $250, then the old values of the columns "Year" and "Product Price" have to be updated and replaced with the new values.
  
  
• In this Type 1, there is no way to find out the old value of the product "Product1" in year 2004 since the table now contains only the new price and year information.
  
  
• Type 2: Creating an another additional record.In this Type 2, the old values will not be replaced but a new row containing the new values will be added to the product table.
  
  
• So at any point of time, the difference between the old values and new values can be retrieved and easily be compared. This would be very useful for reporting purposes.
  
  
• The problem with the above mentioned data structure is "Product ID" cannot store duplicate values of "Product1" since "Product ID" is the primary key.
  
  
• Also, the current data structure doesn't clearly specify the effective date and expiry date of Product1 like when the change to its price happened. So, it would be better to change the current data structure to overcome the above primary key violation
  
  
• Type 3: Creating new fields. In this Type 3, the latest update to the changed values can be seen. Example mentioned below illustrates how to add new columns and keep track of the changes. From that, we are able to see the current price and the previous price of the product, Product1.
  
  
• The problem with the Type 3 approach, is over years, if the product price continuously changes, then the complete history may not be stored, only the latest change will be stored. For example, in year 2006, if the product1's price changes to $350, then we would not be able to see the complete history of 2004 prices, since the old values would have been updated with 2005 product information.

So how to handle Slowly Changing Dimensions

• Usually, when the data warehouse administrator encounters a changed description in a dimension record such as product or customer, the correct response is to issue a new dimension record.
  
  
• But to do this, the data warehouse must have a more general key structure. Hence the need for a surrogate key

Back to Entity Relational Modeling   vs.  Dimensional Modeling Index