3 Replies Latest reply on Jan 22, 2020 1:03 AM by Rodrigo Radtke Souza

    Slowly Changing Dimension in ODI 12c - How to handle deleted rows in the source table

    3931048

      Dear All,

       

      Some rows in my target tables  are still flagged as 1/"Yes" as Current_flag, even they've been deleted in my Source table.

       

      How can I solve this issue?

       

      I'm currently using Slowly Changing Dimension as Integration Type.

       

      In advance thank's for Your answers!

       

      Olav

        • 1. Re: Slowly Changing Dimension in ODI 12c - How to handle deleted rows in the source table
          Rodrigo Radtke Souza

          ODI SCD KMs does not handle source deletes automatically. In other words, if the source table gets some rows deleted, they will still be flagged as current flag = yes in the target. You have some options to avoid this problem. If the source table does not delete the records very often, you may create a process (to run over the weekend maybe) to compare source/target table and update the missing records current flag to No. If the source deletes the records very often and you need to keep track of that, then I would suggest you to add an extra step in the SCD KM to compare the source and target tables (probably using the I$ table) and flag the current flag to No (be aware that running this in every load may be time consuming). Or you may just assume that your target table will always have a track of everything that always existed and simple dont flag them as "deleted" (this option is very used in a lot of projects).

           

          Thanks

          • 2. Re: Slowly Changing Dimension in ODI 12c - How to handle deleted rows in the source table
            3931048

            Dear Rodrigo,

             

            Thank's for Your spot-on answer. Beeing quite New to ODI, I'm wondering where I can find the code for the extra step mentionned in Your answer

             

            Olav

            • 3. Re: Slowly Changing Dimension in ODI 12c - How to handle deleted rows in the source table
              Rodrigo Radtke Souza

              Well, since this code is something that you will customize in your KM, probably it will not be something that you will just "copy and paste" and it will work. You will need to play with it and adjust it accordingly to your needs. Below I'm sending you a code that I used in a Incremental Update KM that could be used in a SCD KM with some adjustments. The code below would use "POST_FLOW" detection strategy to create a full I$ from the source and would use "UD1" to figure out which target column would be used for the soft delete detection. The KM step could would be something like this:

               

              /* SOFT_DELETE = <%=odiRef.getOption ("SOFT_DELETE")%> */

              update <%=odiRef.getTable("L", "TARG_NAME", "A")%> T

              set          <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "UD1")%> = 'Y'

                             <%=odiRef.getColList(",", "T.[COL_NAME]             = [EXPRESSION]", ",\n\t", "", "(UD2 AND REW)")%>

              where not exists (select                 'X'

                                              from      <%=odiRef.getTable("L", "INT_NAME", "A")%> T2

                                              where   <%=odiRef.getColList("", "T2.[COL_NAME]           =T.[COL_NAME]", "\n\t\t\tand\t", "","UK")%> )

              and <%=odiRef.getColList("", "T.[COL_NAME]", ",\n\t", "", "UD1")%> = 'N'

               

              One example of a code that it would generate is below:

               

              update ABSTRACT.MSTG_STUDENT T

              set          T.DELETED_FLAG = 'Y'

              where not exists (select                 'X'

                                              from      ERDW_WORK_ODI.I$_MSTG_STUDENT T2

                                              where   T2.STUDENTID           =T.STUDENTID )

              and T.DELETED_FLAG = 'N'

               

              Basically it would compare the target table with I$ table (which is a full copy of the source table due to the "POST_FLOW" detection strategy) and would "soft delete" those records.

               

              Take a look if this would help you.

               

              Thanks!