WIXLIB

Oracle Data Integrator 11g:Integration and AdministrationStudent Guide - Volume ID64974GC20Edition 2.0September 2012D78954

AuthorCopyright 2012, Oracle and/or its affiliates. All rights reserved.Richard GreenDisclaimerTechnical Contr ibutorsand Reviewer sAlex KotopoulisDenis GrayChristophe DupupetJulien TestutDavid AllanSachin ThatteViktor TchemodanovGerry JurrensVeerabhadra PutrevuEditor sRashmi RajagopalVijayalakshmi NarasimhanAju Kumar KumarGr aphic Designer sSatish BettegowdaSeema BopaiahPublisher sGiri VenugopalSumesh KoshySrividya RameshkumarThis document contains proprietary information and is protected by copyright andother intellectual property laws. You may copy and print this document solely for yourown use in an Oracle training course. The document may not be modified or alteredin any way. Except where your use constitutes "fair use" under copyright law, youmay not use, share, download, upload, copy, print, display, perform, reproduce,publish, license, post, transmit, or distribute this document in whole or in part withoutthe express authorization of Oracle.The information contained in this document is subject to change without notice. If youfind any problems in the document, please report them in writing to: Oracle University,500 Oracle Parkway, Redwood Shores, California 94065 USA. This document is notwarranted to be error-free.Restricted Rights NoticeIf this documentation is delivered to the United States Government or anyone usingthe documentation on behalf of the United States Government, the following notice isapplicable:U.S. GOVERNMENT RIGHTSThe U.S. Government’s rights to use, modify, reproduce, release, perform, display, ordisclose these training materials are restricted by the terms of the applicable Oraclelicense agreement and/or the applicable U.S. Government contract.Trademark NoticeOracle and Java are registered trademarks of Oracle and/or its affiliates. Other namesmay be trademarks of their respective owners.

Contents1Introduction to Integration and AdministrationCourse Objectives 1-2Lesson Objectives 1-3Agenda of Lessons 1-4Oracle Data Integrator: Introduction 1-7Why Oracle Data Integrator? 1-8Conventional Integration Process: ETL 1-10ELT 1-11ODI Architecture and Components 1-13ODI Architecture 1-14ODI Components: Overview 1-15Using ODI Studio 1-16Designer Navigator (Work Repository) 1-17Operator Navigator (Work Repository) 1-18Topology Navigator (Master Repository) 1-19Security Navigator (Master Repository) 1-20What Is an Agent? 1-21ODI Agents 1-22Two Types of Agents: Java EE and Standalone 1-23Using the Two Types of Agents 1-24Example of Standalone Agent 1-25ODI Console 1-26Enterprise Manager Console 1-27Oracle Data Integrator Repositories 1-28ODI Repositories 1-29Master and Work Repositories 1-30Repository Setup: Example 1-32Repository Setup: Multiple Master Repositories 1-33Components: A Global View 1-34Possible ODI Methodology 1-35Checklist of Practice Activities 1-36Starting Oracle Data Integrator 1-37Using Online Help 1-38Quiz 1-39iii

Summary 1-41Practice 1-1: Overview 1-422Administering ODI RepositoriesObjectives 2-2Administering the ODI Repositories 2-3Initial Repository Administration Tasks 2-4Steps to Set Up ODI Repositories 2-51. Creating Repository Storage Spaces 2-62. Creating the Master Repository 2-73. Connecting to the Master Repository 2-9Importing the Master Repository 2-11Exporting the Master Repository 2-124. Creating a Work Repository 2-135. Connecting to the Work Repository 2-15Changing the Work Repository Password 2-16Creating Repositories with the RCU 2-17Quiz 2-20Summary 2-21Checklist of Practice Activities 2-22Practice 2-1: Overview 2-233ODI Topology ConceptsObjectives 3-2ODI Topology: Overview 3-3What Is Topology? 3-4What Is in the Topology? 3-5Data Servers and Physical Schemas 3-6What Is a Data Server? 3-7Important Guideline 1 3-9What Is a Physical Schema? 3-10Physical Schemas: Properties 3-11Technology Terminology Among Vendors 3-12Important Guideline 2 and Recommendations 3-13Defining Topology: Example 3-14Example: Infrastructure for Two Production Sites 3-15ODI Design: Physical Architecture of the Two Production Sites 3-16Logical Schemas and Contexts 3-17What Is a Logical Schema? 3-18Important Guideline 3 3-19Logical Versus Physical Architecture 3-20iv

Note: Design Time Versus Run Time 3-21What Is a Context? 3-22A Context Maps a Logical to a Physical Schema 3-23Defining Contexts 3-24Mapping Logical and Physical Resources 3-25Agents in Topology 3-27ODI Physical Agents 3-28Creating a Physical Agent 3-29ODI Agent Parameters 3-30Launching a Standalone Agent: Examples 3-32Stopping the ODI Agent 3-33Deploying and Configuring a Java EE Agent 3-34Deploying and Configuring Java EE Agent 3-36Load Balancing: Example 3-37Important Guideline 5 3-39Infrastructure with Agents: Example 3-40Defining Agents: Example 3-41Special Case: Fragmentation Problem 3-42Special Case: Important Guideline 6 3-44Special Case: Defining the Physical Architecture 3-45Special Case: The Infrastructure 3-46Special Case: Physical Architecture in ODI 3-47Defining a Topology: Best Practices 3-48Planning the Topology 3-49Matrix of Logical and Physical Mappings 3-50Quiz 3-51Summary 3-54Checklist of Practice Activities 3-55Practice 3-1: Overview 3-564Describing the Physical and Logical ArchitectureObjectives 4-2Topology Navigator 4-3What Topology Navigator Contains 4-4Topology Navigator: Overview 4-5Review: Context Connects Logical to Physical 4-7Objects You Create in the Practice 4-8Defining a Context 4-9Creating Physical Architecture 4-10Physical Architecture View 4-11Prerequisites for Connecting to a Server 4-12v

Important Note 4-13Creating a Data Server 4-14Creating a Data Server: JDBC 4-15JDBC Driver 4-16JDBC URL 4-17Creating a Data Server: JNDI 4-18Testing a Data Server Connection 4-19Creating a Physical Schema 4-20Creating Logical Architecture 4-21Logical Architecture and Context Views 4-22Creating a Logical Schema 4-23Creating a Logical Agent 4-24Editing a Context to Link the Logical and Physical Agents 4-25Quiz 4-26Summary 4-28Checklist of Practice Activities 4-29Practice 4-1: Overview 4-305Setting Up a New ODI ProjectObjectives 5-2ODI Projects 5-3What Is a Project? 5-4Oracle Data Integrator Projects: Overview 5-5How to Use ODI Projects in Your Work 5-6Creating a New Project 5-7Using Folders 5-8What Is a Folder? 5-9Creating a New Folder 5-10Organizing Projects and Folders 5-11Understanding Knowledge Modules 5-12What Is a Knowledge Module? 5-13Types of Knowledge Modules 5-14Which Knowledge Modules Are Needed? 5-15Knowledge Modules: Examples 5-16Importing Knowledge Modules 5-17Replacing Existing KMs 5-18Knowledge Module Editor 5-20Editing a Knowledge Module 5-21Exporting and Importing Objects 5-22Exporting and Importing 5-23Exporting an Object 5-24vi

Importing an Object 5-25ID Numbers: Overview 5-26Import Types 5-27Choosing the Import Mode 5-28Import Report 5-29What Is a Marker? 5-31Tagging Objects with Markers 5-32Removing Markers 5-33Marker Groups 5-34Project and Global Markers 5-35Creating a Marker Group 5-36Quiz 5-37Summary 5-39Checklist of Practice Activities 5-40Practice 5-1: Overview 5-416Oracle Data Integrator Model ConceptsObjectives 6-2What Is a Model? 6-3Understanding the Relational Model 6-4Relational Model 6-5Relational Model: Tables and Columns 6-6Relational Model: Keys 6-7Relational Model: Foreign Keys 6-8Relational Model: Constraints 6-9Relational Model: Indexes 6-11Relational Model Support in ODI 6-12Additional Metadata in ODI 6-13FlexFields 6-15Understanding Reverse-Engineering 6-16What Is Reverse-Engineering? 6-17Methods for DBMS Reverse-Engineering 6-18Other Methods for Reverse-Engineering 6-19Standard Versus Customized Reverse- Engineering 6-20Note 6-21Creating Models 6-22How to Create a Model by Reverse-Engineering 6-23Step 1: Creating and Naming a New Model 6-24Note About Creating and Naming a New Model 6-25Step 2: Defining a Reverse-Engineering Strategy 6-26Step 3: Starting the Reverse-Engineering Process 6-28vii

Using RKM for Customized Reverse-Engineering 6-29Selective Reverse-Engineering 6-31Step 4: Fleshing Out Models 6-32Shortcuts 6-33Smart Export and Import 6-34Quiz 6-35Summary 6-37Checklist of Practice Activities 6-38Practice 6-1 Overview: Results of Reverse-Engineering into Models 6-397Organizing ODI Models and Creating ODI DatastoresObjectives 7-2Organizing Models 7-3What Is a Model Folder? 7-4Creating a Model Folder 7-5What Is a Submodel? 7-6Creating a Submodel 7-7Organizing Datastores into Submodels 7-8Setting Up Automatic Distribution 7-9Creating Datastores 7-10Creating a Datastore in a Model 7-12Adding Columns to a Datastore 7-13Constraints in ODI 7-14What Is a Constraint in ODI? 7-15Constraints in ODI 7-16Creating a Mandatory Column 7-17Creating Keys and References 7-18Creating a Key 7-19Checking a Key 7-20Creating a Reference 7-21Creating a Simple Reference 7-22Creating a Complex Reference 7-23Checking a Reference 7-24Creating Conditions 7-25Creating a Condition 7-26Checking a Condition 7-27Overview 7-28When and Why? 7-29An Overview of the Process 7-30Exploring Your Data 7-31Displaying the Contents of a Datastore 7-32viii

Viewing the Distribution of Values 7-33Analyzing the Contents of a Datastore 7-34Constructing Business Rules 7-35Defining Business Rules in ODI 7-36From Business Rules to Constraints 7-37Deducing Constraints from Data Analysis 7-38Testing a Constraint 7-39Auditing a Model or Datastore 7-40How to Review Erroneous Records 7-41Quiz 7-42Summary 7-44Checklist of Practice Activities 7-45Practice 7-1: Overview 7-468ODI Interface ConceptsObjectives 8-2ODI Interfaces 8-3What Is an Interface? 8-4Business Rules for Interfaces 8-5Where Are the Rules Defined? 8-6Mapping, Join, Filter, Lookup, and Data Sets 8-7What Is a Mapping? 8-8What Is a Join? 8-9What Is a Filter? 8-10What Is a Lookup? 8-11What Is a Data Set? 8-12Behind the Rules 8-13How Does ODI Implement Business Rules? 8-14A Business Problem 8-15Implementing the Rules 8-16Integration Process 8-17Process Details 8-18Process Implementation: Example 1 8-19Process Implementation: Example 2 8-20Process Implementation: Example 3 8-21Staging Area and Execution Location 8-22What Is the Staging Area? 8-23Execution Location 8-24Understanding Knowledge Modules 8-25From Business Rules to Processes 8-26Knowledge Modules 8-27ix

What Is a Knowledge Module? 8-28Code Generation 8-29KM Types Used in Interfaces 8-30Interfaces: An Overview 8-31The Purpose of an Interface 8-32Creating a One-to-One Interface 8-33Creating and Naming an Interface 8-34Defining the Target Datastore 8-35Important Note 8-36Defining the Source Datastore 8-37What Is a Mapping? 8-38Defining the Mappings 8-39Valid Mapping Types 8-40Saving the Interface 8-41Executing the Interface 8-42Quiz 8-43Summary 8-45Checklist of Practice Activities 8-46Practice 8-1: Overview 8-479Designing InterfacesObjectives 9-2Multiple Sources and Joins 9-3Multiple-Source Datastores 9-4Creating a Join Manually 9-5Advanced Joins 9-6Types of Joins 9-7Setting Up a Join 9-8Lookups 9-10Using Lookups 9-11Lookup Wizard 9-12Lookup Limitations 9-13Filtering Data 9-14Filters in ODI 9-15Defining a Filter Manually 9-16Setting Up a Filter 9-17Overview of the Flow in ODI Interface 9-18Flow 9-19What Defines the Flow? 9-20The Scenario 9-21The Basic Process 9-22x