BlackSoft Consulting

ORACLE DATA INTEGRATOR (ODI) BEST PRACTICES…

DO YOU KNOW HOW FLEXIBLE ODI IS?

Gurcan Orhan (the blues man)

DWH Architect and DI Development Consultant

igorhan@gmail.com
http://gurcanorhan.wordpress.com
@gurcan_orhan
http://tr.linkedin.com/in/gurcanorhan
WHO AM I ? BlackSoft Consulting

+19 years of IT experience.

+10 years of DWH experience.
+7 years of Oracle Data Integrator experience,
+5 years of Oracle Warehouse Builder experience.

ODI, OWB, Data Warehousing experience

Sybase Power Designer, CA ERwin Data Modeler
OBIEE, Cognos, Microstrategy, Business Objects

Oracle Excellence Awards - Technologist of the Year 2011 :

Enterprise Architect
(Oracle Magazine Editors’ Choice of Awards, Enterprise Architect)

DWH & BI Chair : TROUG (Turkish Oracle User Group)

Published Customer Snapshot for NODI @Oracle.com

Published video about ODI @Oracle.com (Oracle Media Network)

Presenter in Oracle Open World since 2010 (hat-trick)

Presenter in ODTUG Kscope’11
Presenter in UKOUG, other OUGs and various universities.
MY DEFINITION OF ODI BlackSoft Consulting

ODI (Oracle Data Integrator) is a tool,

that can talk,
or learn how to talk,
with any database system,
or any operating system,
in its own language.

This is the power of ODI.

ATTENTION …!!! BlackSoft Consulting

Make sure you have;

v  Backup your repository

v  Backup your Knowledge Modules

(export, duplicate)

v  Backup your necessary ODI development

Before trying something in your environment

Remember to create a zzz_Test folder and test before apply

AGENDA BlackSoft Consulting
USE A VARIABLE WITHIN A VARIABLE BlackSoft Consulting

select count(1) from msdb.dbo.sysjobhistory

where step_id = 0
and run_status = 1
and job_id = (select job_id from msdb.dbo.sysjobs
where name = 'My_BI_Job')
and run_date = CAST(SUBSTRING('#V_SYSDATE',1,8) as integer)
USE A ODIREF FUNCTION IN A VARIABLE BlackSoft Consulting

SELECT *
FROM odiwd.snp_session sess,
odiwd.snp_step_log step,
odiwd.snp_sess_task sess_task,
odiwd.snp_exp_txt exp_txt
WHERE sess.sess_no = step.sess_no
AND sess.sess_no = sess_task.sess_no
AND step.nno = sess_task.nno
AND step.i_txt_step_mess = exp_txt.i_txt
AND step.step_status = 'E'
AND exp_txt.txt_ord = 0
AND sess.sess_no = <%=odiRef.getSession("SESS_NO")%>

SELECT NVL(MAX(ALARM_ID), 0) FROM <

%=odiRef.getSchemaName("MYDB.DWH", "D")%>.TABLE_NAME

SELECT NVL(MAX(ALARM_ID), 0) FROM DWH.TABLE_NAME

AGENDA BlackSoft Consulting
 HINTS IN ORACLE BlackSoft Consulting

Oracle’s most powerful querying attribute when in right hands.

An Oracle hint is an optimizer directive that is embedded into
an SQL statement to suggest to Oracle how the statement
should be executed.
Most common hints in a DWH system;
v APPEND
v PARALLEL
v USE_HASH
v USE_MERGE
v FULL
v INDEX
v ORDERED
v MERGE
http://psoug.org/reference/hints.html or just google «Oracle hints»
KM’S… HOW TO APPLY STATIC HINTS BlackSoft Consulting
KM’S… HOW TO APPLY SEMI-DYNAMIC HINTS BlackSoft Consulting

Step 1 : Create OPTIONS for KM’s

KM’S… HOW TO APPLY SEMI-DYNAMIC HINTS BlackSoft Consulting

Step 1 : Create OPTIONS for KM’s

KM’S… HOW TO APPLY SEMI-DYNAMIC HINTS BlackSoft Consulting

Step 2 : Insert this OPTIONS into KM’s

insert <%=odiRef.getOption("INSERT_HINT")%>
into <%=snpRef.getTable("L","TARG_NAME","A")%>

select <%=odiRef.getOption("SELECT_HINT")%> <

%=snpRef.getPop("DISTINCT_ROWS")%>
<%=snpRef.getColList("", "[EXPRESSION]\t[ALIAS_SEP]
[CX_COL_NAME]", ",\n\t", "", "")%>
from <%=snpRef.getFrom()%>

INSERT /*+ APPEND PARALLEL(t3, 8) */

INTO t3
SELECT /*+ parallel(t1) parallel(t2)
ordered use_hash(t2) index(t1 t1_abc)
index(t2 t2_abc) */ COUNT(*)
FROM t1, t2
WHERE t1.col1 = t2.col1;
KM’S… HOW TO APPLY VARIABLED DYNAMIC HINTS BlackSoft Consulting

the detailed guide

http://gurcanorhan.wordpress.com/2013/02/18/adding-variabled-hints-in-odi

v  Create table INTEFACE_HINTS to read from database

v  Add Command “Get Hints” as first command in KM

v  ‘Command on target’ to KM hints #HINT_n'
v  ‘Command on target’ to select hints from table

v  Apply hints in necessary order as you read from table to

KM’s SQL statements

v  Be sure you are using variables in right order

AGENDA BlackSoft Consulting
ADDING DATATYPES BlackSoft Consulting

Right Click è Insert Datatype

ADDING DATATYPES BlackSoft Consulting

Converted To, Converted From

AGENDA BlackSoft Consulting
 ADDING DATABASE RELATED FUNCTIONS BlackSoft Consulting

Topology Manager è Languages è SQL è Aggregate (or other)

ADDING USER FUNCTIONS BlackSoft Consulting

User Functions è Right Click è New User Function

ADDING USER FUNCTIONS BlackSoft Consulting

User Functions è Impact Analysis

AGENDA BlackSoft Consulting
ARCHIVING ODI LOGS, REQUIRED TABLES BlackSoft Consulting

Repository table list for logging of ODI stored in work

repository schema
SNP_EXP_TXT ARC_SNP_EXP_TXT

SNP_SCEN_REPORT ARC_SNP_SCEN_REPORT

SNP_SESS_STEP ARC_SNP_SESS_STEP

SNP_SESS_TASK ARC_SNP_SESS_TASK

SNP_SESS_TASK_LOG ARC_SNP_SESS_TASK_LOG
Create these tables (without
SNP_SESS_TXT_LOG referential integrity, ARC_SNP_SESS_TXT_LOG

SNP_SESSION
constraints, indexes, etc.) ARC_SNP_SESSION
with a suffix or prefix in a
SNP_STEP_LOG different schema. ARC_SNP_STEP_LOG

SNP_STEP_REPORT ARC_SNP_STEP_REPORT

SNP_TASK_TXT ARC_SNP_TASK_TXT

SNP_VAR_DATA ARC_SNP_VAR_DATA

SNP_VAR_SESS ARC_SNP_VAR_SESS
ARCHIVING ODI LOGS, REVERSE BlackSoft Consulting

Reverse all required tables in ODI

ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_EXP_TXT

SNP_EXP_TXT ARC_SNP_EXP_TXT

TRUNC(SNP_EXP_TXT.FIRST_DATE) < TRUNC(SYSDATE) - (none)

#V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SCEN_REPORT

SNP_SCEN_REPORT ARC_SNP_SCEN_REPORT

SNP_SCEN_REPORT.CONTEXT_CODE = 'PRODUCTION' AND (none)

TRUNC(SNP_SCEN_REPORT.SESS_BEG) < TRUNC(SYSDATE)
- #V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SESS_STEP

SNP_SESS_STEP ARC_SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SESS_TASK

SNP_SESS_TASK ARC_SNP_SESS_TASK
SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_SESS_TASK.SESS_NO=SNP_SESS_STEP.SESS_NO AND

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) - SNP_SESS_TASK.NNO=SNP_SESS_STEP.NNO
#V_Purge_Log_Retention
SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SESS_TASK_LOG

SNP_SESS_TASK_LOG ARC_SNP_SESS_TASK_LOG
SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_SESS_TASK_LOG.NNO=SNP_STEP_LOG.NNO AND

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) - SNP_SESS_TASK_LOG.SESS_NO=SNP_STEP_LOG.SESS_NO AND
#V_Purge_Log_Retention SNP_SESS_TASK_LOG.NB_RUN=SNP_STEP_LOG.NB_RUN

SNP_STEP_LOG.SESS_NO=SNP_SESS_STEP.SESS_NO AND
SNP_STEP_LOG.NNO=SNP_SESS_STEP.NNO

SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SESS_TXT_LOG

SNP_SESS_TXT_LOG ARC_SNP_SESS_TXT_LOG
SNP_SESS_TASK_LOG
SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_SESS_TXT_LOG.SESS_NO=SNP_SESS_TASK_LOG.SESS_NO AND

SNP_SESS_TXT_LOG.NNO=SNP_SESS_TASK_LOG.NNO AND
TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) - SNP_SESS_TXT_LOG.NB_RUN=SNP_SESS_TASK_LOG.NB_RUN AND
#V_Purge_Log_Retention SNP_SESS_TXT_LOG.SCEN_TASK_NO=SNP_SESS_TASK_LOG.SCEN_TASK_NO

SNP_SESS_TASK_LOG.NNO=SNP_STEP_LOG.NNO AND
SNP_SESS_TASK_LOG.SESS_NO=SNP_STEP_LOG.SESS_NO AND
SNP_SESS_TASK_LOG.NB_RUN=SNP_STEP_LOG.NB_RUN

SNP_STEP_LOG.SESS_NO=SNP_SESS_STEP.SESS_NO AND
SNP_STEP_LOG.NNO=SNP_SESS_STEP.NNO

SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_SESSION

SNP_SESSION ARC_SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND (none)

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_STEP_LOG

SNP_STEP_LOG ARC_SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_STEP_LOG.SESS_NO=SNP_SESS_STEP.SESS_NO AND

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) - SNP_STEP_LOG.NNO=SNP_SESS_STEP.NNO
#V_Purge_Log_Retention
SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_STEP_REPORT

SNP_STEP_REPORT ARC_SNP_STEP_REPORT
SNP_SCEN_REPORT

SNP_SCEN_REPORT.CONTEXT_CODE = 'PRODUCTION' AND SNP_STEP_REPORT.SCEN_NO=SNP_SCEN_REPORT.SCEN_NO AND

TRUNC(SNP_SCEN_REPORT.SESS_BEG) < TRUNC(SYSDATE) SNP_STEP_REPORT.SCEN_RUN_NO=SNP_SCEN_REPORT.SCEN_RUN_NO
- #V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_TASK_TXT

SNP_TASK_TXT ARC_SNP_TASK_TXT
SNP_SESS_TASK
SNP_SESS_STEP
SNP_SESSION

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND SNP_TASK_TXT.SESS_NO=SNP_SESS_TASK.SESS_NO AND

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) - SNP_TASK_TXT.NNO=SNP_SESS_TASK.NNO AND
#V_Purge_Log_Retention SNP_TASK_TXT.SCEN_TASK_NO=SNP_SESS_TASK.SCEN_TASK_NO

SNP_SESS_TASK.SESS_NO=SNP_SESS_STEP.SESS_NO AND
SNP_SESS_TASK.NNO=SNP_SESS_STEP.NNO

SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_VAR_DATA

SNP_VAR_DATA ARC_SNP_VAR_DATA

SNP_VAR_DATA.CONTEXT_CODE = 'PRODUCTION' AND (none)

TRUNC(SNP_VAR_DATA.FIRST_DATE) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention
ARCHIVING ODI LOGS, CREATE INTERFACES BlackSoft Consulting

I_SNP_VAR_SESS

SNP_VAR_SESS ARC_SNP_VAR_SESS

SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND (none)

TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention
 ARCHIVING ODI LOGS, CREATE PROCEDURE BlackSoft Consulting

Create a delete procedure… Delete from child to parent

ORDER   STEP NAME   COMMAND  
DELETE /*+ USE_HASH(A) PARALLEL(A) */ FROM
ODIWD.SNP_SESS_TXT_LOG A
DELETE WHERE SESS_NO IN
0  
ODIWD.SNP_SESS_TXT_LOG   (SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention)  
DELETE /*+ USE_HASH(A) PARALLEL(A) */ FROM
ODIWD.SNP_SESS_TASK_LOG A
DELETE WHERE SESS_NO IN
10  
ODIWD.SNP_SESS_TASK_LOG   (SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention)  
DELETE /*+ USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_TASK_TXT A
WHERE SESS_NO IN
20   DELETE ODIWD.SNP_TASK_TXT   (SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention)  
DELETE /*+ USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_STEP_LOG A
WHERE SESS_NO IN
30   DELETE ODIWD.SNP_STEP_LOG   (SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention)  
DELETE /*+ USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESS_TASK A
WHERE SESS_NO IN
40   DELETE ODIWD.SNP_SESS_TASK   (SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention)  
 ARCHIVING ODI LOGS, CREATE PROCEDURE BlackSoft Consulting

ORDER   STEP NAME   COMMAND  

DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_SESS_STEP  A  
DELETE   WHERE  SESS_NO  IN  
50  
ODIWD.SNP_SESS_STEP   (SELECT  SESS_NO  FROM  ODIWD.SNP_SESSION  SESS  
 WHERE  TRUNC(SESS.SESS_BEG)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon)  

DELETE   DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_VAR_DATA  A  

60  
ODIWD.SNP_VAR_DATA   WHERE  TRUNC(A.FIRST_DATE)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  

DELETE  FROM  ODIWD.SNP_VAR_SESS  

DELETE  
70   WHERE  SESS_NO  IN  (SELECT  SESS_NO  FROM  ODIWD.SNP_SESSION  A  
ODIWD.SNP_VAR_SESS  
WHERE  TRUNC(SESS_BEG)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  

DELETE   DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_EXP_TXT  A  

80  
ODIWD.SNP_EXP_TXT   WHERE  TRUNC(A.FIRST_DATE)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  

DELETE   DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_SESSION  A  

90  
ODIWD.SNP_SESSION   WHERE  TRUNC(SESS_BEG)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  

DELETE   DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_STEP_REPORT  A  

100  
ODIWD.SNP_STEP_REPORT   WHERE  TRUNC(A.STEP_BEG)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  

DELETE   DELETE  /*+  USE_HASH(A)  PARALLEL(A)  */  FROM  ODIWD.SNP_SCEN_REPORT  A  

110  
ODIWD.SNP_SCEN_REPORT   WHERE  TRUNC(SESS_BEG)  <  TRUNC(SYSDATE)  -­‐  #V_Purge_Log_RetenJon  
ARCHIVING ODI LOGS, PACKAGING BlackSoft Consulting

Running in «Asynchronous Mode»

Running in «Asynchronous Mode»

AGENDA BlackSoft Consulting
HANDLING ALERTS BlackSoft Consulting

Running in «Asynchronous Mode»

Error Handler
Max. Number of Failed Child
Sessions = 1

Raise Error
(error refresh variable)

Mail body
(refresh variable) Send mail
AGENDA BlackSoft Consulting
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Known data quality issues that can be covered in ETL

Step 1 : Prepare your data quality scripts.

Step 2 : Put those scripts into files.

Step 3 : Read contents of those files or create a table for

scripts. Execute this script into your database, insert output to
an ERROR table.

Step 4 : Select count from ERROR table by ERROR_CODE and

loop it from beginning to end for sysdate.

Step 5 : Send e-mail for each script, attach the appropriate file
and show how many rows are generated in this ERROR_CODE.
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 1 : Prepare your data quality scripts.

•  If you are generating a hierarchical tree, make sure your
every node connects to its parent
•  Check duplicates in names, addresss and other important
fields
•  Check primary key behaviour from your sources
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 2 : Put those scripts into files.

•  Create as many scripts you can.

•  Copy files to operating system, where agent is running.

You should have read grant for this directory.
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 3 : Create tables for scripts and output.

CREATE TABLE MY_ERROR_TABLE
(DATETIME DATE,
TRX_ID INTEGER,
ERROR_CODE NUMBER(2),
CREATE TABLE MY_ERROR_SCRIPTS ERROR_DESC VARCHAR2(150 BYTE)
(ERROR_CODE NUMBER(2), )
ERROR_DESC VARCHAR2(150 BYTE),
TABLESPACE MY_TBS
ERROR_SCRIPT_DWH CLOB,
ERROR_SCRIPT_OLTP CLOB LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;
)
LOB (ERROR_SCRIPT_DWH) STORE AS (
TABLESPACE MY_TBS
ENABLE STORAGE IN ROW CHUNK 32768 RETENTION NOCACHE NOLOGGING STORAGE
(INITIAL 160K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0))
LOB (ERROR_SCRIPT_OLTP) STORE AS (
TABLESPACE MY_TBS
ENABLE STORAGE IN ROW CHUNK 32768 RETENTION NOCACHE NOLOGGING STORAGE
(INITIAL 160K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0))
TABLESPACE MY_TBS
LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;
 HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 3 : Read scripts sequentially. Execute this script in your

database, insert output to an ERROR table.
•  Delete todays records;
•  Execute all Error Scripts; (since I am changing my own
codes, below codes must be rewritten to your environment)
DECLARE
TYPE TransactionRec IS RECORD (transaction_id integer);
TYPE TransactionSet IS TABLE OF TransactionRec;
ContractSet TransactionSet;CURSOR C1 IS
select ERROR_CODE, ERROR_DESC, ERROR_SCRIPT_DWH from <
%=odiRef.getSchemaName("DB.MY_SCHEMA", "D")%>.ERROR_SCRIPTS;
BEGIN
FOR C1_REC IN C1 LOOPEXECUTE IMMEDIATE to_char(C1_REC.ERROR_SCRIPT_DWH) BULK COLLECT
INTO ContractSet;IF ContractSet.FIRST IS NOT NULL THEN
FOR i IN ContractSet.FIRST..ContractSet.LAST
LOOP
INSERT INTO <%=odiRef.getSchemaName("DB.MY_SCHEMA", "D")%>.MY_ERROR_TABLE (DATETIME,
TRANSACTION_ID, ERROR_CODE, ERROR_DESC) VALUES
(TO_DATE('SYSDATE', 'YYYYMMDD'), ContractSet(i). transaction_id, C1_REC.ERROR_CODE,
C1_REC.ERROR_DESC);
COMMIT;
END LOOP;
delete from <%=odiRef.getSchemaName("DB.MY_SCHEMA", "D")
END IF;
END LOOP; %>.MY_ERROR_TABLE where DATETIME = TO_DATE('SYSDATE', 'YYYYMMDD')
END;
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 4 : Read ERROR table by ERROR_CODE and loop it from

beginning to end. Refresh
ERROR_CODE_COUNT
Procedure to run scripts.
Output è insert into
MY_ERROR_TABLE daily

Assign the initial ERROR_CODE

Check if last ERROR_CODE

Increment ERROR_CODE (+1)

Check ERROR_CODE_COUNT>0

Refresh mail body

Send mail attaching file

obtained from operating system
HANDLING - IN ETL - DATA QUALITY BlackSoft Consulting

Step 5 : Send e-mail for each script, attach the appropriate file
and show how many rows are generated in this ERROR_CODE.
v  Mail Server : #V_MAIL_SERVER_IP
v  From : This is static, user that you are sending mail.
v  TO : #V_ERROR_MAIL_TO (need to be refreshed in the beginning of
your ETL or current package)
v  CC : #V_ERROR_MAIL_CC (need to be refreshed in the beginning of
your ETL or current package)
v  BCC : #V_ERROR_MAIL_BCC (need to be refreshed in the beginning of
your ETL or current package)
v  Subject : There are #V_MY_ERROR_COUNT errors exist in your system
(Error Code = #V_MY_ERROR_CODE)
v  Attachment : /data/my_errors/MY_Error_#V_MY_ERROR_CODE.txt
(will represent as /data/my_errors/MY_Error_1.txt initially, then will attach
regarding file in the loop, every step will attach its own script file)
v  Message Body : #V_MY_ERROR_DESC
AGENDA BlackSoft Consulting
FILE2TABLE… SUMMARY (F2T) BlackSoft Consulting

Step 1 : Operating system folders and read file names

Step 2 : IKM Knowledge Module
Step 3 : ETL_FILE LOG (database table)
Step 4 : ODI Procedure to rename files for external table usage
Step 5 : ODI Procedures to finish working with files
Step 6 : ODI Procedure to Get File List of operating system
Step 7 : ODI Interface (loading and transforming)
Step 8 : Some ODI Variables
Step 9 : ODI Package for running everything in right order
F2T… PREPARE FOLDERS (STEP 1) BlackSoft Consulting

Illustration for files received from «invoice_logs»

_ logs
nvoice
/data/i

lo gs/
a ta /i n voice_
/d
d
rejecte

e_log s/log
i n v o i c
/data/
FILE2TABLE… PREPARE IKM (STEP 2) BlackSoft Consulting

v Copy current KM : IKM SQL Control Append

v Rename as : IKM SQL Control Append (Direct Load,HINT)

v Add Options : «SELECT HINT», «INSERT HINT»

v Add New Row : «Parallel DML», Transaction 0, No Commit
alter session enable parallel dml

v Modify : «Insert new rows» to Transaction 0, No Commit

v Add New fixed Row : «Commit transaction» to Transaction 0,

Commit
/* commit */
F2T… PREPARE IKM (STEP 2) BlackSoft Consulting

INSERT /*+ APPEND PARALLEL(4) */

INTO ODIDB.MY_TARGET_TABLE
(
MY_TARGET_COLUMN_1,
MY_TARGET_COLUMN_2,
MY_TARGET_COLUMN_3
)
SELECT
/*+ PARALLEL(MY_SOURCE_TABLE) FULL(MY_SOURCE_TABLE) */
MY_SOURCE_TABLE.MY_SOURCE_COLUMN_1,
MY_SOURCE_TABLE.MY_SOURCE_COLUMN_2,
MY_SOURCE_TABLE.MY_SOURCE_COLUMN_3
FROM ODISTG.I$MY_TARGET_TABLE MY_SOURCE_TABLE
WHERE MY_SOURCE_TABLE.MY_SOURCE_COLUMN_4 = 'USA‘
AND MY_SOURCE_TABLE.MY_SOURCE_COLUMN_5 = ‘New York‘
F2T… PREPARE LOG TABLE (STEP 3) BlackSoft Consulting

Create table ETL_FILE_LOG

COLUMN NAME   PK   NULL?  DATA TYPE   DEF  COMMENT  

FILE_ID   1   N   NUMBER (10)     The unique identification number of file.  
FILE_NAME     N   VARCHAR2 (50 Byte)   The name of file to be processed.  
Source system name or group with the
FILE_GROUP  
N   VARCHAR2 (20 Byte)   same template.  
0:not copied, 1:copied successfully,
FILE_COPIED_FLAG  
Y   NUMBER (1)   0   2:error in copy.  
FILE_COPY_DATE   Y   DATE   Date of file copied.  
0:not read, 1:read successfully, 2:error
FILE_READ_FLAG  
Y   NUMBER (1)   0   in read.  
FILE_READ_DATE   Y   DATE   Date of file read.  
FILE_PROCESSED_FLAG   Y   NUMBER (1)   0   Date of file processed.  
0:not processed, 1:processed
FILE_PROCESSED_DATE  
Y   DATE   successfully, 2:error in process.  
F2T… RENAME FILE ODI PROCEDURE (STEP 4) BlackSoft Consulting

v Create external table «STG.INVOICE_LOG»

v Create ODI Procedure «Rename File»

v  Step 1 ; delete previous file

rm /data/invoice_logs/my_external_table.ext

v  Step 2 ; rename next file

mv /data/invoice_logs/#FILE_NAME /data/
invoice_logs/my_external_table.ext
F2T… UPDATE PROCESSED FILE (STEP 5) BlackSoft Consulting

v Create ODI Procedure – «UPDATE PROCESSED FILE»

v  Step 1 ; update ETL_FILE_LOG (Processed)

UPDATE ODI.ETL_FILE_LOG
SET FILE_PROCESSED_FLAG = 1, FILE_PROCESSED_DATE = SYSDATE
WHERE FILE_ID = #FILE_ID

v  Step 2 ; delete processed data file

rm /data/invoice_logs/my_external_table.ext

v  Step 3 ; delete processed log file

rm /data/invoice_logs/my_external_table.log
F2T… GETFILELIST FROM OS (STEP 6) BlackSoft Consulting

v Create ODI Procedure Jyhton Technology – «GetFileList»

import java.lang as lang
import java.sql as sql
import snpsftp
import java.lang.String
import os
import java.io.File as File
#db connection
driverSrc = ‘oracle.jdbc.driver.OracleDriver’
lang.Class.forName(driverSrc)
#Production Environment
urlSrc = ‘jdbc:oracle:thin:@<host>:<port>:<sid>’
#Development Environment
#urlSrc = ‘jdbc:oracle:thin:@<host>:<port>:<sid>’
userSrc = ‘ODI’
passwdSrc = ‘ODI’
ConSrc = sql.DriverManager.getConnection(urlSrc, userSrc, passwdSrc);
readDBLink = ConSrc.createStatement()
syslist = os.listdir(‘<%=odiRef.getOption( “SOURCE_DIRECTORY” )%>’)
for sys in syslist:
str = java.lang.String(sys)
if str.length() > 8:
sqlDBLink = “select * from ODI.ETL_FILE_LOG where FILE_NAME = ‘” + sys + “‘”
rqteDBLink = readDBLink.executeQuery(sqlDBLink)
if not rqteDBLink.next():
sqlDBLink = “insert into ODI.ETL_FILE_LOG (FILE_ID, FILE_NAME, FILE_GROUP, FILE_SUB_GROUP,
FILE_READ_FLAG, FILE_READ_DATE) values (ODI.SEQ_FILE_ID.NEXTVAL, ‘” + sys + “‘,
‘<source_system_name>’, ‘<file_type>’, ’1′, SYSDATE)”
rqteDBLink = readDBLink.execute(sqlDBLink)
ConSrc.close()
F2T… ODI INTERFACE (STEP 7) BlackSoft Consulting

v Create ODI Interface (external table è db table)

v  Source : STG.INVOICE_LOG

(based on external table è my_external_table.ext)

v  Target : DWH.INVOICE_LOGS

v KM : IKM SQL Control Append (Direct Load, HINT)

Truncate : No
Select Hint : /*+ PARALLEL(4) */
Insert Hint : /*+ APPEND PARALLEL(4) NOLOGGING */
F2T… PREPARE ODI VARIABLES (STEP 8) BlackSoft Consulting

v Create ODI Variable to refresh – «File_ID»

SELECT NVL(MIN(FILE_ID), 0) FROM ODI.ETL_FILE_LOG

WHERE FILE_READ_FLAG = 1
AND FILE_PROCESSED_FLAG = 0
AND FILE_GROUP = 'INVOICE_LOGS'

v Create ODI Variable to refresh – «File_Name»

SELECT FILE_NAME FROM ODI.ETL_FILE_LOG

WHERE FILE_ID = #FILE_ID
 F2T… TIME TO PACK-UP (STEP 9) BlackSoft Consulting

Get_File_List FILE_ID FILE_NAME RENAME_FILE

ODI Procedure Evaluate Variable Refresh Variable ODI Procedure

INTERFACE
From:Ext_Table
FILE_ID To:DB.Table
Refresh Variable

UPDATE_PROCESSED
ODI Procedure

ODISendMail Determine_Error_Desc UPDATE_REJECTED

Rejected File ODI Procedure ODI Procedure
BONUS – ORACLE 2 ORACLE LOAD BlackSoft Consulting

LKM SQL to Oracle

SOURCE A

Staging TARGET
(C$%-src A) + (C$-srcB) +
(I$%TARGET) ODI Agent

ODI
SOURCE B
Agent
BONUS – ORACLE 2 ORACLE LOAD BlackSoft Consulting

LKM Oracle to Oracle (DBLINK)

SOURCE A Create DBLink-A

Create View-A
Create Synonym-A

TARGET
+
ODI Agent

Create DBLink-B ODI

SOURCE B View-B
Synonym-B Agent
BONUS – ORACLE 2 ORACLE LOAD BlackSoft Consulting

LKM Oracle to Oracle (datapump)

SOURCE A

TARGET
External Table +
ODI Agent

ODI
SOURCE B
Agent
…FINAL WORDS… BlackSoft Consulting

igorhan@gmail.com
http://gurcanorhan.wordpress.com
@gurcan_orhan

http://tr.linkedin.com/in/gurcanorhan