ORACLE DATA INTEGRATOR (ODI) BEST PRACTICES DO Y K H F ODI I

2y ago
155 Views
7 Downloads
8.86 MB
65 Pages
Last View : 2m ago
Last Download : 2m ago
Upload by : Maleah Dent
Transcription

BlackSoft ConsultingORACLE DATA INTEGRATOR (ODI) BEST PRACTICES DO YOU KNOW HOW FLEXIBLE ODI IS?Gurcan Orhan (the blues man)DWH Architect and DI Development press.com@gurcan orhanhttp://tr.linkedin.com/in/gurcanorhan

WHO AM I ? 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 experienceSybase Power Designer, CA ERwin Data ModelerOBIEE, Cognos, Microstrategy, Business ObjectsOracle 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.comPublished video about ODI @Oracle.com (Oracle Media Network)Presenter in Oracle Open World since 2010 (hat-trick)Presenter in ODTUG Kscope’11Presenter in UKOUG, other OUGs and various universities.BlackSoft Consulting

MY DEFINITION OF ODIODI (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.BlackSoft Consulting

ATTENTION !!!BlackSoft ConsultingMake sure you have;v Backup your repositoryv Backup your Knowledge Modules(export, duplicate)v Backup your necessary ODI developmentBefore trying something in your environmentRemember to create a zzz Test folder and test before apply

AGENDABlackSoft Consulting

USE A VARIABLE WITHIN A VARIABLEBlackSoft Consultingselect count(1) from msdb.dbo.sysjobhistorywhere step id 0and run status 1and job id (select job id from msdb.dbo.sysjobswhere name 'My BI Job')and run date CAST(SUBSTRING('#V SYSDATE',1,8) as integer)

USE A ODIREF FUNCTION IN A VARIABLEBlackSoft ConsultingSELECT *FROM odiwd.snp session sess,odiwd.snp step log step,odiwd.snp sess task sess task,odiwd.snp exp txt exp txtWHERE sess.sess no step.sess noAND sess.sess no sess task.sess noAND step.nno sess task.nnoAND step.i txt step mess exp txt.i txtAND step.step status 'E'AND exp txt.txt ord 0AND sess.sess no % odiRef.getSession("SESS NO")% SELECT NVL(MAX(ALARM ID), 0) FROM % odiRef.getSchemaName("MYDB.DWH", "D")% .TABLE NAMESELECT NVL(MAX(ALARM ID), 0) FROM DWH.TABLE NAME

AGENDABlackSoft Consulting

HINTS IN ORACLEBlackSoft ConsultingOracle’s most powerful querying attribute when in right hands.An Oracle hint is an optimizer directive that is embedded intoan SQL statement to suggest to Oracle how the statementshould be executed.Most common hints in a DWH system;v APPENDv PARALLELv USE HASHv USE MERGEv FULLv INDEXv ORDEREDv MERGEhttp://psoug.org/reference/hints.html or just google «Oracle hints»

KM’S HOW TO APPLY STATIC HINTSBlackSoft Consulting

KM’S HOW TO APPLY SEMI-DYNAMIC HINTSStep 1 : Create OPTIONS for KM’sBlackSoft Consulting

KM’S HOW TO APPLY SEMI-DYNAMIC HINTSStep 1 : Create OPTIONS for KM’sBlackSoft Consulting

KM’S HOW TO APPLY SEMI-DYNAMIC HINTSBlackSoft ConsultingStep 2 : Insert this OPTIONS into KM’sinsert % 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 t3SELECT /* parallel(t1) parallel(t2)ordered use hash(t2) index(t1 t1 abc)index(t2 t2 abc) */ COUNT(*)FROM t1, t2WHERE t1.col1 t2.col1;

KM’S HOW TO APPLY VARIABLED DYNAMIC HINTS BlackSoft Consultingthe detailed dding-variabled-hints-in-odiv Create table INTEFACE HINTS to read from databasev Add Command “Get Hints” as first command in KMv ‘Command on target’ to KM hints #HINT n'v ‘Command on target’ to select hints from tablev Apply hints in necessary order as you read from table toKM’s SQL statementsv Be sure you are using variables in right order

AGENDABlackSoft Consulting

ADDING DATATYPESBlackSoft ConsultingRight Click è Insert Datatype

ADDING DATATYPESConverted To, Converted FromBlackSoft Consulting

AGENDABlackSoft Consulting

ADDING DATABASE RELATED FUNCTIONSBlackSoft ConsultingTopology Manager è Languages è SQL è Aggregate (or other)

ADDING USER FUNCTIONSBlackSoft ConsultingUser Functions è Right Click è New User Function

ADDING USER FUNCTIONSUser Functions è Impact AnalysisBlackSoft Consulting

AGENDABlackSoft Consulting

ARCHIVING ODI LOGS, REQUIRED TABLESBlackSoft ConsultingRepository table list for logging of ODI stored in workrepository schemaSNP EXP TXTARC SNP EXP TXTSNP SCEN REPORTARC SNP SCEN REPORTSNP SESS STEPARC SNP SESS STEPSNP SESS TASKARC SNP SESS TASKSNP SESS TASK LOGSNP SESS TXT LOGSNP SESSIONSNP STEP LOGCreate these tables (withoutreferential integrity,constraints, indexes, etc.)with a suffix or prefix in adifferent schema.ARC SNP SESS TASK LOGARC SNP SESS TXT LOGARC SNP SESSIONARC SNP STEP LOGSNP STEP REPORTARC SNP STEP REPORTSNP TASK TXTARC SNP TASK TXTSNP VAR DATAARC SNP VAR DATASNP VAR SESSARC SNP VAR SESS

ARCHIVING ODI LOGS, REVERSEReverse all required tables in ODIBlackSoft Consulting

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP EXP TXTSNP EXP TXTARC SNP EXP TXTTRUNC(SNP EXP TXT.FIRST DATE) TRUNC(SYSDATE) #V Purge Log Retention(none)

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SCEN REPORTSNP SCEN REPORTARC SNP SCEN REPORTSNP SCEN REPORT.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SCEN REPORT.SESS BEG) TRUNC(SYSDATE)- #V Purge Log Retention(none)

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SESS STEPSNP SESS STEPSNP SESSIONARC SNP SESS STEPSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SESS TASKSNP SESS TASKSNP SESS STEPSNP SESSIONARC SNP SESS TASKSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP SESS TASK.SESS NO SNP SESS STEP.SESS NO ANDSNP SESS TASK.NNO SNP SESS STEP.NNOSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SESS TASK LOGSNP SESS TASK LOGSNP STEP LOGSNP SESS STEPSNP SESSIONARC SNP SESS TASK LOGSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP SESS TASK LOG.NNO SNP STEP LOG.NNO ANDSNP SESS TASK LOG.SESS NO SNP STEP LOG.SESS NO ANDSNP SESS TASK LOG.NB RUN SNP STEP LOG.NB RUNSNP STEP LOG.SESS NO SNP SESS STEP.SESS NO ANDSNP STEP LOG.NNO SNP SESS STEP.NNOSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SESS TXT LOGSNP SESS TXT LOGSNP SESS TASK LOGSNP STEP LOGSNP SESS STEPSNP SESSIONARC SNP SESS TXT LOGSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP SESS TXT LOG.SESS NO SNP SESS TASK LOG.SESS NO ANDSNP SESS TXT LOG.NNO SNP SESS TASK LOG.NNO ANDSNP SESS TXT LOG.NB RUN SNP SESS TASK LOG.NB RUN ANDSNP SESS TXT LOG.SCEN TASK NO SNP SESS TASK LOG.SCEN TASK NOSNP SESS TASK LOG.NNO SNP STEP LOG.NNO ANDSNP SESS TASK LOG.SESS NO SNP STEP LOG.SESS NO ANDSNP SESS TASK LOG.NB RUN SNP STEP LOG.NB RUNSNP STEP LOG.SESS NO SNP SESS STEP.SESS NO ANDSNP STEP LOG.NNO SNP SESS STEP.NNOSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP SESSIONSNP SESSIONARC SNP SESSIONSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention(none)

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP STEP LOGSNP STEP LOGSNP SESS STEPSNP SESSIONARC SNP STEP LOGSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP STEP LOG.SESS NO SNP SESS STEP.SESS NO ANDSNP STEP LOG.NNO SNP SESS STEP.NNOSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP STEP REPORTSNP STEP REPORTSNP SCEN REPORTARC SNP STEP REPORTSNP SCEN REPORT.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SCEN REPORT.SESS BEG) TRUNC(SYSDATE)- #V Purge Log RetentionSNP STEP REPORT.SCEN NO SNP SCEN REPORT.SCEN NO ANDSNP STEP REPORT.SCEN RUN NO SNP SCEN REPORT.SCEN RUN NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP TASK TXTSNP TASK TXTSNP SESS TASKSNP SESS STEPSNP SESSIONARC SNP TASK TXTSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log RetentionSNP TASK TXT.SESS NO SNP SESS TASK.SESS NO ANDSNP TASK TXT.NNO SNP SESS TASK.NNO ANDSNP TASK TXT.SCEN TASK NO SNP SESS TASK.SCEN TASK NOSNP SESS TASK.SESS NO SNP SESS STEP.SESS NO ANDSNP SESS TASK.NNO SNP SESS STEP.NNOSNP SESS STEP.SESS NO SNP SESSION.SESS NO

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP VAR DATASNP VAR DATAARC SNP VAR DATASNP VAR DATA.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP VAR DATA.FIRST DATE) TRUNC(SYSDATE) #V Purge Log Retention(none)

ARCHIVING ODI LOGS, CREATE INTERFACESBlackSoft ConsultingI SNP VAR SESSSNP VAR SESSARC SNP VAR SESSSNP SESSION.CONTEXT CODE 'PRODUCTION' ANDTRUNC(SNP SESSION.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention(none)

ARCHIVING ODI LOGS, CREATE PROCEDUREBlackSoft ConsultingCreate a delete procedure Delete from child to parentORDERSTEP NAME0DELETEODIWD.SNP SESS TXT LOG10DELETEODIWD.SNP SESS TASK LOG20DELETE ODIWD.SNP TASK TXT30DELETE ODIWD.SNP STEP LOG40DELETE ODIWD.SNP SESS TASKCOMMANDDELETE /* USE HASH(A) PARALLEL(A) */ FROMODIWD.SNP SESS TXT LOG AWHERE SESS NO IN(SELECT SESS NO FROM ODIWD.SNP SESSION SESSWHERE TRUNC(SESS.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention)DELETE /* USE HASH(A) PARALLEL(A) */ FROMODIWD.SNP SESS TASK LOG AWHERE SESS NO IN(SELECT SESS NO FROM ODIWD.SNP SESSION SESSWHERE TRUNC(SESS.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention)DELETE /* USE HASH(A) PARALLEL(A) */ FROM ODIWD.SNP TASK TXT AWHERE SESS NO IN(SELECT SESS NO FROM ODIWD.SNP SESSION SESSWHERE TRUNC(SESS.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention)DELETE /* USE HASH(A) PARALLEL(A) */ FROM ODIWD.SNP STEP LOG AWHERE SESS NO IN(SELECT SESS NO FROM ODIWD.SNP SESSION SESSWHERE TRUNC(SESS.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention)DELETE /* USE HASH(A) PARALLEL(A) */ FROM ODIWD.SNP SESS TASK AWHERE SESS NO IN(SELECT SESS NO FROM ODIWD.SNP SESSION SESSWHERE TRUNC(SESS.SESS BEG) TRUNC(SYSDATE) #V Purge Log Retention)

ARCHIVING ODI LOGS, CREATE PROCEDUREORDERSTEP NAMEBlackSoft ConsultingCOMMANDDELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP SESS STEPAWHERESESS NOIN(SELECTSESS NOFROMODIWD.SNP SESSIONSESSWHERETRUNC(SESS.SESS BEG) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon)50DELETEODIWD.SNP SESS STEP60DELETEODIWD.SNP VAR DATADELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP VAR DATAAWHERETRUNC(A.FIRST DATE) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon70DELETEODIWD.SNP VAR SESSDELETEFROMODIWD.SNP VAR SESSWHERESESS NOIN(SELECTSESS NOFROMODIWD.SNP SESSIONAWHERETRUNC(SESS BEG) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon80DELETEODIWD.SNP EXP TXTDELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP EXP TXTAWHERETRUNC(A.FIRST DATE) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon90DELETEODIWD.SNP SESSIONDELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP SESSIONAWHERETRUNC(SESS BEG) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon100DELETEDELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP STEP REPORTAODIWD.SNP STEP REPORTWHERETRUNC(A.STEP BEG) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon110DELETEDELETE/* USE HASH(A)PARALLEL(A)*/FROMODIWD.SNP SCEN REPORTAODIWD.SNP SCEN REPORTWHERETRUNC(SESS BEG) TRUNC(SYSDATE)- ‐#V Purge Log RetenJon

ARCHIVING ODI LOGS, PACKAGINGBlackSoft ConsultingRunning in «Asynchronous Mode»Running in «Asynchronous Mode»

AGENDABlackSoft Consulting

HANDLING ALERTSBlackSoft ConsultingRunning in «Asynchronous Mode»Error HandlerMax. Number of Failed ChildSessions 1Raise Error(error refresh variable)Mail body(refresh variable)Send mail

AGENDABlackSoft Consulting

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingKnown data quality issues that can be covered in ETLStep 1 : Prepare your data quality scripts.Step 2 : Put those scripts into files.Step 3 : Read contents of those files or create a table forscripts. Execute this script into your database, insert output toan ERROR table.Step 4 : Select count from ERROR table by ERROR CODE andloop it from beginning to end for sysdate.Step 5 : Send e-mail for each script, attach the appropriate fileand show how many rows are generated in this ERROR CODE.

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingStep 1 : Prepare your data quality scripts. If you are generating a hierarchical tree, make sure yourevery node connects to its parent Check duplicates in names, addresss and other importantfields Check primary key behaviour from your sources

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingStep 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 QUALITYBlackSoft ConsultingStep 3 : Create tables for scripts and output.CREATE TABLE MY ERROR TABLE(DATETIME DATE,TRX ID INTEGER,ERROR CODE NUMBER(2),ERROR DESC VARCHAR2(150 BYTE))TABLESPACE MY TBSLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;CREATE TABLE MY ERROR SCRIPTS(ERROR CODE NUMBER(2),ERROR DESC VARCHAR2(150 BYTE),ERROR SCRIPT DWH CLOB,ERROR SCRIPT OLTP CLOB)LOB (ERROR SCRIPT DWH) STORE AS (TABLESPACE MY TBSENABLE 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 TBSENABLE STORAGE IN ROW CHUNK 32768 RETENTION NOCACHE NOLOGGING STORAGE(INITIAL 160K NEXT 1M MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0))TABLESPACE MY TBSLOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING;

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingStep 3 : Read scripts sequentially. Execute this script in yourdatabase, insert output to an ERROR table. Delete todays records; Execute all Error Scripts; (since I am changing my owncodes, below codes must be rewritten to your environment)DECLARETYPE TransactionRec IS RECORD (transaction id integer);TYPE TransactionSet IS TABLE OF TransactionRec;ContractSet TransactionSet;CURSOR C1 ISselect ERROR CODE, ERROR DESC, ERROR SCRIPT DWH from % odiRef.getSchemaName("DB.MY SCHEMA", "D")% .ERROR SCRIPTS;BEGINFOR C1 REC IN C1 LOOPEXECUTE IMMEDIATE to char(C1 REC.ERROR SCRIPT DWH) BULK COLLECTINTO ContractSet;IF ContractSet.FIRST IS NOT NULL THENFOR i IN ContractSet.FIRST.ContractSet.LASTLOOPINSERT 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;% .MY ERROR TABLE where DATETIME TO DATE('SYSDATE', 'YYYYMMDD')END LOOP;END;

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingStep 4 : Read ERROR table by ERROR CODE and loop it frombeginning to end.RefreshERROR CODE COUNTProcedure to run scripts.Output è insert intoMY ERROR TABLE dailyAssign the initial ERROR CODECheck if last ERROR CODEIncrement ERROR CODE ( 1)Check ERROR CODE COUNT 0Refresh mail bodySend mail attaching fileobtained from operating system

HANDLING - IN ETL - DATA QUALITYBlackSoft ConsultingStep 5 : Send e-mail for each script, attach the appropriate fileand show how many rows are generated in this ERROR CODE.v Mail Server : #V MAIL SERVER IPv From : This is static, user that you are sending mail.v TO : #V ERROR MAIL TO (need to be refreshed in the beginning ofyour ETL or current package)v CC : #V ERROR MAIL CC (need to be refreshed in the beginning ofyour ETL or current package)v BCC : #V ERROR MAIL BCC (need to be refreshed in the beginning ofyour 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 attachregarding file in the loop, every step will attach its own script file)v Message Body : #V MY ERROR DESC

AGENDABlackSoft Consulting

FILE2TABLE SUMMARY (F2T)BlackSoft ConsultingStep 1 : Operating system folders and read file namesStep 2 : IKM Knowledge ModuleStep 3 : ETL FILE LOG (database table)Step 4 : ODI Procedure to rename files for external table usageStep 5 : ODI Procedures to finish working with filesStep 6 : ODI Procedure to Get File List of operating systemStep 7 : ODI Interface (loading and transforming)Step 8 : Some ODI VariablesStep 9 : ODI Package for running everything in right order

F2T PREPARE FOLDERS (STEP 1)BlackSoft ConsultingIllustration for files received from «invoice logs»nvoice/data/ilogslovoiceni/ata/ddrejectee logciovni/data/s/loggs/

FILE2TABLE PREPARE IKM (STEP 2)BlackSoft Consultingv Copy current KM : IKM SQL Control Appendv 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 Commitalter session enable parallel dmlv Modify : «Insert new rows» to Transaction 0, No Commitv Add New fixed Row : «Commit transaction» to Transaction 0,Commit/* commit */

F2T PREPARE IKM (STEP 2)BlackSoft ConsultingINSERT /* 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 3FROM ODISTG.I MY TARGET TABLE MY SOURCE TABLEWHERE 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 ConsultingCreate table ETL FILE LOGCOLUMN NAMEFILE IDFILE NAMEPKNULL?DATA TYPEDEFCOMMENT1NNUMBER (10)The unique identification number of file.NVARCHAR2 (50 Byte)The name of file to be processed.Source system name or group with theFILE GROUPsame template.NVARCHAR2 (20 Byte)0:not copied, 1:copied successfully,FILE COPIED FLAGYNUMBER (1)02:error in copy.FILE COPY DATEYDATEDate of file copied.0:not read, 1:read successfully, 2:errorFILE READ FLAGYNUMBER (1)0in read.FILE READ DATEYDATEDate of file read.FILE PROCESSED FLAGYNUMBER (1)0Date of file processed.0:not processed, 1:processedFILE PROCESSED DATEsuccessfully, 2:error in process.YDATE

F2T RENAME FILE ODI PROCEDURE (STEP 4)BlackSoft Consultingv Create external table «STG.INVOICE LOG»v Create ODI Procedure «Rename File»v Step 1 ; delete previous filerm /data/invoice logs/my external table.extv Step 2 ; rename next filemv /data/invoice logs/#FILE NAME /data/invoice logs/my external table.ext

F2T UPDATE PROCESSED FILE (STEP 5)BlackSoft Consultingv Create ODI Procedure – «UPDATE PROCESSED FILE»v Step 1 ; update ETL FILE LOG (Processed)UPDATE ODI.ETL FILE LOGSET FILE PROCESSED FLAG 1, FILE PROCESSED DATE SYSDATEWHERE FILE ID #FILE IDv Step 2 ; delete processed data filerm /data/invoice logs/my external table.extv Step 3 ; delete processed log filerm /data/invoice logs/my external table.log

F2T GETFILELIST FROM OS (STEP 6)BlackSoft Consultingv Create ODI Procedure Jyhton Technology – «GetFileList»import java.lang as langimport java.sql as sqlimport snpsftpimport java.lang.Stringimport osimport java.io.File as File#db connectiondriverSrc ‘oracle.jdbc.driver.OracleDriver’lang.Cla

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

Related Documents:

Oracle Data Integrator Best Practices for a Data Warehouse Page 7 Oracle Data Integrator for Best Practices for a Data Warehouse PREFACE Purpose This book describes the best practices for implementing Oracle Data Integrator (ODI) for a data warehouse solution. It is designed to help setup a successful

Design ODI mappings, procedures, and packages to perform ELT data transformations. Administer ODI resources and set up security with ODI. Perform data integration and transformation among various platforms. Use the ODI graphical interface to define procedures, packages, and ELT jobs. Set up and maintain a secure, multi-user ODI environment.

Why Oracle Data Integrator? 1 -8 Conventional Integration Process: ETL 1 -10 Extract Load Transform (E -LT) 1 -11 ODI Architecture and Components 1 -13 ODI Architecture 1 -14 ODI Components: Overview 1 -16 Using ODI Studio 1 -17 Designer Navigator (Work Rep

The 12c version of Oracle Data Integrator (ODI) has a lot of new features and changes. Change is something that's always expected in any new version — and the installation of the full version of ODI 12c is no exception. The two parts of ODI that are most used are the agent and the Studio. To get both of these you essentially have

1Z0-482- Oracle Data Integrator 11g Certified Implementation Essentials pg. 3 Schedule Exam Pearson VUE - Oracle Recommended Practice 1Z0-482 Online Practice Exam Oracle 1Z0-482 Exam Syllabus: Introduction to Oracle Data Integrator (ODI) - Describe the benefits of ODI (including ODI architec

Oracle Data Integrator Log Locations and Configuration 4-5 Oracle Data Integrator High Availability and Failover Considerations 4-6 Oracle Data Integrator Clustered Deployment 4-7 Oracle Data Integrator Protection from Failure and Expected Behavior 4-8 WebLogic Server or Standalone Agent Crash 4-8

Introduction to Oracle Data Integrator (ODI) Objectives The objective of this chapter is to Introduce the key concepts of a business-rule driven architecture Introduce the key concepts of E-LT Understand what an Oracle Data Integrator (ODI) interface is Through a business problem case study, understand and evaluate some differentFile Size: 732KB

Thermal system engineering is not usually thought of as a first rank engineering discipline as Mechanical, Civil, Electrical and Chemical Engineering, and it is usually ascribed to the leading one (like Aerospace, Naval, and Automotive Engineering) because the paradigmatic thermal systems has always been the heat engine, but its importance pervades all other branches (e.g. thermal control .