loading History data
Posted: Wed Jan 20, 2010 3:28 pm
HI All,
I have to load history data into person dimension table from 3 sources.
Data is like this
table1
emplid effdt name
xxxx 01/jan/2001 abc
xxxx 06/jan/2001 abcd
emplid and effdt are key columns
table2
emplid effdt marital_status
xxxx 01/jan/2001 U
xxxx 04/jan/2001 M
emplid and effdt are key fields
in the target table data should be like this
sid emplid effdt name marital_status
1 xxxx 01/jan/2001 abc U
2 xxxx 04/jan/2001 abc M
3 xxxx 06/jan/2001 abcd M
in the target table SID is the key
Here I can't load the data based on just emplid . the effective dates are all different.
Can you please advice me how to develop ETL job or query
Thanks
I have to load history data into person dimension table from 3 sources.
Data is like this
table1
emplid effdt name
xxxx 01/jan/2001 abc
xxxx 06/jan/2001 abcd
emplid and effdt are key columns
table2
emplid effdt marital_status
xxxx 01/jan/2001 U
xxxx 04/jan/2001 M
emplid and effdt are key fields
in the target table data should be like this
sid emplid effdt name marital_status
1 xxxx 01/jan/2001 abc U
2 xxxx 04/jan/2001 abc M
3 xxxx 06/jan/2001 abcd M
in the target table SID is the key
Here I can't load the data based on just emplid . the effective dates are all different.
Can you please advice me how to develop ETL job or query
Thanks