Automatically fill fields `valid_from` and `valid_until` when extracting a SCD2 dimension

[rfvoltolini]

When a SCD2 dimension get extracted, the Extractor could automatically fill the fields valid_from and valid_until if they are present.

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
-	-	-	-	-	-

I believe that the following rules should be applied:

• When the extractor gets a new record, it fills valid_from and valid_until with a constant MIN_DATE_TIME and MAX_DATE_TIME respectively. Ex:

patient_sk	patient_bk	patient_name	patient_weight	valid_from	valid_until
32145	101	Joseph Cliver	78 Kg	1900-01-01 00:00:00	2100-12-31 23:59:59

• When one or more records already exists at the DW for that business record, and a change is detected on a SCD2 attribute: the last record inserted on the DW for that business entity should have it's valid_until field updated to the current (server's) date time. The new DW record get it's valid_from filled with the current date time, and it's valid_until field filled with MAX_DATE_TIME. Ex (Joseph Cliver got fatter):

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

And in case a new record gets added:

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	2016-04-01 22:18:12
45855	101	Joseph Cliver	88 Kg	2016-04-01 22:18:12	2100-12-31 23:59:59