4 Replies Latest reply: Aug 14, 2014 12:17 PM by Nishikant-Oracle RSS

    SCM on ODI12c

    MyOAF

      I am trying to create a simple, straight-forward slowly changing dimension mapping. I have set the integration type as SCM  (please see the attached file for screen shots for set up and error). The mapping fails with the below error:

       

      ODI-1228: Task IKM Oracle Slowly Changing Dimension (Insert flow into I$ table) fails on the target ORACLE connection SSS.

      Caused By: java.sql.SQLSyntaxErrorException: ORA-00936: missing expression

       

      This happens when the mapping tries to load the interface table and the code generated by the ODI doesn't include all the columns of the dimension table. Not sure why the code generated by ODI is missing the columns name in the insert statement.

       

      INSERT /*+ APPEND */

      INTO DW_WORK.I$_W_SCMDIM_D

        ( IND_UPDATE

        )

      SELECT *

      FROM

        (SELECT 'I' IND_UPDATE

        FROM DW_SRC.CODE_DETL CODE_DETL

        WHERE (1                =1)

        AND (CODE_DETL.CATGRY_ID=72)

        ) S

      WHERE NOT EXISTS

        ( SELECT 'x' FROM DW_TRG.W_SCMDIM_D T WHERE

        )

        • 1. Re: SCM on ODI12c
          Nishikant-Oracle

          Hi

          You have not given the snapshot of the columns showing their SCD behavior (surrogate key, natural key, add row on change, update on change, ...).

          Have you set the SCD behavior for all the columns for the Target datastore?

           

          Regards

          Nishikant

          • 2. Re: SCM on ODI12c
            MyOAF

            I did have set the SCD behaviour for all the columns - will upload the screen print when I get back to my work laptop. Thanks for looking into this.

            • 3. Re: SCM on ODI12c
              Nishikant-Oracle

              Sure. I have been using ODI 12.1.2 for some months for loading dimensions, but haven't faced any issue yet.

              What is the version you are using, is it 12.1.2 or 12.1.3?

               

              Regards

              Nishikant

              • 4. Re: SCM on ODI12c
                Nishikant-Oracle

                May I request you to try something?

                Please change the IKM from SCD to simple Control Append and execute this Mapping. This is just to make sure that the error comes from using the SCD KM

                 

                If it doesn't fail in Control Append, then also upload:

                - snapshot of the physical flow

                - snapshot of the operator (steps which are generated and error step)

                 

                I see that you have deleted the default source area, but I don't think that the error is related to it.

                 

                This error has something to do with ODI not being able to relate the 3 mapped columns and the source table with the staging area (I$).