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

Quiz 9-38Summary 9-39Practice 9-1 Overview: Integrating ODI in the Enterprise Environment 9-40Practice 9-2 Overview: Exposing an ODI Scenario as a Web Service 9-41Practice 9-3 Overview: Integrating ODI with a BPEL Process Within SOA 9-4210 Enhancing ODI SecurityObjectives 10-2Oracle Data Integrator (ODI) Security: Overview 10-3Security Concepts: Overview 10-4Authorizing by Object Instance 10-6ODI Security Navigator: Overview 10-7Using Generic and Nongeneric Profiles 10-9Built-in Profiles 10-10Implementing a Strongly Secured Approach 10-12Security Policy Approach 10-13Defining Security Policies: Using Generic Profiles 10-14Defining Security Policies: Using Nongeneric Profiles 10-15Using Nongeneric Profiles: Granting an Authorization by Object Instance 10-16Unused Authorizations 10-17Cleaning Up Unused Authorizations 10-18ODI Security Integration: Overview 10-19Implementing External Authentication (OPSS) 10-21Configuring External Authentication 10-23Using External LDAP Server 10-24Oracle Internet Directory (OID): Architecture 10-25Configuring ODI External Authentication 10-26Configuring External Authentication with OID 10-271. Creating a New OID User with Directory Services Manager 10-282. Editing the jps-config.xml File to Point to OID LDAP Server 10-293. Running the Script (odi credtool.cmd) to Set Up the Credentials for the IdentityStore 10-304. Creating New ODI Master Repository Referencing a User in the External LDAPServer 10-315. Creating a New ODI Connection Referencing a User in the External OID LDAPServer 10-33Switching the Master Repository Authentication Mode 10-34Using Switch Authentication Mode Wizard 10-35Reactivating Users After Switching to Internal Authentication 10-38Re-enabling Users After Switching to External Authentication 10-39External Password Storage 10-40ix

Setting Up External Password Storage 10-41Implementing External Password Storage 10-42Quiz 10-44Summary 10-45Practice 10-1: Implementing ODI External User Authentication 10-4611 Choosing Integration Strategies: Best PracticesObjectives 11-2Defining an ODI Integration Strategy 11-3Integration Strategies 11-4Strategies with Staging Area on the Target 11-5Strategies with Staging Area on the Target: Append 11-7Strategies with Staging Area on the Target: Control Append 11-8Strategies with Staging Area on the Target: Incremental Update 11-10Strategies with Staging Area on the Target: Incremental Update:Optimization 11-12Working with Slowly Changing Dimensions 11-13Three Types of Slowly Changing Dimensions 11-14Working with Type 2 Slowly Changing Dimensions 11-15Type 2 SCDs: Example 11-16Implementing Type 2 SCDs 11-17Implementing Type 2 SCDs: Steps 11-18Strategies with Staging Area Different from the Target 11-20Using ODI for Bulk Processing 11-24Using ODI for Bulk Processing: Design Patterns 11-26Using Cross-Reference (XREF) Table: Overview 11-28Using the XREF Knowledge Module 11-29Real-Time Data Integration: Using ODI with Oracle GoldenGate 11-31Loading Data Patterns 11-32Oracle GoldenGate: Overview 11-33Oracle GoldenGate Solutions: Overview 11-34Oracle GoldenGate Concepts 11-35Using ODI with Oracle GoldenGate 11-36Using ODI and GoldenGate Together 11-37Initialize CDC Process and Perform Change Operations withOracle GoldenGate 11-38Working on ODI Projects: Best Practices 11-391. Use Context-Independent Design 11-402. Use Procedures Only when Needed 11-413. Always Enforce Data Quality 11-424. Handle Error Cases in Packages 11-43x

5. Choose Right Knowledge Module 11-446. Other Best Practices 11-45Quiz 11-46Summary 11-47xi

Oracle Data Integrator (ODI) Security: Overview 10 -3 Security Concepts: Overview 10 -4 Author izing by Object Instance 10 -6 ODI Security Navigator: Overview 10 -7 Using Generic and Nongeneric Profiles 10 -9 Built -in Profiles 10 -10 Implementing a Strongly Secured Approach 10 -12 Security Policy Approach 10 -13