Type 3 Slowly Changing Dimension




Data Warehousing > Concepts > Type 3 Slowly Changing Dimension

In Type 3 Slowly Changing Dimension, there will be two columns to indicate the particular attribute of interest, one indicating the original value, and one indicating the current value. There will also be a column that indicates when the current value becomes active.

In our example, recall we originally have the following table:

Customer Key Name State
1001 Christina Illinois

To accommodate Type 3 Slowly Changing Dimension, we will now have the following columns for our dimension table:

  • Customer Key
  • Name
  • Original_State
  • Current_State
  • Effective_Date

After Christina moved from Illinois to California, the original information gets updated, and we have the following table (assuming the effective date of change is January 15, 2003):

Customer Key Name Original_State Current_State Effective_Date
1001 Christina Illinois California 2020-01-15

Advantages:

- This does not increase the size of the table, since new information is updated.

- This allows us to keep some part of history.

Disadvantages:

- Type 3 will not be able to keep all history where an attribute is changed more than once. For example, if Christina later moves to Texas on December 15, 2003, the California information will be lost.

Usage:

Type 3 is rarely used in actual practice.

When to use Type 3:

Type III slowly changing dimension should only be used when it is necessary for the data warehouse to track historical changes, and when such changes will only occur for a finite number of time.







Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy   About   Contact




AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.