best practices with oracle data integrator (odi): flexibility · pdf filegürcan orhan...

Gürcan ORHAN Enterprise Data Warehouse Architect Ekol Logistics Corp.

30th May 2017

Best Practices with Oracle Data Integrator (ODI): Flexibility

http://gurcanorhan.wordpress.com

@gurcan_orhan

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

Who Am I ? +20 years of IT experience. +14 years of DWH experience. +10 years of Oracle Data Integrator experience. +8 years of Oracle Warehouse Builder experience. Sybase Power Designer, ERwin Data Modeler, SDDM OBIEE, Cognos, Microstrategy, Business Objects, Qlikview, Tableau IBM Data Stage, SAP Data Services, Informatica, etc…

Oracle Excellence Awards - Technologist of the Year 2011 : Enterprise Architect

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

Published Customer Snapshot for NODI @Oracle.com Published videos about ODI @Oracle.com (Oracle Media Network) Published OTN Podcasts about

“Data Warehousing and ODI” “ODI and the Evolution of Data Integration” 3 different “2MTT”s

Articles in OTech Magazine, SearchSoftwareQuality.com Annual panelist for ODTUG “Ask the Experts Panel : ODI”

Presenter in OOW since 2010 (7 times in a row ⭐ ) Presenter in many OUG conferences in globe Presenter in various universities in Turkey

23RD MAY 2017 / #OTNEMEATOUR

Ekol Germany

Warehousing Solutions begin with the Kardelen Facility

1996 2003 2010 2012 2014 2016

2015 2013 2011 2008 2002 1990

Acquire STS Int. Transport

Ekol Bosnia Ekol France Ekol Greece Ekol Ukraine

Ekol Spain

Ekol Bulgaria Ekol Czech Rep. Ekol Iran

Ekol Poland Ekol Italy Ekol Romania

Ekol Hungary Acquire Unok/Unatsan

Rainbow Replaced by Quadro (software)

Intermodal operations Ro-Ro

operations

Established

Ekol Milestones

Headquarters

USA

Ekol Locations

Current countries

15 80 locations

2017 23

countries

92 locations

2018 31

countries

104 locations

2019 42

countries

122 locations

CN

42 countries

By the end of 2019, Ekol will have locations in

5,500+

6,500+

750Km2

750

Warehouse Space

2016 Turnover

MIL

EURO

Workforce

Self-Owned Vehicles

Overview

30 MAY 2017 / #OTNEMEATOUR

My Definition of ODI

6


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

Attention …!!!

7

AGENDA


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 Variable Within A Variable

9


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

Use A odiRef Function In A Variable

10

AGENDA


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.

http://psoug.org/reference/hints.html or just google «Oracle hints»

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

HINTS in Oracle

12


KM’s… How To Apply Static Hints

13


Step 1 : Create OPTIONS for KM’s

KM’s… How To Apply semi-Dynamic Hints

14


Step 1 : Create OPTIONS for KM’s


15


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;


16


KM’s… How To Apply Variabled Dynamic Hints

17

Step 1 : Create table INTEFACE_HINTS to read from database

CREATE TABLE ODI.INTERFACE_HINTS( INTERFACE_NAME VARCHAR2(255 BYTE), INTERFACE_KM VARCHAR2(255 BYTE), KM_STEP_NAME VARCHAR2(255 BYTE), KM_STEP_ORDER INTEGER, STEP_HINT_NAME VARCHAR2(255 BYTE), STEP_HINT_ORDER INTEGER, STEP_HINT_VALUE VARCHAR2(4000 BYTE))TABLESPACE TBS_ODINOLOGGING NOCOMPRESS NOCACHENOPARALLELMONITORING;



18

Step 2 : Add 1st command (Get Hints) in Knowledge Module



19

Step 2 : Add 1st command (Get Hints) in Knowledge Module



20

Step 3 : Use Hints in SQL statements in Knowledge Module



21

Output of step “Get Hints” in ODI



22

Output of “SQL Statements” in ODI

AGENDA


Right Click ➜ Insert Datatype

Adding Datatypes

24


Adding Datatypes

25

Converted To, Converted From

AGENDA


Topology Manager ➜ Languages ➜ SQL ➜ Aggregate (or other)

Adding Database Related Functions

27


Adding User Functions

28

User Functions ➜ Right Click ➜ New User Function AVGCORRCOUNT

COVAR_POPCOVAR_SAMPCUME_DISTDENSE_RANK

FIRSTLAST

LISTAGG,MAXMIN

PERCENT_RANKPERCENTILE_CONTPERCENTILE_DISC

RANKSTDDEV

STDDEV_POPSTDDEV_SAMP

SUMVAR_POPVAR_SAMPVARIANCE


Adding User Functions

29

User Functions ➜ Impact Analysis

AGENDA


Running in «Asynchronous Mode»

Error Handler Max. Number of Failed Child

Sessions = 1

Mail body (refresh variable) Send mail

Raise Error (error refresh variable)

Handling Alerts

31


Send e-mail for each script, attach the appropriate file.

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

Handling - in ETL - Data Quality

32

AGENDA


Step 1 : Operating system folders and read file names

Step 2 : IKM Knowledge Module (edited to fit for purpose)

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 : a single 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… Summary (F2T)

34


Illustration for files received from «web_logs»

/data/web_logs

/data/web_logs/log

/data/web_logs/

rejected

F2T… Prepare Folders (Step 1)

35


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

v Copy current KM : IKM SQL Control Append

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

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

v Add New fixed Row : «Commit transaction» to Transaction 0, Commit /* commit */

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

File2Table… Prepare IKM (Step 2)

36


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,4) 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 IKM (Step 2)

37


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.

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

F2T… Prepare log Table (Step 3)

38


v Create external table «STG.INVOICE_LOG»

v Create ODI Procedure «Rename File»

mv /data/invoice_logs/#FILE_NAME/data/invoice_logs/my_external_table.ext

rm /data/invoice_logs/my_external_table.ext

v Step 1 ; delete previous file

v Step 2 ; rename next file

F2T… Rename File ODI Procedure (Step 4)

39


v Create ODI Procedure – «UPDATE PROCESSED FILE»

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

v Step 1 ; update ETL_FILE_LOG (Processed)

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… Update Processed File (Step 5)

40


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()

v Create ODI Procedure Jyhton Technology – «GetFileList»

F2T… GetFileList from OS (Step 6)

41


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… ODI Interface (Step 7)

42


v Create ODI Variable to refresh – «File_ID»

v Create ODI Variable to refresh – «File_Name»

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'

SELECT FILE_NAME FROM ODI.ETL_FILE_LOG WHERE FILE_ID = #FILE_ID

F2T… Prepare ODI Variables (Step 8)

43


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

F2T… Time to Pack-UP (Step 9)

44


http://gurcanorhan.wordpress.com

@gurcan_orhan

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

…Final Words…

45

best practices with oracle data integrator (odi): flexibility · pdf filegürcan orhan...

Documents