Implementing SCD Type 2 and Flow Control in ODI 11G or 12c

Implementing SCD Type 2 and Flow Control in ODI 11G or 12c.

Solution for insert PK errors in Control step. 




































ODI-1228: Task SCD2 (Control) fails on the target ORACLE connection TARGET_DS.
Caused By: java.sql.SQLSyntaxErrorException: ORA-00904: "SUB"."DIM_EMP_ID": invalid identifier

In SCD2 table DIM_EMP_ID Surrogate key data we are inserting using Sequence. This column data
we are not receiving from source, directly we are inserting using Sequence.  But In Flow Control
It will considar Data Quality check at E$Table.

insert into TARGET."E$_15022SCD2"
(
    ODI_PK,
    ODI_SESS_NO,
    ODI_ROW_ID,
    ODI_ERR_TYPE,
    ODI_ERR_MESS,
    ODI_ORIGIN,
    ODI_CHECK_DATE,
    ODI_CONS_NAME,
    ODI_CONS_TYPE,
    EMPNO,
    ENAME,
    JOB,
    MGR,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO,
    FLAG_STATUS,
    START_DATE,
    END_DATE
)
select    SYS_GUID(),
    15022,
    rowid,
    'F',
    'ODI-15064: The primary key PK_DIM_ID is not unique.',
    '(5022)DEV.In SCD2 001',
    sysdate,
    'PK_DIM_ID',
    'PK',   
    SCD2.EMPNO,
    SCD2.ENAME,
    SCD2.JOB,
    SCD2.MGR,
    SCD2.HIREDATE,
    SCD2.SAL,
    SCD2.COMM,
    SCD2.DEPTNO,
    SCD2.FLAG_STATUS,
    SCD2.START_DATE,
    SCD2.END_DATE
from    TARGET."I$_15022SCD2"   SCD2
where    exists  (
        select    SUB.DIM_EMP_ID
        from     TARGET."I$_15022SCD2" SUB
        where     SUB.DIM_EMP_ID=SCD2.DIM_EMP_ID
        group by     SUB.DIM_EMP_ID
        having     count(1) > 1
        )

Step 1:  
But we need to Consider Source table SK as for our Flow Control UK. We need to Add  Natural Key (Alternate Key)  for EMPNO at SCD2 table and We need to disable SK in Control Tab.

Step2:
Adding Alternate Key at Model level for EMPNO column SCD2 Table.

Step3:  Changing CKM Oracle KM Creating Error Table Step and Saving as new KM
CKM Oracle SCD2
 

SCD Type 2 Table Structure.

CREATE TABLE SCD2
  (
    DIM_EMP_ID        NUMBER,  ---  Surrogate Key                                    
    EMPNO                   NUMBER(4, 0) , ---- Natural Key                           
    ENAME                   VARCHAR2(10 BYTE) , -----Overwrite On Change                            
    JOB                          VARCHAR2(9 BYTE) ,--- Overwrite On Change                                      
    MGR                        NUMBER(4, 0) ,   ---- Overwrite On Change                                       
    HIREDATE             DATE ,                 --  Overwrite On Change                                       
    SAL                          NUMBER(7, 2) ,  ---  Add Row On Change                                       
    COMM                    NUMBER(7, 2) ,   ---   Add Row On Change                                    
    DEPTNO                 NUMBER(2, 0) ,  ----  Overwrite On Change                                        
    FLAG_STATUS     VARCHAR2(1 BYTE) ,---Current Record Flag                                            
    START_DATE        DATE , ---      Starting Timestamp                                        
    END_DATE            DATE, ---       Ending Timestamp    
    PRIMARY KEY(DIM_EMP_ID)                    
  );

 

While Inserting Primary Key errors it got changed to Insert AK errors. we can see the

column got changed from DIM_EMP_ID to EMPNO.

insert into TARGET."E$_17022SCD2"
(
    ODI_PK,
    ODI_SESS_NO,
    ODI_ROW_ID,
    ODI_ERR_TYPE,
    ODI_ERR_MESS,
    ODI_ORIGIN,
    ODI_CHECK_DATE,
    ODI_CONS_NAME,
    ODI_CONS_TYPE,
    EMPNO,
    ENAME,
    JOB,
    MGR,
    HIREDATE,
    SAL,
    COMM,
    DEPTNO,
    FLAG_STATUS,
    START_DATE,
    END_DATE
)
select    SYS_GUID(),
    17022,
    rowid,
    'F',
    'ODI-15063: The alternate key AK_EMPNO is not unique.',
    '(5022)DEV.In SCD2 001',
    sysdate,
    'AK_EMPNO',
    'AK',   
    SCD2.EMPNO,
    SCD2.ENAME,
    SCD2.JOB,
    SCD2.MGR,
    SCD2.HIREDATE,
    SCD2.SAL,
    SCD2.COMM,
    SCD2.DEPTNO,
    SCD2.FLAG_STATUS,
    SCD2.START_DATE,
    SCD2.END_DATE
from    TARGET."I$_17022SCD2"   SCD2
where    exists  (
        select    SUB.EMPNO
        from     TARGET."I$_17022SCD2" SUB
        where     SUB.EMPNO=SCD2.EMPNO
        group by     SUB.EMPNO
        having     count(1) > 1
        )

NOTE: If you are using CKM SQL or CKM Sybase IQ  or CKM Teradata or other than Oracle CKM
we need follow this additional Steps for deleting Error records from E$table script.

Step1:  Enable  UD1 for Alternate Key or Natural Key ( EMPNO).

Step2: Open CKM SQL or Sybase or any other than Oracle

Open=> Delete errors from controlled table step

Change below UK and PK values to UD1 and AK

UK to UD1 and PK to AK