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
