WIXLIB

Oracle Data Integrator 12c:Integration and AdministrationStudent Guide – Volume ID82167GC20Edition 2.0 August 2016 D97384Learn more from Oracle University at oracle.com/education/

AuthorsCopyright 2016, Oracle and/or its affiliates. All rights reserved.Elio BonazziDisclaimerSteve FriedbergThis document contains proprietary information and is protected by copyright andother intellectual property laws. You may copy and print this document solely for yourown use in an Oracle training course. The document may not be modified or altered inany way. Except where your use constitutes "fair use" under copyright law, you maynot use, share, download, upload, copy, print, display, perform, reproduce, publish,license, post, transmit, or distribute this document in whole or in part without theexpress authorization of Oracle.Technical Contributorsand ReviewersJulien TestutAlex KotopoulisEditorsThe 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.Raj KumarVijayalakshmi NarasimhanRestricted Rights NoticeNikita AbrahamIf this documentation is delivered to the United States Government or anyone usingthe documentation on behalf of the United States Government, the following notice isapplicable:Graphic DesignerSeema BopaiahPublishersU.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.Giri VenugopalTrademark NoticeJoseph FernandezOracle 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-4Agenda 1-7Why Oracle Data Integrator? 1-8Conventional Integration Process: ETL 1-10Extract Load Transform (E-LT) 1-11ODI Architecture and Components 1-13ODI Architecture 1-14ODI Components: Overview 1-16Using ODI Studio 1-17Designer Navigator (Work Repository) 1-18Operator Navigator (Work Repository) 1-19Topology Navigator (Master Repository) 1-20Security Navigator (Master Repository) 1-21What Is an Agent? 1-22ODI Agents 1-23Three Types of Agents: Java EE, Standalone, Collocated Standalone 1-24Using the Three Types of Agents 1-25Standalone Agent: Example 1-26ODI Console 1-27Enterprise Manager FMW Console 1-28Management Pack for ODI for Enterprise Manager Cloud Control 1-29Management Pack for ODI for EM CC ODI Home Page 1-30Agenda 1-31ODI Repositories 1-32Master and Work Repositories 1-33Repository Setup: Example 1-35Repository Setup: Multiple Master Repositories 1-36Components: Global View 1-37Possible ODI Methodology 1-38Checklist of Practice Activities 1-39Starting Oracle Data Integrator 1-40Using Online Help 1-41iii

Quiz 1-42Summary 1-44Practice 1-1 Overview: Logging In and Using Help 1-452Administering ODI RepositoriesObjectives 2-2Agenda 2-3Initial Repository Administration Tasks 2-4Steps to Set Up the ODI Repositories 2-51. Run Repository Creation Utility 2-61a. Create Schemas 2-71b. Create Passwords and Tablespaces 2-82. Connect to the Master/Work Repository 3. Create a Wallet 2-9Connecting to the Master/Work Repository 2-10Exporting the Master Repository 2-11Importing the Master Repository 2-13Creating a Work Repository – 1 2-14Creating a Work Repository – 2 2-15Changing the Work Repository Password 2-16Quiz 2-17Summary 2-18Checklist of Practice Activities 2-19Practice 2-1 Overview: Creating and Connecting to ODI Master and Work Repositories 2-203ODI Topology ConceptsObjectives 3-2Agenda 3-3What Is Topology? 3-4What Is in the Topology? 3-5Agenda 3-6What Is a Data Server? 3-7Data Servers: Examples 3-8Important Guideline 1 3-9What Is a Physical Schema? 3-10Physical Schemas: Properties 3-11Technology Terminology Among Vendors 3-12Important Guideline 2 3-13Agenda 3-14Infrastructure for Two Production Sites: Example 3-15ODI Design: Physical Architecture of the Two Production Sites 3-16Logical Schemas and Contexts 3-17iv

What Is a Logical Schema? 3-18Important Guideline 3 3-19Logical Versus Physical Architecture 3-20Design 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-25Agenda 3-27ODI Physical Agents 3-28Creating a Physical Agent 3-29ODI Agent Parameters 3-30Launching a Stand-Alone Agent: Examples 3-32Stopping the ODI Agent 3-33Deploying and Configuring a Java EE Agent 3-34Load 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-47Agenda 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: Configuring a Standalone Agent by Using the Common AdministrationModel 3-564Describing the Physical and Logical ArchitectureObjectives 4-2Agenda 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-9v

Agenda 4-10Physical Architecture View 4-11Prerequisites for Connecting to a Server 4-12Important 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-20Agenda 4-21Logical Architecture and Context Views 4-22Creating a Logical Schema 4-23Creating a Logical Agent 4-24Editing a Context to Link Logical and Physical Agents 4-25Quiz 4-26Summary 4-28Checklist of Practice Activities 4-29Practice 4-1 Overview: Working with Topology 4-305Setting Up a New ODI ProjectObjectives 5-2Agenda 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-7Agenda 5-8What Is a Folder? 5-9Creating a New Folder 5-10Organizing Projects and Folders 5-11Agenda 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-21vi

Agenda 5-22Exporting and Importing 5-23Exporting an Object 5-24Importing an Object 5-25ID Numbers: Overview 5-26Import Types 5-27Choosing the Import Mode 5-28Import Report 5-29Agenda 5-30What 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: Setting Up a New ODI Project 5-416Oracle Data Integrator Model ConceptsObjectives 6-2What Is a Model? 6-3Agenda 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-15Agenda 6-16What Is Reverse-Engineering? 6-17Methods for DBMS Reverse-Engineering 6-18Other Methods for Reverse-Engineering 6-19Standard Versus Customized Reverse-Engineering 6-20Reverse-Engineering Life Cycle 6-21Agenda 6-22Creating a Model by Reverse-Engineering 6-23vii

Step 1: Creating and Naming a New Model 6-24Note: Creating and Naming a New Model 6-25Step 2: Defining a Reverse-Engineering Strategy 6-26Step 3: Starting the Reverse-Engineering Process 6-28Using 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: Creating Models by Reverse-Engineering 6-397Organizing ODI Models and Creating ODI DatastoresObjectives 7-2Agenda 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-9Agenda 7-10Creating Datastores 7-11Creating a Datastore in a Model 7-12Adding Columns to a Datastore 7-13Agenda 7-14What Is a Constraint in ODI? 7-15Constraints in ODI 7-16Creating a Mandatory Column 7-17Agenda 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-24Agenda 7-25Creating a Condition 7-26Checking a Condition 7-27viii

Agenda 7-28Audit/Explore: When and Why 7-29Audit/Explore Process: Overview 7-30Agenda 7-31Displaying the Contents of a Datastore 7-32Viewing the Distribution of Values 7-33Analyzing the Contents of a Datastore 7-34Agenda 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-40Reviewing Erroneous Records 7-41Quiz 7-42Summary 7-44Checklist of Practice Activities 7-45Practice 7-1 Overview: Checking Data Quality in the Model 7-468ODI Mapping ConceptsObjectives 8-2Agenda 8-3What Is a Mapping? 8-4Business Rules for Mappings 8-5Where Are the Rules Defined? 8-6Agenda 8-7What Is an Expression? 8-8What Is a Join? 8-9What Is a Filter? 8-10What Is a Lookup? 8-11What Is a Set? 8-12What Are Some of the Others? 8-13New with Patch: Pivot and Unpivot 8-14Agenda 8-15How Does ODI Implement Business Rules? 8-16Business Problem 8-17Implementing the Rules 8-18Integration Process 8-19Process Details 8-20Process Implementation: Example 1 8-21Process Implementation: Example 2 8-22ix

Process Implementation: Example 3 8-23Agenda 8-24What Is a Staging Area? 8-25Execution Location 8-26Agenda 8-27From Business Rules to Processes 8-28Knowledge Modules 8-29What Is a Knowledge Module? 8-30Code Generation 8-31KM Types Used in Mappings 8-32Agenda 8-33Purpose of a Mapping 8-34What Is an Expression? 8-35Creating a One-to-One Mapping 8-36Creating and Naming a Mapping 8-37Defining the Target Datastore 8-38Multiple Targets 8-39Defining the Source Datastore 8-40Connecting the Ports to Make the Map 8-41Defining the Expressions 8-42Valid Expression Types 8-43Saving the Mapping 8-44Running the Mapping 8-45Quiz 8-46Summary 8-48Checklist of Practice Activities 8-49Practice 8-1 Overview: Mapping: Simple Transformations 8-509Designing MappingsObjectives 9-2Agenda 9-3Multiple-Source Datastores 9-4Creating a Join Manually 9-5Advanced Joins 9-6Types of Joins 9-7Setting Up a Join 9-8Creating Lookups 9-10Using Lookups 9-11Agenda 9-13Filters in ODI 9-14Defining a Filter Manually 9-15x