ODI Best Practices

07.Dec.
2012 Grcan Orhan NYOUG LI SIG Webinar

http://gurcanorhan.wordpress.com
http://www.twitter.com/gurcan_orhan
http://tr.linkedin.com/in/gurcanorhan
New York Oracle Users Group (NYOUG)
Long I sland SI G
Oracle Data Integrator (ODI) Best Practices
Do You Know How Flexible ODI Is?
Grcan Orhan
Software Architect & Expert Developer
[email protected]
07.Dec.2012 Grcan Orhan NYOUG LI SIG Webinar
+16years of IT experience.
+9 years of DWH experience.
+5 years of Oracle Data Integrator experience,
+4 years of Oracle Warehouse Builder experience.
Cognos, Microstrategy, Business Objects, OBIEE
Sybase Power Designer, CA ERwin Data Modeler
J oined Turkcell October 2008, Turkcell Technology March 2010.
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)
WHO AM I ? WHO AM I ?
My definition of ODI My definition of ODI
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!!! ATTENTION!!!
4
Make sure you have;
Backup your repository
Backup your Knowledge Modules
(export, duplicate)
Backup your necessary ODI development
Before trying something in your environment
Remember to create a zzz_Test folder and test before apply
Variables Use a variable within a variable Variables Use a variable within a variable
5
sel ect count ( 1) f r ommsdb. dbo. sysj obhi st or y
wher e st ep_i d = 0
and r un_st at us = 1
and j ob_i d = ( sel ect j ob_i d f r ommsdb. dbo. sysj obs
wher e name = ' My_BI _J ob' )
and r un_dat e = CAST( SUBSTRI NG( ' #V_SYSDATE' , 1, 8) as i nt eger )
http://gurcanorhan.wordpress.com/2010/12/14/odi-variables/
SELECT *
FROM odi wd. snp_sessi on sess,
odi wd. snp_st ep_l og st ep,
odi wd. snp_sess_t ask sess_t ask,
odi wd. snp_exp_t xt exp_t xt
WHERE sess. sess_no = st ep. sess_no
AND sess. sess_no = sess_t ask. sess_no
AND st ep. nno = sess_t ask. nno
AND st ep. i _t xt _st ep_mess = exp_t xt . i _t xt
AND st ep. st ep_st at us = ' E'
AND exp_t xt . t xt _or d = 0
AND sess. sess_no = <%=odiRef.getSession("SESS_NO")%>
Variables Variables Use a Use a odiRef function within odiRef function within a variable a variable
6
http://gurcanorhan.wordpress.com/2010/12/14/odi-variables/
SELECT NVL( MAX( ALARM_I D) , 0) FROM
<%=odiRef.getSchemaName("MYDB.DWH", "D")%>. TABLE_NAME
SELECT NVL( MAX( ALARM_I D) , 0) FROM DWH. TABLE_NAME
HINTS in Oracle HINTS in Oracle
7
Oracles 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.
http://psoug.org/reference/hints.html or just google Oracle hints
Most common hints in DWH system;
APPEND
PARALLEL
USE_HASH
USE_MERGE
FULL
INDEX
ORDERED
MERGE
KMs How to apply KMs How to apply static HINTS static HINTS..
8
KMs How to apply dynamic HINTS. KMs How to apply dynamic HINTS.
9
Step 1 : Create OPTIONS for KMs
KMs How to apply dynamic HINTS. KMs How to apply dynamic HINTS.
10
Step 2 : Insert this OPTIONS into KMs
i nser t <%=odiRef.getOption("INSERT_HINT")%>
i nt o <%=snpRef . get Tabl e( " L" , " TARG_NAME" , "A" ) %>
sel ect <%=odiRef.getOption("SELECT_HINT")%>
<%=snpRef . get Pop( " DI STI NCT_ROWS" ) %>
<%=snpRef . get Col Li st ( " " , " [ EXPRESSI ON] \ t [ ALI AS_SEP]
[ CX_COL_NAME] " , " , \ n\ t " , " " , " " ) %>
f r om <%=snpRef . get Fr om( ) %>
I NSERT /*+ APPEND PARALLEL(t3, 8) */
I NTO t 3
SELECT /*+ parallel(t1) parallel(t2)
ordered use_hash(t2) index(t1 t1_abc)
index(t2 t2_abc) */ COUNT( *)
FROM t 1, t 2
WHERE t 1. col 1 = t 2. col 1;
Adding Datatypes Adding Datatypes
11
Right Click Insert Datatype
http://gurcanorhan.wordpress.com/2012/02/28/adding-datatypes-to-odi/
Adding Functions Adding Functions
12
Topology Manager Languages SQL Aggregate (or other)
http://gurcanorhan.wordpress.com/2012/12/05/adding-functions-to-odi/
Archiving ODI Logs Required tables. Archiving ODI Logs Required tables.
13
Repository table list for logging of ODI stored in work
repository schema
SNP_EXP_TXT
SNP_SCEN_REPORT
SNP_SESS_STEP
SNP_SESS_TASK
SNP_SESS_TASK_LOG
SNP_SESS_TXT_LOG
SNP_SESSION
SNP_STEP_LOG
SNP_STEP_REPORT
SNP_TASK_TXT
SNP_VAR_DATA
SNP_VAR_SESS
ARC_SNP_EXP_TXT
ARC_SNP_SCEN_REPORT
ARC_SNP_SESS_STEP
ARC_SNP_SESS_TASK
ARC_SNP_SESS_TASK_LOG
ARC_SNP_SESS_TXT_LOG
ARC_SNP_SESSION
ARC_SNP_STEP_LOG
ARC_SNP_STEP_REPORT
ARC_SNP_TASK_TXT
ARC_SNP_VAR_DATA
ARC_SNP_VAR_SESS
Create these tables (without
referential integrity,
constraints, indexes, etc.)
with a suffix or prefix in a
different schema.
http://gurcanorhan.wordpress.com/2012/12/05/archiving-odi-logs/
Archiving ODI Logs Archiving ODI Logs Reverse. Reverse.
14
Reverse all required tables in ODI
Archiving ODI Logs Create Archiving ODI Logs Create interfaces. interfaces.
15
SNP_EXP_TXT ARC_SNP_EXP_TXT
TRUNC(SNP_EXP_TXT.FIRST_DATE) < TRUNC(SYSDATE) -
#V_Purge_Log_Retention
(none)
I_SNP_EXP_TXT
16
SNP_SCEN_REPORT ARC_SNP_SCEN_REPORT
SNP_SCEN_REPORT.CONTEXT_CODE = 'PRODUCTION' AND
TRUNC(SNP_SCEN_REPORT.SESS_BEG) < TRUNC(SYSDATE)
- #V_Purge_Log_Retention
I_SNP_SCEN_REPORT
(none)
17
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_SESS_STEP
SNP_SESSION.CONTEXT_CODE = 'PRODUCTION' AND
TRUNC(SNP_SESSION.SESS_BEG) < TRUNC(SYSDATE) -
I_SNP_SESS_STEP
SNP_SESS_STEP.SESS_NO=SNP_SESSION.SESS_NO
18
SNP_SESS_TASK
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_SESS_TASK
I_SNP_SESS_TASK
SNP_SESS_TASK.SESS_NO=SNP_SESS_STEP.SESS_NO AND
SNP_SESS_TASK.NNO=SNP_SESS_STEP.NNO
19
SNP_SESS_TASK_LOG
SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_SESS_TASK_LOG
I_SNP_SESS_TASK_LOG
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
20
SNP_SESS_TXT_LOG
SNP_SESS_TASK_LOG
SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_SESS_TXT_LOG
I_SNP_SESS_TXT_LOG
SNP_SESS_TXT_LOG.SESS_NO=SNP_SESS_TASK_LOG.SESS_NO AND
SNP_SESS_TXT_LOG.NNO=SNP_SESS_TASK_LOG.NNO AND
SNP_SESS_TXT_LOG.NB_RUN=SNP_SESS_TASK_LOG.NB_RUN AND
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
21
SNP_SESSION ARC_SNP_SESSION
I_SNP_SESSION
(none)
22
SNP_STEP_LOG
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_STEP_LOG
I_SNP_STEP_LOG
23
SNP_STEP_REPORT
SNP_SCEN_REPORT
ARC_SNP_STEP_REPORT
I_SNP_STEP_REPORT
SNP_SCEN_REPORT.CONTEXT_CODE = 'PRODUCTION' AND
TRUNC(SNP_SCEN_REPORT.SESS_BEG) < TRUNC(SYSDATE)
- #V_Purge_Log_Retention
SNP_STEP_REPORT.SCEN_NO=SNP_SCEN_REPORT.SCEN_NO AND
SNP_STEP_REPORT.SCEN_RUN_NO=SNP_SCEN_REPORT.SCEN_RUN_NO
24
SNP_TASK_TXT
SNP_SESS_TASK
SNP_SESS_STEP
SNP_SESSION
ARC_SNP_TASK_TXT
I_SNP_TASK_TXT
SNP_TASK_TXT.SESS_NO=SNP_SESS_TASK.SESS_NO AND
SNP_TASK_TXT.NNO=SNP_SESS_TASK.NNO AND
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
25
SNP_VAR_DATA ARC_SNP_VAR_DATA
I_SNP_VAR_DATA
SNP_VAR_DATA.CONTEXT_CODE = 'PRODUCTION' AND
TRUNC(SNP_VAR_DATA.FIRST_DATE) < TRUNC(SYSDATE) -
(none)
26
SNP_VAR_SESS ARC_SNP_VAR_SESS
I_SNP_VAR_SESS
(none)
Archiving ODI Logs Create Archiving ODI Logs Create procedure procedure
27
Create a delete procedure Delete from parent to child
ORDERSTEP NAME COMMAND
0
DELETE
ODIWD.SNP_SESS_TXT_LOG
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESS_TXT_LOGA
WHERE SESS_NO IN
(SELECT SESS_NO FROM ODIWD.SNP_SESSION SESS
WHERE TRUNC(SESS.SESS_BEG) <TRUNC(SYSDATE) - #V_Purge_Log_Retention)
10
DELETE
ODIWD.SNP_SESS_TASK_LOG
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESS_TASK_LOGA
WHERE SESS_NO IN
20
DELETE
ODIWD.SNP_TASK_TXT
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_TASK_TXTA
WHERE SESS_NO IN
30
DELETE
ODIWD.SNP_STEP_LOG
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_STEP_LOG A
WHERE SESS_NO IN
40
DELETE
ODIWD.SNP_SESS_TASK
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESS_TASKA
WHERE SESS_NO IN
Archiving ODI Logs Create procedure Archiving ODI Logs Create procedure
28
ORDERSTEP NAME COMMAND
50
DELETE
ODIWD.SNP_SESS_STEP
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESS_STEPA
WHERE SESS_NO IN
60
DELETE
ODIWD.SNP_VAR_DATA
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_VAR_DATAA
WHERE TRUNC(A.FIRST_DATE) <TRUNC(SYSDATE) - #V_Purge_Log_Retention
70
DELETE
ODIWD.SNP_VAR_SESS
DELETE FROM ODIWD.SNP_VAR_SESS
WHERE SESS_NO IN (SELECT SESS_NO FROM ODIWD.SNP_SESSIONA
WHERE TRUNC(SESS_BEG) <TRUNC(SYSDATE) - #V_Purge_Log_Retention
80
DELETE
ODIWD.SNP_EXP_TXT
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_EXP_TXTA
WHERE TRUNC(A.FIRST_DATE) <TRUNC(SYSDATE) - #V_Purge_Log_Retention
90
DELETE
ODIWD.SNP_SESSION
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SESSIONA
100
DELETE
ODIWD.SNP_STEP_REPORT
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_STEP_REPORTA
WHERE TRUNC(A.STEP_BEG) <TRUNC(SYSDATE) - #V_Purge_Log_Retention
110
DELETE
ODIWD.SNP_SCEN_REPORT
DELETE /*+USE_HASH(A) PARALLEL(A) */ FROM ODIWD.SNP_SCEN_REPORTA
Archiving ODI Logs Archiving ODI Logs Packaging Packaging
29
Running in Asynchronous Mode
Handling Alerts Handling Alerts
30
http://gurcanorhan.wordpress.com/2012/11/30/odi-alert-mechanism/
Error Handler
Max. Number of Failed Child Sessions =1
Mail body
(refresh variable)
Send mail
Raise Error
(error refresh variable)
Handling Handling {in ETL} {in ETL} Data Quality Data Quality
31
http://gurcanorhan.wordpress.com/2012/11/30/odi-handling-dq/
Known data quality issues that can be covered in ETL
Step 1 : Prepare your data quality scripts.
Step 4 : Select count from ERROR table by ERROR_CODE and
loop it from beginning to end for sysdate.
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 5 : Send e-mail for each script, attach the appropriate file
and show how many rows are generated in this ERROR_CODE.
32
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
33
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.
34
Step 3 : Create tables for scripts and output.
CREATE TABLE MY_ERROR_SCRI PTS
( ERROR_CODE NUMBER( 2) ,
ERROR_DESC VARCHAR2( 150 BYTE) ,
ERROR_SCRI PT_DWH CLOB,
ERROR_SCRI PT_OLTP CLOB
)
LOB ( ERROR_SCRI PT_DWH) STORE AS (
TABLESPACE MY_TBS
ENABLE STORAGE I N ROWCHUNK 32768 RETENTI ON NOCACHE NOLOGGI NG STORAGE
( I NI TI AL 160K NEXT 1M MI NEXTENTS 1 MAXEXTENTS UNLI MI TED PCTI NCREASE 0) )
LOB ( ERROR_SCRI PT_OLTP) STORE AS (
TABLESPACE MY_TBS
ENABLE STORAGE I N ROWCHUNK 32768 RETENTI ON NOCACHE NOLOGGI NG STORAGE
( I NI TI AL 160K NEXT 1M MI NEXTENTS 1 MAXEXTENTS UNLI MI TED PCTI NCREASE 0) )
TABLESPACE MY_TBS
LOGGI NG NOCOMPRESS NOCACHE NOPARALLEL MONI TORI NG;
CREATE TABLE MY_ERROR_TABLE
( DATETI ME DATE,
TRX_I D I NTEGER,
ERROR_CODE NUMBER( 2) ,
ERROR_DESC VARCHAR2( 150 BYTE)
)
TABLESPACE MY_TBS
LOGGI NG NOCOMPRESS NOCACHE NOPARALLEL MONI TORI NG;
35
Step 3 : Read scripts. Execute this script into your database,
insert output to an ERROR table.
Delete todays records;
DECLARE
TYPE Tr ansact i onRec I S RECORD ( t r ansact i on_i d i nt eger ) ;
TYPE Tr ansact i onSet I S TABLE OF Tr ansact i onRec;
Cont r act Set Tr ansact i onSet ; CURSOR C1 I S
sel ect ERROR_CODE, ERROR_DESC, ERROR_SCRI PT_DWH f r om
<%=odi Ref . get SchemaName( "DB. MY_SCHEMA", "D") %>. ERROR_SCRI PTS;
BEGI N
FOR C1_REC I N C1 LOOPEXECUTE I MMEDI ATE t o_char ( C1_REC. ERROR_SCRI PT_DWH) BULK COLLECT
I NTO Cont r act Set ; I F Cont r act Set . FI RST I S NOT NULL THEN
FOR i I N Cont r act Set . FI RST. . Cont r act Set . LAST
LOOP
I NSERT I NTO <%=odi Ref . get SchemaName( "DB. MY_SCHEMA", "D") %>. MY_ERROR_TABLE ( DATETI ME,
TRANSACTI ON_I D, ERROR_CODE, ERROR_DESC) VALUES
( TO_DATE( ' SYSDATE' , ' YYYYMMDD' ) , Cont r act Set ( i ) . t r ansact i on_i d, C1_REC. ERROR_CODE,
C1_REC. ERROR_DESC) ;
COMMI T;
END LOOP;
END I F;
END LOOP;
END;
del et e f r om<%=odi Ref . get SchemaName( " DB. MY_SCHEMA" ,
" D" ) %>. MY_ERROR_TABLE wher e DATETI ME = TO_DATE( ' SYSDATE' , ' YYYYMMDD' )
Execute all Error Scripts; (since I am changing my own codes,
below codes must be rewritten to your environment)
36
Step 4 : Read ERROR table by ERROR_CODE and loop it from
beginning to end.
Procedure to run scripts.
Output insert into
MY_ERROR_TABLEdaily
Assign the initial ERROR_CODE
Check if last ERROR_CODE
Refresh
ERROR_CODE_COUNT
Increment ERROR_CODE (+1)
Check ERROR_CODE_COUNT>0
Refresh mail body
Send mail
37
Step 5 : Send e-mail for each script, attach the appropriate file
and show how many rows are generated in this ERROR_CODE.
Mail Server : #V_MAIL_SERVER_IP
From : This is static, user that you are sending mail.
TO: #V_ERROR_MAIL_TO (need to be refreshed in the beginning of
your ETL or current package)
CC : #V_ERROR_MAIL_CC (need to be refreshed in the beginning of your
ETL or current package)
BCC : #V_ERROR_MAIL_BCC (need to be refreshed in the beginning of
your ETL or current package)
Subject : There are #V_MY_ERROR_COUNT errors exist in your system
(Error Code = #V_MY_ERROR_CODE)
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)
Message Body : #V_MY_ERROR_DESC
File2Table Summary File2Table Summary
38
http://gurcanorhan.wordpress.com/2010/11/13/loading-multiple-files-with-odi/
Step 1 : Operating system folders
Step 2 : IKM Knowledge Module
Step 4 : ODI Procedure to rename files for external table usage
Step 3 : ETL_FILE LOG (database table)
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
Step 5 : ODI Procedures to finish working with files
File2Table Preparing environment, folders (Step 1) File2Table Preparing environment, folders (Step 1)
39
Illustration for files received from invoice_logs
File2Table Preparing environment, File2Table Preparing environment, IKM (Step 2) IKM (Step 2)
40
Rename as : IKM SQL Control Append (Direct Load,HINT)
Copy current KM : IKM SQL Control Append
Add New Row : Parallel DML, Transaction 0, No Commit
al t er sessi on enabl e par al l el dml
Add Options : SELECT HINT, INSERT HINT
Add New fixed Row : Commit transaction to Transaction 0,
Commit
/* commit */
Modify : Insert new rows to Transaction 0, No Commit
File2Table Preparing environment, File2Table Preparing environment, IKM (Step 2) IKM (Step 2)
41
I NSERT /*+ APPEND PARALLEL(4) */
I NTO ODI DB. 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 ODI STG. 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 Yor k
File2Table Prepare log table (Step 3) File2Table Prepare log table (Step 3)
42
COLUMN NAME PKNULL?DATA TYPE DEFCOMMENT
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.
FILE_GROUP
N VARCHAR2 (20 Byte)
Source system name or group
with the same template.
FILE_COPIED_FLAG
Y NUMBER (1) 0
0:not copied, 1:copied
successfully, 2:error in copy.
FILE_COPY_DATE Y DATE Date of file copied.
FILE_READ_FLAG
Y NUMBER (1) 0
0:not read, 1:read successfully,
2:error in read.
FILE_READ_DATE Y DATE Date of file read.
FILE_PROCESSED_FLAG Y NUMBER (1) 0 Date of file processed.
FILE_PROCESSED_DATE
Y DATE
0:not processed, 1:processed
successfully, 2:error in process.
Create table ETL_FILE_LOG
File2Table File2Table Rename File Rename File -- ODI Procedure ODI Procedure (Step 4) (Step 4)
43
Create external table STG.INVOICE_LOG
Create ODI Procedure Rename File
mv / dat a/ i nvoi ce_l ogs/ #FILE_NAME
/ dat a/ i nvoi ce_l ogs/ my_ext er nal _t abl e. ext
r m/ dat a/ i nvoi ce_l ogs/ my_ext er nal _t abl e. ext
Step 1 ; delete previous file
Step 2 ; rename next file
File2Table Update Processed File (Step 5) File2Table Update Processed File (Step 5)
44
Create ODI Procedure UPDATE PROCESSED FILE
UPDATE ODI . ETL_FI LE_LOG
SET FI LE_PROCESSED_FLAG = 1, FI LE_PROCESSED_DATE = SYSDATE
WHERE FI LE_I D = #FI LE_I D
Step 1 ; update ETL_FILE_LOG (Processed)
Step 2 ; delete processed data file
r m/ dat a/ i nvoi ce_l ogs/ my_ext er nal _t abl e. ext
Step 3 ; delete processed log file
r m/ dat a/ i nvoi ce_l ogs/ my_ext er nal _t abl e. l og
File2Table GetFileList from OS (Step 6) File2Table GetFileList from OS (Step 6)
45
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()
Create ODI Procedure J yhton Technology GetFileList
File2Table ODI Interface (Step 7) File2Table ODI Interface (Step 7)
46
Create ODI Interface (external table db table)
Source : STG.INVOICE_LOG
(based on external table my_external_table.ext)
Target : DWH.INVOICE_LOGS
KM : IKM SQL Control Append (Direct Load,HINT)
Truncate : No
Select Hint : /*+ PARALLEL(4) */
Insert Hint : /*+ APPEND PARALLEL(4) NOLOGGING */
File2Table ODI Variables (Step 8) File2Table ODI Variables (Step 8)
47
Create ODI Variable to refresh File_ID
Create ODI Variable to refresh File_Name
SELECT NVL( MI N( FI LE_I D) , 0) FROM ODI . ETL_FI LE_LOG
WHERE FI LE_READ_FLAG = 1
AND FI LE_PROCESSED_FLAG = 0
AND FI LE_GROUP = ' I NVOI CE_LOGS'
SELECT FI LE_NAME FROM ODI . ETL_FI LE_LOG
WHERE FI LE_I D = #FILE_ID
File2Table Pack File2Table Pack--up everything (Step 9) up everything (Step 9)
48
Get_File_List
ODI Procedure
FILE_ID
Refresh Variable
FILE_ID
Evaluate Variable
FILE_NAME
Refresh Variable
RENAME_FILE
ODI Procedure
INTERFACE
From:Ext_Table
To:DB.Table
UPDATE_PROCESSED
ODI Procedure
UPDATE_REJECTED
ODI Procedure
Determine_Error_Desc
ODI Procedure
ODISendMail
Rejected File
Final Words Final Words
Final Words Final Words
[email protected]

ODI Best Practices

Uploaded by

Copyright:

Available Formats

ODI Best Practices

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ODI Best Practices

Uploaded by

Copyright:

Available Formats

07.Dec.

2012 Grcan Orhan NYOUG LI SIG Webinar

You might also like