5 Replies Latest reply on May 14, 2013 4:51 PM by Veeresh Rayan

    What is  Integration_id in informatica?

    Ashish Gupta
      Integration_id is populated by SDE mapping, but what exactly is the use of it.

      My DAC tasks are failing on Create index stuff

      Error message is below.

      ANOMALY INFO::: Error while executing : CREATE INDEX:W_EAM_WORKORDER_F:W_EAM_WORKORDER_F_U1
      MESSAGE:::java.lang.Exception: Error while execution : CREATE UNIQUE INDEX
           W_EAM_WORKORDER_F_U1
      ON
           W_EAM_WORKORDER_F
      (
           INTEGRATION_ID Asc
           ,DATASOURCE_NUM_ID Asc
           ,ETL_PROC_WID Asc
      )
      NOLOGGING
      with error DataWarehouse:CREATE UNIQUE INDEX
           W_EAM_WORKORDER_F_U1
      ON
           W_EAM_WORKORDER_F
      (
           INTEGRATION_ID Asc
           ,DATASOURCE_NUM_ID Asc
           ,ETL_PROC_WID Asc
      )
      NOLOGGING
      ORA-01452: cannot CREATE UNIQUE INDEX; duplicate keys found

      Thanks
      Ashish
        • 1. Re: What is  Integration_id in informatica?
          Veeresh Rayan
          Most of the time integration id's are the primary keys in source system.
          They will be used to differetiate unique record in the table.

          Whenever you face such issues it means that you might have duplicate entries otherwise you need to relook
          into your configuration

          Temporary fix:
          Identify duplicates using below sql and try to delete them

          select
          INTEGRATION_ID,
          DATASOURCE_NUM_ID,
          ETL_PROC_WID,
          count(*)
          from W_EAM_WORKORDER_F group by INTEGRATION_ID,DATASOURCE_NUM_ID,ETL_PROC_WID having count(*)>1

          Delete from W_EAM_WORKORDER_F where integration_id =?,datasource_num_id=?,ETL_Proc_wid=?

          But It is not advised to delete these entries which may lead to data mis match.

          So identfy duplicates and try to fix them.

          mark correct or helpful if it helps,

          Regards,
          Veeresh Rayan
          1 person found this helpful
          • 2. Re: What is  Integration_id in informatica?
            Ashish Gupta
            Do we have a way to ignore the tasks or ignore the errors in DAC and continue with the rest of the steps ?

            Also i found out this ...

            The required staging table columns, DATASOURCE_NUM_ID and INTEGRATION_ID are loaded into the staging table.

            INTEGRATION_ID stores the primary key or the unique identifier of a record in the source table and DATASOURCE_NUM_ID stores the datasource from which the data is extracted. These required columns are used by SIL mapping to generate the surrogate key for OBAW.

            Thanks
            • 3. Re: What is  Integration_id in informatica?
              Veeresh Rayan
              Nope..You cant ignore..Each one of the tasks have priorities assigned to them and run based on the parent tasks run's.

              Regards,
              Veeresh Rayan
              • 4. Re: What is  Integration_id in informatica?
                Ashish Gupta
                One of my colleague told me to go to Design view and then inactivate that index through indices tab.
                I did that and the load completed successfully.

                Now just wanted to understand, what would be the repurcussions of it?

                Thanks
                Ashish
                • 5. Re: What is  Integration_id in informatica?
                  Veeresh Rayan
                  Thats not a good practice..You are not suppose to inactivate index.

                  There might be implications on reports giving you wrong results(Because of duplicate entries) and running slow..

                  Try to identify the duplicate entries and then fix instead of bypassing the issue.

                  Regards,
                  Veeresh Rayan