Dt: 15 March 2017
Most of you must have designed an ETL extract for a slowly changing type 2 dimension .
However how do you manage the first load as it will populate all the existing active dimensions.
Today while we were discussing the design we came across an interesting dilemma.What should be the date in the EFFECTIVE FROM DATE columns for the first record (version) of a dimension loaded into the warehouse table.
Typically most ETL tools provide a mapping which will set the EFFECTIVE FROM DATE for the first version of the dimension as the SYSDATE when the dimension table was first loaded.
Although this is not incorrect but when you read the data in the dimension table it seems as if the first known version of the dimension only existed from the date when you first loaded the warehouse table.
A different approach to this is to update the EFFECTIVE FROM DATE column to an early date( 01/01/1900 ) just to denote that this is the first known active record for this table.
This is what I would prefer.Let me know your thoughts on this .
Most of you must have designed an ETL extract for a slowly changing type 2 dimension .
However how do you manage the first load as it will populate all the existing active dimensions.
Today while we were discussing the design we came across an interesting dilemma.What should be the date in the EFFECTIVE FROM DATE columns for the first record (version) of a dimension loaded into the warehouse table.
Typically most ETL tools provide a mapping which will set the EFFECTIVE FROM DATE for the first version of the dimension as the SYSDATE when the dimension table was first loaded.
Although this is not incorrect but when you read the data in the dimension table it seems as if the first known version of the dimension only existed from the date when you first loaded the warehouse table.
A different approach to this is to update the EFFECTIVE FROM DATE column to an early date( 01/01/1900 ) just to denote that this is the first known active record for this table.
This is what I would prefer.Let me know your thoughts on this .
