You can achieve this by adding a new field, which will hold the old Employee_identifier( 7-digit identifier) in both source and target.
Lookup will happen on new Employee_identifier (9-digit identifier). Both the Employee_identifiers(Old and new) has to be populated till the target, as old Employee_identifier will be used to do update(ie link old Employee_identifier -> new Employee_identifier in the target) for the update flow.
Apart from following changes, rest of the design will remain same:
1. Addition of new field to both source and target.
2. Population of old Employee_identifier till the target and update based on its value.
Hope this helps.
Lekha G M
Thank you for your reply. Old identifier(7-digit) will no longer come from the source. Only the new identifier(9-digit) will come. It will be really helpful if you can please elaborate a bit more on the solution. Thanking you once again.
If I understand you correctly the business key to identify the employee will change.
Then basically you will have some sort of conversion of the key in the source.
You would need to have the same conversion on your data warehouse.
I would go for adding a field to preserve the old value and perform the conversion which will happen in the source as well on the data warehouse (new records will only get the new key and the additional field to preserve the old key can either be null or a default value).
This is assuming in your data warehouse the functional or business key is not nullable.
1. If the source is internal (maintained by you), you can add an additional field to hold old_Employee_identifier(7 digits). So ultimately the source has both old_Employee_identifier(7 digits) and new_Employee_identifier(9 digits).
2. In lookup transformation, do a lookup based on new Employee_identifier (Lookup condition: new_Employee_identifier=Target_Employee_identifier)
3. Router transformation will have 3 groups:
-Group1-Insert- ISNULL(Target_Employee_identifier) AND ISNULL(old_Employee_identifier) //Regular insert of new employee for whom the old_Employee_identifier would be NULL
-Group2-Update- NOT ISNULL(Target_Employee_identifier) AND DECODE(EMAIL,TGT_EMAIL,1,0)=0 //Regular update for updating attributes other than Employee_identifier. eg:EMAIL
-Group3-Update- ISNULL(Target_Employee_identifier) //Update of Employee_identifier
- Regular insert will have no change.
- Regular update also will have no changes. 2 update strategys (one to insert, other to update the effective end date/flag), link new_Employee_identifier->Target_Employee_identifier for update
- New flow to update the Employee_identifier . 2 update strategys(one to insert, other to update the effective end date/flag), link old_Employee_identifier->Target_Employee_identifier for update
Note: Small change to my previous comment, there is no need to add additional field to the target. But you might have to change the precision of Employee_identifier to store 9 digits.
Hope this helps.!
Thank you so much for your reply, Lekha. The source (an external team) will send only the new identifiers. But the most recent update I have from them is that they will provide a mapping between old and new identifiers. This mapping will help to implement. Your reply has given me a lot of hints too. Thank you once again!
Thank you so much for the reply. The conversion that you mentioned is being provided by the source team as a mapping between the old and new ids. With that, I can build a relationship between those and implement. Thank you for the hints! Thank you once again!