9 Replies Latest reply: Apr 8, 2014 11:31 AM by user12005706 RSS

    ODI SCD2

    user12005706

      Hi , I have an question regarding for SCD 2 , I am using ODI KM "IKM Oracle Slowly Changing Dimension"

      Can someone please let me know how can i handle multiple changes to the record on the same day ?

      I am capturing the source changes into the staging table but while loading Dimension how can i end oldest record if there are 2 records on the same day using the above IKM ?

      Any inputs are appreciated ? Thank you.

        • 1. Re: ODI SCD2
          VINAY_B

          Hi ,

           

          Please use current_record_flag from the IKM to identify latest record.

          current_record_flag is a single numeric column mapped to 0 or 1 , representing the status of the Record.

          It is automatically updated by the Knowledge Module (1 : Current Record ; 0 : Past Records)

           

           

          Thanks

          Vinay B

          • 2. Re: ODI SCD2
            user12005706

            Thanks for the reply i Understand that,but how to handle 2 records with the changes in the same load.

            If you see the IKM SCD2 and step "Historize old rows" will fail because the query returns 2 rows.

            I can limit the query to pull the latest record however it doesn't satisfy my requirement i have to get the 2 records and identify the old record and out of these two records and update it and set the current_record_flag to inactive and the pick up the new record and set the current_record_flag to active.

            • 3. Re: ODI SCD2
              Phanikanth

              Hi

               

              Hope below URL will give complete details about SCD Type2

               

              http:// dwteam.in/scd-type2-in-odi/

               

              Regards,

              Phanikanth

              • 4. Re: ODI SCD2
                user12005706

                Hi Phanikanth,

                 

                Thanks for your reply, Link has great information on how to implement SCD2 with standard KM which comes with ODI. However Standard Km which comes with ODI doesn't satisfy my requirement since it doesn't handle multiple changes on the same record.

                Can you please let me know if there is any other way how to handle multiple changes on the same record in ODI.

                 

                For ex:         

                Source Table                Target SCD2 (Dimension Table)

                Day 1 :  A B C                1(Surrogate Key) A B C  'Y'(Active Indicator) '04/08/2014'(Start date)  '01/01/2400'(End Date)

                Day 2 :  A D E

                Day 2 :  A F G

                 

                Note : A is the Natural Key

                 

                On day 2 when the source record changes twice (as shown above) ODI should be able recognize both the records and End one record and make other record active in Dimension table as shown below, On day 2 my dimension should look like as below.

                Can you please let me know if there is standard way how to handle multiple changes on the same record or do i have to customize KM?

                 

                Source Table                Target SCD2 (Dimension Table)

                 

                Day 1 :  A B C                1(Surrogate Key) A B C  'N'(Active Indicator) '04/08/2014'(Start date)  '04/09/2014'(End Date)

                Day 2 :  A D E               2(Surrogate Key) A D E  'N'(Active Indicator) ''04/09/2014'(Start date)  ''04/09/2014'(End Date)

                Day 2 :  A F G                3(Surrogate Key) A F G  'Y'(Active Indicator) ''04/09/2014'(Start date)  ''01/01/2400'(End Date)




                Thanks you.

                • 5. Re: ODI SCD2
                  Phanikanth

                  Hi

                   

                  If this is helpful, you can mark this as answered or like so it is helpful for other people as well

                   

                  Regards

                  Phanikanth

                  • 6. Re: ODI SCD2
                    user12005706

                    URL you provided is helpful for standard SCD2 implementations, Can you please check my above response and let me know if you have any ideas how we can implement this ? http:// dwteam.in/scd-type2-in-odi/

                    • 7. Re: ODI SCD2
                      Phanikanth

                      Hi

                       

                      If you need the multiple changes reflection, then you need to do the changes on Slowly changing diamention behaviour for each and every column (as per your requirement) on target table to fulfill your requirement and the above link for SCD type2 understanding. If you understan the SCD type2 then you can convert the KM as per your requirement.

                       

                      Regards,

                      Phanikanth

                      • 8. Re: ODI SCD2
                        Prerna Vij

                        Hi,

                         

                        We had a similar requirement wherein we had two records coming in on the same day. We wanted the expiry date of the latest one to be set as null and the other one to be set as the effective date of the latest -1.

                         

                        IKM Oracle for Slowly Changing Dimension would not be able to handle this as you rightly said. We handled it using LEAD function in Oracle which is used to return data from the next row in the mapping for EXPIRY Date.

                         

                        You can tweak the KM as well if needed.

                         

                        Do let me know in case you find a different and more effective approach of handling this.

                         

                        Thanks,

                        Prerna

                        • 9. Re: ODI SCD2
                          user12005706

                          Thank you prerna Vij, That's exactly where I was heading to...using Oracle analytical functions and customizing the KM to detect the records and expire them. Will surely let you know if I find a better approach. Thank you.