Overview of Slowly Changing Dimensions


Slowly changing dimensions are a way of updating dimension data.

  • Dimension data
    • describes attributes of facts
    • is often categorical data
    • as a general rule will not change as often as fact data
  • e.g., A company sells a variety of products, which are named in a field called Product Name
    • Each time a new order is created, that order is given a purchase order (PO) number
    • product name is dimension data
      • because it doesn’t change often
    • data related to the specific orders (such as quantity ordered) is fact data
      • because it is continually changing
  • three primary types of slowly changing dimensions:
    1. slowly changing dimension type 1
      • overwrites the existing value in a field of data with a new value
      • does not retain the history of that field
    2. slowly changing dimension type 2
      • adds a new row for the new value
      • maintains the historical record
    3. slowly changing dimension type 3
      • adds a new column for the current value
      • but also retains the original column with the original value