Effective date and expiry date is data warehousing
442336May 16 2005 — edited May 20 2005I am new to OWB. I would like to know how we update expiry dates in slowly changing dimension tables by using OWB. Suppose if my source data is as follows:
Emplid Job Action Eff. Date
---------------------------------
1000 "New Hire" 24-JUN-1999
1000 "Promotion" 27-SEP-2000
1000 "Merit" 01-JAN-2001
I would like to populate my target table like this.
Emplid Job Action Eff. Date Expiry Date
--------------------------------------------------
1000 "New Hire" 24-JUN-1999 26-SEP-2000
1000 "Promotion" 27-SEP-2000 31-DEC-2000
1000 "Merit" 01-JAN-2001 31-DEC-9999
I tried to read the source system rows in the order of emplid and eff. date desc. I wanted to pass eff. date of current row to the next row in the source system. In this way, I can easily determine expiry date of current transaction. I have a hard time to do this in OWB. I would like to know
1) Whether it is possible or not by simple mapping.
OR
2) Do I have to write any post mapping procedure?
OR
3) Is there any better way?
Your input is greatly appreciated.