6 Replies Latest reply on May 12, 2021 11:52 PM by Divya Karun

    Primary Key change and SCD Type 2

    Divya Karun Active Member

      Hello Gurus,

       

      I have a dimension table with surrogate key as the primary key. Employee_identifier is the key which is used to lookup and maintain SCD Type 2 history in the table. Employee_identifer is a 7-digit number. We had been using this table for the last 4-5 years. Now, due to some organizational hierarchy changes, Employee_identifier is going to be replaced by a 9-digit number(with no similarity to the earlier 7-digit identifier). There are no other columns in the table which can be used as key for identification because all values(columns are like email_id, employment_status, address etc.) can change.

       

      What can I do if I still want to continue to maintain history for an employee(even after the employee_identifer changes)?

       

      Please help. Thanks.

        • 1. Re: Primary Key change and SCD Type 2
          Lekha G M Active Member

          Hi Divya,

           

          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.

           

          Thanks,

          Lekha G M

          • 2. Re: Primary Key change and SCD Type 2
            Divya Karun Active Member

            Hello Lekha,

             

            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.

             

            Regards,

            Divya

            • 3. Re: Primary Key change and SCD Type 2
              JanLeendert Wijkhuijs Guru

              Hi,

               

              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.

              Regards,

              JanLeendert

              • 4. Re: Primary Key change and SCD Type 2
                Lekha G M Active Member

                Hi Divya,

                 

                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

                 

                4.

                - 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.!

                 

                Thanks,

                Lekha

                • 5. Re: Primary Key change and SCD Type 2
                  Divya Karun Active Member

                  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!

                  • 6. Re: Primary Key change and SCD Type 2
                    Divya Karun Active Member

                    Hello JanLeendert,

                     

                    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!