d64974gc10_toc.pdf

Oracle Data Integrator 11g: Integration and Administration

Student Guide – Volume 1

D64974GC10

Edition 1.0

December 2010

D68947

Copyright © 2010, Oracle and/or its affiliates. All rights reserved. Disclaimer This document contains proprietary information and is protected by copyright and other intellectual property laws. You may copy and print this document solely for your own use in an Oracle training course. The document may not be modified or altered in any way. Except where your use constitutes "fair use" under copyright law, you may not use, share, download, upload, copy, print, display, perform, reproduce, publish, license, post, transmit, or distribute this document in whole or in part without the express authorization of Oracle. The information contained in this document is subject to change without notice. If you find any problems in the document, please report them in writing to: Oracle University, 500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is not warranted to be error-free. Restricted Rights Notice If this documentation is delivered to the United States Government or anyone using the documentation on behalf of the United States Government, the following notice is applicable: U.S. GOVERNMENT RIGHTS The U.S. Government’s rights to use, modify, reproduce, release, perform, display, or disclose these training materials are restricted by the terms of the applicable Oracle license agreement and/or the applicable U.S. Government contract. Trademark Notice Oracle and Java are registered trademarks of Oracle and/or its affiliates. Other names may be trademarks of their respective owners.

Authors

Viktor Tchemodanov, Richard Green

Technical Contributors and Reviewers

Nagavalli Pataballa, Nancy Greenberg, Denis Gray, Jeff Pollock, FX Nicolas,

Christophe Dupupet, Art Hetherington, Usha Ramanathan, Gerry Jurrens,

Phillip Scott, Taj-ul Islam

This book was published using: Oracle Tutor

Copyright © 2010, Oracle and/or its affiliates. All rights reserved.

Oracle Data Integrator 11g: Integration and Administration Table of Contents

i

Table of Contents

Introduction to Integration and Administration ........................................................................................ 1-1 Introduction to Integration and Administration ........................................................................................... 1-2 Course Objectives ................................................................................................................................... 1-3 Agenda of Lessons ................................................................................................................................. 1-5 Oracle Data Integrator: Introduction ......................................................................................................... 1-8 Why Oracle Data Integrator? ................................................................................................................... 1-9 Conventional Integration Process: ETL .................................................................................................... 1-11 ELT ........................................................................................................................................................ 1-12 ODI Architecture and Components .......................................................................................................... 1-14 ODI Architecture ..................................................................................................................................... 1-15 ODI Components: Overview .................................................................................................................... 1-17 Designer Navigator ................................................................................................................................. 1-19 Operator Navigator ................................................................................................................................. 1-20 Topology Navigator ................................................................................................................................. 1-21 Security Navigator ................................................................................................................................... 1-22 What Is an Agent? .................................................................................................................................. 1-23 ODI Agents ............................................................................................................................................. 1-24 Two Types of Agents: Java EE and Standalone ....................................................................................... 1-25 Using the Two Types of Agents ............................................................................................................... 1-26 Example of Standalone Agent ................................................................................................................. 1-27 ODI Console ........................................................................................................................................... 1-28 Enterprise Manager Console ................................................................................................................... 1-29 Oracle Data Integrator Repositories ......................................................................................................... 1-30 ODI Repositories..................................................................................................................................... 1-31 Master and Work Repositories ................................................................................................................. 1-32 Repository Setup: Example ..................................................................................................................... 1-34 Repository Setup: Multiple Master Repositories ....................................................................................... 1-35 Components: A Global View .................................................................................................................... 1-36 Possible ODI Methodology ...................................................................................................................... 1-37 Checklist of Lab Practice Activities........................................................................................................... 1-38 Quiz ....................................................................................................................................................... 1-39 Summary ................................................................................................................................................ 1-41

Administering ODI Repositories ............................................................................................................... 2-1 Administering ODI Repositories ............................................................................................................... 2-2 Starting Oracle Data Integrator ................................................................................................................ 2-4 Using Online Help ................................................................................................................................... 2-5 Administering the ODI Repositories ......................................................................................................... 2-6 Initial Repository Administration Tasks ..................................................................................................... 2-7 Steps to Set Up ODI Repositories ............................................................................................................ 2-8 1. Creating Repository Storage Spaces ................................................................................................... 2-9 2. Creating the Master Repository ........................................................................................................... 2-10 3. Connecting to the Master Repository ................................................................................................... 2-13 Importing Master Repository .................................................................................................................... 2-15 Exporting the Master Repository .............................................................................................................. 2-16 4. Creating a Work Repository ................................................................................................................. 2-18 5. Connecting to the Work Repository ...................................................................................................... 2-21 Changing the Work Repository Password ................................................................................................ 2-22



ii

Creating Repositories with the RCU ......................................................................................................... 2-23 Quiz ....................................................................................................................................................... 2-26 Summary ................................................................................................................................................ 2-27 Checklist of Lab Practice Activities........................................................................................................... 2-28 Practice 2-1 Overview ............................................................................................................................. 2-29

ODI Topology Concepts ........................................................................................................................... 3-1 ODI Topology Concepts .......................................................................................................................... 3-2 Objectives............................................................................................................................................... 3-3 ODI Topology: Overview ......................................................................................................................... 3-4 What Is Topology? .................................................................................................................................. 3-5 What Is in the Topology? ......................................................................................................................... 3-6 Data Servers and Physical Schemas ....................................................................................................... 3-7 What Is a Data Server? ........................................................................................................................... 3-8 Important Guideline 1 .............................................................................................................................. 3-10 What Is a Physical Schema? ................................................................................................................... 3-11 Physical Schemas: Properties ................................................................................................................. 3-12 Technology Terminology Among Vendors ................................................................................................ 3-13 Important Guideline 2 and Recommendations .......................................................................................... 3-15 Defining Topology: Example .................................................................................................................... 3-16 Example: Infrastructure for Two Production Sites ..................................................................................... 3-17 ODI Design: Physical Architecture of the Two Production Sites ................................................................ 3-18 Logical Schemas and Contexts ............................................................................................................... 3-19 What Is a Logical Schema? ..................................................................................................................... 3-20 Important Guideline 3 .............................................................................................................................. 3-21 Logical Versus Physical Architecture ....................................................................................................... 3-22 Note: Design Time Versus Run Time ....................................................................................................... 3-23 What Is a Context? ................................................................................................................................. 3-24 A Context Maps a Logical to a Physical Schema ...................................................................................... 3-25 Defining Contexts.................................................................................................................................... 3-26 Mapping Logical and Physical Resources ................................................................................................ 3-27 Agents in Topology ................................................................................................................................. 3-29 ODI Physical Agents ............................................................................................................................... 3-30 Creating a Physical Agent ....................................................................................................................... 3-31 ODI Agent Parameters ............................................................................................................................ 3-32 Launching a Stand-Alone Agent: Examples ............................................................................................. 3-34 Stopping the ODI Agent .......................................................................................................................... 3-35 Deploying and Configuring Java EE Agent ............................................................................................... 3-36 Load Balancing: Example ........................................................................................................................ 3-39 Important Guideline 5 .............................................................................................................................. 3-41 Infrastructure with Agents: Example ......................................................................................................... 3-42 Defining Agents: Example ....................................................................................................................... 3-43 Special Case: Fragmentation Problem ..................................................................................................... 3-44 Special Case: Important Guideline 6 ........................................................................................................ 3-46 Special Case: Defining the Physical Architecture...................................................................................... 3-47 Special Case: The Infrastructure.............................................................................................................. 3-48 Special Case: Physical Architecture in ODI .............................................................................................. 3-49 Defining a Topology: Best Practices ........................................................................................................ 3-50 Planning the Topology............................................................................................................................. 3-51 Matrix of Logical and Physical Mappings .................................................................................................. 3-52



iii

Quiz ....................................................................................................................................................... 3-53 Summary ................................................................................................................................................ 3-56 Checklist of Lab Practice Activities........................................................................................................... 3-57 Practice 3-1 Overview ............................................................................................................................. 3-58

Describing the Physical and Logical Architecture ................................................................................... 4-1 Describing the Physical and Logical Architecture...................................................................................... 4-2 Objectives............................................................................................................................................... 4-3 Topology Navigator ................................................................................................................................. 4-4 What Topology Navigator Contains .......................................................................................................... 4-5 Topology Navigator: Overview ................................................................................................................. 4-6 Review: Context Connects Logical to Physical ......................................................................................... 4-8 Objects You Create in the Practice .......................................................................................................... 4-9 Defining a Context .................................................................................................................................. 4-10 Creating Physical Architecture ................................................................................................................. 4-11 Physical Architecture View ...................................................................................................................... 4-12 Prerequisites for Connecting to a Server .................................................................................................. 4-13 Important Note ........................................................................................................................................ 4-14 Creating a Data Server ............................................................................................................................ 4-15 Creating a Data Server: JDBC ................................................................................................................. 4-16 JDBC Driver............................................................................................................................................ 4-17 JDBC URL .............................................................................................................................................. 4-18 Creating a Data Server: JNDI .................................................................................................................. 4-19 Testing a Data Server Connection ........................................................................................................... 4-20 Creating a Physical Schema .................................................................................................................... 4-21 Creating Logical Architecture ................................................................................................................... 4-22 Logical Architecture and Context Views ................................................................................................... 4-23 Creating a Logical Schema...................................................................................................................... 4-24 Creating a Logical Agent ......................................................................................................................... 4-25 Editing a Context to Link the Logical and Physical .................................................................................... 4-26 Quiz ....................................................................................................................................................... 4-27 Summary ................................................................................................................................................ 4-29 Checklist of Lab Practice Activities........................................................................................................... 4-30 Practice 4-1 Overview ............................................................................................................................. 4-31

Oracle Data Integrator Model Concepts ................................................................................................... 5-1 Oracle Data Integrator Model Concepts ................................................................................................... 5-2 Objectives............................................................................................................................................... 5-3 What Is a Model? .................................................................................................................................... 5-4 Understanding the Relational Model ........................................................................................................ 5-5 Relational Model ..................................................................................................................................... 5-6 Relational Model: Tables and Columns .................................................................................................... 5-7 Relational Model: Keys ............................................................................................................................ 5-8 Relational Model: Foreign Keys ............................................................................................................... 5-9 Relational Model: Constraints .................................................................................................................. 5-10 Relational Model: Indexes ....................................................................................................................... 5-12 Relational Model Support in ODI.............................................................................................................. 5-13 Additional Metadata in ODI ...................................................................................................................... 5-14 Understanding Reverse Engineering........................................................................................................ 5-16 What Is Reverse Engineering? ................................................................................................................ 5-17 Methods for DBMS Reverse Engineering ................................................................................................. 5-18



iv

Other Methods for Reverse Engineering .................................................................................................. 5-19 Standard Versus Customized Reverse Engineering ................................................................................. 5-20 Note ....................................................................................................................................................... 5-21 Creating Models ...................................................................................................................................... 5-22 How to Create a Model by Reverse Engineering ...................................................................................... 5-23 Step 1: Creating and Naming a New Model .............................................................................................. 5-24 Note About Creating and Naming a New Model ....................................................................................... 5-25 Step 2: Defining a Reverse-Engineering Strategy ..................................................................................... 5-26 Step 3: Starting the Reverse-Engineering Process ................................................................................... 5-28 Using RKM for Customized Reverse Engineering..................................................................................... 5-29 Selective Reverse Engineering ................................................................................................................ 5-31 Step 4: Fleshing Out Models ................................................................................................................... 5-32 Quiz ....................................................................................................................................................... 5-33 Summary ................................................................................................................................................ 5-35 Checklist of Lab Practice Activities........................................................................................................... 5-36 Practice 5-1 Overview Results of Reverse Engineering into Models .......................................................... 5-37

Setting Up a New ODI Project ................................................................................................................... 6-1 Setting Up a New ODI Project ................................................................................................................. 6-2 Objectives............................................................................................................................................... 6-3 ODI Projects ........................................................................................................................................... 6-4 What Is a Project? ................................................................................................................................... 6-5 Oracle Data Integrator Projects: Overview ............................................................................................... 6-6 How to Use ODI Projects in Your Work .................................................................................................... 6-7 Creating a New Project ........................................................................................................................... 6-8 Using Folders ......................................................................................................................................... 6-9 What Is a Folder?.................................................................................................................................... 6-10 Creating a New Folder ............................................................................................................................ 6-11 Organizing Projects and Folders .............................................................................................................. 6-12 Understanding Knowledge Modules ......................................................................................................... 6-13 What Is a Knowledge Module? ................................................................................................................ 6-14 Types of Knowledge Modules .................................................................................................................. 6-15 Which Knowledge Modules Are Needed? ................................................................................................ 6-17 Knowledge Modules: Examples ............................................................................................................... 6-18 Importing Knowledge Modules ................................................................................................................. 6-19 Replacing Existing KMs ........................................................................................................................... 6-20 Knowledge Module Editor ........................................................................................................................ 6-22 Editing a Knowledge Module ................................................................................................................... 6-23 Exporting and Importing Objects .............................................................................................................. 6-24 Exporting and Importing .......................................................................................................................... 6-25 Exporting an Object................................................................................................................................. 6-26 Importing an Object ................................................................................................................................. 6-27 ID Numbers: Overview ............................................................................................................................ 6-28 Import Types........................................................................................................................................... 6-29 Choosing the Import Mode ...................................................................................................................... 6-30 Import Report .......................................................................................................................................... 6-31 What Is a Marker? ................................................................................................................................... 6-33 Tagging Objects with Markers ................................................................................................................. 6-34 Removing Markers .................................................................................................................................. 6-35 Marker Groups ........................................................................................................................................ 6-36



v

Project and Global Markers ..................................................................................................................... 6-37 Creating a Marker Group ......................................................................................................................... 6-38 Quiz ....................................................................................................................................................... 6-39 Summary ................................................................................................................................................ 6-41 Checklist of Lab Practice Activities........................................................................................................... 6-42 Practice 6-1 Overview ............................................................................................................................. 6-43

Organizing ODI Models and Creating ODI Datastores ............................................................................. 7-1 Organizing ODI Models and Creating ODI Datastores ............................................................................. 7-2 Objectives............................................................................................................................................... 7-3 Organizing Models .................................................................................................................................. 7-4 What Is a Model Folder? ......................................................................................................................... 7-5 Creating a Model Folder .......................................................................................................................... 7-6 What Is a Submodel? .............................................................................................................................. 7-7 Creating a Submodel .............................................................................................................................. 7-8 Organizing Datastores into Submodels .................................................................................................... 7-9 Setting Up Automatic Distribution ............................................................................................................ 7-10 Creating Datastores ................................................................................................................................ 7-11 Creating a Datastore in a Model .............................................................................................................. 7-13 Adding Columns to a Datastore ............................................................................................................... 7-14 Constraints in ODI ................................................................................................................................... 7-15 What Is a Constraint in ODI? ................................................................................................................... 7-16 Constraints in ODI ................................................................................................................................... 7-17 Creating a Mandatory Column ................................................................................................................. 7-18 Creating Keys and References ................................................................................................................ 7-19 Creating a Key ........................................................................................................................................ 7-20 Checking a Key....................................................................................................................................... 7-21 Creating a Reference .............................................................................................................................. 7-22 Creating a Simple Reference ................................................................................................................... 7-23 Creating a Complex Reference ................................................................................................................ 7-24 Checking a Reference ............................................................................................................................. 7-25 Creating Conditions................................................................................................................................. 7-26 Creating a Condition ............................................................................................................................... 7-27 Checking a Condition .............................................................................................................................. 7-28 Overview ................................................................................................................................................ 7-29 When and Why? ..................................................................................................................................... 7-30 An Overview of the Process .................................................................................................................... 7-31 Exploring Your Data ................................................................................................................................ 7-32 Displaying the Contents of a Datastore .................................................................................................... 7-33 Viewing the Distribution of Values ............................................................................................................ 7-34 Analyzing the Contents of a Datastore ..................................................................................................... 7-35 Constructing Business Rules ................................................................................................................... 7-36 Defining Business Rules in ODI ............................................................................................................... 7-37 From Business Rules to Constraints ........................................................................................................ 7-38 Deducing Constraints from Data Analysis ................................................................................................ 7-39 Testing a Constraint ................................................................................................................................ 7-40 Auditing a Model or Datastore ................................................................................................................. 7-41 How to Review Erroneous Records ......................................................................................................... 7-42 Quiz ....................................................................................................................................................... 7-43 Summary ................................................................................................................................................ 7-45



vi

Checklist of Lab Practice Activities........................................................................................................... 7-46 Practice 7-1 Overview ............................................................................................................................. 7-47

ODI Interface Concepts............................................................................................................................. 8-1 ODI Interface Concepts ........................................................................................................................... 8-2 Objectives............................................................................................................................................... 8-3 ODI Interfaces ........................................................................................................................................ 8-4 What Is an Interface? .............................................................................................................................. 8-5 Business Rules for Interfaces .................................................................................................................. 8-6 Where Are the Rules Defined? ................................................................................................................ 8-7 Mapping, Join, Filter, Lookup, and Data Sets ........................................................................................... 8-8 What Is a Mapping? ................................................................................................................................ 8-9 What Is a Join? ....................................................................................................................................... 8-10 What Is a Filter? ...................................................................................................................................... 8-11 What Is a Lookup? .................................................................................................................................. 8-12 What Is a Data Set? ................................................................................................................................ 8-13 Behind the Rules .................................................................................................................................... 8-14 How Does ODI Implement Business Rules?............................................................................................. 8-15 A Business Problem ................................................................................................................................ 8-16 Implementing the Rules ........................................................................................................................... 8-17 Integration Process ................................................................................................................................. 8-18 Process Details ....................................................................................................................................... 8-19 Process Implementation: Example 1 ........................................................................................................ 8-20 Process Implementation: Example 2 ........................................................................................................ 8-21 Process Implementation: Example 3 ........................................................................................................ 8-22 Staging Area and Execution Location ...................................................................................................... 8-23 What Is the Staging Area? ....................................................................................................................... 8-24 Execution Location .................................................................................................................................. 8-25 Understanding Knowledge Modules ......................................................................................................... 8-26 From Business Rules to Processes ......................................................................................................... 8-27 Knowledge Modules ................................................................................................................................ 8-28 What Is a Knowledge Module? ................................................................................................................ 8-29 Code Generation .................................................................................................................................... 8-30 KM Types Used in Interfaces ................................................................................................................... 8-31 Interfaces: An Overview .......................................................................................................................... 8-32 The Purpose of an Interface .................................................................................................................... 8-33 Creating a One-to-One Interface.............................................................................................................. 8-34 Creating and Naming an Interface ........................................................................................................... 8-35 Defining the Target Datastore .................................................................................................................. 8-36 Important Note ........................................................................................................................................ 8-37 Defining the Source Datastore ................................................................................................................. 8-38 What Is a Mapping? ................................................................................................................................ 8-40 Defining the Mappings............................................................................................................................. 8-41 Valid Mapping Types ............................................................................................................................... 8-42 Saving the Interface ................................................................................................................................ 8-43 Executing the Interface............................................................................................................................ 8-44 Quiz ....................................................................................................................................................... 8-45 Summary ................................................................................................................................................ 8-47 Checklist of Lab Practice Activities........................................................................................................... 8-48 Practice 8-1 Overview ............................................................................................................................. 8-49



vii

Designing Interfaces ................................................................................................................................. 9-1 Designing Interfaces ............................................................................................................................... 9-2 Objectives............................................................................................................................................... 9-3 Multiple Sources and Joins ...................................................................................................................... 9-4 Multiple-Source Datastores ..................................................................................................................... 9-5 Creating a Join Manually ......................................................................................................................... 9-6 Advanced Joins ...................................................................................................................................... 9-7 Types of Joins ........................................................................................................................................ 9-8 Setting Up a Join .................................................................................................................................... 9-9 Lookups.................................................................................................................................................. 9-11 Using Lookups ........................................................................................................................................ 9-12 Lookup Wizard ........................................................................................................................................ 9-13 Lookup Limitations .................................................................................................................................. 9-14 Filtering Data .......................................................................................................................................... 9-15 Filters in ODI........................................................................................................................................... 9-16 Defining a Filter Manually ........................................................................................................................ 9-17 Setting Up a Filter ................................................................................................................................... 9-18 An Overview of the Flow in ODI Interface ................................................................................................. 9-19 Flow ....................................................................................................................................................... 9-20 What Defines the Flow? .......................................................................................................................... 9-21 The Scenario .......................................................................................................................................... 9-22 The Basic Process .................................................................................................................................. 9-23 Selecting a Staging Area ......................................................................................................................... 9-24 The Purpose of a Staging Area ................................................................................................................ 9-25 Placing the Staging Area ......................................................................................................................... 9-26 Important Note ........................................................................................................................................ 9-27 How to Specify the Staging Area ............................................................................................................. 9-28 Configuring Filters, Joins, Mappings, and Lookups ................................................................................... 9-29 Options for Filters, Joins, Mappings, and Lookups .................................................................................... 9-30 Setting Options for Filters, Joins, Mappings, and Lookups ........................................................................ 9-31 How to Disable a Transformation ............................................................................................................. 9-32 How to Enable a Mapping for Inserts or Updates ...................................................................................... 9-33 Execution Location .................................................................................................................................. 9-34 Execution Location and Syntax ................................................................................................................ 9-35 Why Change the Execution Location? ..................................................................................................... 9-36 How to Change the Execution Location.................................................................................................... 9-37 ODI Interface Execution Simulation ......................................................................................................... 9-38 Selecting the Knowledge Module ............................................................................................................. 9-39 Which KMs for Which Flow? .................................................................................................................... 9-40 More About KMs ..................................................................................................................................... 9-42 Identifying IKMs and LKMs ...................................................................................................................... 9-43 IKMs and LKMs: Strategies and Methods................................................................................................. 9-44 How to Specify an LKM ........................................................................................................................... 9-45 How to Specify an IKM ............................................................................................................................ 9-46 Common KM Options .............................................................................................................................. 9-47 Flow: Example 1 ..................................................................................................................................... 9-48 Flow: Example 2 ..................................................................................................................................... 9-49 Flow: Example 3 ..................................................................................................................................... 9-51 Quiz ....................................................................................................................................................... 9-52



viii

Summary ................................................................................................................................................ 9-53 Checklist of Lab Practice Activities........................................................................................................... 9-54 Practice 9-1 Overview ............................................................................................................................. 9-55 Practice 9-2 Overview ............................................................................................................................. 9-56

Interfaces: Monitoring and Debugging ..................................................................................................... 10-1 Interfaces: Monitoring and Debugging ...................................................................................................... 10-2 Objectives............................................................................................................................................... 10-3 Monitoring Interfaces ............................................................................................................................... 10-4 Operator Navigator: Viewing the Log ....................................................................................................... 10-5 Using Operator Navigator ........................................................................................................................ 10-6 Sessions, Steps, Tasks: The Hierarchy .................................................................................................... 10-7 Viewing Details of Sessions, Steps, and Tasks ........................................................................................ 10-8 How to Monitor Execution of an Interface ................................................................................................. 10-9 How to Troubleshoot a Session ............................................................................................................... 10-10 1. Identifying the Error ............................................................................................................................. 10-11 2. Reviewing the Code ............................................................................................................................ 10-12 3. How to Fix the Code and Restart the Session ...................................................................................... 10-13 4. Fixing the Interface .............................................................................................................................. 10-14 Keys to Reviewing the Generated Code................................................................................................... 10-15 Working with Errors ................................................................................................................................. 10-16 Common Errors and Symptoms ............................................................................................................... 10-17 Important Note ........................................................................................................................................ 10-19 Tips for Preventing Errors ........................................................................................................................ 10-20 Using Quick-Edit Editor ........................................................................................................................... 10-21 Quiz ....................................................................................................................................................... 10-23 Summary ................................................................................................................................................ 10-25 Checklist of Lab Practice Activities........................................................................................................... 10-26 Practice 10-1 Overview ........................................................................................................................... 10-27

Designing Interfaces: Advanced Topics 1 ................................................................................................ 11-1 Designing Interfaces: Advanced Topics 1 ................................................................................................ 11-2 Objectives............................................................................................................................................... 11-3 Working with Business Rules................................................................................................................... 11-4 Business Rules in Interfaces ................................................................................................................... 11-5 Business Rule Elements.......................................................................................................................... 11-6 More Elements........................................................................................................................................ 11-7 The Expression Editor ............................................................................................................................. 11-8 Using Variables....................................................................................................................................... 11-10 Using a Variable in Code ......................................................................................................................... 11-11 Binding Versus Substitution ..................................................................................................................... 11-13 Note: Case Sensitivity ............................................................................................................................. 11-14 Data Sets and Set-Based Operators ........................................................................................................ 11-15 Flow with Multiple Data Sets .................................................................................................................... 11-16 Defining a Data Set ................................................................................................................................. 11-17 Using Set-Based Operators ..................................................................................................................... 11-18 Using Sequences .................................................................................................................................... 11-19 Types of Sequences ............................................................................................................................... 11-20 Support for Native Sequences ................................................................................................................. 11-21 Creating a Native Sequence .................................................................................................................... 11-22 Referring to Sequences ........................................................................................................................... 11-23



ix

Note: Sequences Updated by Agent ........................................................................................................ 11-24 Using Standard Sequences in Mappings Correctly ................................................................................... 11-25 Using ODI Standard Sequences in Mappings ........................................................................................... 11-26 Populating Native Identity Columns ......................................................................................................... 11-27 Note ....................................................................................................................................................... 11-28 Automatic Temporary Index Management ................................................................................................ 11-29 Quiz ....................................................................................................................................................... 11-30 Summary ................................................................................................................................................ 11-31 Checklist of Lab Practice Activities........................................................................................................... 11-32 Practice 11-1 Overview ........................................................................................................................... 11-33 Practice 11-2 Overview ........................................................................................................................... 11-34 Practice 11-3 Overview ........................................................................................................................... 11-35

Designing Interfaces: Advanced Topics 2 ................................................................................................ 12-1 Designing Interfaces: Advanced Topics 2 ................................................................................................ 12-2 Objectives............................................................................................................................................... 12-3 Partitioning ............................................................................................................................................. 12-4 Definition in Datastore after Reverse Engineering .................................................................................... 12-5 Using Partitioning in an Interface ............................................................................................................. 12-6 Temporary Interfaces .............................................................................................................................. 12-7 Using Temporary Interfaces: Example ..................................................................................................... 12-8 Derived Select for Temporary Interfaces .................................................................................................. 12-9 Using User Functions .............................................................................................................................. 12-10 What Is a User Function? ........................................................................................................................ 12-11 Why Use User Functions? ....................................................................................................................... 12-12 Properties of User Functions ................................................................................................................... 12-14 Using User Functions .............................................................................................................................. 12-15 How to Create a User Function ................................................................................................................ 12-16 Defining an Implementation ..................................................................................................................... 12-17 Syntax and Implementations ................................................................................................................... 12-18 User Functions at Design Time ................................................................................................................ 12-19 User Functions at Run Time .................................................................................................................... 12-20 Note: Functions in Execution Log ............................................................................................................ 12-21 Substitution Methods ............................................................................................................................... 12-22 Using Substitution Methods ..................................................................................................................... 12-23 Substitution Methods: Examples .............................................................................................................. 12-25 Modifying Knowledge Modules ................................................................................................................ 12-26 Description of KM Steps .......................................................................................................................... 12-27 Details of the Steps ................................................................................................................................. 12-28 Setting KM Options ................................................................................................................................. 12-29 Developing Your Own KM: Guidelines ..................................................................................................... 12-30 Quiz ....................................................................................................................................................... 12-32 Summary ................................................................................................................................................ 12-33 Checklist of Lab Practice Activities........................................................................................................... 12-34 Practice 12-1 Overview ........................................................................................................................... 12-35 Practice 12-2 Overview ........................................................................................................................... 12-36

Using ODI Procedures .............................................................................................................................. 13-1 Using ODI Procedures ............................................................................................................................ 13-2 Objectives............................................................................................................................................... 13-3 Procedures: Overview ............................................................................................................................. 13-4



x

What Is a Procedure? ............................................................................................................................. 13-5 Procedure: Examples .............................................................................................................................. 13-6 Creating Procedures: Overview ............................................................................................................... 13-8 Creating a Blank Procedure..................................................................................................................... 13-9 How to Create a New Procedure ............................................................................................................. 13-10 Adding Commands ................................................................................................................................. 13-11 Creating a Command .............................................................................................................................. 13-12 Arranging Steps in Order ......................................................................................................................... 13-14 Which Parameters Should Be Set? .......................................................................................................... 13-15 Valid Types of Commands ...................................................................................................................... 13-16 More Elements........................................................................................................................................ 13-17 Why Use a Source Command? ............................................................................................................... 13-18 Adding Options ....................................................................................................................................... 13-19 Types of Options ..................................................................................................................................... 13-20 How to Create a New Option ................................................................................................................... 13-21 How to Make a Command Optional ......................................................................................................... 13-22 Using an Option Value in a Command ..................................................................................................... 13-23 Running a Procedure .............................................................................................................................. 13-24 Procedure Execution ............................................................................................................................... 13-25 Using the Operator Navigator to View Results .......................................................................................... 13-26 Quiz ....................................................................................................................................................... 13-27 Summary ................................................................................................................................................ 13-29 Checklist of Lab Practice Activities........................................................................................................... 13-30 Practice 13-1 Overview ........................................................................................................................... 13-31

Using ODI Packages ................................................................................................................................. 14-1 Using ODI Packages ............................................................................................................................... 14-2 Objectives............................................................................................................................................... 14-3 Packages: Overview ............................................................................................................................... 14-4 What Is a Package? ................................................................................................................................ 14-5 How to Create a Package ........................................................................................................................ 14-6 1. Creating and Naming a Package ......................................................................................................... 14-7 How to Create and Name a Package ....................................................................................................... 14-8 Package Diagram ................................................................................................................................... 14-9 Package Diagram Toolbar ....................................................................................................................... 14-10 2. Adding Steps to the Package ............................................................................................................... 14-12 Package Steps........................................................................................................................................ 14-13 How to Create a Package Step ................................................................................................................ 14-14 What Is an ODI Tool? .............................................................................................................................. 14-15 How to Create an ODI Tool Step ............................................................................................................. 14-16 Note ....................................................................................................................................................... 14-17 3. Arranging Package Steps in a Sequence ............................................................................................. 14-18 Sequencing Steps ................................................................................................................................... 14-19 A Simple Package ................................................................................................................................... 14-20 How to Sequence Package Steps ............................................................................................................ 14-21 Executing a Package .............................................................................................................................. 14-23 Review of Package Steps ........................................................................................................................ 14-25 Basic Step Types .................................................................................................................................... 14-26 Advanced Step Types ............................................................................................................................. 14-27 Model, Submodel, and Datastore Steps ................................................................................................... 14-28



xi

How to Create Model, Submodel, and Datastore Steps ........................................................................... 14-29 Models, Submodels, and Datastore Steps................................................................................................ 14-30 Variable Steps ........................................................................................................................................ 14-33 How to Create a Variable Step ................................................................................................................ 14-34 Variable Steps ........................................................................................................................................ 14-35 Controlling the Execution Path ................................................................................................................. 14-37 Controlling Execution .............................................................................................................................. 14-38 Error Handling ........................................................................................................................................ 14-39 How to Create a Loop ............................................................................................................................. 14-40 The Advanced Tab .................................................................................................................................. 14-41 Quiz ....................................................................................................................................................... 14-42 Summary ................................................................................................................................................ 14-44 Checklist of Lab Practice Activities........................................................................................................... 14-45 Practice 14-1 Overview ........................................................................................................................... 14-46 Practice 14-2 Overview ........................................................................................................................... 14-47

Managing ODI Versions ............................................................................................................................ 15-1 Managing ODI Versions .......................................................................................................................... 15-2 Objectives............................................................................................................................................... 15-3 Overview of ODI Version Management .................................................................................................... 15-4 What Is Version Management? ................................................................................................................ 15-5 Working with Object Flags ....................................................................................................................... 15-6 Creating Versions ................................................................................................................................... 15-7 Restoring Versions .................................................................................................................................. 15-8 Using Version Browser ............................................................................................................................ 15-9 Using the Version Comparison Tool ......................................................................................................... 15-10 Opening the Version Comparison Tool .................................................................................................... 15-12 Working with Solutions ............................................................................................................................ 15-14 Working with Solutions: Synchronizing ..................................................................................................... 15-16 Handling Concurrent Changes ................................................................................................................. 15-18 Quiz ....................................................................................................................................................... 15-20 Summary ................................................................................................................................................ 15-21 Checklist of Lab Practice Activities........................................................................................................... 15-22 Practice 15-1 Overview ........................................................................................................................... 15-23

Managing ODI Scenarios .......................................................................................................................... 16-1 Managing ODI Scenarios ........................................................................................................................ 16-2 Objectives............................................................................................................................................... 16-3 Scenarios ............................................................................................................................................... 16-4 What Is a Scenario? ................................................................................................................................ 16-5 Properties of Scenarios ........................................................................................................................... 16-6 Managing Scenarios ............................................................................................................................... 16-7 Scenario-Related Tasks .......................................................................................................................... 16-8 Generating a Scenario ............................................................................................................................ 16-9 Regenerating a Scenario ......................................................................................................................... 16-10 Generation Versus Regeneration ............................................................................................................. 16-11 Executing a Scenario from the GUI .......................................................................................................... 16-12 Executing a Scenario from a Command Line............................................................................................ 16-13 Executing a Scenario from a Package ..................................................................................................... 16-14 Exporting a Scenario ............................................................................................................................... 16-15 Preparing for Deployment ........................................................................................................................ 16-16



xii

Preparing Scenarios for Deployment........................................................................................................ 16-17 Automating Scenario Management .......................................................................................................... 16-18 Scheduling the ODI Scenario .................................................................................................................. 16-19 Scheduling ODI Scenario with External Scheduler ................................................................................... 16-22 Managing Schedules ............................................................................................................................... 16-23 Quiz ....................................................................................................................................................... 16-24 Summary ................................................................................................................................................ 16-25 Checklist of Lab Practice Activities........................................................................................................... 16-26 Practice 16-1 Overview ........................................................................................................................... 16-27

Enforcing Data Quality with ODI ............................................................................................................... 17-1 Enforcing Data Quality with ODI .............................................................................................................. 17-2 Objectives............................................................................................................................................... 17-3 Data Quality ............................................................................................................................................ 17-4 Why Data Quality? .................................................................................................................................. 17-5 When to Enforce Data Quality ................................................................................................................. 17-6 Data Quality in Source Applications ......................................................................................................... 17-7 Data Quality Control in the Integration Process ........................................................................................ 17-8 Data Quality in the Target Applications .................................................................................................... 17-9 Business Rules for Data Quality ............................................................................................................. 17-10 Data Quality Business Rules ................................................................................................................... 17-11 From Business Rules to Constraints ........................................................................................................ 17-12 Enforcing Data Quality with ODI .............................................................................................................. 17-13 Data Quality System: Overview ............................................................................................................... 17-14 Static and Flow Controls: Differences ...................................................................................................... 17-15 Data Quality Control: Properties .............................................................................................................. 17-16 Synchronous Control ............................................................................................................................... 17-17 What Is a Constraint? .............................................................................................................................. 17-18 What Can Be Checked? .......................................................................................................................... 17-19 How to Enforce Data Quality in an Interface ............................................................................................. 17-20 1. Enabling Static or Flow Control for an Interface .................................................................................... 17-21 Setting Up Static or Flow Control ............................................................................................................. 17-22 How to Enable Static or Flow Control ....................................................................................................... 17-23 2. Setting the Options .............................................................................................................................. 17-24 How to Set the Options ........................................................................................................................... 17-25 3. Selecting Which Constraints to Enforce ............................................................................................... 17-27 How to Select Which Constraints to Enforce ............................................................................................ 17-28 How to Select Which Constraints to Check .............................................................................................. 17-29 Differences Between Control Types ......................................................................................................... 17-30 4. Reviewing Erroneous Records ............................................................................................................. 17-31 How to Review Erroneous Records ......................................................................................................... 17-32 Other Data Quality Tools ......................................................................................................................... 17-33 Quiz ....................................................................................................................................................... 17-34 Summary ................................................................................................................................................ 17-36 Checklist of Lab Practice Activities........................................................................................................... 17-37 Practice 17-1 Overview ........................................................................................................................... 17-38

Working with Changed Data Capture ....................................................................................................... 18-1 Working with Changed Data Capture ....................................................................................................... 18-2 Objectives............................................................................................................................................... 18-3 Why Changed Data Capture? .................................................................................................................. 18-4



xiii

CDC Techniques..................................................................................................................................... 18-5 Changed Data Capture in ODI ................................................................................................................. 18-6 Journalizing Components ........................................................................................................................ 18-7 CDC Infrastructure in ODI ....................................................................................................................... 18-8 Simple Versus Consistent Set Journalizing .............................................................................................. 18-9 Limitations of Simple CDC Journalizing: Example .................................................................................... 18-10 Consistent CDC Journalizing ................................................................................................................... 18-11 Consistent CDC: Infrastructure ................................................................................................................ 18-12 Setting Up Journalizing ........................................................................................................................... 18-13 Setting CDC Parameters: Example .......................................................................................................... 18-14 Adding a Subscriber: Example ................................................................................................................. 18-15 Starting Journal: Example ....................................................................................................................... 18-16 Journalizing Status .................................................................................................................................. 18-17 Viewing Data/Changed Data: Example .................................................................................................... 18-18 Using Changed Data ............................................................................................................................... 18-19 Quiz ....................................................................................................................................................... 18-21 Summary ................................................................................................................................................ 18-23 Checklist of Lab Practice Activities........................................................................................................... 18-24 Practice 18-1 Overview ........................................................................................................................... 18-25

Advanced ODI Administration .................................................................................................................. 19-1 Advanced ODI Administration .................................................................................................................. 19-2 Objectives............................................................................................................................................... 19-3 Setting Up ODI Security .......................................................................................................................... 19-4 Introduction to ODI Security Navigator ..................................................................................................... 19-5 Overview of Security Concepts ................................................................................................................ 19-8 Defining Security Policies ........................................................................................................................ 19-10 Creating Profiles ..................................................................................................................................... 19-11 Using Generic and Nongeneric Profiles.................................................................................................... 19-12 Built-in Profiles ........................................................................................................................................ 19-13 Creating Users ........................................................................................................................................ 19-14 Assigning a Profile to a User ................................................................................................................... 19-15 Assigning an Authorization by Profile or User ........................................................................................... 19-16 Defining Password Policies ..................................................................................................................... 19-18 Setting User Parameters ......................................................................................................................... 19-20 Overview of ODI Security Integration ....................................................................................................... 19-21 Implementing External Authentication (OPSS) ......................................................................................... 19-22 Implementing External Authentication (OPSS): Switching the Authentication Mode .................................. 19-24 Implementing External Password Storage ................................................................................................ 19-25 Managing ODI Reports ............................................................................................................................ 19-27 Types of ODI Reports ............................................................................................................................. 19-28 Generating Topology Reports .................................................................................................................. 19-29 Generated Topology Report: Example ..................................................................................................... 19-30 Version Comparison Report: Example ..................................................................................................... 19-31 Generating Object Reports ...................................................................................................................... 19-32 ODI Integration with Java EE ................................................................................................................... 19-33 Integration of ODI with Enterprise Manager .............................................................................................. 19-34 Overview of Java EE Agent and Enterprise Manager Configuration with WebLogic Domain ...................... 19-35 Using ODI Console: Example .................................................................................................................. 19-36 Quiz ....................................................................................................................................................... 19-37



xiv

Summary ................................................................................................................................................ 19-38 Checklist of Lab Practice Activities........................................................................................................... 19-39 Practice 19-1 Overview ........................................................................................................................... 19-40 Practice 19-2 Overview ........................................................................................................................... 19-41

Extending Oracle Data Integrator with SDK, Web Services, and SOA ..................................................... 20-1 Extending Oracle Data Integrator with SDK, Web Services, and SOA ....................................................... 20-2 Objectives............................................................................................................................................... 20-3 Interacting Programmatically with ODI ..................................................................................................... 20-4 Overview of ODI SDK ............................................................................................................................. 20-5 SDK-Supported ODI Operations .............................................................................................................. 20-6 ODI Operations Not Supported by SDK ................................................................................................... 20-7 ODI 11g SDK Usage Examples ............................................................................................................... 20-9 Combining Different APIs ........................................................................................................................ 20-10 Example of ODI SDK Setup and Performing an SDK Task Using Java...................................................... 20-11 Using Web Services with ODI .................................................................................................................. 20-13 Web Services in Action ........................................................................................................................... 20-14 Two Types of Web Services .................................................................................................................... 20-15 What Are Data Services? ........................................................................................................................ 20-16 Generation of Data Services .................................................................................................................... 20-17 Data Services in Action ........................................................................................................................... 20-18 Using Public Web Services...................................................................................................................... 20-19 What Are Public Web Services? .............................................................................................................. 20-20 Using Public Web Services...................................................................................................................... 20-21 Public Web Services in Action: Java EE................................................................................................... 20-22 Public Web Services in Action: Standalone Agent .................................................................................... 20-23 Installing Public Web Services ................................................................................................................. 20-24 A Simple SOAP Request for the OdiInvoke Web Service with Standalone Agent: Example ...................... 20-25 Note ....................................................................................................................................................... 20-26 A Simple SOAP Response for the OdiInvoke Web Service: Example ....................................................... 20-27 Invoking Web Services ............................................................................................................................ 20-28 OdiInvokeWebService Tool ..................................................................................................................... 20-29 Invoking a Web Service: Example ........................................................................................................... 20-32 Processing a Web Service Response ...................................................................................................... 20-36 Integration of ODI with SOA .................................................................................................................... 20-39 Ways to Integrate ODI with SOA.............................................................................................................. 20-40 ODI with SOA: Example 1 ....................................................................................................................... 20-42 ODI and Business Process Management ................................................................................................. 20-43 ODI with SOA: Example 2 ....................................................................................................................... 20-44 Creating an ODI Error Hospital with BPEL Human Workflow .................................................................... 20-46 Quiz ....................................................................................................................................................... 20-51 Summary ................................................................................................................................................ 20-53 Checklist of Lab Practice Activities........................................................................................................... 20-54 Practice 20-1 Overview ........................................................................................................................... 20-55

d64974gc10_toc.pdf

Documents