The Type 1 SCD overwrites old data with new data, and therefore does not track historical data. This is useful for making changes to dimension data. This is the default type of dimension that is created. You do not need to specify any additional information to create a Type 1 SCD.
In this example, N_PRODUCT_SKEY is the surrogate key column which is a unique key for each record in the dimension table. V_PRODUCT_NAME is the product name. D_START_DATE indicates the date from which this product record is valid.
|
N_PRODUCT_ SK EY |
V_PRODUCT_ NAME |
D_START_DATE |
D_END_DAT E |
F_LATEST_RECORD_ INDICATOR |
|
1 |
PL |
5/31/2010 |
12/31/9999 |
Y |
D_END_DATE indicates the date until which this product record is valid.
F_LATEST_RECORD_INDICATOR with value 'Y', which indicates that this is the latest record in the dimension table for this product and 'N' indicates it is not. If the V_PRODUCT_NAME column is set as a Type 1 SCD column and if there is a change in the product name to 'Personal Loan' from 'PL' in the above example; in the next processing period, when SCD is executed for the new processing period, the record in the above example changes to:
|
N_PRODUCT_ SK EY |
V_PRODUCT_ NAME |
D_START_DATE |
D_END_DAT E |
F_LATEST_RECORD_ INDICATOR |
|
1 |
Personal Loan |
6/30/2010 |
12/31/9999 |
Y |