4 Replies Latest reply: Mar 1, 2014 9:27 AM by Saro RSS

    DML Operation as a part of scheduling a load

    Saro

      Hi friends,

       

      Im in a doubt while scheduling a load using ETL Tool. Firstly if we schedule any load plan in the ETL Tool then while it is loading as a first time in DWH it will do full load and from the second time onwards it will do an incremental load.

       

      But im sure that the INSERT and UPDATE process will happen from source to target if any records are inserted/updated in the source. But my doubt is regarding deletion.

       

      Suppose if we delete any records in the source then the same record will be deleted in the target as a part of the load scheduling. Kindly clarify my doubt friends.

       

      Thanks in advance.

       

      Regards,

      Saro

        • 1. Re: DML Operation as a part of scheduling a load
          FPonte

          Hi.

           

          No. It will depend on the IKM you are using. If its customized or not.

           

          The existing ones will just do an Incremental Update (INSERT/UPDATE).

           

          The reason is:

          If you are performing an Incremental Update it means that you are bringing from the source just what was inserted and updated. You are not bringing the whole source table.

           

          Example: Lets say that your incremental filter in a SourceTable.UPDATED_DATE attribute brings only 10% or rows (updated and inserted). This set of data doesn't represent the whole source table that you initialy loaded. If you have a delete option against this source set you will end up with just 10% of rows in your target table. ODI will insert new rows, update existing ones and delete the rows he didn't find in the extracted set. Right?

          If you need to fully synchronize the source and the target just truncate and insert.

           

          My example is:

          In my case I perform and Incremental Update in one table (to big and wide to perform full load everyday) and I have ANOTHER interface to FLAG the deleted rows from the source using just the primary key. We only logicaly exclude the row. Doing this, from time to time we archive that data.

           

          You may flag first and delete using a simple DELETE statement where your FLAG = 'D', for example.

           

          Hope this help.

           

          Cheers.

          • 2. Re: DML Operation as a part of scheduling a load
            Saro

            Thanks ponte, This answered my query.

             

            Hence my understanding from your saying is that "Insert/update will happen defaultly as a part of full/incr load. But it will not perform delete operations in the target defaultly, unless and until if we have some delete flag kind of logic defined in the interface.

             

            Regards,

            Saro

            • 3. Re: DML Operation as a part of scheduling a load
              FPonte

              Hi Saro.

               

              Yes. The default is that the existing Incremental/Update IKM will only perform INSERTs and UPDATEs.

               

              To execute a DELETE you will have to perform in another way as I explained in my example.

              One Interface to perform and Incremental Update and another to FLAG the deleted columns. After flagged the columns, you can delete using a simple delete statement inside an ODI procedure.

               

              Cheers.

              • 4. Re: DML Operation as a part of scheduling a load
                Saro

                Thanks ponte.

                 

                Regards,

                Saro