When extracting a Dimension record, and a SCD1 field changes, the Extractor class should reflect these changes on all records from the same business entity.
Example:
Suppose the following patient_dimension at the DW where patient_name is SCD1 and patient_weight is SCD2:
| patient_sk |
patient_bk |
patient_name |
patient_weight |
valid_from |
valid_until |
| 32145 |
101 |
Joseph Cliver |
78 Kg |
1900-01-01 00:00:00 |
2016-02-20 12:44:58 |
| 35158 |
101 |
Joseph Cliver |
83 Kg |
2016-02-20 12:44:58 |
2100-12-31 23:59:59 |
If a new record is extracted where the only changes are on SDC1 fields, all DW records for the same business entity should be updated. No new record should be created at the DW.
Ex, Joseph Cliver changes his name to J. Cliver:
| patient_sk |
patient_bk |
patient_name |
patient_weight |
valid_from |
valid_until |
| 32145 |
101 |
J. Cliver |
78 Kg |
1900-01-01 00:00:00 |
2016-02-20 12:44:58 |
| 35158 |
101 |
J. Cliver |
83 Kg |
2016-02-20 12:44:58 |
2100-12-31 23:59:59 |
If both SDC1 and SCD2 fields changes, a new record needs to be created, and all other fields from the same business entity should have it's SDC1 attribute updated:
Ex. J. Cliver change his name to JC and gets fatter:
| patient_sk |
patient_bk |
patient_name |
patient_weight |
valid_from |
valid_until |
| 32145 |
101 |
JC |
78 Kg |
1900-01-01 00:00:00 |
2016-02-20 12:44:58 |
| 35158 |
101 |
JC |
83 Kg |
2016-02-20 12:44:58 |
2016-04-01 22:18:12 |
| 45855 |
101 |
JC |
88 Kg |
2016-04-01 22:18:12 |
2100-12-31 23:59:59 |
When extracting a Dimension record, and a SCD1 field changes, the Extractor class should reflect these changes on all records from the same business entity.
Example:
Suppose the following
patient_dimensionat the DW wherepatient_nameis SCD1 andpatient_weightis SCD2:If a new record is extracted where the only changes are on SDC1 fields, all DW records for the same business entity should be updated. No new record should be created at the DW.
Ex, Joseph Cliver changes his name to J. Cliver:
If both SDC1 and SCD2 fields changes, a new record needs to be created, and all other fields from the same business entity should have it's SDC1 attribute updated:
Ex. J. Cliver change his name to JC and gets fatter: