Design issue: Dimesnion Key that changes.
Posted: Wed Jan 25, 2006 11:44 am
Hi all,
I have a dimension for which key column can change. I have SSN (Social Security Number) and employee_num that are key columns in employee dimension. We have found that we ended up getting duplicate records for same employee when ever there is change in either of columns.
Why change in SSN? Due to typo they might enter incorrect SSN. So when this is corrected it comes as a new record into data warehouse.
Why Change in Employee_num? When an employee quits and leave the company and rejoins after sometime he is assigned a new employee number. Also if an employee gets transferred to a new business unit, he is given a new employee_num. (Treated as termination + rejoin)
Can someone explain how to handle this? This is more a design problem than DataStage problem. TIA.
Thanks,
Sarath
I have a dimension for which key column can change. I have SSN (Social Security Number) and employee_num that are key columns in employee dimension. We have found that we ended up getting duplicate records for same employee when ever there is change in either of columns.
Why change in SSN? Due to typo they might enter incorrect SSN. So when this is corrected it comes as a new record into data warehouse.
Why Change in Employee_num? When an employee quits and leave the company and rejoins after sometime he is assigned a new employee number. Also if an employee gets transferred to a new business unit, he is given a new employee_num. (Treated as termination + rejoin)
Can someone explain how to handle this? This is more a design problem than DataStage problem. TIA.
Thanks,
Sarath