Monday, 8 December 2014
Wednesday, 3 December 2014
What is Simulation in Oracle Data Integrator ?
---------------------
Simulation is a option for to know complete background process report for Interfaces,
Procedures and Packages. Simulation will give us complete report for execution process like
Each step and stak information, SQL qeuries for your source and target commands...
but simulation is not creating any session and it is not transforring any data from source taget.
Tuesday, 5 August 2014
odi-20730: There are critical issues still outstanding, please fix all critical issues.
odi-20730: There are critical issues still outstanding, please fix all critical issues.
Solution:
This is an common issue with if are using smart import. While using smart import we need to select action as
"Merge" or "Overwrite" or "Ignore" . It will throw this error if objects are already exits in repository.
Solution:
This is an common issue with if are using smart import. While using smart import we need to select action as
"Merge" or "Overwrite" or "Ignore" . It will throw this error if objects are already exits in repository.
Sunday, 3 August 2014
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)
);
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
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
)
(
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
Saturday, 2 August 2014
How To Change Slowly Changing Dimension Behavour in All Columns at a time other than Column=>Description tab
How To Change Slowly Changing Dimension Behavior in All Columns at a time other than Column=>Description tab
One of my friend asked me this SCD Behavior changes in ODI 11G. 12C SCD Behavor changes at column level is very easy. We can Open table and change at column level directly. But in 11G We have to go for individual column and Description tab and need to change SCD Behavior.
In He project almost all Dimension tables having more than 200 Columns. It is very difficult to go to individual column=>description Tan and changing the SCD Behavior.
Here is an example for SCD Behavor changes at Repository Table Level.
Step1: All Your Model tables we can find Work Repository Tables.
SNP_TABLE: All your tables
SNP_COL: All columns
Step2: Using Below query we can find the all columns for SCD2 table.
Default SCD_COL_TYPE value will be null. Whenever we are changing the Column SCD behavior it will update the value in this column as below values.
SELECT
SNP_COL.COL_NAME,
SNP_TABLE.TABLE_NAME,
SNP_COL.SCD_COL_TYPE
FROM SNP_COL col,
SNP_TABLE tab
WHERE tab.i_table=col.i_table
AND table_name ='SCD2';
Step3: SCD2 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
);
Using Below Query We can Change the Above SCD Behavior.
UPDATE SNP_COL
SET SCD_COL_TYPE='OC'
WHERE COL_NAME in ('ENAME','JOB','MGR','HIREDATE','DEPTNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='IR'
WHERE COL_NAME in ('COMM','SAL')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='SK'
WHERE COL_NAME in ('DIM_EMP_ID')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='NK'
WHERE COL_NAME in ('EMPNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='CR'
WHERE COL_NAME in ('FLAG_STATUS')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='ST'
WHERE COL_NAME in ('START_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='ET'
WHERE COL_NAME in ('END_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
Compare to 11G, 12C 12.1.2 or 12.1.3 very easy for changing SCD Behavior
One of my friend asked me this SCD Behavior changes in ODI 11G. 12C SCD Behavor changes at column level is very easy. We can Open table and change at column level directly. But in 11G We have to go for individual column and Description tab and need to change SCD Behavior.
In He project almost all Dimension tables having more than 200 Columns. It is very difficult to go to individual column=>description Tan and changing the SCD Behavior.
Here is an example for SCD Behavor changes at Repository Table Level.
Step1: All Your Model tables we can find Work Repository Tables.
SNP_TABLE: All your tables
SNP_COL: All columns
Step2: Using Below query we can find the all columns for SCD2 table.
Default SCD_COL_TYPE value will be null. Whenever we are changing the Column SCD behavior it will update the value in this column as below values.
| SCD_COL_TYPE | SCD Meaning |
| SK | Surrogate Key |
| NK | Natural Key |
| OC | Overwrite On Change |
| IR | Add Row On Change |
| OC | Overwrite On Change |
| CR | Current Record Flag |
| ST | Starting Timestamp |
| ET | Ending Timestamp |
SELECT
SNP_COL.COL_NAME,
SNP_TABLE.TABLE_NAME,
SNP_COL.SCD_COL_TYPE
FROM SNP_COL col,
SNP_TABLE tab
WHERE tab.i_table=col.i_table
AND table_name ='SCD2';
Step3: SCD2 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
);
Using Below Query We can Change the Above SCD Behavior.
UPDATE SNP_COL
SET SCD_COL_TYPE='OC'
WHERE COL_NAME in ('ENAME','JOB','MGR','HIREDATE','DEPTNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='IR'
WHERE COL_NAME in ('COMM','SAL')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='SK'
WHERE COL_NAME in ('DIM_EMP_ID')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='NK'
WHERE COL_NAME in ('EMPNO')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='CR'
WHERE COL_NAME in ('FLAG_STATUS')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='ST'
WHERE COL_NAME in ('START_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
UPDATE SNP_COL
SET SCD_COL_TYPE='ET'
WHERE COL_NAME in ('END_DATE')
AND I_TABLE= (SELECT I_TABLE FROM SNP_TABLE WHERE TABLE_NAME='SCD2');
COMMIT;
Compare to 11G, 12C 12.1.2 or 12.1.3 very easy for changing SCD Behavior
ODI 12c 12.1.2 Export issue fixing with bug : Patch 17671595
Oracle Data Integrator - Version 12.1.2.0.0 to 12.1.2.0.1 [Release 12c]
Exporting an ODI 12c 12.1.2 Work Repository using the standard export tool fails with the following error:
java.lang.RuntimeException: java.lang.NullPointerException
at com.sunopsis.dwg.smartie.SmartExportOrderList$1$1.doAction(SmartExportOrderList.java:561)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at com.sunopsis.dwg.smartie.SmartExportOrderList$1.doInTransaction(SmartExportOrderList.java:551)
at oracle.odi.core.persistence.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:99)
at com.sunopsis.dwg.smartie.SmartExportOrderList.printExportObjects(SmartExportOrderList.java:545)
at com.sunopsis.dwg.smartie.SmartExportUtils.exportToXml(SmartExportUtils.java:2889)
at com.sunopsis.dwg.smartie.SmartExportUtils.exportToXml(SmartExportUtils.java:2763)
at oracle.odi.impexp.smartie.impl.DependencyServiceImpl.exportToXml(DependencyServiceImpl.java:429)
at oracle.odi.ui.smartie.exp.ExportSmartDialog.exportToXml(ExportSmartDialog.java:1462)
at oracle.odi.ui.smartie.exp.ExportSmartDialog$1.doInBackground(ExportSmartDialog.java:1252)
at oracle.odi.ui.smartie.exp.ExportSmartDialog$1.doInBackground(ExportSmartDialog.java:1220)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:920)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.lang.NullPointerException
at com.sunopsis.dwg.dbobj.SnpMcConstraint.getForeignKeyLst(SnpMcConstraint.java:104)
at com.sunopsis.dwg.ImpExpFkXrefManager.addFkXRefs(ImpExpFkXrefManager.java:164)
at com.sunop
at com.sunopsis.dwg.smartie.SmartExportOrderList$1$1.doAction(SmartExportOrderList.java:561)
at oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
at com.sunopsis.dwg.smartie.SmartExportOrderList$1.doInTransaction(SmartExportOrderList.java:551)
at oracle.odi.core.persistence.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:99)
at com.sunopsis.dwg.smartie.SmartExportOrderList.printExportObjects(SmartExportOrderList.java:545)
at com.sunopsis.dwg.smartie.SmartExportUtils.exportToXml(SmartExportUtils.java:2889)
at com.sunopsis.dwg.smartie.SmartExportUtils.exportToXml(SmartExportUtils.java:2763)
at oracle.odi.impexp.smartie.impl.DependencyServiceImpl.exportToXml(DependencyServiceImpl.java:429)
at oracle.odi.ui.smartie.exp.ExportSmartDialog.exportToXml(ExportSmartDialog.java:1462)
at oracle.odi.ui.smartie.exp.ExportSmartDialog$1.doInBackground(ExportSmartDialog.java:1252)
at oracle.odi.ui.smartie.exp.ExportSmartDialog$1.doInBackground(ExportSmartDialog.java:1220)
at oracle.odi.ui.framework.AbsUIRunnableTask.run(AbsUIRunnableTask.java:258)
at oracle.ide.dialogs.ProgressBar.run(ProgressBar.java:920)
at java.lang.Thread.run(Thread.java:744)
Caused by: java.lang.NullPointerException
at com.sunopsis.dwg.dbobj.SnpMcConstraint.getForeignKeyLst(SnpMcConstraint.java:104)
at com.sunopsis.dwg.ImpExpFkXrefManager.addFkXRefs(ImpExpFkXrefManager.java:164)
at com.sunop
Friday, 1 August 2014
Get Physical Schema name using odiref in ODI
Using below method we can get Database SchemaName.
select * from all_tables where
owner= '<%=odiRef.getSchemaName()%>'
Using below method we can get Database SchemaName for Default Physical Schema from your Databaserver.
select * from all_tables where
owner= '<%=odiRef.getSchemaNameDefaultPSchema()%>
'
Subscribe to:
Posts (Atom)