e-dokumen.id

Oracle Data Integrator 11g:Advanced Integration andDevelopmentStudent GuideD78191GC10Edition 1.0Febraury 2013D80589

AuthorCopyright 2013, Oracle and/or its affiliates. All rights reserved.Viktor TchemodanovDisclaimerDenis GrayAlex KotopoulisThis document contains proprietary information and is protected by copyright andother intellectual property laws. You may copy and print this document solely for yourown use in an Oracle training course. The document may not be modified or altered inany way. Except where your use constitutes "fair use" under copyright law, you maynot use, share, download, upload, copy, print, display, perform, reproduce, publish,license, post, transmit, or distribute this document in whole or in part without theexpress authorization of Oracle.Julien TestutChristophe DupupetRebecca SlyThe information contained in this document is subject to change without notice. If youfind any problems in the document, please report them in writing to: Oracle University,500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is notwarranted to be error-free.Technical Contr ibutorsand Reviewer sGerry JurrensSophia ChenVishal ParasharRichard GreenEditor sRaj KumarMalavika JinkaAju KumarGr aphic Designer sSeema BopaiahMaheshwari KrishnamurthyPublisher sMichael SebastianSrividya RameshkumarRestricted Rights NoticeIf this documentation is delivered to the United States Government or anyone usingthe documentation on behalf of the United States Government, the following notice isapplicable:U.S. GOVERNMENT RIGHTSThe U.S. Government’s rights to use, modify, reproduce, release, perform, display, ordisclose these training materials are restricted by the terms of the applicable Oraclelicense agreement and/or the applicable U.S. Government contract.Trademark NoticeOracle and Java are registered trademarks of Oracle and/or its affiliates. Other namesmay be trademarks of their respective owners.

Contents1IntroductionLesson Objectives 1-2Course Objectives 1-3Target Audience 1-4Class Introductions 1-5Agenda of Lessons 1-6Course Environment 1-7Course Materials 1-8Course Practices 1-9How can I Learn More? 1-10Related Training 1-11Overview of Oracle Data Integrator 1-12Features of Oracle Data Integrator 1-13Conventional Integration Process: ETL 1-15EL-T 1-16ODI Architecture 1-18Summary 1-19Practice 1-1: Exploring Your Environment 1-202Overview of ODI Knowledge ModulesObjectives 2-2ODI Knowledge Modules: Overview 2-3Knowledge Modules: Overview 2-4Global Knowledge Modules 2-5Knowledge Modules in Action 2-6Code Generation 2-7Types of Knowledge Modules: Overview 2-8Working with Knowledge Modules 2-9How to Select Knowledge Modules? 2-10Creating New Knowledge Modules 2-11Developing Knowledge Modules 2-12Editing KMs 2-13Description of KM Steps 2-15Details of the Steps are Generic 2-16Setting KM Options 2-17iii

Replacing Existing KMs 2-18Developing Knowledge Modules 2-20Developing Your Own KM: General Guidelines 2-21Understanding the Substitution API 2-22Using Substitution Methods 2-23Understanding Various ODI Substitution Methods 2-24Substitution Methods: Basic Examples 2-25Using Substitution Methods in Actions 2-26Working with Object Names 2-28Working with Lists of Tables, Columns, and Expressions 2-30Example: Using getTargetColList to Create a Table 2-33Example: Using getColList in an Insert Values Statement 2-34Example: Using getSrcTableList 2-35Generating the Source Select Statement 2-36Generating the Source Select Statement: Example 2-37Working with Data Sets 2-38Obtaining Other Information with the API 2-39Quiz 2-40Summary 2-41Practice 2-1 Overview: Developing and Enhancing an Integration KnowledgeModule 2-423Developing Knowledge ModulesObjectives 3-2Developing Knowledge Modules: Best Practices 3-3Developing KMs: Target a Particular Stage of your Integration Process 3-4Developing KMs: What to Avoid? 3-6Developing KMs: Other Recommendations 3-7Using Java in Knowledge Modules 3-8Using Code Generation Tags: 3-10Using Code Generation Tags: Examples 3-11Understanding Code Generation Techniques 3-12Using Substitution Methods: Code Examples 3-14Using ODI Substitution Methods: getJoin() 3-15Using ODI Substitution Methods: getFilter() 3-16Using ODI Substitution Methods: getPK() 3-17Using ODI Substitution Methods in Journalizing Knowledge Modules:Examples 3-18Using ODI Substitution Methods in Reverse-Engineering KM: getModel() 3-20Troubleshooting Knowledge Modules 3-22Troubleshooting KMs 3-23iv

Quiz 3-27Summary 3-28Practice 3-1 Overview: Creating a New Knowledge Module forData Generation 3-294Designing ODI Integration InterfacesObjectives 4-2Overview of Integration Process 4-3Conventional Integration Process: ETL 4-4E-LT 4-5Integration Process: Overview 4-7Typical Integration Process 4-9Overview of Integration Interfaces 4-10Integration Interfaces: Overview 4-11Basic Integration Process: The Sequence of Operations 4-13The Staging Area 4-14Placing the Staging Area 4-15Designing Integration Interfaces: Best Practices 4-16Designing Integration Interfaces: E-LT- and ETL-Style Interfaces 4-17Designing an ETL-Style Interface 4-18Designing an ETL-Style Interface: Using Multiconnection IKM 4-19Designing an ETL-Style Interface: Using an LKM and a mono-connection IKM 4-22Designing an ETL-Style Interface: Limitations of Using an LKM and amono-connection IKM 4-23Using an LKM and a Mono-Connection IKM: Steps 4-24Maintaining Integrity of Data 4-25Enforcing ODI Data Quality 4-26Ways to Handle Erroneous data 4-27Error Recycling 4-28Building a Data Quality Framework 4-29Quiz 4-30Summary 4-31Practice 4-1: Creating an ODI Interface for XML to Database Transformation withODI Constraint and Error Recycling 4-325Designing Advanced Integration InterfacesObjectives 5-2ODI Interfaces: Advanced Functionality 5-3Creating Lookups 5-4Lookup Wizard 5-6Using Set-Based Operators with Integration Interfaces 5-7v

Example: Flow with Multiple Data Sets 5-9Defining a Data Set 5-10Using Set-Based Operators: Guidelines 5-11Using Partitioning with ODI Interfaces 5-12Partitioning: Definition in Data Store After Reverse Engineering 5-13Using Partitioning in an Interface 5-14Using Temporary Interfaces 5-15Using Temporary Interfaces: Example 5-16Derived Select for Temporary Interfaces 5-17Derived Select for Temporary Interfaces: Limitations 5-18Quiz 5-19Summary 5-20Practice 5-1: Implementing ODI Integration with Temporary Interfaces 5-216Using Variables in ODIObjectives 6-2Using Variables: Overview 6-3Using Variables in ODI: Overview 6-4Variable Scope 6-5Referring to a Variable 6-6Referring to a Variable: Using “:” Instead of “#” 6-8Using Variables in ODI Objects 6-9Using Variables in Packages 6-10Using Variables in Packages: Example 6-11Using Variables in Interfaces 6-12Using Variables in Interfaces: Examples 6-14Using Variables in Object Properties 6-15Using Variables in Procedures 6-16Using Variables Within Variables 6-18Using Variables in the Resource Name of a Data Store 6-19Using a Variable as a Startup Parameter: Example 6-21Using ODI Variables in Topology 6-24Using ODI Variables in Topology: A Server URL 6-25Using Variables in a Server URL: Example 6-26Tracking Variables 6-28Tracking Variables: Notes 6-32Quiz 6-34Summary 6-35Practice 6-1 Overview: Using Variables in ODI Package 6-36Practice 6-2 Overview: Using an ODI Variable as a Startup Parameter 6-37vi

78Accelerating Development in ODI with GroovyObjectives 7-2Interacting Programmatically with ODI 7-3Overview of ODI SDK 7-4SDK-Supported ODI Operations 7-5ODI Operations Not Supported by SDK 7-6Combining Different APIs 7-7Using ODI Groovy Editor 7-8Introduction to Groovy 7-9Introduction to the Groovy Editor 7-10Executing Script with Groovy Editor 7-11Performing SDK Tasks Using Java with Groovy Editor 7-12Example of Performing an SDK Task Using Java with Groovy EditorPerforming Advanced Actions with Groovy 7-14Using Custom Libraries 7-15Defining Additional Groovy Execution Classpath 7-16Read Input with the odiInputStream Variable 7-17Automating Development Tasks: Example 1 7-18Automating Development Tasks: Example 2 7-19Automating Development Tasks: Example 3 7-21Automating Development Tasks: Example 4 7-22Quiz 7-23Summary 7-24Practice 7-1 Overview: Automating ODI Tasks with Groovy 7-25Working with Complex Files in ODIObjectives 8-2Complex Files Concept: Overview 8-3Types of Files in ODI 8-4Complex Files Concept 8-5Knowledge Modules for Complex Files 8-7Requirements for Working with Complex Files 8-8Complex Files: Configuring the Topology 8-9Setting Up the Topology 8-10JDBC URL Properties 8-11JDBC URL: Example 8-12Complex File Data Server Definition: Example 8-13Definition of Physical and Logical Schema: Example 8-15Complex Files in Integration Project 8-16Setting Up an Integration Project and Creating a Complex File Modelvii7-138-17

Setting Up an Integration project and Creating a Complex File Model:Example 8-18Designing an Interface with Complex File Model 8-19Creating an nXSD File 8-20Example of nXSD Schema 8-21Using Native Format Builder Wizard 8-24Quiz 8-27Summary 8-28Practice 8-1 Overview: Configuring ODI Topology and ODI Model withComplex Files 8-299Integration of ODI in Enterprise Environment and SOAObjectives 9-2ODI Integration with Java EE 9-3Understanding ODI Java EE Agent and Standalone Agent 9-4Java EE Agent Deployment Features 9-5Integration of ODI with Fusion Middleware Control 9-6Using ODI Console 9-7Using ODI Console: Example 9-8Overview of Using Web Services with ODI 9-9Types of Web Services 9-10Overview of Data Services 9-11Generation of Data Services 9-12Overview of Public Web Services 9-13Using Public Web Service OdiInvoke 9-14Installing Public Web Services 9-15Invoking Web Services 9-16OdiInvokeWebService Tool 9-17Integration of ODI with SOA 9-20ODI with SOA Integration Scenarios 9-21Integration of ODI Within SOA in Action 9-22Example 1: Using Data Services 9-23Example 2: Exposing ODI Process as a Web Service 9-24Creating the BPEL process 9-25Creating ODI Interface and ODI Package 9-26Creating ODI Scenario and Editing the BPEL Process 9-28Deploying the BPEL Process to the Application Server and Invoking from EnterpriseManager 9-29Example 3: Calling a Web Service from Within ODI for Processing ODI Errors withBPEL Human Workflow 9-30Processing ODI Errors with BPEL Human Workflow 9-31viii