Oracle Data Integrator Integation And Administration Student Guide

User Manual:

Open the PDF directly: View PDF .
Page Count: 466

Download
Open PDF In Browser	View PDF

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Student Guide - Volume I

D64974GC20

Edition 2.0

September 2012

D78954

Oracle University and (Oracle Corporation) use only.

Oracle Data Integrator 11g:
Integration and Administration

Richard Green

Disclaimer

Technical Contr ibutors
and Reviewer s
Alex Kotopoulis
Denis Gray
Christophe Dupupet
Julien Testut
David Allan
Sachin Thatte
Viktor Tchemodanov
Gerry Jurrens
Veerabhadra Putrevu

Editor s
Rashmi Rajagopal
Vijayalakshmi Narasimhan
Aju Kumar Kumar

Gr aphic Designer s
Satish Bettegowda
Seema Bopaiah

Publisher s
Giri Venugopal
Sumesh Koshy
Srividya Rameshkumar

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.

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Author

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

iii

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Contents

Administering ODI Repositories
Objectives 2-2
Administering the ODI Repositories 2-3
Initial Repository Administration Tasks 2-4
Steps to Set Up ODI Repositories 2-5
1. Creating Repository Storage Spaces 2-6
2. Creating the Master Repository 2-7
3. Connecting to the Master Repository 2-9
Importing the Master Repository 2-11
Exporting the Master Repository 2-12
4. Creating a Work Repository 2-13
5. Connecting to the Work Repository 2-15
Changing the Work Repository Password 2-16
Creating Repositories with the RCU 2-17
Quiz 2-20
Summary 2-21
Checklist of Practice Activities 2-22
Practice 2-1: Overview 2-23

ODI Topology Concepts
Objectives 3-2
ODI Topology: Overview 3-3
What Is Topology? 3-4
What Is in the Topology? 3-5
Data Servers and Physical Schemas 3-6
What Is a Data Server? 3-7
Important Guideline 1 3-9
What Is a Physical Schema? 3-10
Physical Schemas: Properties 3-11
Technology Terminology Among Vendors 3-12
Important Guideline 2 and Recommendations 3-13
Defining Topology: Example 3-14
Example: Infrastructure for Two Production Sites 3-15
ODI Design: Physical Architecture of the Two Production Sites 3-16
Logical Schemas and Contexts 3-17
What Is a Logical Schema? 3-18
Important Guideline 3 3-19
Logical Versus Physical Architecture 3-20
iv

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Summary 1-41
Practice 1-1: Overview 1-42

Describing the Physical and Logical Architecture
Objectives 4-2
Topology Navigator 4-3
What Topology Navigator Contains 4-4
Topology Navigator: Overview 4-5
Review: Context Connects Logical to Physical 4-7
Objects You Create in the Practice 4-8
Defining a Context 4-9
Creating Physical Architecture 4-10
Physical Architecture View 4-11
Prerequisites for Connecting to a Server 4-12
v

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Note: Design Time Versus Run Time 3-21
What Is a Context? 3-22
A Context Maps a Logical to a Physical Schema 3-23
Defining Contexts 3-24
Mapping Logical and Physical Resources 3-25
Agents in Topology 3-27
ODI Physical Agents 3-28
Creating a Physical Agent 3-29
ODI Agent Parameters 3-30
Launching a Standalone Agent: Examples 3-32
Stopping the ODI Agent 3-33
Deploying and Configuring a Java EE Agent 3-34
Deploying and Configuring Java EE Agent 3-36
Load Balancing: Example 3-37
Important Guideline 5 3-39
Infrastructure with Agents: Example 3-40
Defining Agents: Example 3-41
Special Case: Fragmentation Problem 3-42
Special Case: Important Guideline 6 3-44
Special Case: Defining the Physical Architecture 3-45
Special Case: The Infrastructure 3-46
Special Case: Physical Architecture in ODI 3-47
Defining a Topology: Best Practices 3-48
Planning the Topology 3-49
Matrix of Logical and Physical Mappings 3-50
Quiz 3-51
Summary 3-54
Checklist of Practice Activities 3-55
Practice 3-1: Overview 3-56

Setting Up a New ODI Project
Objectives 5-2
ODI Projects 5-3
What Is a Project? 5-4
Oracle Data Integrator Projects: Overview 5-5
How to Use ODI Projects in Your Work 5-6
Creating a New Project 5-7
Using Folders 5-8
What Is a Folder? 5-9
Creating a New Folder 5-10
Organizing Projects and Folders 5-11
Understanding Knowledge Modules 5-12
What Is a Knowledge Module? 5-13
Types of Knowledge Modules 5-14
Which Knowledge Modules Are Needed? 5-15
Knowledge Modules: Examples 5-16
Importing Knowledge Modules 5-17
Replacing Existing KMs 5-18
Knowledge Module Editor 5-20
Editing a Knowledge Module 5-21
Exporting and Importing Objects 5-22
Exporting and Importing 5-23
Exporting an Object 5-24
vi

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Note 4-13
Creating a Data Server 4-14
Creating a Data Server: JDBC 4-15
JDBC Driver 4-16
JDBC URL 4-17
Creating a Data Server: JNDI 4-18
Testing a Data Server Connection 4-19
Creating a Physical Schema 4-20
Creating Logical Architecture 4-21
Logical Architecture and Context Views 4-22
Creating a Logical Schema 4-23
Creating a Logical Agent 4-24
Editing a Context to Link the Logical and Physical Agents 4-25
Quiz 4-26
Summary 4-28
Checklist of Practice Activities 4-29
Practice 4-1: Overview 4-30

Oracle Data Integrator Model Concepts
Objectives 6-2
What Is a Model? 6-3
Understanding the Relational Model 6-4
Relational Model 6-5
Relational Model: Tables and Columns 6-6
Relational Model: Keys 6-7
Relational Model: Foreign Keys 6-8
Relational Model: Constraints 6-9
Relational Model: Indexes 6-11
Relational Model Support in ODI 6-12
Additional Metadata in ODI 6-13
FlexFields 6-15
Understanding Reverse-Engineering 6-16
What Is Reverse-Engineering? 6-17
Methods for DBMS Reverse-Engineering 6-18
Other Methods for Reverse-Engineering 6-19
Standard Versus Customized Reverse- Engineering 6-20
Note 6-21
Creating Models 6-22
How to Create a Model by Reverse-Engineering 6-23
Step 1: Creating and Naming a New Model 6-24
Note About Creating and Naming a New Model 6-25
Step 2: Defining a Reverse-Engineering Strategy 6-26
Step 3: Starting the Reverse-Engineering Process 6-28
vii

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Importing an Object 5-25
ID Numbers: Overview 5-26
Import Types 5-27
Choosing the Import Mode 5-28
Import Report 5-29
What Is a Marker? 5-31
Tagging Objects with Markers 5-32
Removing Markers 5-33
Marker Groups 5-34
Project and Global Markers 5-35
Creating a Marker Group 5-36
Quiz 5-37
Summary 5-39
Checklist of Practice Activities 5-40
Practice 5-1: Overview 5-41

Organizing ODI Models and Creating ODI Datastores
Objectives 7-2
Organizing Models 7-3
What Is a Model Folder? 7-4
Creating a Model Folder 7-5
What Is a Submodel? 7-6
Creating a Submodel 7-7
Organizing Datastores into Submodels 7-8
Setting Up Automatic Distribution 7-9
Creating Datastores 7-10
Creating a Datastore in a Model 7-12
Adding Columns to a Datastore 7-13
Constraints in ODI 7-14
What Is a Constraint in ODI? 7-15
Constraints in ODI 7-16
Creating a Mandatory Column 7-17
Creating Keys and References 7-18
Creating a Key 7-19
Checking a Key 7-20
Creating a Reference 7-21
Creating a Simple Reference 7-22
Creating a Complex Reference 7-23
Checking a Reference 7-24
Creating Conditions 7-25
Creating a Condition 7-26
Checking a Condition 7-27
Overview 7-28
When and Why? 7-29
An Overview of the Process 7-30
Exploring Your Data 7-31
Displaying the Contents of a Datastore 7-32
viii

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Using RKM for Customized Reverse-Engineering 6-29
Selective Reverse-Engineering 6-31
Step 4: Fleshing Out Models 6-32
Shortcuts 6-33
Smart Export and Import 6-34
Quiz 6-35
Summary 6-37
Checklist of Practice Activities 6-38
Practice 6-1 Overview: Results of Reverse-Engineering into Models 6-39

ODI Interface Concepts
Objectives 8-2
ODI Interfaces 8-3
What Is an Interface? 8-4
Business Rules for Interfaces 8-5
Where Are the Rules Defined? 8-6
Mapping, Join, Filter, Lookup, and Data Sets 8-7
What Is a Mapping? 8-8
What Is a Join? 8-9
What Is a Filter? 8-10
What Is a Lookup? 8-11
What Is a Data Set? 8-12
Behind the Rules 8-13
How Does ODI Implement Business Rules? 8-14
A Business Problem 8-15
Implementing the Rules 8-16
Integration Process 8-17
Process Details 8-18
Process Implementation: Example 1 8-19
Process Implementation: Example 2 8-20
Process Implementation: Example 3 8-21
Staging Area and Execution Location 8-22
What Is the Staging Area? 8-23
Execution Location 8-24
Understanding Knowledge Modules 8-25
From Business Rules to Processes 8-26
Knowledge Modules 8-27
ix

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Viewing the Distribution of Values 7-33
Analyzing the Contents of a Datastore 7-34
Constructing Business Rules 7-35
Defining Business Rules in ODI 7-36
From Business Rules to Constraints 7-37
Deducing Constraints from Data Analysis 7-38
Testing a Constraint 7-39
Auditing a Model or Datastore 7-40
How to Review Erroneous Records 7-41
Quiz 7-42
Summary 7-44
Checklist of Practice Activities 7-45
Practice 7-1: Overview 7-46

Designing Interfaces
Objectives 9-2
Multiple Sources and Joins 9-3
Multiple-Source Datastores 9-4
Creating a Join Manually 9-5
Advanced Joins 9-6
Types of Joins 9-7
Setting Up a Join 9-8
Lookups 9-10
Using Lookups 9-11
Lookup Wizard 9-12
Lookup Limitations 9-13
Filtering Data 9-14
Filters in ODI 9-15
Defining a Filter Manually 9-16
Setting Up a Filter 9-17
Overview of the Flow in ODI Interface 9-18
Flow 9-19
What Defines the Flow? 9-20
The Scenario 9-21
The Basic Process 9-22
x

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Knowledge Module? 8-28
Code Generation 8-29
KM Types Used in Interfaces 8-30
Interfaces: An Overview 8-31
The Purpose of an Interface 8-32
Creating a One-to-One Interface 8-33
Creating and Naming an Interface 8-34
Defining the Target Datastore 8-35
Important Note 8-36
Defining the Source Datastore 8-37
What Is a Mapping? 8-38
Defining the Mappings 8-39
Valid Mapping Types 8-40
Saving the Interface 8-41
Executing the Interface 8-42
Quiz 8-43
Summary 8-45
Checklist of Practice Activities 8-46
Practice 8-1: Overview 8-47

10 Interfaces: Monitoring and Debugging
Objectives 10-2
Monitoring Interfaces 10-3
Operator Navigator: Viewing the Log 10-4
Using Operator Navigator 10-5
Sessions, Steps, Tasks: The Hierarchy 10-6
Viewing Details of Sessions, Steps, and Tasks 10-7
How to Monitor Execution of an Interface 10-8
How to Troubleshoot a Session 10-9
xi

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Selecting a Staging Area 9-23
The Purpose of a Staging Area 9-24
Placing the Staging Area 9-25
Important Note 9-26
How to Specify the Staging Area 9-27
Configuring Filters, Joins, Mappings, and Lookups 9-28
Options for Filters, Joins, Mappings, and Lookups 9-29
Setting Options for Filters, Joins, Mappings, and Lookups 9-30
How to Disable a Transformation 9-31
How to Enable a Mapping for Inserts or Updates 9-32
Execution Location 9-33
Execution Location and Syntax 9-34
Why Change the Execution Location? 9-35
How to Change the Execution Location 9-36
ODI Interface Execution Simulation 9-37
Selecting the Knowledge Module 9-38
Which KMs for Which Flow? 9-39
More About KMs 9-41
Identifying IKMs and LKMs 9-42
IKMs and LKMs: Strategies and Methods 9-43
How to Specify an LKM 9-44
How to Specify an IKM 9-45
Common KM Options 9-46
Flow: Example 1 9-47
Flow: Example 2 9-48
Flow: Example 3 9-49
Quiz 9-50
Summary 9-51
Checklist of Practice Activities 9-52
Practice 9-1: Overview 9-53
Practice 9-2: Overview 9-54

11 Designing Interfaces: Advanced Topics 1
Objectives 11-2
Working with Business Rules 11-3
Business Rules in Interfaces 11-4
Business Rule Elements 11-5
More Elements 11-6
The Expression Editor 11-7
Using Variables 11-9
Using a Variable in Code 11-10
Binding Versus Substitution 11-12
Note: Case Sensitivity 11-13
Data Sets and Set-Based Operators 11-14
Flow with Multiple Data Sets 11-15
Defining a Data Set 11-16
Using Set-Based Operators 11-17
Using Sequences 11-18
Types of Sequences 11-19
Support for Native Sequences 11-20
Creating a Native Sequence 11-21
Referring to Sequences 11-22
Note: Sequences Updated by Agent 11-23
Using Standard Sequences in Mappings Correctly 11-24
Using ODI Standard Sequences in Mappings 11-25
Populating Native Identity Columns 11-26
Note 11-27
Automatic Temporary Index Management 11-28
xii

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

1. Identifying the Error 10-10
2. Reviewing the Code 10-11
3. How to Fix the Code and Restart the Session 10-12
4. Fixing the Interface 10-13
Keys to Reviewing the Generated Code 10-14
Working with Errors 10-15
Common Errors and Symptoms 10-16
Important Note 10-18
Tips for Preventing Errors 10-19
Using Quick-Edit Editor 10-20
Quiz 10-21
Summary 10-23
Checklist of Practice Activities 10-24
Practice 10-1: Overview 10-25

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

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Tracking Variables and Sequences 11-29
How Variable and Sequence Tracking Works 11-30
New Variable Actions 11-31
Definition Tab of Session Step or Session Task 11-32
Quiz 11-33
Summary 11-34
Checklist of Practice Activities 11-35
Practice 11-1: Overview 11-36
Practice 11-2: Overview 11-37
Practice 11-3: Overview 11-38

13 Using ODI Procedures
Objectives 13-2
Procedures: Overview 13-3
What Is a Procedure? 13-4
Procedure: Examples 13-5
Creating Procedures: Overview 13-7
Creating a Blank Procedure 13-8
How to Create a New Procedure 13-9
Adding Commands 13-10
Creating a Command 13-11
Arranging Tasks in Order 13-13
Which Parameters Should Be Set? 13-14
Valid Types of Commands 13-15
More Elements 13-16
Why Use a Source Command? 13-17
Adding Options 13-18
Types of Options 13-19
How to Create a New Option 13-20
How to Make a Command Optional 13-21
Using an Option Value in a Command 13-22
Running a Procedure 13-23
Procedure Execution 13-24
Using the Operator Navigator to View Results 13-25
Quiz 13-26
Summary 13-28
Checklist of Practice Activities 13-29
Practice 13-1: Overview 13-30
14 Using ODI Packages
Objectives 14-2
Packages: Overview 14-3
What Is a Package? 14-4
How to Create a Package 14-5
1. Creating and Naming a Package 14-6
How to Create and Name a Package 14-7
Package Diagram 14-8
Package Diagram Toolbar 14-9
xiv

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities 12-34
Practice 12-1: Overview 12-35
Practice 12-2: Overview 12-36

15 Managing ODI Scenarios
Objectives 15-2
Scenarios 15-3
What Is a Scenario? 15-4
Properties of Scenarios 15-5
Managing Scenarios 15-6
Scenario-Related Tasks 15-7
Generating a Scenario 15-8
Regenerating a Scenario 15-9
Generation Versus Regeneration 15-10
xv

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

2. Adding Steps to the Package 14-11
Package Steps 14-12
How to Create a Package Step 14-13
What Is an ODI Tool? 14-14
How to Create an ODI Tool Step 14-15
Note 14-16
3. Arranging Package Steps in a Sequence 14-17
Sequencing Steps 14-18
A Simple Package 14-19
How to Sequence Package Steps 14-20
Executing a Package 14-21
Review of Package Steps 14-23
Basic Step Types 14-24
Advanced Step Types 14-25
Model, Submodel, and Datastore Steps 14-26
How to Create Model, Submodel, and Datastore Steps 14-27
Models, Submodels, and Datastore Steps 14-28
Variable Steps 14-30
How to Create a Variable Step 14-31
Variable Steps 14-32
Controlling the Execution Path 14-34
Controlling Execution 14-35
Error Handling 14-36
How to Create a Loop 14-37
The Advanced Tab 14-38
Quiz 14-39
Summary 14-41
Checklist of Practice Activities 14-42
Practice 14-1: Overview 14-43
Practice 14-2: Overview 14-44

16 Using Load Plans
Objectives 16-2
Should You Organize Executions with Load Plans? 16-3
What Are Load Plans? 16-4
Load Plan Editor 16-5
Load Plan Steps 16-6
Defining the Restart Behavior 16-7
Are Load Plans Substitutes for Packages or Scenarios? 16-9
Benefits of Utilizing Load Plans 16-10
Handling Failed Load Plans 16-11
Quiz 16-12
Summary 16-13
Checklist of Practice Activities 16-14
Practice 16-1: Overview 16-15
17 Managing ODI Versions
Objectives 17-2
Overview of ODI Version Management 17-3
What Is Version Management? 17-4
Working with Object Flags 17-5
Creating Versions 17-6
Restoring Versions 17-7
Using Version Browser 17-8
Using the Version Comparison Tool 17-9
Opening the Version Comparison Tool 17-11
Working with Solutions 17-12
xvi

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Executing a Scenario from the GUI 15-11
Executing a Scenario from a Command Line 15-12
Executing a Scenario from a Package 15-13
Exporting a Scenario 15-14
Preparing for Deployment 15-15
Preparing Scenarios for Deployment 15-16
Automating Scenario Management 15-17
Scheduling the ODI Scenario 15-18
Scheduling ODI Scenario with External Scheduler 15-21
Managing Schedules 15-22
Quiz 15-23
Summary 15-24
Checklist of Practice Activities 15-25
Practice 15-1: Overview 15-26

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

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Working with Solutions: Synchronizing 17-14
Handling Concurrent Changes 17-15
Quiz 17-17
Summary 17-18
Checklist of Practice Activities 17-19
Practice 17-1: Overview 17-20

19 Working with Changed Data Capture
Objectives 19-2
Why Changed Data Capture? 19-3
CDC Techniques 19-4
Changed Data Capture in ODI 19-5
Journalizing Components 19-6
CDC Infrastructure in ODI 19-7
Simple Versus Consistent Set Journalizing 19-8
Limitations of Simple CDC Journalizing: Example 19-9
Consistent CDC Journalizing 19-10
Consistent CDC: Infrastructure 19-11
Setting Up Journalizing 19-12
Setting CDC Parameters: Example 19-13
Adding a Subscriber: Example 19-14
Starting Journal: Example 19-15
Journalizing Status 19-16
Viewing Data/Changed Data: Example 19-17
Using Changed Data 19-18
Quiz 19-20
Summary 19-22
Checklist of Practice Activities 19-23
Practice 19-1: Overview 19-24
20 Advanced ODI Administration
Objectives 20-2
Setting Up ODI Security 20-3
Introduction to ODI Security Navigator 20-4
Overview of Security Concepts 20-6
Defining Security Policies 20-8
Creating Profiles 20-9
Using Generic and Nongeneric Profiles 20-10
Built-in Profiles 20-11
Creating Users 20-12
Assigning a Profile to a User 20-13
Assigning an Authorization by Profile or User 20-14
Defining Password Policies 20-15
Setting User Parameters 20-17
Overview of ODI Security Integration 20-18
xviii

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities 18-36
Practice 18-1: Overview 18-37

21 Extending Oracle Data Integrator with SDK, Web Services, and SOA
Objectives 21-2
Interacting Programmatically with ODI 21-3
Overview of ODI SDK 21-4
SDK-Supported ODI Operations 21-5
ODI Operations Not Supported by SDK 21-6
ODI 11g SDK Usage Examples 21-7
Combining Different APIs 21-8
Example of ODI SDK Setup and Performing an SDK Task Using Java 21-9
Using Web Services with ODI 21-10
Web Services in Action 21-11
Two Types of Web Services 21-12
What Are Data Services? 21-13
Generation of Data Services 21-14
Data Services in Action 21-15
Using Public Web Services 21-16
What Are Public Web Services? 21-17
Using Public Web Services 21-18
Public Web Services in Action: Java EE 21-19
Public Web Services in Action: Standalone Agent 21-20
xix

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Implementing External Authentication (OPSS) 20-19
Implementing External Authentication (OPSS): Switching the
Authentication Mode 20-21
Implementing External Password Storage 20-22
Managing ODI Reports 20-24
Types of ODI Reports 20-25
Generating Topology Reports 20-26
Generated Topology Report: Example 20-27
Version Comparison Report: Example 20-28
Generating Object Reports 20-29
ODI Integration with Java EE 20-30
Integration of ODI with Enterprise Manager 20-31
Overview of Java EE Agent and Enterprise Manager Configuration
with WebLogic Domain 20-32
Using ODI Console: Example 20-33
Quiz 20-34
Summary 20-35
Checklist of Practice Activities 20-36
Practice 20-1: Overview 20-37
Practice 20-2: Overview 20-38

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Installing Public Web Services 21-21
A Simple SOAP Request for the OdiInvoke Web Service with
Standalone Agent: Example 21-22
Note 21-23
A Simple SOAP Response for the OdiInvoke Web Service: Example 21-24
Invoking Web Services 21-25
OdiInvokeWebService Tool 21-26
Invoking a Web Service: Example 21-29
Processing a Web Service Response 21-33
Integration of ODI with SOA 21-36
Ways to Integrate ODI with SOA 21-37
ODI with SOA: Example 1 21-39
ODI and Business Process Management 21-40
ODI with SOA: Example 2 21-41
Creating an ODI Error Hospital with BPEL Human Workflow 21-43
Quiz 21-48
Summary 21-49
Checklist of Practice Activities 21-50
Practice 21-1: Overview 21-51

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle University and (Oracle Corporation) use only.

Introduction to Integration and Administration

After completing this course, you should be able to:
• Describe the Oracle Data Integrator (ODI) 11g architecture
and apply ODI Topology Concepts for data integration
• Describe Oracle Data Integrator model concepts
• Design ODI interfaces, procedures, and packages to
perform data transformations
• Explore, audit data, and enforce data quality with ODI
• Administer ODI resources and set up security with ODI
• Implement Changed Data Capture with ODI
• Use ODI Web services and perform integration of ODI with
Service-Oriented Architecture (SOA)

Oracle Data Integrator 11g: Integration and Administration 1 - 2

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Course Objectives

After completing this lesson, you should be able to:
• Describe the course objectives
and agenda of lessons
• Describe the benefits of using ODI
• Describe the ODI 11g architecture
and components
• Describe how to use ODI Studio
to create, administer, and monitor
ODI objects
• Start Oracle Data Integrator (ODI) client
• Access online Help

This lesson provides a general overview of the ODI architecture. You learn the roles of the
different ODI components. The lesson also covers repositories, the most important
component of ODI, in detail.

Oracle Data Integrator 11g: Integration and Administration 1 - 3

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Lesson Objectives

•

Day 1:
–
–
–
–

•

Lesson 1: Introduction to Integration and Administration
Lesson 2: Administering ODI Repositories
Lesson 3: ODI Topology Concepts
Lesson 4: Describing the Physical and Logical Architecture

Day 2:
– Lesson 5: Setting Up a New ODI Project
– Lesson 6: Oracle Data Integrator Model Concepts
– Lesson 7: Organizing ODI Models and Creating ODI
Datastores
– Lesson 8: ODI Interface Concepts

Oracle Data Integrator 11g: Integration and Administration 1 - 4

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Agenda of Lessons

•

Day 3:
–
–
–
–

•

Lesson 9: Designing Interfaces
Lesson 10: Interfaces: Monitoring and Debugging
Lesson 11: Designing Interfaces: Advanced Topics 1
Lesson 12: Designing Interfaces: Advanced Topics 2

Day 4:
–
–
–
–

Lesson 13: Using ODI Procedures
Lesson 14: Using ODI Packages
Lesson 15: Managing ODI Scenarios
Lesson 16: Using Load Plans

Oracle Data Integrator 11g: Integration and Administration 1 - 5

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Agenda of Lessons

•

Day 5:
–
–
–
–
–

Lesson 17: Managing ODI Versions
Lesson 18: Enforcing Data Quality with ODI
Lesson 19: Working with Changed Data Capture
Lesson 20: Advanced ODI Administration
Lesson 21: Extending ODI with the SDK, Web Services,
and SOA

Oracle Data Integrator 11g: Integration and Administration 1 - 6

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Agenda of Lessons

First, look at Oracle Data Integrator’s approach to data integration.

Oracle Data Integrator 11g: Integration and Administration 1 - 7

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle Data Integrator: Introduction

•

ELT architecture provides high performance.
– ELT faster than ETL

•

Active integration enables real-time data warehousing and
operational data hubs.
– Changed data capture technology for real-time data
warehousing
– Data services provided to the Oracle SOA Suite

•

Declarative design improves developer productivity.
– Business users specify what they want; ODI generates the
flows and code.

•

Knowledge modules provide flexibility and extensibility.
– Predefined, reusable code templates with built-in
connectivity to all major databases
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

•

ODI’s ELT architecture leverages disparate RDBMS engines to process and transform
data. This approach optimizes performance and scalability, and lowers overall solution
costs.
ODI turns the promise of active integration into reality by providing all the key
components that are required to enable real-time data warehousing and operational
data hubs. ODI combines three styles of data integration: data-based, event-based, and
service-based. ODI unifies silos of integration by transforming large volumes of data in
batch mode, by processing events in real time through its advanced Changed Data
Capture, and by providing data services to the Oracle SOA Suite.
Oracle Data Integrator shortens implementation times with its declarative design
approach. Designers specify what they want to accomplish with their data, and then the
tool generates the details of how to perform the task. With ODI, the business user or the
developer specifies the rules to apply to the integration processes. The tool
automatically generates data flows and administers correct instructions for the various
source and target systems. With declarative design, the number and complexity of steps
is greatly reduced, which in turn shortens implementation times. Automatic code
generation reduces the learning curve for integration developers and streamlines non-IT
professionals to the definition of integration processes and data formats.

Oracle Data Integrator 11g: Integration and Administration 1 - 8

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Why Oracle Data Integrator?

Knowledge modules are at the core of the ODI architecture. They make all ODI processes
modular, flexible, and extensible. Knowledge modules implement the actual data flows
and define the templates for generating code across the multiple systems involved in each
process. ODI provides a comprehensive library of knowledge modules, which can be
tailored to implement existing best practices (for example, for highest performance, for
adhering to corporate standards, or for specific vertical knowhow). By helping companies
capture and reuse technical expertise and best practices, ODI’s knowledge module
framework reduces the cost of ownership. It also enables metadata-driven extensibility of
product functionality to meet the most demanding data integration challenges.

ODI streamlines the high-performance movement and transformation of data between
heterogeneous systems in batch, real-time, synchronous, and asynchronous modes. It
dramatically enhances user productivity with an innovative, modularized design approach and
built-in connectivity to all major databases, data warehouse appliances, analytic applications,
and SOA suites.

Oracle Data Integrator 11g: Integration and Administration 1 - 9

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

•

Integration process
Extract - Transform (check) - Load

A machine

A machine
Source

Target

ORDERS

A machine

LINES

Transform

SALES

Errors

CORRECTIONS
File

Extract

Transform

Load

This integration process is also known as an extract, transform, and load (ETL) process.
The first part of an ETL process involves extracting data from the source systems. Most data
warehousing projects consolidate data from different source systems.
The transform stage applies a series of rules or functions to the data extracted from the
source to derive the data for loading into the target. Some data sources will require very little
or even no manipulation of data. In other cases, transformations (such as filtering, joining,
sorting, and so on) may be required to meet the business and technical needs of the target
database.
The load phase loads the data into the target, usually the data warehouse.
Note: You can add to this process the checks that ensure the quality of data flow, as shown in
the slide.

Oracle Data Integrator 11g: Integration and Administration 1 - 10

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Conventional Integration Process: ETL

1. Extract: Extracting data from various sources
2. Load: Loading the data into the destination target
3. Transform: Transforming the data according to a set of
business rules

Conventional ETL architecture

Next-generation ELT architecture

Transform

Transform
Extract

Load

Extract

Load

Data is one of the most important assets of any company, and data integration constitutes the
backbone of any enterprise’s IT systems. Choosing the technology for data integration is
critical for productivity and responsiveness of business divisions within an enterprise.
ELT stands for extract, load, and transform. It includes the processes that enable companies
to move data from multiple sources, reformat and cleanse the data, and load it into another
database, or a data warehouse for analysis, to support a business process.
ODI provides a strong and reliable integration platform for IT infrastructure. Built on the nextgeneration architecture of extract, load, and transform (ELT), ODI delivers superior
performance and scalability connecting heterogeneous systems at a lower cost than
traditional, proprietary ETL products. Unlike the conventional extract, transform, and load
(ETL) design, with ODI, ELT architecture extracts data from sources, loads it into a target, and
transforms it by using the database power according to business rules. The tool automatically
generates data flows, manages their complexity, and administers correct instructions for the
various source and target systems.

Oracle Data Integrator 11g: Integration and Administration 1 - 11

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ELT

The first step in the ELT process is extracting data from various sources. Each of the source
systems may store its data in a completely different format. The sources are usually flat files or
RDBMS, but almost any data storage can be used as a source for an ELT process.
Load
This step involves loading the data into the destination target, which might be a database or
data warehouse.
Transform
After the data has been extracted and loaded, the next step is to transform the data according to
a set of business rules. The data transformation may involve various operations including, but
not limited to filtering data, sorting data, aggregating data, joining data, cleaning data,
generating calculated data based on existing values, and validating data.

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Extract

Oracle Data Integrator 11g: Integration and Administration 1 - 12

For more information, see the Installation Guide and User’s Guide.
To find ODI documentation, go to otn.oracle.com/goto/odi

To fully understand the ODI architecture, you must look at each of its components.

Oracle Data Integrator 11g: Integration and Administration 1 - 13

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Architecture and Components

OpenTools for
adding OS
functionality to any
ELT/ETL job

Desktop
JVM

WebLogic 11g / Application Server
FMW Console

ODI Studio

ODI Plug-in

Designer
Java EE
Application

Operator

Servlet Container

Topology

ODI SDK

Java EE
Application

Security

ODI Console

Java EE
Agent

Data
Services

Public WS

Data Sources Connection Pool

Heterogeneous access

Repositories

Web Service Container
Runtime WS

ODI SDK
SDK APIs for Java
generation of any
ODI system
metadata

Open Web Services for
decoupled invocation of
any ODI jobs

Sources and Targets

JVM
Run-time WS

ODI Master
Repository
Metadata is an open
relational model
with built-in Flex
Fields

ODI Work
Repository

Standalone
Agent
Legacy

Files / XML

DBMS

Applications
ERP/CRM/PLM/SCM

DW / BI / EPM

The repository forms the central component of the ODI architecture. This stores configuration
information about the IT infrastructure; the metadata for all applications, projects, scenarios,
and execution logs. Repositories can be installed in an online transaction processing (OLTP)
relational database. The repository also contains information about the ODI infrastructure,
defined by the administrators. The two types of ODI repositories are Master and Work
Repositories.
At design time, developers work in a repository to define metadata and business rules. The
resulting processing jobs are executed by the agent, which orchestrates the execution by
leveraging existing systems. The agent connects to available servers and asks them to
execute the code. It then stores all return codes and messages in the repository. The agent
also stores statistics, such as the number of records processed, and the elapsed time.
Several repositories can coexist in an IT infrastructure. The graphic in this slide shows two
repositories: one for the development environment and the other for the production
environment. Developers release their projects in the form of scenarios that are sent to
production.
In production, these scenarios are scheduled and executed on a Scheduler Agent that also
stores all its information in the repository. Operators have access to this information and can
monitor the integration processes in real time.
Business users, as well as developers, administrators, and operators, can gain web-based
read access to the repository by using the ODI Console.
Oracle Data Integrator 11g: Integration and Administration 1 - 14

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Architecture

•

ODI Studio Components:
–
–
–
–

•
•
•

Designer Navigator
Operator Navigator
Topology Navigator
Security Navigator

ODI agents
ODI Console
ODI repositories

ODI Studio provides four navigators for managing the different aspects and steps of an ODI
integration project.
The navigators are discussed in detail in the next several slides.
• ODI agents are runtime processes that orchestrate executions.
• ODI Console provides users web access to ODI metadata.
• ODI repositories store all of your ODI objects as databases in a relational database
management system.

Oracle Data Integrator 11g: Integration and Administration 1 - 15

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Components: Overview

ODI Studio

Designer Navigator

Operator Navigator

Topology Navigator

Security Navigator

Reverse-engineer.
Develop projects.
Release scenarios.

Operate production.
Monitor sessions.

Define the
infrastructure of the IS.

Manage user
privileges.

Repository

The Fusion Client Platform (FCP) based UI provides an efficient and
flexible way to manage navigators, panels, and editors.

Graphical Navigators
Administrators, developers, and operators use the Oracle Data Integrator Studio to access the
repositories. This Fusion Client Platform (FCP) based UI is used for administering the
infrastructure (security and topology), reverse-engineering the metadata, developing projects,
and scheduling, operating, and monitoring executions. FCP provides an efficient and flexible
way to manage navigators, panels, and editors.
Business users (as well as developers, administrators, and operators) can have read access
to the repository. They can also perform topology configuration and production operations
through a web-based UI called Oracle Data Integrator Console. This web application can be
deployed in a Java EE application server such as Oracle WebLogic.
The four ODI graphical navigators are based on the Java programming language and can be
installed on any platform that supports Java Virtual Machine 1.6, including Windows, Linux,
HP-UX, Solaris, and pSeries. All ODI navigators store their information in the centralized ODI
repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 16

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Using ODI Studio

Toolbar

Designer
Projects
editor

Object
tree

Other
Designer
editors

In ODI Designer, you
can create, configure,
and execute various
ODI objects.

Workspace to
define ODI objects and
design ELT
transformations

Designer Navigator is the GUI for defining metadata and rules for transformation and data
quality. ODI uses this information to generate scenarios for production, and is where all the
project development takes place. Designer Navigator is the core module for developers and
metadata administrators.
Through the Designer Navigator, you can handle the following:
• Models: Descriptions of data and application structures
• Projects: Development of various ODI objects
Note: Designer Navigator stores this information in a Work Repository, while using the
topology and the security information defined in the Master repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 17

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Designer Navigator (Work Repository)

Toolbar

In the Operator
Navigator, you can
monitor execution of
ODI objects.

The monitoring
session steps enable
you to perform
debugging.

The Operator Navigator is used to manage and monitor ODI in production. It is designed for
production operators, and displays the execution logs with error counts, the number of rows
processed, execution statistics, and so on. At design time, developers use the Operator
Navigator for debugging purposes.
Through the Operator Navigator, you can manage your interface executions in the sessions,
as well as the scenarios in production.
The Operator Navigator stores this information in a Work Repository, while using the topology
defined in the Master Repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 18

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Operator Navigator (Work Repository)

Toolbar

Technologies
tree
Workspace to
define ODI Topology
objects
In ODI Topology Navigator,
you set the architecture of
your information system,
technologies, servers,
schemas, and repositories.

The Topology Navigator manages the physical and logical architecture of the infrastructure.
Servers, schemas, and agents are registered in the ODI Master Repository—a major ODI
component that contains information about the topology of the company’s IT resources,
security, and ODI resources that will be discussed later in this course.
Using the Topology Navigator, you can define the topology of your information system to ODI
so that it can be accessed by other ODI modules. In addition, the Topology Navigator enables
you to manage the repositories. The Topology Navigator stores this information in a Master
Repository. This information can be used by all the other modules.

Oracle Data Integrator 11g: Integration and Administration 1 - 19

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Topology Navigator (Master Repository)

Object
tree

In the Security Navigator,
you manage objects,
profiles, users and their
privileges, and hosts.

Workspace to
define objects, profiles,
users, and hosts

The Security Navigator manages users and their privileges in ODI. It is used to create profiles
and provide rights to users to access ODI objects and features. This navigator is usually used
by security administrators.
It is used to assign user rights for methods (edit and delete, for example) on generic objects
(data server and data types, for example), and to fine-tune these rights on the object
instances (Server 1 and Server 2, for example).
The Security Navigator stores this information in a Master Repository. This information can be
used by all the other modules.

Oracle Data Integrator 11g: Integration and Administration 1 - 20

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Security Navigator (Master Repository)

•
•
•

•

An agent is a runtime component of ODI that orchestrates
the integration process.
It is a lightweight Java program that retrieves code from
the repository at run time.
At design time, developers generate scenarios from the
business rules that they have designed. The code of these
scenarios is then retrieved from the repository by the agent
at run time.
This agent then connects to the data servers, and
orchestrates the code execution on these servers.

An agent is a special ODI component that runs in the background.
At design time, developers generate scenarios from the business rules that they have
designed.
The code of these scenarios is then retrieved from the repository by the agent at run time.
This agent then connects to the data servers, and orchestrates the code execution on these
servers.
The agent orchestrates the integration process by sending commands to data servers, the
operating system, or other technologies. It retrieves the return codes and messages for the
execution, as well as additional logging information, such as the number of rows processed,
execution time, and so on, in the repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 21

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is an Agent?

•

Agents are lightweight Java
processes that orchestrate the
execution of objects at run time.
Agents can do one of the following:
– Execute objects on demand
– Execute according to predefined
schedules

You can install an agent on any machine with network access. This is worth bearing in mind
when planning your deployment strategy.
Note: Before ODI 11g, ODI had two types of agents: listener agents and scheduler agents.
With ODI 11g, agents are always connected to a Master Repository, and are started with the
built-in scheduler service activated. This scheduler service takes its schedules from all the
Work Repositories attached to the connected Master.

Oracle Data Integrator 11g: Integration and Administration 1 - 22

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Agents

Standalone
agent

Java EE
agent
JDeveloper

Standalone Java
Any Java App

ODI Studio

Any Web App
WebLogic 11g (or other Java EE)
Servlet Container

ODI Agent
Designer

Operator

Security

Topology

ODI SDK APIs

MBeans Server Registry

Any Application Container

Web Service Container

ODI Agent

Any MBeans App

Any Application

ODI Public
Web Service

ODI Console

ODI MBeans
for ODI Agent

ODI SDK APIs

ODI Data
Services

RDBMS – ODI Repositories
ODI Master
Repository

Data Sources Connection Pool

Repository Data Sources
Sources Data Sources
Repository Data Sources
Sources Data Sources
Repository Data Sources
Sources Data Sources

Knowledge
Modules

ODI Work
ODI WorkODI Work
Repository
Repository
Repository

Target Data Sources
Target Data Sources
Target Data Sources

Sources and Targets
---------------

-- -

Legacy

ERP

PLM

---------------

CRM

---------------

Best-of-Breed Applications

The Agent comes in two types:
• The Java Enterprise Edition (Java EE; formerly J2EE) agent can be deployed as a web
application and benefit from the features of an application server.
• The Standalone agent runs in a simple Java Machine and can be deployed where
needed to perform the integration flows.
Both agents are multithreaded Java programs that support load balancing (discussed in the
lesson titled “Administering ODI Repositories”) and can be distributed across the information
system. The agent can hold its own execution schedule, which can be defined in Oracle Data
Integrator, and can also be called from an external scheduler. It can also be invoked from a
Java API or a web service interface.

Oracle Data Integrator 11g: Integration and Administration 1 - 23

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Two Types of Agents: Java EE and Standalone

•

Deploying a Java EE agent in a Java EE Application
Server (Oracle WebLogic Server):
1. In ODI, define the Java EE agent in Topology Navigator.
2. In ODI, create the WLS template for the Java EE agent.
3. Deploy the template directly using WLS Configuration
Wizard.

•

Using a standalone agent:
1. Launch an Agent.
2. Display Scheduling Information.
3. Stop the Agent.

•

Advantages of Java EE agents over standalone agents:
–
–
–
–

High availability
Multiple agents, using Coherence
Load balancing
Connection pooling back to repositories
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Deploying an Oracle Data Integrator Agent in Oracle WebLogic Server (WLS)
The easiest way to deploy an Oracle Data Integrator agent in Oracle WebLogic Server (WLS)
is to generate a WLS template with Oracle Data Integrator. This template can directly be
deployed by using the WLS Configuration Wizard.
Deploying an agent in a Java EE Application Server (Oracle WebLogic Server) involves the
following tasks:
• Task 1: Define the Java EE agent in the Topology. Defining a Java EE agent consists of
two tasks. First, you need to create the physical agent corresponding to your Java EE
agent, and then create a logical agent.
• Task 2: Create the WLS template for the Java EE agent. Oracle Data Integrator
provides a WLS Template Generation wizard to help you create a WLS template for a
runtime agent.
• Task 3: Deploy the template directly by using the WLS Configuration Wizard.
The next slide discusses using a standalone agent.

Oracle Data Integrator 11g: Integration and Administration 1 - 24

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Using the Two Types of Agents

Java - Any Platform

Designer

Operator

Reverse-engineer
Develop projects
Release
scenarios

Operate
production
monitor sessions

Use Designer
or Operator to
Submit Jobs
to execute

Monitor sessions
View Reports

Repository
Read sessions
Write reports

Any ISO-92 RDBMS
Standalone Agent
Orchestrates
sessions

Java - Any Platform

Return Code

Lightweight
Distributed Architecture
Execute Jobs

Information System

At run time, the standalone agent orchestrates the execution of the developed scenarios. It
can be installed on any platform provided that it supports a JVM 1.6 at minimum (Windows,
Linux, HP-UX, Solaris, pSeries, iSeries, zSeries, and so on).
Execution may be from one of the graphical modules or by using the built-in scheduler.
Due to the ELT architecture of ODI, the standalone agent rarely performs transformation itself.
It usually retrieves code from the execution repository and requests database servers,
operating systems, or scripting engines to execute it. When the execution is completed, the
standalone agent updates the logs in the repository, reporting error messages and execution
statistics. The execution log can be viewed from the Operator Navigator.
Note: Although it can act as a transformation engine, the agent is rarely used for this purpose.
Agents are installed at tactical locations in the information system to orchestrate integration
processes and leverage existing systems. Agents are lightweight components in this
distributed integration architecture.

Oracle Data Integrator 11g: Integration and Administration 1 - 25

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Example of Standalone Agent

Development

Agent

ODI
Console

Web access
ODI
Console

The ODI Console provides web access to ODI repositories. It enables users to navigate
projects, models, logs, and so on. Business users, developers, operators, and administrators
use their web browsers to access the ODI Console. The ODI Console replaces the Metadata
Navigator of ODI releases before ODI 11g.
Note that with the ODI Console, you also can perform executions.

Oracle Data Integrator 11g: Integration and Administration 1 - 26

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Console

Development

Agent

ODI Plug-in
for EM
Console

Enterprise Manager
Console

Oracle Data Integrator provides an extension that is integrated into the Enterprise Manager
Fusion Middleware Control Console. The Oracle Data Integrator components can be
monitored as a “domain server” (a WebLogic term) through this Console, and administrators
can have a global view of these components, along with other Fusion Middleware
components from a single administration console.

Oracle Data Integrator 11g: Integration and Administration 1 - 27

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Enterprise Manager Console

The repository is the central component in ODI. The following slides cover what the repository
stores.

Oracle Data Integrator 11g: Integration and Administration 1 - 28

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle Data Integrator Repositories

•

Two types of repositories are included in ODI:
– Master repository
– Work repository
—
—

•

MASTER
repository

Development repository
Execution repository

Security
Topology
Versioning

Work repositories are always
attached to a single
Master repository.
Models

Models

Projects

Execution

WORK repository
(Dev)

WORK repository
(Prod)

ODI repositories are databases stored in relational database management systems. There
are two types of ODI repositories: Master Repository and Work Repository. All the objects
configured, developed, or used by the ODI modules are stored in one of these two types of
repositories. The repositories are accessed in client/server mode by various components of
the ODI architecture.
Whereas a Master Repository is usually associated with multiple Work Repositories, each
Work repository can belong to only one Master Repository. This restriction supports ODI
version management.

Oracle Data Integrator 11g: Integration and Administration 1 - 29

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Repositories

Security
Topology
Versioning

Master repository

Models
Projects

Execution

Work repository
(Development)

Execution repository
(Production)

You can create a Work repository that stores only execution information,
usually for PRODUCTION purposes. This type of Work repository can be
called an execution repository.

Two types of repositories are included in ODI:
• Master Repository: It is the data structure containing information about the topology of
the company’s IT resources, security, and version management of projects and data
models. This repository is stored on a relational database accessible in client/server
mode from the different ODI modules. In general, you need only one Master Repository.
• Work Repository: It is the data structure containing information about data models,
projects, and their use. This repository is stored on a relational database accessible in
client/server mode from the different ODI modules.
Several Work Repositories can be designated with several Master Repositories, if necessary.
However, a Work Repository can be linked with only one Master Repository for version
management purposes. The ODI Repository comprises a Master Repository and several
Work Repositories. There is usually only one Master Repository, which contains the following
information:
• Security information, including users, profiles, and access privileges for the ODI platform
• Topology information, including technologies, definitions of servers and schemas,
contexts and languages
• Old versions of objects

Oracle Data Integrator 11g: Integration and Administration 1 - 30

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Master and Work Repositories

The Work Repository is where the projects are developed. Several Work Repositories may
coexist in the same ODI installation. This is useful, for example, in maintaining separate
environments or in reflecting a particular versioning life cycle.
A Work Repository stores information for the following:
• Data models, which include the descriptions of schemas, data store structures and
metadata, fields and columns, data quality constraints, cross-references, data lineage, and
so on
• Projects, which include business rules, packages, procedures, folders, knowledge
modules, variables, and so on
• Execution, which means scenarios, scheduling information, and logs
The contents of a Work Repository are managed by using Designer and Operator. They are
also accessed by the agent at run time.
When a Work Repository is used only to store execution information (typically for production
purposes), it is called an execution repository. Execution repositories are accessed at run time
by using Operator and also by using agents.
Note: Work Repositories are always attached to one Master Repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 31

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

The information contained in the Master Repository is maintained with the Topology Navigator
and the Security Navigator. All modules access the Master Repository because they all need
the topology and security information stored there.

Security
Create and
archive versions
of models,
projects, and
scenarios.

Import released
and tested
versions of
scenarios for
production.

Topology
Versioning

Master
Repository

Import released
versions of models,
projects, and
scenarios for
testing.

Models
Projects
Models

Execution

Projects

Work Repository
(Development)

Execution

Work Repository
(Test and QA)

Execution
Repository
(Production)

Development-test-production cycle
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

The graphic in the slide gives an overview of a typical repository architecture where
development, testing, and production are carried out in separate Work Repositories. When
the development team finishes work on certain projects, it releases them into the Master
Repository. The testing team imports these released versions for testing in a separate Work
repository, thereby allowing the development team to continue working on the next versions.
When the test team successfully validates the developed items, the production team exports
the executable versions (called scenarios) into the final production Work Repository.
This repository structure corresponds to a simple development-test-production cycle.

Oracle Data Integrator 11g: Integration and Administration 1 - 32

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Repository Setup: Example

Create and
archive versions
of models,
projects, and
scenarios.

Master
Repository

Security

Topology

Versioning

Versioning
Import released and
tested versions of
scenarios for production.

Import released
versions of models,
projects, and
scenarios for
testing.

Models
Projects
Models

Execution

Projects

Work Repository
(Development)

Execution

Work Repository
(Test and QA)

Execution
Repository
(Production)
Firewall

Development-test-production cycle
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

Imagine that a firewall protects Production from Development/Test/QA. This diagram shows
that a second Master Repository can be used to deal with the firewall.

Oracle Data Integrator 11g: Integration and Administration 1 - 33

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Repository Setup: Multiple Master Repositories

ODI Studio
Designer
Navigator

Operator
Navigator

Topology
Navigator

Security
Navigator

Java - Any Platform

Any web browser
Browse metadata
lineage
Operate production

Repository
Any ISO-92 RDBMS
Standalone Agent

Java EE Agent
ODI Console

Orchestrates
sessions

Web access to the
repository

Java - Any Platform
Return Code

Execute Jobs

Information System

ODI Plug-in for EM Console

Java EE Application Server
Repository
Access
HTTP
Connection
Execution
Query

You now have a global view of the components that make up ODI: the graphical components,
the repository, the standalone or Java EE agent, and the ODI Console.

Oracle Data Integrator 11g: Integration and Administration 1 - 34

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Components: A Global View

Install

1.
2.
3.
4.

Security
Topology

Install the GUI.
Create the Master and Work Repositories.
Define users and profiles.
Define the IS architecture.
a. Identify the sources and targets.
b. Physical and logical schemas, contexts to associate them
c. Source and target data server connections, agents

Designer

5. Metadata into data models

• Model Definition

6. Define elementary transformations.

a. Table, views, constraints
a. Define transformation rules and control rules.
b. Define the transfer rules.

• Project Interface

7. Unit tests of interfaces

Operator

a. Understand the outcome.
b. Debug.

8. Optimize strategies.
a. Knowledge modules

Designer

9. Define the sequencing.
a. Order the interfaces and procedures in packages.
b. Integration tests.

• Project/KM
• Project/Package

10. Dev to QA
a.
b.
c.
d.

• Project/Scenario
• Solution/
Versioning

Generate scenario.
Create a solution for the project.
Version the solution.
Restore solution in QA repository.

11. QA to Prod
a. Export scenario from QA.
b. Import in Production.

OEM or Operator

12. Operations
a. Define execution schedules.
b. Follow up executions.

This slide suggests, at a very high level, one possible methodology for using ODI in a project.
Due to class time constraints, the practices of this course do not follow this generic checklist.
The next slide provides a checklist of the practices that you perform in this course.

Oracle Data Integrator 11g: Integration and Administration 1 - 35

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Possible ODI Methodology

1-1:
2-1:
3-1:
4-1:
5-1:
6-1:
7-1:
8-1:
9-1:
9-2:
10-1:
11-1:
11-2:
11-3:
12-1:
12-2:
13-1:
14-1:
14-2:
15-1:
16-1:
17-1:
18-1:
19-1:
20-1:
20-2:
21-1:

Logging In and Using Help
Creating Users and Repositories and Logging In
Creating an ODI Agent for Orchestrating Execution of ODI Objects
Defining the Topology
Setting Up a New ODI Project
Creating a Model
Checking Data Quality in the Model
ODI Interface: Simple Transformations
ODI Interface: Complex Transformations
ODI Interface: Implementing Lookup
ODI Interface: Exporting a Flat File to a Relational Table
Using Native Sequences with ODI Interface
Using Temporary Indexes
Using Data Sets with ODI Interface
Using Temporary ODI Interfaces
Developing a New Knowledge Module
Creating an ODI Procedure
Creating an ODI Package
Using ODI Package with Variables and User Functions
Creating and Scheduling an ODI Scenario
Using Load Plans
Working with ODI Versions
Enforcing Data Quality with ODI Interface
Implementing Changed Data Capture
Setting Up ODI Security
Integrating with Enterprise Manager and Using ODI Console
Executing an ODI Scenario Through the ODI Public Web Service

This checklist provides a preview of the practices that you perform in this course. These
hands-on practices begin in the next lesson.

Oracle Data Integrator 11g: Integration and Administration 1 - 36

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities

Start Page

1
2
3
4
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

You can find the ODI client in the Oracle > Middleware subfolders. In this class, a desktop
icon is provided to start the ODI client.
When you log in to the ODI client for the first time, you are presented with the Start Page, with
links to common sets of tasks. The top level of links is organized by a typical sequence of ODI
tasks:
1. Define the Topology
2. Manage Data Models
3. Build Projects
4. Operate Integration Jobs

Oracle Data Integrator 11g: Integration and Administration 1 - 37

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Starting Oracle Data Integrator

The online Help is available immediately, even before creating or connecting to repositories,
and has been entirely rewritten to support the new ODI 11g user interface.
The Help Center provides quick access to help and common tasks, as well as links to useful
Oracle resources.
Help bookmarks: The Help window has a tab labeled Favorites. While browsing help, you
can click the Add to Favorites button to add the document to this tab.

Oracle Data Integrator 11g: Integration and Administration 1 - 38

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Using Online Help

Which of the following does not belong to the ODI runtime
components?
a. ODI Operator
b. ODI Standalone agent
c. ODI Topology Navigator
d. ODI Security Navigator
e. ODI Repository

Answer: c, d
Explanation: The ODI Topology Navigator and the ODI Security Navigator are not runtime
components. The Topology Navigator is used for creating physical and logical architecture
and is not involved in runtime processing. Similarly, the Security Navigator is not a runtime
component because it is used for managing users and maintaining security policies.

Oracle Data Integrator 11g: Integration and Administration 1 - 39

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

If necessary, several Work Repositories can be designated with
several Master Repositories.
a. True
b. False

Answer: a
Explanation: A Work repository can be linked with only one Master Repository, for version
management purposes. However, several Work Repositories can be designated with one or
several Master Repositories, if necessary.

Oracle Data Integrator 11g: Integration and Administration 1 - 40

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In this lesson, you should have learned how to describe:
• The course objectives and agenda of lessons
• The benefits of using ODI
• The ODI 11g architecture and components
• How to use ODI Studio to create, administer, and monitor
ODI objects
• Start Oracle Data Integrator (ODI) client
• Access online Help

In this lesson, you learned about the ODI architecture; the different architecture components,
including Topology Navigator, Designer, Operator, Security Navigator, and the agents that
enable information processing; and the types of information stored in each type of repository.

Oracle Data Integrator 11g: Integration and Administration 1 - 41

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Summary

1. Start the ODI client and log in.
2. Use the Start Page and Help system.

In this practice, you learn how to start the ODI client and log in. You also practice using the
Start Page and Help system.

Oracle Data Integrator 11g: Integration and Administration 1 - 42

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Practice 1-1: Overview

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle University and (Oracle Corporation) use only.

Administering ODI Repositories

After completing this lesson, you should be able to:
• Create and connect to the Master Repository
• Export and import the Master Repository
• Create, connect, and set a password to the Work
Repository

Oracle Data Integrator 11g: Integration and Administration 2 - 2

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Objectives

In the next few slides, you will learn how to create and administer ODI Master and Work
Repositories.

Oracle Data Integrator 11g: Integration and Administration 2 - 3

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Administering the ODI Repositories

•

ODI Master and Work Repositories require creating
repository storage spaces (database schema/user).
– Name
– Password
– Privileges

•

You need to set up connections to ODI Master and Work
Repositories:
– Setting ODI connection information (login name, user, and
password)
– Entering database connection information (driver and URL)

•

You may need to import or export an existing repository in
ODI.

The initial tasks involved in setting up an ODI environment include creation of Master and
Work Repository database schemas/users, and defining connections to the Master and Work
Repositories.

Oracle Data Integrator 11g: Integration and Administration 2 - 4

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Initial Repository Administration Tasks

1.
2.
3.
4.
5.

Create repository storage spaces.
Create the Master Repository.
Connect to the Master Repository.
Create a Work Repository.
Connect to the Work Repository.

These five steps for setting up ODI repositories are covered in detail in the following slides.

Oracle Data Integrator 11g: Integration and Administration 2 - 5

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Steps to Set Up ODI Repositories

Create a schema to host the Master repository and a schema
to host the Work repository.
SQL> create user identified by
default tablespace temporary tablespace
;
SQL> grant connect, resource to ;

Example: Creating User/Schema snpm1

You must first create a storage space in each database that will be the host for a repository. In
this slide, you see the Oracle SQL syntax to create a User/Schema to host the Master
Repository. You use the same commands to create a User/Schema to host the ODI Work
Repository.
Note: Your Master Repository can be stored in the same schema as one of your Work
Repositories. However, you cannot create two different Work repositories in the same
schema. Thus you must create a separate schema for any additional Work Repository that
you create.

Oracle Data Integrator 11g: Integration and Administration 2 - 6

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

1. Creating Repository Storage Spaces

Creating the Master Repository consists of creating the tables and the automatic importing of
definitions for the different technologies.
To create the Master Repository, perform the following:
1. From the File menu, select New. In the New Gallery dialog box, select Master
Repository Creation Wizard. Alternatively, launch bin/repcreate.bat or
bin/repcreate.sh.
2. Complete the fields that define database connectivity:
- Technology: From the list, select the technology your repository will be based on.
- JDBC Driver: Type the name of the driver used to access the technology that will
host the repository.
- JDBC URL: Type the complete path for the data server to host the repository.
- User: Provide the user ID or login of the owner of the tables (previously created
under the name snpm1).
- Password: This is the user’s password.
- Id: This is a specific ID for the new repository, rather than the default 0.
Note: You are strongly urged to ensure that every ID is unique and not used for any other
Master Repository, because it affects imports and exports between repositories.
Oracle Data Integrator 11g: Integration and Administration 2 - 7

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

2. Creating the Master Repository

3. Select the authentication mode that the Master Repository will use.
4. On the Password Storage screen, click Finish.
5. Verify that the Master Repository is created successfully. When the Master Repository is
successfully created, you will see the Oracle Data Integrator Information message. Click
OK. The ODI Master Repository is now created.

Oracle Data Integrator 11g: Integration and Administration 2 - 8

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

2. Creating the Master Repository

To connect to the new Master Repository, perform these steps in ODI Topology Navigator:
1. Open the New Gallery by choosing File > New. In the New Gallery, in the Categories
tree, select General, then ODI. From Items, select Create a New ODI Master Login.
2. Complete the fields that define a connection in ODI and a connection to the database:
Oracle Data Integrator Connection:
- Login Name: A generic alias (for example, Master Repository)
- User: SUPERVISOR (use uppercase)
- Password: SUNOPSIS (use uppercase; SUNOPSIS is the default password of the
SUPERVISOR account.)
Database Connection:
- User: User ID or login of the owner of tables you created for the Master Repository
- Password: The user’s password
- Driver List and Driver Name: Choose the driver required to connect to the DBMS
supporting the Master Repository that you have just created.
- Url: Type the complete path of the data server hosting the repository.
Click Test to check if the connection is working. Validate by clicking OK twice.

Oracle Data Integrator 11g: Integration and Administration 2 - 9

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

3. Connecting to the Master Repository

3. Click the Topology Navigator tab. Click Connect To Repository.
4. Select the newly created repository connection (Master Repository) from the dropdown list. Click OK. The ODI Topology Navigator starts. You are now successfully
logged in to the ODI Topology Navigator.
5. Click the Topology tab. Click the Repositories tab in the left panel of the Topology
Navigator. Verify that your newly created Master Repository is in the Repositories
window.

Oracle Data Integrator 11g: Integration and Administration 2 - 10

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

3. Connecting to the Master Repository

Optionally, instead of creating a new empty Master Repository, you can create a new Master
Repository and populate it by importing an existing one.
The Master Repository Import/Export procedure enables you to optionally transfer the whole
repository (Topology and Security domains included) from one repository to another. It can be
performed in Topology, to import the exported objects in an existing repository, or while
creating a new Master Repository.
To import a Master Repository into an existing Master repository, perform the following:
1. From the File menu, select New.
2. In the New Gallery dialog box, select Master Repository Import Wizard.
3. Navigate to the folder of the zip file to locate the Master Repository that you are
importing and click OK.
The specified file or files are imported into the current Master Repository.

Oracle Data Integrator 11g: Integration and Administration 2 - 11

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Importing the Master Repository

Click drop box

1
2

The Master Repository Import/Export procedure enables you to optionally transfer the whole
repository (Topology and Security domains included) from one repository to another.
1. In the Topology Navigator, click the drop box on the upper-right corner of the Topology
tab, and select Export > Master Repository.
2. Fill in the export parameters:
- Export to directory: Directory in which the export file(s) will be created
- Export to zip file: When this option is selected, a unique compressed file
containing all export files will be created. Otherwise, a set of export files is created.
- Zip File name: Name of the compressed file if the “Make a Compressed Export”
option is selected
- XML Character Set: Encoding specified in the export file. Parameter encoding in
the Extensible Markup Language (XML) file header
- Java Character Set: Java character set used to generate the file
- Export versions: Exports all stored versions of objects that are stored in the
repository. You may want to deselect this option to reduce the size of the exported
repository, and to avoid transferring irrelevant project work.
- Export solutions: Exports all stored solutions that are stored in the repository
3. Click OK. The export files are created in the Export Directory specified.

Oracle Data Integrator 11g: Integration and Administration 2 - 12

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Exporting the Master Repository

You use ODI Work Repositories when designing new transformations with ODI Designer.
Several Work Repositories can be designated with several Master Repositories, if necessary.
However, a Work Repository can be linked with only one Master Repository for version
management purposes.
To launch a Work Repository creation, perform the following steps:
1. Connect to your Master Repository through the Topology Navigator. In the Topology
Navigator, click the Repositories tab. Right-click Work Repositories and select New
Work Repository. A Create Work Repository wizard appears, asking you to complete
the connection parameters for your Work Repository.
2. In the wizard’s first window, complete the following parameters:
- Technology: Select the technology of the server to host your Work Repository.
- JDBC Driver: Set the JDBC Driver (the driver required for the connection to the
DBMS to host the Work Repository).
- JDBC URL: Set the JDBC URL (the path of the data server to host the Work
Repository).
- User: Enter the user ID or login of the owner of the tables that you will create and
host in the Work Repository.
- Password: Enter the user’s password.

Oracle Data Integrator 11g: Integration and Administration 2 - 13

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

4. Creating a Work Repository

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

3. In the wizard’s second window, complete the following parameters:
ID: Give a unique number to your repository, from 1 to 998 (included).
Name: Give a unique name to your Work Repository (for example, WORKREP1).
Password: SUNOPSIS
Note: You are urged not to set a password for the Work Repository (can be more
painful than useful). See the lesson titled “Advanced ODI Administration” to learn
about using the Security Navigator to handle passwords at the security level.
Type: Select Development from the list.
Note: Development type of repository enables management of design-time objects
such as data models and projects (including interfaces, procedures, and so on). A
development repository also includes the runtime objects (scenarios and sessions).
This type of repository is suitable for development environments.
4. Click Finish. In the Create Work Repository login window, click Yes. Enter the Login
name: WORKREP1 as shown in the screenshot. Click OK.
5. Verify that the newly created Work Repository is now in the Work Repositories tree view.
When the Work Repository has been created, the Work Repository window closes. You
can now access this repository through the Designer and Operator modules.

Oracle Data Integrator 11g: Integration and Administration 2 - 14

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

4. Creating a Work Repository

Now you need to create a connection to your Work Repository.
1. Start Oracle Data Integrator, if necessary. Click Connect to Repository.
2. Select Work Repository WORKREP1 from the Login Name drop-down list. Click OK.
3. The ODI Designer Navigator appears.
You have now successfully created and connected to the ODI Work Repository. You can
begin working in the ODI Designer.

Oracle Data Integrator 11g: Integration and Administration 2 - 15

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

5. Connecting to the Work Repository

For security purposes, you might have to change the Work Repository password. To change
the password, you perform the following steps:
1. On the Repositories tab of the Topology Navigator, double-click the Work Repository. A
repository window opens.
2. Click the Change password button.
3. Enter the old password and the new one. Click the OK button.

Oracle Data Integrator 11g: Integration and Administration 2 - 16

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Changing the Work Repository Password

You have seen how to manually create repositories by using the ODI Studio. Now you will see
how to use Fusion Middleware Repository Creation Utility (RCU) to create a Master
Repository and user repositories for a single schema. You can find details in the ODI
Installation Guide.
The RCU wizard guides you through seven steps:
1. Create Repository
2. Database Connection Details
3. Select Components
4. Schema Passwords
5. Custom Variables
6. Map Tablespaces
7. Summary
The first step involves clicking the Create button.
The second step involves specifying database connection details.

Oracle Data Integrator 11g: Integration and Administration 2 - 17

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating Repositories with the RCU

The third step involves selecting components. On this screen, the prefix BETA is used to
group the components associated with this deployment.
The fourth step involves selecting the passwords for the main and additional schema users.

Oracle Data Integrator 11g: Integration and Administration 2 - 18

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating Repositories with the RCU

The fifth step involves entering values for a set of custom variables.
The sixth step involves choosing tablespaces for the selected components.
The seventh step involves checking a summary of your selection, and proceeding with the
creation.

Oracle Data Integrator 11g: Integration and Administration 2 - 19

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating Repositories with the RCU

The following steps are performed to set up an ODI repository
environment:
1. Create repository storage spaces.
2. Create the Master Repository.
3. Create one or more Work Repositories.
a. True
b. False

Answer: a
Explanation: Storage spaces are created in a database by using SQL*Plus or SQL
Developer. One Master Repository is defined in ODI. One or more Work Repositories are
defined in ODI.

Oracle Data Integrator 11g: Integration and Administration 2 - 20

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In this lesson, you should have learned how to:
• Create and connect to the Master Repository
• Export and import the Master Repository
• Create, connect, and set a password to the Work
Repository

In this lesson, you learned about the ODI architecture; the different architecture components,
including the Topology Manager, Designer, Operator, Security Manager, and the agents that
enable information processing; and the types of information stored in each type of repository.

Oracle Data Integrator 11g: Integration and Administration 2 - 21

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Summary

1-1:
2-1:
3-1:
4-1:
5-1:
6-1:
7-1:
8-1:
9-1:
9-2:
10-1:
11-1:
11-2:
11-3:
12-1:
12-2:
13-1:
14-1:
14-2:
15-1:
16-1:
17-1:
18-1:
19-1:
20-1:
20-2:
21-1:

Oracle Data Integrator 11g: Integration and Administration 2 - 22

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities

SQL Developer

• Create SNPM1 database
user for Master Repository.
• Create SNPW1 database
user for Work Repository.

Oracle Data
Integrator
• Create Master Repository.
– Create Master Repository
connection “Master
Repository Pr 2-1” with
SNPM1 as the user.

• Create Work Repository.
– Create WORKREP_PR2-1
Work Repository connection
with SNPW1 as the user.

Note: These are practice repositories. In later lessons, you use other
Master and Work Repositories that are partially predefined with source
and target objects.

In this practice, you use two tools. You use SQL Developer to create the database users for
the Master and Work Repositories. You use Oracle Data Integrator to create the Master and
Work Repositories.
1. Create RDBMS users for the Master and Work Repositories by using SQL Developer.
2. Create the ODI Master Repository by using the ODI New Gallery Master Repository
Creation Wizard.
3. Create the ODI Master Login by using ODI New Gallery Create a New ODI Master
Login.
4. Create an RDBMS user for the Work Repository by using SQL Developer.
5. Create the Work Repository in ODI Topology Manager.
6. Log out of the Master Repository and log in to the Work Repository in ODI Topology
Manager.

Oracle Data Integrator 11g: Integration and Administration 2 - 23

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Practice 2-1: Overview

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle University and (Oracle Corporation) use only.

ODI Topology Concepts

After completing this lesson, you should be able to:
• Describe the basic concepts of the ODI topology
• Describe the logical and physical architecture
• Plan a topology
• Use best practices to set up
a topology
• Launch ODI agents and
set agent parameters

This lesson is a detailed introduction to the Oracle Data Integrator (ODI) topology and gives
some basic recommendations about how to set it up. In this lesson, you should learn:
• The basic concepts of the ODI topology
• The definition of a physical architecture in ODI and its relationship to a logical
architecture
• To plan a topology by using a simple set of guidelines
• Some of the current best practices in setting up a topology in ODI

Oracle Data Integrator 11g: Integration and Administration 3 - 2

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Objectives

Logical and Physical Architectures and
Contexts

In the following slides, you learn what the ODI topology contains, how it defines the logical
and physical architectures of your information system, and how the contexts link the two
together.

Oracle Data Integrator 11g: Integration and Administration 3 - 3

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Topology: Overview

•
•

Topology is the representation of the information system in
ODI.
ODI uses the topology to connect to the resources in the
information system for integration processes.

In ODI, the Topology Navigator is used to define a complete representation of your
information system.
It includes everything—from data servers and schemas through reserved keywords in
languages used by different technologies. ODI uses this topology to access the resources
available in the information system to carry out integration tasks.

Oracle Data Integrator 11g: Integration and Administration 3 - 4

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is Topology?

•

Physical architecture
– Technologies: Oracle, DB2, File,
and so on
—
—

Data types for the given technology
Data Servers: Definition of your
servers, databases, and so on
—

Schemas: Subdivisions of data
servers

– Agents: ODI runtime modules

•
•

Contexts: Mapping logical to physical
Logical architecture
– Technologies and Agents

•
•
•

Languages
Repositories
Generic Actions
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

All available technologies are defined in the topology. For each technology, the available data
types are defined. You rarely, if ever, need to modify these. However, you define all the data
servers that use the technology in your information system. For each data server, you then
define the subdivisions that are known as schemas in ODI.
Next, you must define the agents that carry out the integration tasks at run time. You also set
up the contexts that enable you to define an integration process at an abstract logical level,
and then link it to the physical data servers where it will be performed.
Languages and actions are also found in the topology. Languages specify the keywords that
exist for each technology, and actions are used to generate the data definition language
(DDL) scripts. You would only need to modify these parts of the topology if you were adding a
new technology to ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 5

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is in the Topology?

The most essential things to define in your topology are your data servers and physical
schemas. These enable you to connect to your data through ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 6

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Data Servers
and Physical Schemas

•
•

A data server is any system that is capable of storing data
and making it available in the form of tables.
A data server is always attached to a specific technology.

The definition of a data server in ODI is fairly broad. A data server may not always be a
traditional DBMS. Instead, any system that is capable of storing data and making that data
available in the form of tables is a potential data server in ODI. In ODI topology, a data server
is always attached to a specific technology, such as Oracle, Oracle Essbase, Sybase,
Extensible Markup Language (XML), or Microsoft Access.

Oracle Data Integrator 11g: Integration and Administration 3 - 7

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Data Server?

•
•

ODI connects to a data server by using JDBC or JNDI.
Examples of data servers:
–
–
–
–
–
–

Oracle instance
Microsoft SQL Server instance
IBM DB2
Oracle’s Hyperion Essbase
Oracle E-Business instance
File server

There are multiple ways of connecting a data server to ODI. Most often, Java Database
Connectivity (JDBC) is used. JDBC drivers exist for a wide range of technologies. Java
Naming and Directory Interface (JNDI) is another way to connect to your data server through
a directory service. Additionally new technologies can be created that enable connectivity to
application-specific data sources such as Oracle’s Hyperion Essbase.
Some concrete examples of data servers may be helpful. Each instance of a traditional
database engine, such as Oracle or Microsoft SQL Server, is a data server. Thus, if you have
two instances that are running on the same machine, ODI considers them to be separate data
servers.
A Hyperion Essbase server instance is also a data server. The ODI physical schema
represents an Essbase application and the database represents the Essbase database
(cube). This is an example of an application that is capable of storing data and presenting it in
the form of tables.

Oracle Data Integrator 11g: Integration and Administration 3 - 8

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Data Server?

Guideline 1: Each physical data server must be defined once in
the topology.

There are a number of simple guidelines to remember when setting up your topology in ODI.
Guideline 1 is that every data server in your information system should appear once in your
topology. For example, if you have a machine running two instances of Oracle and one
Paradox data source, you have three physical data servers.

Oracle Data Integrator 11g: Integration and Administration 3 - 9

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Guideline 1

•
•

A physical schema is a technology-dependent subdivision
of a data server.
Datastores on a data server are located in a physical
schema.

A physical schema is a subdivision of a data server whose precise definition depends on the
technology involved. Physical schemas indicate the physical location of the datastores, such
as tables, files, topics, and queues in a data server. The names of physical schemas are used
to prefix object names when generating code to produce their qualified names.
The physical schemas that need to be accessed must be defined in their corresponding data
servers. You will now see some examples of how this works on different technologies.

Oracle Data Integrator 11g: Integration and Administration 3 - 10

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Physical Schema?

•

An ODI physical schema always consists of two data
server schemas:
– The data schema, which contains the datastores
– The work schema, which stores temporary objects

•

A data server schema is technology dependent.
– Catalog name and/or schema name
– Example: Database and owner, schema

•

A data server has:
– One or more physical schemas
– One default physical schema for server-level temporary
objects

An ODI physical schema comprises two separate data server schemas: A data schema where
the datastores for the physical schema are located and a work schema that is used by ODI to
store temporary objects during integration processing. Several physical schemas can share
the same work schema.
A data server schema in a data server is uniquely identified, depending on the technology, by
a catalog name or a schema name, or both. The terms for the catalog name and the schema
name differ depending on the technology.
For example, in Microsoft SQL Server, the catalog is called “database,” and the schema is
called the “owner.”
In Oracle, there is no catalog, and a schema is called a “schema” or “user.” You will see some
examples later in this lesson.
Though a data server can have several physical schemas, it has one default physical schema
that is used to select a work schema to store server-level temporary objects during certain
integration processes.

Oracle Data Integrator 11g: Integration and Administration 3 - 11

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Physical Schemas: Properties

Technology

Data Server

Schema

Oracle

Instance

Schema

Microsoft SQL Server

Server

Database/Owner

Sybase ASE

Server

Database/Owner

DB2/400

Server

Library

Teradata

Server

Schema

Microsoft Access

Database

(N/A)

JMS Topic

Router

Topic

File

File Server

Directory

The terms “data server” and “schema” are general terms in ODI that represent different
concepts in different database technologies. The table in the slide shows the relationship
between the ODI terms “data server” and “schema” with their equivalents in some common
technologies.
For example, an Oracle server “instance” is equivalent to an ODI “data server.” A “schema” on
this Oracle instance is represented as a “schema” in ODI.
However, for SQL Server, an ODI schema represents a combination of “database” and
“owner.” For example, a table might be called “salesdb.bill.mytable.” In ODI, the
schema is “salesdb.bill.”
Not every technology supports schemas. For example, an Access “database” corresponds to
an ODI data server. However, Access databases are not subdivided into schemas. Thus,
when you define an Access data server, you do not need to specify a schema.
ODI also supports technologies that are not true databases. For example, you can set up a
directory of files as a database. Here, the file server becomes the data server and a specific
directory is an ODI schema. Individual files in that directory are then treated as datastores.
Note: The recommended practice in ODI is to create a separate area on each data server
specifically for the temporary objects. You should use this dedicated area as the work schema
for your physical schema. Thus, there is no risk of temporary objects created by ODI polluting
your production data.
Oracle Data Integrator 11g: Integration and Administration 3 - 12

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Technology Terminology Among Vendors

•

Guideline 2: Under each data server, define a physical
schema for each subdivision of the server that will be used
in ODI.
Recommendations:
– All schemas in the same instance of the database must be
defined under the same data server.
– The best possible login to the data server is the owner of the
staging schema (also called “work schema”).
– Ensure that all data servers have a DEFAULT schema (by
default, the first one that is created). This is required for most
knowledge modules.

For each data server in your topology, you must define a physical schema to represent each
subdivision of the server that will be used. So, if a server has four technology-specific
subdivisions, but you want to use only two in ODI, you must define two physical schemas for
that server.

Oracle Data Integrator 11g: Integration and Administration 3 - 13

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Guideline 2 and Recommendations

The most essential things to define in your topology are your data servers and physical
schemas. These enable you to connect to your data through ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 14

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Defining Topology: Example

Production site: Boston
Windows machine

Linux machine
Oracle 10g

MS SQL Server

ACCOUNTING
db_dwh

Oracle 11g

db_purchase

SALES

Production site: Tokyo
Windows machine

Windows machine

Linux machine

MS SQL Server A

MS SQL Server B

Oracle

dwh

purchase

ACCT
SAL

Here is an example that you will refer to throughout this lesson. It is a simple setup with
accounting and sales information that is used to populate a data warehouse and a purchasing
database.
There are two separate production sites, with the first based in Boston. The Boston production
site has a Windows machine that runs SQL Server. The data warehouse for Boston is hosted
on this machine. The SQL Server database is named db_dwh at this site and the Boston
purchasing database, also hosted on this machine, is named db_purchase.
The example also shows a Linux machine, which, for historical reasons, has two different
versions of Oracle running on the same system. One version has a schema for accounting,
and the other version has a schema for sales.
A second production site is in Tokyo. Here, the data warehouse and purchasing databases
are split onto different Windows machines that run SQL Server. The data servers are labeled
“MS SQL Server A” and “MS SQL Server B.” However, the accounting and sales databases
run on a single Oracle server. Note that the physical schemas at this site have different
names from those at the Boston site.
The next slide shows how to model this physical architecture in ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 15

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Example: Infrastructure for Two Production Sites

Production site: Boston
An ODI data server
representing one SQL
Server instance with 2
schemas

Oracle-Boston 10g

MSSQL-Boston

ACCOUNTING
db_dwh

Data server

db_purchase

ODI Design Considerations:
• Disregard which machines
physically run which servers.
• Each database instance is
represented as a data server.

Oracle-Boston11g
SALES

Data server

2 ODI data servers, each
representing one Oracle
instance with 1 schema
(disregard that they are
hosted on 1 Linux machine)

Production site: Tokyo
MSSQL-TokyoA
ODI data servers
representing one SQL
Server instance with 1
schema

MSSQL-TokyoB

dwh

Data server

Oracle-Tokyo
ACCT

purchase

SAL

Data server

An ODI data server
representing one
Oracle instance with
2 schemas

Applying the previous slide’s architecture in ODI design, guidelines 1 and 2 enable you to
design this physical architecture in ODI. You disregard which machines physically run which
servers.
Each Oracle and SQL Server instance is represented as a data server in the ODI topology.
Similarly, each Oracle schema or SQL Server database is represented as a physical schema.

Oracle Data Integrator 11g: Integration and Administration 3 - 16

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Design: Physical Architecture of the Two
Production Sites

Select a
context

Design time

Run time

Logical
architecture

Physical
architecture

Logical schema
Accounting

Development
New York

Physical schemas
ACCOUNTING

Production
Boston

ACCOUNTING

Production
Tokyo

ACCT

You learned about the physical architecture, which describes how ODI physically accesses
data. Now, you look at the logical architecture, which is a more general view that takes into
account the similarities between the physical schemas in different environments.

Oracle Data Integrator 11g: Integration and Administration 3 - 17

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Logical Schemas and Contexts

•

A logical schema is a single alias for different physical
schemas that have similar data structures based on the
same technology, but in different contexts.
If two data schemas stored in data servers contain the
same data structures, they are declared as:
– Two physical schemas
– One single logical schema

1 Logical schema
Accounting

Production site:
Boston

Linux
Oracle

Production site:
Tokyo

Linux
Oracle

ACCOUNTING
ACCT

Oracle

SAL

SALES

The essential concept to understand in the logical architecture is the logical schema. A logical
schema is a single alias for different physical schemas. These schemas should have similar
or identical data structures, and must be based on the same technology. The logical schema
thus brings together different physical schemas representing the same kind of data in different
contexts.
If two similar data schemas are stored in two different data servers, but they have the same
data structures, you declare the two data schemas as physical schemas in ODI, according to
guideline 2. However, you create a single logical schema that represents the pair of schemas.
The context determines the physical schema that will be used at any given time.
A good example of a logical schema is found in the information system example. In Boston,
the accounting database is stored in a schema called ACCOUNTING. In Tokyo, the schema is
called ACCT. Both of them have the same data structure; they contain all tables for an
accounting application. In the ODI topology, you consider them as one logical schema:
Accounting.

Oracle Data Integrator 11g: Integration and Administration 3 - 18

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Logical Schema?

Development:
New York

•

Windows

Guideline 3: Define one
Oracle
MS SQL Server
logical schema for each
db_dwh
ACCOUNTING
SALES
db_purchase
group of physical schemas
containing a similar data
Production site:
structure.
Boston
Windows
Linux
Note that for similar
Oracle 10g
MS SQL Server
ACCOUNTING
data structures,
db_dwh
Oracle 11g
Accounting
db_purchase
only the structure
SALES
counts. The content
Production site: Tokyo
can be completely
Windows
Windows
Linux
different on each
MS SQL Server A
Oracle
MS SQL Server B
physical schema.
ACCT
dwh
purchase

SAL

You should define one logical schema each time you have a group of physical schemas
containing similar data structures.
In this example, Accounting represents the ACCOUNTING Oracle schema on the Windows
machine in New York, the ACCOUNTING schema on the Oracle 10g server running on Linux in
Boston, and the ACCT schema running on Linux in Tokyo.
Note: A simple way to understand guideline 3 is to remember that one logical schema
corresponds to the data structure for one application, implemented in several places called
physical schemas.

Oracle Data Integrator 11g: Integration and Administration 3 - 19

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Guideline 3

•

Physical architecture:
– Defines physical resources
– Describes the real locations of servers and schemas
—

•

If the accounting application data schema exists at several
places, it is declared as different physical schemas.

Logical architecture:
– Abstract view of the resources
—

The accounting application schema is defined only once here. It
is one logical schema implemented in different contexts.

This distinction can be generalized into the logical architecture as distinct from the physical
architecture.
The physical architecture tells ODI about physical resources, such as connections to data
servers and agents, which will be covered later in the lesson. The physical architecture
describes the locations of servers that exist in your information system and the schemas that
you want to use on them. For example, you may logically have one data schema representing
your accounting application. But, if this schema exists in different places, you should declare
these as separate physical schemas.
The logical architecture, on the other hand, gives a more abstract view of these resources. In
this example, your accounting application schema is defined only once in the logical
architecture. It is a single schema implemented in several different contexts. The contexts are
covered in the following slides.

Oracle Data Integrator 11g: Integration and Administration 3 - 20

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Logical Versus Physical Architecture

•
•

In ODI, you work at design time on logical resources.
At run time, execution is started in a particular context,
allowing access to the physical resources of that context.
Select a
context

Design time

Run time

Logical
architecture

Physical
architecture

Logical schema
Accounting

Physical schemas

Development
New York

ACCOUNTING

Production
Boston

ACCOUNTING

Production
Tokyo

ACCT

It is important to understand that in ODI, you always work at design time using objects defined
in the logical architecture. For example, you would refer to the datastores defined in the
logical schema Accounting without specifying whether you meant the server in Tokyo or
Boston.
At run time, physical resources are required to carry out the integration process. You,
therefore, specify a particular context to execute within. ODI is then able to provide access to
the physical resources of that context.

Oracle Data Integrator 11g: Integration and Administration 3 - 21

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Note: Design Time Versus Run Time

•
•

A context is an ODI object you define that represents a
“situation” where a similar group of resources appears.
A context maps logical resources onto their
implementations as physical resources (logical architecture
onto a physical architecture).
In a given context, one logical resource is mapped to one
unique physical resource.
Example of contexts:
– Sites: Boston, Tokyo, and Development
– Environments: Development, Test, and Production

A context represents a “situation” where you see the same group of resources. From a
technical perspective, a context maps individual logical resources onto individual physical
resources. So, given a context and a logical resource, ODI can determine the unique physical
resource that is appropriate.
In terms of topology, the logical architecture is mapped onto the physical architecture through
a context. In your example, Boston, Tokyo, and Development are contexts representing
different geographical sites. However, it can also represent situations for different purposes,
such as development, test, or production sites.

Oracle Data Integrator 11g: Integration and Administration 3 - 22

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Context?

Data server
Physical schemas

Contexts

Logical schema
Logical to Physical
mapping, per the
Development context

A logical schema can have multiple physical schemas resolved per context. In this example,
for the Development context, the ORACLE_ORCL_LOCAL_SALES logical schema maps to the
ORACLE_ORCL_LOCAL.SALES_DEV physical schema.

Oracle Data Integrator 11g: Integration and Administration 3 - 23

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

A Context Maps a Logical to a Physical Schema

•
•

Guideline 4: Similar resources appear on the three
different sites.
Production site: Boston
Development site: New York
Three contexts:
– Development
– Boston
– Tokyo

Windows

MS SQL Server

ACCOUNTING

db_dwh

SALES

db_purchase

Oracle

Windows

Linux

Oracle 10g
ACCOUNTING

Oracle 11g
SALES

Production site: Tokyo
Linux
Windows

MS SQL Server A

MS SQL Server B

Oracle
ACCT

dwh
purchase

SAL

Guideline 4: If you have similar groups of resources in different situations, you should define
one context for each situation.
If you apply guideline 4 to the example, each site clearly represents the same collection of
four different resources. Therefore, you have three contexts: Development, Boston, and
Tokyo.

Oracle Data Integrator 11g: Integration and Administration 3 - 24

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Defining Contexts

Logical
architecture

Datawarehouse
(Logical schema)

Contexts

Physical
architecture

Development

Windows

Tokyo

Windows

db_dwh

Boston

dwh

db_purchase

MS SQL Server

Development site: New York

db_dwh
db_purchase

MS SQL Server A

Production site: Tokyo

MS SQL Server

Production site: Boston

You now illustrate the meaning of a context graphically by using the infrastructure example.
You know about the data warehouses in New York, Tokyo, and Boston that have different
names but share similar data structures. At the logical architecture level, you have a single
logical schema called Datawarehouse. However, you have three contexts. In the
“Development” context, this logical schema is mapped onto the physical schema db_dwh at
the New York site. In the “Tokyo” context, it is mapped onto the physical schema dwh at the
Tokyo site. In the Boston context, the logical schema is mapped onto the physical schema
db_dwh at the Boston site.

Oracle Data Integrator 11g: Integration and Administration 3 - 25

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Mapping Logical and Physical Resources

•
•
•
•

Logical resources may remain unmapped to any physical
resource in a given context.
However, unmapped resources cannot be used in the
context.
A single physical resource may be mapped in several
contexts.
In a given context, a logical resource is mapped at the
most to one physical resource.

You may wonder what would happen if you had a large number of logical resources and a
large number of contexts. Would you have to map every single logical resource in every
context?
The answer is no. You can leave logical resources unmapped in any context. However, when
executing an object in that context, unmapped logical resources cannot be reached.
In addition, one single physical resource may be useful in several contexts. For example, you
may want to retrieve information from the company web server in various contexts. This does
not pose a problem. You simply map it to a logical resource in each context.
There is a restriction regarding contexts and mapping logical and physical resources. In a
given context, a logical resource can be mapped to one physical resource at the most. That
is, in a certain context, the data warehouse logical schema cannot be mapped to the data
warehouses in Boston and Tokyo simultaneously.
This means that when you ask for one logical resource in one context, you will always have
access to one physical resource if the logical resource is mapped in the context.

Oracle Data Integrator 11g: Integration and Administration 3 - 26

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Mapping Logical and Physical Resources

The following slides cover defining agents in your topology. In any production environment,
you have at least one agent running in ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 27

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Agents in Topology

•

Agents are lightweight runtime components.
– Can start execution on demand or on schedule
– Can be installed on any machine

•

Agents orchestrate the integration process.
– Send generated code to be executed by data servers
– Update the execution log

•

Agents must be declared in the topology.
– Physical agents represent components running at run time.
– Physical agents must also be abstracted as logical agents.

•

Two types of agents:
– The Java EE agent can be deployed as a web application
and benefit from the features of an application server.
– The Standalone agent runs in a simple Java machine and
can be deployed where needed to perform the integration
flows.
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

The physical agent is a Java service, which can be placed as a listener on a TCP/IP port.
An agent orchestrates the entire process of integration. It carries out data transformations by
sending generated code to the relevant technologies. This could mean sending SQL
statements to a data server, shell commands to the operating system, or even Simple Mail
Transfer Protocol (SMTP) commands to an email server.
Just as data servers, agents are a part of your topology. Physical agents in the topology
correspond to the agents that run at run time. However, you must also define logical agents
that are mapped onto physical agents through contexts.

Oracle Data Integrator 11g: Integration and Administration 3 - 28

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Physical Agents

2
128.1585.34.118

Though you do not need to have an agent running before you declare it in ODI, the best
practice is to have it running. The physical agent should be created with ODI Topology
Navigator.
1. Connect to the Topology Navigator. Log in to the Master Repository.
2. Right-click the Agents node on the Physical Architecture tab and select New Agent.
3. Define the agent’s parameters. These parameters essentially tell ODI how to find the
agent on the network.
For agent name, you can specify anything. It is, however, recommended that you use the
name of the machine, an underscore, and then the port that it is running on. Then specify
the host or IP address of the machine, and finally the port.
Note: If you want to set up load balancing, click the Load balancing tab and select a set of
physical agents to which the current agent can delegate executions. When the agent has
queued more than the specified number of sessions, it will reject further requests. You can
also define other agents to which this agent can delegate sessions. Load balancing will be
discussed in detail later in this lesson.

Oracle Data Integrator 11g: Integration and Administration 3 - 29

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Physical Agent

•

Agent parameters:

-port=: Port on which the agent is listening (default port is 20910)
-help: Displays the options and parameters that can be used, without launching the
agent
-name=: The name of the physical agent used
-v=: Enables the agent to generate traces (verbose mode)

•

Work Repository connection parameters:

-SECU_DRIVER=: JDBC driver to access the Master Repository
-SECU_URL=: JDBC URL to access for the Master Repository
-SECU_USER=: User of the Master Repository connection
-SECU_PASS=: Password of the user of the Master Repository. This
password must be encrypted.
-WORK_REPOSITORY=: Name of the Work Repository
containing scenarios to be executed

Connection parameters are specified in the odiparams file.
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

These parameters are generally stored in the odiparams file. This slide lists the ODI Agent
parameters that enable the agent to be configured. The parameters are preceded by the “-”
character and the possible values are preceded by the “=” character. The ODI Agent
parameters include:
• -port=: Is the port on which the agent is listening. If this parameter is not
specified, the agent runs as a listener on the default port 20910.
• -help: Displays the options and parameters that can be used, without launching the
agent
• -name=: Is the name of the physical agent used. ODI needs this
parameter to identify the agent that executes a session in the following cases:
- More than one agent is declared on the same machine (on different ports).
- Your machine’s IP configuration is insufficient to enable ODI to identify the agent
(this problem occurs frequently on AS/400).
- The agent’s IP address does not enable the agent to be identified (127.0.0.1 or
“loopback”).
• -v=: Enables the agent to generate traces (verbose mode). There are
five trace levels:
- Displays the start and end of each session
Oracle Data Integrator 11g: Integration and Administration 3 - 30

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Agent Parameters

•

Work Repository connection parameters. These parameters, which are used to specify the
connection to the Work Repository, are specified in the odiparams file.
- -SECU_DRIVER=: JDBC driver to access the Master Repository,
for example: oracle.jdbc.driver.OracleDriver
- -SECU_URL=: JDBC URL to access for the Master Repository, for example,
jdbc:oracle:thin@localhost:1521:XE
- -SECU_USER=: User of the Master Repository connection (the database
user)
- -SECU_PASS=: Password of the user of the Master Repository
connection. This password must be encrypted by using the command agent
ENCODE .
- -WORK_REPOSITORY=: Name of the Work Repository
containing the scenarios to be executed

Oracle Data Integrator 11g: Integration and Administration 3 - 31

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

- Displays level 1, and the start and end of each step
- Displays level 2, and each task executed
- Displays the SQL queries executed
- Complete trace, generally reserved for support
Some of these traces can be voluminous and so it is recommended that you redirect them
to a text file by using the following command:
- In Windows: agent.bat "-v=5" > trace.txt
- In UNIX: agent.sh -v=5 > trace.txt

•

On Windows:

Agent.bat "-port=20300" "-v=5" launches the Standalone agent on
port 20300 with a level 5 trace on the console.

•

On UNIX:

./agent.sh "-port=20300" "-name=Agent001" launches the
Standalone agent and names it Agent001.

Note: Each agent will have its own directory.
By default, the agent.bat and odiparams.bat (or sh in Linux) files are available in the
/oracledi/agent/bin directory.

Oracle Data Integrator 11g: Integration and Administration 3 - 32

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Launching a Standalone Agent: Examples

•

On Windows:

agentstop "-PORT=20300" stops the Listener agent on port 20300.

•

On UNIX:

./agentstop.sh stops the Listener agent on the default port.

OPMN Integration
Standalone agents can be made highly available by using Oracle Process
Manager and Notification Server (OPMN). Scripts are provided to configure
OPMN to protect standalone agents against failure.

You can stop a standalone ODI agent, which is listening on a TCP/IP port by using the
agentstop command.
To stop an agent:
1. Open a UNIX shell, CMD, or QSH (for AS/400) session
2. Launch the appropriate command file (agentstop.bat on Windows, agentstop.sh
on UNIX or AS/400-QSH), with any required parameters. The listening agent is stopped.
Note: For security reasons, you can stop an agent only from a command line launched on the
same machine from which the agent’s process was started. You cannot stop a remote agent.
OPMN Integration
The OPMN files are in the /oracledi/agent/bin directory. For more
information, see Oracle Fusion Middleware Installation Guide for Oracle Data Integrator.

Oracle Data Integrator 11g: Integration and Administration 3 - 33

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Stopping the ODI Agent

The deployment features:
• Oracle WebLogic Server Integration
• Java EE Agent Template Generation
• Oracle WebLogic Configuration Wizard
• Automatic Data Source Creation for WebLogic Server

The Runtime Agent can be deployed as a Java EE component within an application server. It
benefits in this configuration from the application server layer features such as clustering and
connection pooling for large configurations. This Java EE agent exposes an MBeans
interface, enabling lifecycle operations (start/stop) from the application server console and
metrics that can be used by the application server console to monitor the agent activity and
health.
• Oracle WebLogic Server Integration: Oracle Data Integrator components integrate
seamlessly with the Java EE application server.
• Java EE Agent Template Generation: Oracle Data Integrator provides a wizard to
automatically generate templates for deploying Java EE agents in Oracle WebLogic
Server. Such a template includes the Java EE agent and its configuration, and can
optionally include the JDBC data sources definitions required for this agent, as well as
the drivers and libraries files for these data sources to work.
• Oracle WebLogic Configuration Wizard: By using the Oracle WebLogic Configuration
Wizard, domain administrators can extend their domains or create a new domain for the
Oracle Data Integrator Java EE runtime agents.

Oracle Data Integrator 11g: Integration and Administration 3 - 34

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Deploying and Configuring a Java EE Agent

1
2

3
4
5

•

Automatic Data Source Creation for WebLogic Server: Java EE Components use JDBC
data sources to connect to the repositories as well as to the source and target data
servers, and benefit, when deployed in an application server, from the connection
pooling feature of their container.

To facilitate the creation of these data sources in the application server, Oracle Data
Integrator Studio provides an option to deploy a data source into a remote Oracle WebLogic
application server.
1. To deploy and configure domains with WLS, start the config.bat file, which is found
in the ODI Home Install subfolder. On the Configuration Wizard Welcome screen, select
“Create new Weblogic domain.”
2. Extend the domain with the ODI_AGENT application.
3. Start the Admin Server.
4. Set up JPS Security within the Admin Server.
5. Start your Weblogic domain from the command shell.

Oracle Data Integrator 11g: Integration and Administration 3 - 35

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Deploying and Configuring a Java EE Agent

7
6

6. Start ODI and connect to the Work Repository.
7. Test the connectivity of the ODI agent using Topology Manager.
Now you can start executing your ODI objects with the configured Java EE agent.

Oracle Data Integrator 11g: Integration and Administration 3 - 36

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Deploying and Configuring Java EE Agent

Agent localagent2 is
linked to the agent
localagent and to itself.

Agent localagent3 is linked
to the agent localagent,
localagent2, and to itself.

ODI implements load balancing between physical agents. Each physical agent is defined with
the maximum number of sessions it can execute simultaneously and optionally with a number
of linked physical agents to which it can delegate sessions’ executions. An agent’s load is
determined at a given time by the ratio (number of running sessions/maximum number of
sessions) for this agent.
The maximum number of sessions is a value that must be set depending on the capabilities of
the machine running the agent. It can also be set depending on the amount of processing
power you want to give to the Data Integrator agent.
Delegating Sessions
When a session is started on an agent with linked agents, ODI determines which one of the
linked agents is less loaded, and the session is delegated to this linked agent. If the user
parameter “Use new load balancing” is in use, sessions are also rebalanced each time a
session finishes. This means that if an agent runs out of sessions, it will possibly be
reallocated some sessions from another agent.
Note: An agent can be linked to itself. An agent not linked to itself can only delegate sessions
to its linked agents, and will never execute a session. Delegation works on cascades of linked
agents. Besides, it is possible to make loops in agents’ links. This option is not recommended.

Oracle Data Integrator 11g: Integration and Administration 3 - 37

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Load Balancing: Example

When for a given agent the number of running sessions is equal to its maximum number of
sessions, the agent will set incoming sessions in a “queued” status until the number of running
sessions falls below the maximum number of sessions for this agent.
To set up load balancing:
• Define a set of physical agents and link them to a root agent (see “Create a Physical
Agent”)
• Start the root and linked agents
• Run the executions on the root agent. ODI will balance the load of the executions between
its linked agents.
Note: You can see the execution agent for a session in the session window in Operator.

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Agent Unavailable

Oracle Data Integrator 11g: Integration and Administration 3 - 38

Guideline 5: You need one physical agent defined in ODI for
every agent you have started.

Important Note 5
For every agent that you have started, you need to create one physical agent in the topology.

Oracle Data Integrator 11g: Integration and Administration 3 - 39

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Guideline 5

Production site: Boston

Development site: New York
Windows
Oracle

Linux

Windows

Oracle 10g

MS SQL Server

ACCOUNTING
ACCOUNTING

db_dwh

SALES

db_purchase

Oracle 11g
SALES

Production site: Tokyo
Windows

Windows

MS SQL Server A

MS SQL Server B

Linux
Oracle
ACCT

dwh
purchase

SAL

Now you add the agents that you have installed to the graphic. Here, you have one agent in
each context. In the New York and Tokyo sites, the agent runs on a Windows machine. At the
Boston site, it runs on Linux.

Oracle Data Integrator 11g: Integration and Administration 3 - 40

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Infrastructure with Agents: Example

•
•

In ODI, you need to define each of these physical agents–
one per agent launched.
One logical agent (depending on the context) will be
mapped onto one of the physical agents.
Development site: New York

Development site: Boston
Linux

Windows

Windows
Oracle

Development site: Tokyo

MS SQL Server

MS SQL Server B

Oracle 10g
ACCOUNTING

ACCOUNTING

db_dwh

SALES

db_purchase

purchase

Oracle 11g
SALES

In ODI, you must define each of these agents as a physical agent. Then, you must define a
logical agent. For example, for the “Development” context, you should map it onto the agent
running at the New York development site.
Note: The logical agent is selected when processes are started. The execution context then
defines which physical agent will orchestrate the process.

Oracle Data Integrator 11g: Integration and Administration 3 - 41

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Defining Agents: Example

Production site: Boston

Development site: New York
Windows machine
Oracle

Windows machine

Linux machine

MS SQL Server

Oracle 10g
ACCOUNTING

ACCOUNTING

db_dwh

SALES

db_purchase

Oracle 11g
SALES

Production site: Tokyo
Windows machine

Windows machine

Linux machine

MS SQL Server A

MS SQL Server B

Oracle

dwh

purchase

ACCT
SAL

Returning to the earlier example of the infrastructure for two production sites, the
development site must now be added. The site where most of the ODI development occurs is
based in New York. This site has access to the development versions of the databases. For
cost reasons, all databases at this site are hosted on a single Windows machine. This
machine runs one Oracle instance and one SQL Server instance.

Oracle Data Integrator 11g: Integration and Administration 3 - 42

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: Fragmentation Problem

New York
Development Server

Boston
Production Server
Oracle 10g

Oracle_dev

ACCOUNTING
ACCOUNTING

Oracle 11g

SALES

Information System
Physical architecture in Oracle Data Integrator
Oracle_dev

Oracle_dev1

Oracle 10g

ACCOUNTING

Oracle_dev2

Oracle 11g

SALES

ACCOUNTING
SALES

You will now see how to define the development databases in ODI.
You know that the production site in Boston has the accounting and sales schemas on two
separate Oracle instances. A data server was created for each schema in the ODI topology.
However, at the development site in New York, a single Oracle instance, Oracle_dev,
contains the two schemas. Normally, you create a single data server in the ODI topology with
two physical schemas.
When developing the integration processes, you can take advantage of the fact that the
accounting and sales databases are on the same Oracle instance. However, when you try to
put these into production on the Boston site, the processes will not work because the
development environment does not reflect the production environment. For example, a join in
the source in New York might work, while failing when moved into production in Boston.
Therefore, you must develop the processes such that they reflect the production environment
closely. That is, the development site must reflect the fragmentation of databases at the
production site.
You need to define two data servers at the Boston site. In reality, both of them point to the
same server. However, by separating them this way, you prevent the developers and ODI
from making false assumptions.

Oracle Data Integrator 11g: Integration and Administration 3 - 43

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: Fragmentation Problem

Guideline 6: The topology of the development environment
should always reflect the most fragmented production
environment.

This problem and solution is generalized as guideline number six. When you set up the
topology for the development environment, it should mirror the most fragmented production
environment. That is, if a production environment has four schemas on four different servers,
the development environment should be defined in the same way. If several production
environments are fragmented in different ways, the development environment should take all
these fragmentations into account.
This is an exception to guideline one, which states that data servers should be defined only
once.

Oracle Data Integrator 11g: Integration and Administration 3 - 44

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: Important Guideline 6

•

Data servers
– Guideline 1: One data server per Oracle instance or MS SQL
Server
—

Tokyo: 3 + Boston: 3

– Guideline 6: Reflect the fragmented production environment.
—

New York (development): 2 Oracle data servers + 2 MS SQL
data servers

– Total: 10 Data Servers

•

Physical schemas
– Guideline 2: One physical schema per Oracle schema or MS
SQL database/owner
—

Tokyo: 4 + Boston: 4 + New York: 4

– Total: 12 physical schemas

Here is how you define this situation in ODI. First, how many data servers do you need?
Guideline 1 states that you must have one data server for every instance of a server. In this
example, you need to have three servers in Tokyo and three in Boston.
If you apply guideline 6, you get two Oracle servers to reflect the fragmentation of the two
Oracle servers in Boston. Similarly, you have two SQL Servers because the two SQL Servers
in Tokyo are split. That gives a total of 10 data servers.
How many physical schemas do you need?
Guideline 2 states that each physical schema used by ODI must be defined. You have two
Oracle schemas and two MS SQL database/owner combinations in New York, Boston, and
Tokyo. That gives 12 physical schemas in all, each attached to the respective data server.

Oracle Data Integrator 11g: Integration and Administration 3 - 45

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: Defining the Physical Architecture

Production site: Boston

Development site: New York
Windows
Oracle

Windows

Linux
Oracle 10g

MS SQL Server

ACCOUNTING
ACCOUNTING

db_dwh

SALES

db_purchase

Oracle 11g
SALES

Production site: Tokyo
Windows

Windows

MS SQL Server A

MS SQL Server B

Linux
Oracle
ACCT

dwh
purchase

SAL

Eight servers are in the information infrastructure.

Oracle Data Integrator 11g: Integration and Administration 3 - 46

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: The Infrastructure

Development site: New York
Windows
Oracle

MS SQL Server

ACCOUNTING

db_dwh

SALES

db_purchase

Oracle-Dev1

MSSQL-Dev1

ACCOUNTING

db_dwh

MSSQL-Boston

Oracle-Boston 10g
ACCOUNTING

db_dwh

Oracle-Dev2

MSSQL-Dev2

SALES

db_purchase

Oracle-Boston 11g
SALES

MSSQL-TokyoA

MSSQL-TokyoB

Oracle-Tokyo

ACCT

dwh
purchase

SAL

The eight servers in the infrastructure are defined in ODI as 10 data servers containing 12
physical schemas.

Oracle Data Integrator 11g: Integration and Administration 3 - 47

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Special Case: Physical Architecture in ODI

In the following slides, you learn how to set up a topology to capture your information system
in ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 48

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Defining a Topology: Best Practices

1. Identify the physical architecture.
– All data servers
– All physical schemas
– Required physical agents

2. Identify the contexts you have by looking for similar data
schemas and agents in different situations.
3. Define the logical architecture by naming:
– The logical schemas
– The logical agents

4. On paper, write a matrix of the logical and physical
mappings.
– This matrix helps you plan your topology.

To plan your topology:
1. Identify the physical architecture that you have in place. All physical data servers and
schemas need to be defined in ODI.
2. Similarly, consider the agents you would need and the machines on which they would
be located.
3. Identify the different contexts you have by looking for similar data schemas and agents
in different situations.
4. Provide names for those similarities to create a logical architecture. Provide a name for
each logical schema and logical agent.
5. Finally, write a matrix of the mappings from your logical architecture to your physical
architecture. (Use pen and paper.) The next slide shows an example of a matrix.

Oracle Data Integrator 11g: Integration and Administration 3 - 49

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Planning the Topology

2
Logical schemas
Contexts

Accounting

Sales

…

Development

ACCOUNTING in
Oracle on Windows

SALES in
Oracle on
Windows

…

Tokyo

ACCT in Oracle on
Linux

…

3 …

…

This is a simple matrix of mappings from the logical architecture to the physical architecture.
1. In horizontal order, make a list of all the logical schemas (Accounting, Sales).
2. In vertical order, write down all the contexts that you have defined (Development,
Tokyo).
3. Fill in the squares. For each combination of logical schema and context, write down the
name of the physical schema, the data server, and the technology.

Oracle Data Integrator 11g: Integration and Administration 3 - 50

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Matrix of Logical and Physical Mappings

You use a data server that has five technology-specific
subdivisions. You want to use one of them in ODI. How many
physical schemas should you define for that server?
a. One
b. Two
c. Three
d. Five

Answer: a
Explanation: For each data server in your topology, you must define a physical schema to
represent each subdivision of the server that will be used in ODI.

Oracle Data Integrator 11g: Integration and Administration 3 - 51

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In a given context, how many physical resources can a logical
resource be mapped to at the most?
a. None
b. One
c. Two
d. Any number

Answer: b
Explanation: A context maps individual logical resources onto individual physical resources.
So, given a context and a logical resource, ODI can determine the unique physical resource
that is appropriate.

Oracle Data Integrator 11g: Integration and Administration 3 - 52

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In how many contexts can a physical schema be used?
a. None
b. One
c. Two
d. Any number

Answer: d
Explanation: There is no limit (actually the limit is the number of contexts).

Oracle Data Integrator 11g: Integration and Administration 3 - 53

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In this lesson, you should have learned how to:
• Describe the basic concepts of the topology
• Describe the logical and physical architecture
• Plan a topology
• Use best practices to set up a topology
• Launch ODI agents and set agent parameters

Oracle Data Integrator 11g: Integration and Administration 3 - 54

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Summary

1-1:
2-1:
3-1:
4-1:
5-1:
6-1:
7-1:
8-1:
9-1:
9-2:
10-1:
11-1:
11-2:
11-3:
12-1:
12-2:
13-1:
14-1:
14-2:
15-1:
16-1:
17-1:
18-1:
19-1:
20-1:
20-2:
21-1:

Oracle Data Integrator 11g: Integration and Administration 3 - 55

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities

Run encode to generate an encrypted password string.

2.
3.
4.
5.

Edit odiparams.bat, inserting the encrypted password.
In ODI, define a physical agent named localagent.
In ODI, define a logical agent named localagent.
Execute agent.bat, to create, install, and start an agent named
localagent.
Verify connection to the newly created agent localagent in ODI.

One Logical
agent

Maps via
context(s) to
Global
context

localagent

Development
context

Production
context

Physical
agent(s)

localagent for
Global context

localagent for
Development context

Agents for the
other 2 contexts
will be created in
a later lesson.

localagent for
Production context

A common task that is performed by using ODI is to set up and install ODI Agent as a service.
After the ODI scenarios are created, they can be scheduled and orchestrated by using an ODI
Agent, which is a lightweight Java process that orchestrates the execution of ODI scenarios.
In this practice, you learn how to set up and install an ODI Agent, which will then be used in
subsequent practices for orchestration of the execution of ODI objects.

Oracle Data Integrator 11g: Integration and Administration 3 - 56

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Practice 3-1: Overview

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle University and (Oracle Corporation) use only.

Describing the Physical and Logical
Architecture

After completing this lesson, you should be able to:
• Use the Topology Navigator to create the physical and
logical architectures
• Create data servers and physical schemas
• Link the physical and logical architectures

In the previous lessons, you created Master and Work Repositories and installed an ODI
Agent as a background service. To complete setting up your ODI infrastructure, you need to
create contexts, a data server, and physical and logical schemas.
This lesson introduces you to the Topology Navigator and takes you through the process of
defining your physical architecture.
In this lesson, you gain a basic understanding of how to use the Topology Navigator to create
your physical architecture in Oracle Data Integrator (ODI). You also learn to create data
servers and define physical schemas on them, and to declare the physical agents so that ODI
can use them to execute tasks.

Oracle Data Integrator 11g: Integration and Administration 4 - 2

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Objectives

You learn about the Topology Navigator and get an overview of what is represented in the
ODI physical architecture in the next few slides.

Oracle Data Integrator 11g: Integration and Administration 4 - 3

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Topology Navigator

•

Physical architecture
– Data servers, physical schemas, and agents

•

Logical architecture
– Logical schemas and agents, contexts

•

Technology-related information
– Technologies, languages, actions

•

ODI architecture components
– Repositories and agents

Topology Navigator provides access to everything that is considered part of the ODI topology.
The three principal elements of the physical architecture are the data servers, physical
schemas, and physical agents.
The logical architecture provides the logical aliasing of physical objects—that is, logical
schemas and logical agents, as well as the contexts that link logical objects with their physical
counterparts.
Also, elements that define the technologies are available. Languages define the reserved
keywords and functions available in the Expression Editor. Actions are used to generate data
definition language (DDL) statements.
Topology Navigator also contains information specific to ODI architecture, including
information about repositories and agents.
However, this lesson deals only with physical and logical architectures, including data
servers, physical schemas and agents, logical schemas and agents, and contexts.

Oracle Data Integrator 11g: Integration and Administration 4 - 4

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Topology Navigator Contains

•

Topology Navigator stores all its information in the Master
Repository.
– In contrast, Designer Navigator stores project-related
information in Work Repositories.

•

Making changes in Topology Navigator:
– Affects all attached repositories, possibly other people’s work
– May affect the behavior of the work in progress or running
processes

All that you can create or modify in Topology Navigator is stored in the Master Repository.
This contrasts with Designer, which stores project-related information in Work Repositories.
However, be aware that any changes made in Topology Navigator immediately affect all the
Work Repositories attached to it. Such changes may also affect other people’s work.
For example, modifying the definition of a technology immediately affects any code that is
subsequently generated for that technology. Similarly, modifying the connection parameters
for a data server can prevent a scheduled scenario from connecting to that server.

Oracle Data Integrator 11g: Integration and Administration 4 - 5

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Topology Navigator: Overview

Manual
refresh
(from Master
Repository)

Tree view

Workbench

Topology
Navigator
views

This is what the Topology Navigator looks like.
A number of tree views provide access to different kinds of information. You can dock,
undock, and rearrange these views to customize your work environment. For example, the
slide shows the Physical Architecture view that lists data servers and physical schemas
sorted by technology.
The background area is referred to as the Workbench. When you double-click an element, its
window appears in this area.
At the top is the toolbar. Manual refresh refreshes the screen from the Master Repository.

Oracle Data Integrator 11g: Integration and Administration 4 - 6

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Topology Navigator: Overview

ORACLE_ORCL_LOCAL_SALES
(Logical schema)

Logical
architecture

Development

Contexts

Global

Production

(A context maps individual
logical resources onto
individual physical resources.)

Physical
architecture
Windows

SALES_DEV

Oracle

Development site: New York

Linux

SALES_DEV

Oracle

Production site: Tokyo

SALES_PROD

Oracle

Production site: Boston

A context is a set of resources that enables the operation or simulation of one or more data
processing applications. Contexts enable the same jobs (Development, Test, Production, and
so on) to be executed on different databases and/or schemas.
You will define contexts in the practice, and edit them to point logical schemas to physical
schemas.

Oracle Data Integrator 11g: Integration and Administration 4 - 7

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Review: Context Connects Logical to Physical

Data server you
create in practice
Global is a
pre-seeded
context.

Physical schemas
you create in practice
Two contexts you
create in practice

Check mark
indicates
default context.
Logical schema you
create in practice

In this lesson’s practice, you first define two new contexts, Development and Production, in
addition to the pre-seeded context, Global.
You then define a data server, ORACLE_ORCL_LOCAL. Next, you define two physical
schemas for that data server, SALES_DEV and SALES_PROD.
Then you define a logical schema, ORACLE_ORCL_LOCAL_SALES.
Next, you map your one logical schema to your two physical schemas, in terms of the three
contexts.
Finally, you check the mappings of several other predefined logical schemas against
predefined physical schemas, in terms of the three contexts.
Note: When executing any object, the Default context is the context selected by default in the
Execution dialog box. If an invalid context name is specified, the default context in Designer is
used.

Oracle Data Integrator 11g: Integration and Administration 4 - 8

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Objects You Create in the Practice

1. Go to the Topology
Contexts tab.
2. Click the New Context button.
3. Type:
– The name of the context
– The code for the context
– The password (optional)

4. If you want this context to be
the default, select the Default
check box.

To define a context, perform the following steps:
1. Go to the Contexts tab of the Topology Navigator.
2. Click the New Context button. This opens a new context window.
3. Give the context a name and a code. This is the name of the context as it appears in
ODI. The code is used to make reference to the context.
4. You can also define a password that must be entered to launch a session in this context.
To make the context the default context for all sessions launched, select the Default
check box. This determines the execution context that is set by default each time
Designer is loaded.
Note: Although you can specify a password for a context, it is not a best practice. The
addition of context passwords for security would impose upon users the need to manage
additional passwords in addition to their login password. Users should rely on standard ODI
security features for password access control. Nevertheless, special cases might warrant the
use of passwords to control context switching between, for example, Test and Development.

Oracle Data Integrator 11g: Integration and Administration 4 - 9

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Defining a Context

In the next few slides, you learn how to create the physical architecture in ODI Topology
Navigator.

Oracle Data Integrator 11g: Integration and Administration 4 - 10

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating Physical Architecture

Technology
Data server
Physical schemas

Physical agent

To reduce the number of
technologies displayed:
• Select Windows >
Hide Unused Technologies

The Physical Architecture view is where you modify the definition of data servers, physical
schemas, and physical agents.
This view is organized by technology. So, to see data servers and physical schemas based
on Oracle, expand the Oracle node.
Under this node, all data servers are displayed. There are also data types, actions, and index
types for the given technology.
Under a data server, you can access the physical schemas defined for the server. Here you
have a single data server, ORACLE_ORCL_LOCAL, with three physical schemas named
ORACLE_ORCL_LOCAL.ORDERS, ORACLE_ORCL_LOCAL.SALES_DEV, and
ORACLE_ORCL_LOCAL.SALES_PROD.
All physical agents are displayed separately. These will be covered later in the lesson.
The number of technologies available in ODI can be somewhat overwhelming. Fortunately,
you can make Topology Navigator display only technologies with at least one attached data
server. To do this, select Hide Unused Technologies from the Topology Navigator’s menu.
Remember to redisplay the technologies when you want to add a data server to a new
technology.

Oracle Data Integrator 11g: Integration and Administration 4 - 11

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Physical Architecture View

•

Drivers (JDBC or JMS)
– Drivers must be installed in the appropriate driver
subdirectory for ODI Studio, the standalone agent, or the
Java EE agent.
– This installation must be performed on all the machines
connecting to the data server.
—
—

•

Machines running an Oracle Data Integrator GUI
Machines running an Oracle Data Integrator Agent

Connection settings (server-dependent)
– Machine name (IP address), port
– User/password
– Instance/database name, if the server is a database server

ODI connects to a data server by using standard Java connectivity methods. These methods
usually require server-specific drivers.
For example, to connect to a database server supporting Java Database Connectivity (JDBC),
ODI needs the database’s JDBC driver. Similarly, to connect to a message-oriented
middleware (MOM) router supporting the Java Message Service (JMS), it needs the MOM’s
JMS client.
These drivers must be installed in the driver’s subdirectory. A driver is either a .jar or a
.zip file. Driver installation should be performed for every machine that may connect to the
data server with an ODI graphical user interface or an ODI Agent.
You also need to specify appropriate settings to connect to the data server. The settings vary
depending on the type of the data server.
Typically, you need to specify an IP address or a machine name and a port to connect to the
appropriate service on the machine. You will also need a username and password with
sufficient privileges on the server.
Lastly, you may need server-specific information, such as the database and/or instance name
for a database server.

Oracle Data Integrator 11g: Integration and Administration 4 - 12

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Prerequisites for Connecting to a Server

•

The username that you specify for a data server is used to
access all underlying schemas, databases, or libraries in
the data server.
Ensure that this user account has sufficient privileges.
– For database:
—
—
—
—

SELECT
INSERT/UPDATE
CREATE/DROP
READ

Additional Note: It is possible to define for a data server
commands that will be automatically executed when
connections to this data server are created or closed by
ODI components or by a session.

Note that the username that you supply for a data server is used for many purposes by ODI.
This includes accessing and altering data or data structure in schemas, databases, or libraries
in the data server, depending on the technology. You, therefore, must ensure that the user
account has sufficient privileges to be able to do this everywhere.
In the case of a database, the user should have SELECT privileges to all the schemas defined
in the data server. The user should also have INSERT/UPDATE privileges to the tables into
which ODI will integrate data. The user must have sufficient privileges to CREATE/DROP
objects into temporary, or work, schemas, and should be able to READ the structure of the
tables to reverse-engineer them.
Note: As a best practice, generally use the same owner as the schema that owns the Work
schema.

Oracle Data Integrator 11g: Integration and Administration 4 - 13

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Important Note

By default, ODI connects
by JDBC, unless you
select JNDI to use a
directory to access the
server.

To create a data server connection in ODI, perform the following steps:
1. In Topology Navigator, expand the Technologies node in the Physical Architecture
panel.
2. Select the technology to which you want to attach your data server.
3. Right-click and select New Data Server.
4. Give a name for the data server. Prefix the name of the server with the name of the
technology, for example, ORACLE_DW.
5. Type the specific connection settings. Enter the username and password for a
sufficiently privileged user. It is a good idea to create a user especially for ODI.
6. Optionally, you select the JNDI Connection check box to use a directory to gain access
to the server. By default, ODI assumes that you want to connect directly to your data
server by using JDBC. This happens if you do not select the JNDI Connection check
box.
Note: The Data Server field is a technology-specific value that identifies the server. In many
cases, this represents the server instance, as understood by the database engine.

Oracle Data Integrator 11g: Integration and Administration 4 - 14

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Data Server

2
4

Test the
connection.

Select JDBC URL.
Select JDBC driver.

Click the
JDBC tab.

To complete the connection settings, click the JDBC tab.
You must specify the JDBC driver with which to access the defined technology. However, this
must be correctly installed. You can use the browse button to select a driver from the list.
You then need to provide a URL that defines the location of the server. You can use the
“select” button to choose an available URL format. Then, set the URL parameters with your
server’s connection parameters.
Test the connection by clicking the Test Connection button. This operation will be covered in
greater detail later in the lesson.

Oracle Data Integrator 11g: Integration and Administration 4 - 15

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Data Server: JDBC

•

A JDBC driver is a Java driver that provides access to a type of
database.
–
–
–
–

•

Drivers are identified by a Java class name.
–

•

Type 4: Direct access through TCP/IP (the generally preferred type)
Type 3: Specific to three-tier architectures
Type 2: Requires the database client layer
Type 1: Generic driver to connect ODBC data sources
Class must be present on the classpath.

Drivers are distributed as .jar or .zip files.
–

Copy the driver file to the appropriate driver subdirectory.

The JDBC driver is always a Java class. It provides access to a particular type of database.
Four categories of databases require different levels of network infrastructure.
Type 4 drivers are generally preferred. They connect directly to the target machine over
TCP/IP. They do not require any other component.
Type 3 drivers are specific to three-tier architectures. These drivers connect to an
intermediate server that is used as a gateway to the target machine.
Type 2 drivers require the database-specific client layer to be present and correctly configured
on the client machine. This can present difficulties when deploying scenarios onto distant
servers, as each machine must be configured in exactly the same way.
Type 1 is a generic driver to connect to Open Database Connectivity (ODBC) data sources. It
has the same problems as type 2 drivers, but can be even slower. This is why type 4 drivers
are the best if they are available for the given technology.
When you specify the driver, you are actually specifying the name of a Java class. This class
must be present in the Java classpath of ODI. The easiest way to ensure that it is on the
classpath is to copy the driver’s file—usually a .jar or .zip file—in the appropriate driver’s
subdirectory of the ODI installation directory.

Oracle Data Integrator 11g: Integration and Administration 4 - 16

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

JDBC Driver

•

The JDBC driver uses a URL to connect to a database
system.
– The URL describes how to connect to the database system.
– The URL may also contain driver-specific parameters.

•

Use the “select” button to choose the driver class name
and the URL template.

As mentioned earlier, the JDBC driver needs a special JDBC URL to connect to a particular
database. This URL contains the information necessary to connect to this particular database
system. For example, it often contains the IP address of the database server.
However, it can also contain other parameters defined specifically by the chosen JDBC driver.
For example, the ODI file driver contains a parameter to specify the character encoding to
use.
The “select” buttons next to the relevant fields on the JDBC tab enable you to fill in these
fields easily. Here, the Sun JDBC-ODBC bridge has been selected, which is a standard driver
built in Java. You can use it to connect to any ODBC data source. Note that when you select
a URL template, a field is displayed for you to enter the name of the ODBC Data Source
Name (DSN) alias.
Select the Oracle driver, and then click the button next to the JDBC Url field to select Oracle
JDBC Url, as shown in the screenshot in the slide. Fill in the appropriate values for ,
, and to edit the URL.

Oracle Data Integrator 11g: Integration and Administration 4 - 17

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

JDBC URL

1. Click the JNDI tab, after selecting JNDI on the Definition
tab.
2. Set the JNDI parameters.
–
–
–
–
–
–

Authentication
User/password
Protocol
Driver
URL
Resource

3. Test the
connection.

If you selected the option to connect to your data server with JNDI by using a directory, the
JDBC tab is replaced by the JNDI tab. Click this tab to begin defining the parameters. The
meaning of these parameters depends on the type of directory you want to use and the JNDI
driver. The parameters include the JNDI Authentication mode, a user and password, and the
JNDI protocol.
You must also specify the JNDI driver. The JNDI URL is specified in the same way as the
JDBC URL, and depends on the type of directory connected. The JNDI Resource name is the
directory resource corresponding to your data server. For database connections, it should
correspond to a JDBC data source. For JDBC connections, you must test your connection.
Click OK to save your settings.
Recall that when connecting a data server through a JNDI directory, you need both the JNDI
directory driver and the data server driver (for example, a JDBC driver). Both these drivers
should be installed in the driver’s subdirectory.

Oracle Data Integrator 11g: Integration and Administration 4 - 18

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Data Server: JNDI

1. Click Test Connection.
2. Select the agent to test this
connection.
– Local (No Agent) performs
the test with the Topology
Navigator GUI.

3. Click Test.
4. This validates driver, URL, and network connectivity.

After you have chosen a driver and set up a URL, you must test the connection. It is quicker to
test the connection now than to find later that your connection parameters are wrong. To test,
perform the following:
1. Click Test Connection in the data server window.
2. Be sure to test every data server connection for every agent you have defined. If you are
connecting to a production data server, you should test to ensure that the agent that will
be used in production can connect to it.
3. You can select Local (No Agent) to perform the test from your workstation. This setting
uses the agent that is built into ODI, which is always available.
4. Click the Test button to launch the test. This validates the driver, the URL, and the
network connectivity to the server. Generally, if the test is successful, a result is returned
instantly. Note the second information window informing you to register at least one
physical schema for your data server.
You can repeat the test with different agents.
Note: You must always test the connection to check that the data server is correctly
configured.

Oracle Data Integrator 11g: Integration and Administration 4 - 19

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Testing a Data Server Connection

1. Right-click the data
server and select New
Physical Schema.
2. Select or fill in:
– Data Schema
– Work Schema

3. Select whether this is
the Default schema.

Now that you have created a data server, the next step is to define the physical schemas in
ODI. To do this, right-click the data server and select New Physical Schema. If you have just
created a data server, this window opens automatically. The layout of the window depends on
the technology. Certain technologies qualify each data server schema by using a catalog
name and a schema name. Others use only one of these qualifiers, and some do not support
multiple schemas at all.
In this window, you choose the data server schemas that you want to use as the data schema
and work schema. To choose a data server schema, specify the catalog name, the schema
name, both, or none. Remember that the data schema is where your data is stored and the
work schema is a different schema where ODI stores temporary objects.
If you want to use the default schema for the data server, select the Default check box. This
means that ODI will store temporary objects that belong to the entire data server in this work
schema.
Note: The recommended practice in ODI is that you create a separate area on each data
server specifically for ODI temporary objects—the dedicated area. You can then use this
dedicated area as the work schema so that you have no risk of ODI temporary objects
polluting your production data.

Oracle Data Integrator 11g: Integration and Administration 4 - 20

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Physical Schema

You have defined a representation of your system’s actual physical architecture.
Now, you define a higher-level logical representation of that architecture. You can then map
the logical objects to specific physical versions of those objects, by way of the contexts that
you defined.

Oracle Data Integrator 11g: Integration and Administration 4 - 21

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating Logical Architecture

Technology

Contexts

Logical schemas

Logical agent

•

The same technologies are displayed in
Physical and Logical Architecture views.
To reduce the number of
technologies displayed,
select Hide Unused
Technologies.

The Logical Architecture view is where you modify the definition of the logical schemas and
logical agents. The Logical Architecture is organized by technology. So, to see logical
schemas based on Oracle, expand the Oracle node.
Under this node, all logical schemas are displayed. Here, you see two logical schemas,
ORACLE_ORCL_LOCAL_SALES and ORACLE_ORCL_LOCAL_ORDERS, which are based on
Oracle technology. You will define these two schemas in the practice.
The logical agent displayed will be covered at the end of this lesson.
You can see the contexts that link the logical and physical architectures in the Contexts view.
In both the Logical Architecture and Physical Architecture views, the same list of technologies
is presented. Thus, to modify the definition of a technology, you can use either view.
The number of technologies available in ODI can be somewhat overwhelming. Fortunately,
you can make Topology Navigator display only those technologies on which at least one
logical schema is defined. To do this, select Hide Unused Technologies from the Topology
Navigator drop-down menu. Do not forget to redisplay the technologies when you want to
define a logical schema on a new technology.

Oracle Data Integrator 11g: Integration and Administration 4 - 22

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Logical Architecture and Context Views

1. Go to the Logical
Architecture view.
2. Right-click the schema
technology.
3. Select New Logical Schema.
4. Type the schema name.
5. You can associate the
schema with the physical
schemas, in different
contexts, here.

To create a logical schema, perform the following steps:
1. Go to the Logical Architecture view in Topology Navigator.
2. Find the technology appropriate for your logical schema and right-click its node.
3. From the context menu, click New Logical Schema.
4. In the window that opens, provide a name for the logical schema. The best practice is to
include the name of the technology in the logical schema name.
5. To map this logical schema to physical schemas in different contexts, select the
appropriate physical schema for each context.
Note: You can associate the logical schema with any physical schemas defined for this
technology directly on the Definition tab. However, it does not have to be associated with a
physical schema in every context.

Oracle Data Integrator 11g: Integration and Administration 4 - 23

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Logical Schema

1. Go to the Logical
Architecture view.
2. Right-click the Agents
node.
3. Select New Logical Agent.
4. Type the agent name.
5. You can associate the
logical agent with the
physical agents here.

The procedure to create a logical agent is similar to creating a logical schema. Perform the
following:
1. Go to the Logical Architecture view.
2. Right-click the Agents node.
3. Select New Logical Agent from the context menu.
4. Provide a name for the logical agent.
5. As with logical schemas, you can define the associated physical agents here.
Note: The physical agents should be defined before logical agents. You learned how to
create physical agents in the lesson titled “Administering ODI Repositories.”

Oracle Data Integrator 11g: Integration and Administration 4 - 24

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a Logical Agent

1. Double-click the context.
2. Click the Agents tab.
3. For each logical agent,
select the corresponding
physical agent in the context.
4. Click the
Schemas tab.
5. For each logical
schema, select
a corresponding
physical schema
in the context.
Copyright © 2012, Oracle and/or its affiliates. All rights reserved.

As you saw earlier, you can associate logical schemas and agents with physical objects in
their respective windows. However, it is easier to set up an entire context by editing the
context itself.
• To do this, double-click the context in the Contexts view.
• Click the Agents tab to assign agents.
Now, the list of logical agents is displayed. For each logical agent, you can assign a physical
agent from the drop-down list. Or, you can set the physical agent to to make it
unavailable in this context.
Similarly, click the Schemas tab to associate the logical schemas with the physical schemas.
Note: You do not have to associate every logical schema with a physical schema, or every
logical agent with a physical agent, in every context. However, such an unresolved agent or
schema cannot be used in the given context.

Oracle Data Integrator 11g: Integration and Administration 4 - 25

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Editing a Context to Link the Logical and
Physical Agents

Which of the following privileges must the user have to
integrate data into the database tables and reverse-engineer
them?
a. SELECT, INSERT, and UPDATE
b. READ, INSERT, and UPDATE
c. SELECT, INSERT, UPDATE, CREATE, and DROP
d. SELECT, INSERT, UPDATE, CREATE, DROP, and READ

Answer: d
Explanation: In the case of a database, the user should have SELECT privileges to all the
schemas defined in the data server. The user must also have INSERT/UPDATE privileges to
the tables into which ODI will integrate data. The user must have sufficient privileges to
CREATE/DROP objects into temporary, or work, schemas, and should be able to READ the
structure of the tables to reverse-engineer them.
Note: This is the reason why it is a best practice to log in with the owner of the work schema.

Oracle Data Integrator 11g: Integration and Administration 4 - 26

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

On the Definition tab, you must associate the logical schema
with the physical schemas defined for this technology in every
context.
a. True
b. False

Answer: b
Explanation: You do not have to associate every logical schema with a physical schema, in
every context. However, such unresolved schema cannot be used in the given context.

Oracle Data Integrator 11g: Integration and Administration 4 - 27

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Quiz

In this lesson, you should have learned how to:
• Use Topology Navigator to create the physical and logical
architectures
• Create data servers and physical schemas
• Link the physical and logical architectures

Oracle Data Integrator 11g: Integration and Administration 4 - 28

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Summary

1-1:
2-1:
3-1:
4-1:
5-1:
6-1:
7-1:
8-1:
9-1:
9-2:
10-1:
11-1:
11-2:
11-3:
12-1:
12-2:
13-1:
14-1:
14-2:
15-1:
16-1:
17-1:
18-1:
19-1:
20-1:
20-2:
21-1:

Oracle Data Integrator 11g: Integration and Administration 4 - 29

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Checklist of Practice Activities

1.
2.
3.
4.
5.
6.

Define Production context.
Define Development context (a third context, Global, is preseeded).
Define ORACLE_ORCL_LOCAL data server.
Define ODI physical schemas for data server: SALES_DEV,
SALES_PROD.
Define ORACLE_ORCL_LOCAL_SALES ODI logical schema.
Map the logical schema to the two physical schemas, in terms of the
three contexts.
Data server you
create in the practice

Global is a
preseeded
context.
Check mark
indicates
default context.

Physical schemas you
create in the practice
Two contexts you
create in the practice

Logical schema you
create in the practice

In previous practices, you created Master and Work Repositories and installed an ODI Agent
as a background service.
Before you begin working on your ODI projects, you need to describe your ODI infrastructure
in the topology. You need to define contexts, a data server, physical and logical schemas, and
mappings of your logical schema to the physical schemas in terms of contexts.

Oracle Data Integrator 11g: Integration and Administration 4 - 30

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Practice 4-1: Overview

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle University and (Oracle Corporation) use only.

Setting Up a New ODI Project

After completing this lesson,
you should be able to:
• Set up a new project
• Use folders to organize
your work
• Import the right knowledge
modules
• Import and export objects
• Use markers to manage
the project

This lesson explains the basic organization of projects by using markers and folders. In this
lesson, you should learn how to:
• Set up a data integration project in Oracle Data Integrator (ODI)
• Use folders to organize your work into subprojects
• Import knowledge modules and know which ones to import
• Use markers to help organize your project by adding project-specific information to your
objects
• Import and export objects to share your work

Oracle Data Integrator 11g: Integration and Administration 5 - 2

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Objectives

Overview and Setup

This section provides an overview of what projects do and a description of how to set one up.

Oracle Data Integrator 11g: Integration and Administration 5 - 3

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

ODI Projects

A project is a collection of ODI objects created by users for a
particular functional domain.

Oracle Data Integrator 11g: Integration and Administration 5 - 4

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Project?

What can a project contain?
• Folders:
– Packages
– Interfaces
– Procedures

•
•
•

Variables, sequences,
user functions
Knowledge modules
Markers

You know that projects are collections of ODI objects. However, only certain objects can
belong to projects. Similarly, certain objects always belong to projects.
• Packages, procedures, and interfaces always belong to folders, and folders always
belong to projects.
• Variables, sequences, and user functions either belong to projects or can be created
with global scope.
• Knowledge modules and markers always belong to a project.
In this lesson, you learn how to use folders and markers to organize projects. You also learn
to import the correct knowledge modules, which is an important step in project administration.

Oracle Data Integrator 11g: Integration and Administration 5 - 5

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Oracle Data Integrator Projects: Overview

•
•

An ODI project should represent a functional domain or an
integration project.
Projects have their own objects (knowledge modules,
variables, markers, and so on).
– Objects in the projects can be shared by duplication.
– You can also use global variables, sequences, knowledge
modules, and so on.

•

Examples of projects:
– Chaining ACCOUNTING and INVOICING databases
– Loading the data warehouse
– Generating and sharing sales results

You can use projects in your work in different ways. But the best way is to use a project to
represent a functional domain in your integration process or an integration project that you
need to develop with ODI.
Each project contains its own knowledge modules, variables, markers, and other types of
objects. These objects can be shared between different projects by duplicating them.
You can also use global objects such as variables, sequences, or knowledge modules to
define parameters that are common to all projects.
Examples of suitable projects would be chaining the accounting and invoicing databases,
populating the data warehouse, or generating and sharing sales results.

Oracle Data Integrator 11g: Integration and Administration 5 - 6

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

How to Use ODI Projects in Your Work

1. Select New Project from
the drop-down menu.
2. Select a name.
3. Optionally, change the
code.
– Use the code to prefix
project variables.
– Keep it short.

4. Default markers are
added automatically.
5. One folder is created to hold interfaces, packages, and
procedures.

To create a new project, perform the following:
1. In the Designer Navigator, select New Project from the drop-down menu at the top of
Projects view.
2. Name the project. You should generally make it reflect the functional domain that it
covers.
3. A code is generated but can be changed to anything you like. You may want to shorten
the code to something more manageable. The code is used as a prefix when referring to
variables created within this project.
4. Default marker sets are automatically added to your blank project.
5. Similarly, a folder is created automatically to hold the interfaces, packages, and
procedures that you develop.

Oracle Data Integrator 11g: Integration and Administration 5 - 7

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a New Project

The following slides describe the folders that group packages, interfaces, and procedures
within projects.

Oracle Data Integrator 11g: Integration and Administration 5 - 8

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Using Folders

•
•

A folder is a hierarchical grouping beneath a project and
can contain other folders and objects.
Every package, interface, or procedure must belong to a
folder.

•

When should you create folders? One guideline is to create a
folder per "package" or scenario.
– This way, all interfaces that are used in the same
package are grouped together.
– The folder represents all that is necessary for a given
execution unit.
– As a result, maintenance is typically simplified.

Oracle Data Integrator 11g: Integration and Administration 5 - 9

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Folder?

1. Right-click a project
or folder.
2. Select New Folder.
3. Name the folder.

You can drag folders to other
folders.

You can create folders within projects or within other folders as subfolders. To create a new
folder, perform the following:
1. Right-click the project or folder where you want to create the new folder.
2. Select Insert Folder from the context menu.
3. Give a name to the folder.
After the folder is created, you can drag it to other folders, or onto the parent project, to
reorganize the structure of your project.

Oracle Data Integrator 11g: Integration and Administration 5 - 10

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Creating a New Folder

•

When do you need a new project?
– When you start working on a new integration project or
functional domain
– When you want to keep objects separated

•

When do you need a new folder?
– For organizational purposes within an existing project
– To define different user privileges within the project

•

Project/folder boundaries:
– Objects cannot be shared between projects.
—

Global variables, sequences, knowledge modules, and user
functions can be used by any project.

– Objects within a project can be used in all folders.

It is not always easy to know when to create a project or when to create a subfolder within the
same project. As a general rule, if you have a new functional domain or are starting an
integration project, you should create a new project. Also, if you specifically want to keep
objects separate from each other, you should create them in separate projects.
On the other hand, a folder is useful when you want to organize an existing project. When you
have a large number of interfaces, procedures, or packages in a project, you should consider
grouping them into folders. You can also use folders to set up different security levels within
the same project. Each folder can have its own unique privileges.
However, you should be aware that projects do impose strict boundaries on information
sharing. This means that objects created in one project cannot be used by another project. If
you want to enable the objects to be used by other projects, you should make the object
global. Thus, you enable the global variables, sequences, knowledge modules, and user
functions to be used by any project.
Folders, however, do not impose boundaries. Thus, objects in one folder can be used by any
other object in the project.

Oracle Data Integrator 11g: Integration and Administration 5 - 11

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Organizing Projects and Folders

Probably the most important thing when you create a project is importing the correct
knowledge modules. These modules enable your integration interfaces to work on the
technologies used by your data servers. Without importing any knowledge modules, no
interfaces can be made or executed.

Oracle Data Integrator 11g: Integration and Administration 5 - 12

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Understanding Knowledge Modules

•

•
•

A knowledge module is a code template containing the
sequence of commands necessary to implement a data
integration task.
There are different predefined knowledge modules for
loading, integration, checking, reverse-engineering,
journalizing, and deploying data services.
All knowledge modules work by generating code to be
executed at run time.
KMs can be specified as “Global,” allowing them to be
shared across multiple projects.

Technically, this term describes a template containing the code necessary to implement a
particular data integration task. These tasks include loading data, checking it for errors, or
setting up triggers necessary to implement journalization. However, all knowledge modules
basically work the same way: ODI uses them to generate code, which is then executed by a
technology at run time.
ODI 11.1.1.6 introduces Global Knowledge Modules (KMs) allowing specific KMs to be
shared across multiple projects. In previous versions of ODI, Knowledge Modules were
always specific to a Project and could only be used within the project into which they were
imported. Global KMs are listed in the Designer Navigator in the Global Objects accordion.
Note: Knowledge modules are independent of the structure of the source and target
datastores. The same KM can be used, no matter which source table you have, or how many
source tables you have. Likewise, all target tables can use the same knowledge module.

Oracle Data Integrator 11g: Integration and Administration 5 - 13

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

What Is a Knowledge Module?

KM Type

Description

LKM

Assembles data from source datastores to the Staging
Area

IKM

Integration

Uses a given strategy to populate the target datastore from
the Staging Area

CKM Check

Checks data in a datastore for errors statically or during an
integration process

RKM ReverseRetrieves the structure of a data model from a database. It
engineering is needed only for customized reverse-engineering.
JKM

Journalizing Sets up a system for Changed Data Capture to reduce the
amount of data that needs to be processed

SKM

Data
Services

Deploys data services that provide access to data in
datastores

The first group of knowledge modules is critical for doing any work with interfaces.
• Loading Knowledge Modules (LKMs) extract data from the source of interfaces. So, if
your data is stored in flat files, you will need the “File to SQL” LKM.
• Integration Knowledge Modules (IKMs) implement a particular strategy for loading the
target of an interface. Thus, to load an Oracle table while taking into account the slowly
changing dimensions properties, a particular IKM can be used.
• Check Knowledge Modules (CKMs) are selected in Interfaces and constraints can be
individually enforced by ODI at the interface level.
The second group is used for setting up, checking, and configuring models.
• Check Knowledge Modules (CKMs) enforce constraints defined on the target data store.
CKMs are used by models to perform static checks outside of interfaces.
• Reverse-Engineering Knowledge Modules (RKMs) are needed only to perform
customized reverse-engineering. They are used to recover the structure of a data model
and are used when standard reverse- engineering cannot be performed.
• Journalizing Knowledge Modules (JKMs) are used to set up Changed Data Capture.
This makes interfaces react only to changes in data and can vastly reduce the amount
of data that needs to be transferred.
• Service Knowledge Module (SKM) is the code template for generating data services.

Oracle Data Integrator 11g: Integration and Administration 5 - 14

Oracle University and (Oracle Corporation) use only.

Interfaces

There are six types of knowledge modules you may import.

Models

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Types of Knowledge Modules

•
•

All knowledge modules to be used in a project must be
imported into the project.
Which knowledge modules would you need?
– Start with basic SQL knowledge modules.
– Add technology-specific knowledge modules as needed.
– For your projects, you will need LKMs, IKMs, and CKMs
only.

•

You need to become familiar with the library of available
knowledge modules, and what they can do for you.
– Examine the ODI Knowledge Modules Reference Guide.

You now know what the various types of knowledge modules are. But, how do you know
which ones need to be imported into your specific project?
The most important thing to remember is to import all knowledge modules that may be used in
a project. Each time you create a project, you must import the knowledge modules that will be
used by that project.
The basic strategy for importing knowledge modules is as follows:
• First, import the most basic SQL knowledge modules. These work on almost every
database management system (DBMS) with acceptable performance.
• Then, consider importing more specific knowledge modules for the particular
technologies involved in your project. If you are transferring data to an Oracle server,
consider adding the Oracle-based IKMs.
• Technology-specific knowledge modules can take advantage of certain characteristics
or the special tools provided by the technologies. However, it is often best to begin with
the most generic knowledge modules to start your interface, then choose specific
knowledge modules later to increase performance.

Oracle Data Integrator 11g: Integration and Administration 5 - 15

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Which Knowledge Modules Are Needed?

•

LKM File to Oracle (SQLLDR)
– Uses Jython to run SQL*LOADER through the OS
– Much faster than basic LKM File to SQL

•

LKM Oracle to Oracle (DBLINK)
– Loads from Oracle data server to Oracle data server
– Uses Oracle DBLink

•

IKM Sybase ASE Slowly Changing Dimension
– Uses SCD flags on columns
– Creates historical rows when needed

•

CKM Oracle
– Enforces logical constraints during data load
– Automatically captures error records

To make the descriptions of knowledge modules more concrete, here are some examples of
the knowledge modules that are shipped with ODI.
The first module is an LKM that is used to load an Oracle server from a flat file server.
According to the naming convention, the SQLLDR in parentheses must correspond to the
method by which data is loaded.
In this case, the Oracle bulk-loading tool, SQL*LOADER, is the method. This tool is capable
of loading vast amounts of data directly into Oracle. Using this knowledge module is much
faster than the generic File to SQL LKM.
The next module is another LKM, this time loading from an Oracle data server to another
Oracle data server by using Oracle DBLink.
You have an integration knowledge module for populating Sybase ASE servers with the
Slowly Changing Dimension strategy. You would choose this IKM when you need SCD flags
on model columns to be taken into account.
CKM Oracle enforces logical constraints during data load and automatically captures error
records.

Oracle Data Integrator 11g: Integration and Administration 5 - 16

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Knowledge Modules: Examples

1. Right-click the project.
2. Select Import > Import
Knowledge Modules.
3. Choose the
import directory.
–

ODI KMs are found
in the /xml-reference
subdirectory.

4. Select one or more
knowledge modules.
–

Use the Ctrl/Shift keys for
multiple selection.

To import one or more knowledge modules into your project, perform the following steps:
1. Right-click the name of the project in the Projects view.
2. Select Import > Import Knowledge Modules.
3. Select the import directory. Each knowledge module is stored in a separate Extensible
Markup Language (XML) file. The knowledge modules that come with ODI are stored in
the /xml-reference subdirectory.
4. The list of available knowledge modules is displayed. Use the Ctrl/Shift keys for multiple
selection of knowledge modules.

Oracle Data Integrator 11g: Integration and Administration 5 - 17

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Importing Knowledge Modules

•

“Import replace” mode
– Updates all interfaces that use the KM
– Preserves existing options
– Does not update generated scenarios

•

Why replace an existing KM?
– Because a newer version is released
– To undo any undesirable changes made

Normally, if you import a knowledge module with the same name as an existing knowledge
module, you end up with two copies in your project. This can be useful if you want to make
changes to the knowledge module without breaking your existing interfaces.
However, another mode of importing is available: the “import replace” mode. In this mode, the
existing interface is replaced with the version imported from the disk. All interfaces that used
the old knowledge module are automatically updated to use the new version. Also, values that
are set for the knowledge module options in these interfaces are transferred to the new
version. However, any existing scenarios are not regenerated. If you want to incorporate
changes to the knowledge module into your scenarios, you must regenerate them.
There are various reasons why you would want to replace a knowledge module. The most
common reason is that a newer version of the knowledge module is released, perhaps by
someone on your team. You import the knowledge module again and all your existing
interfaces will still work.
Similarly, you may have made some undesirable changes to your knowledge module. You
can quickly undo these changes by reimporting from a saved version.
Note: Any interface that uses the replaced KM also will be impacted.

Oracle Data Integrator 11g: Integration and Administration 5 - 18

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Replacing Existing KMs

1. Right-click the KM.
2. Select Import Replace.
3. Select the replacement file.

Browse for the file.

To import a knowledge module in replacement mode, perform the following:
1. Right-click the name of the knowledge module that is to be replaced.
2. Select Import Replace from the context menu.
3. Enter the name of the XML file that contains the new version of the knowledge module.
You can use the browse button to select a file. Note that you can replace only one
knowledge module at a time.
4. Consider regenerating any scenarios that use the knowledge module to take the
changes into account. This includes both the interfaces and packages. Regenerating
scenarios is discussed later in the course.
Note: You should regenerate any scenarios that use the KM.

Oracle Data Integrator 11g: Integration and Administration 5 - 19

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Replacing Existing KMs

• Create KMs
• Customize KMs

Use the Knowledge Module Editor to create and customize your knowledge modules. In this
example, the third line is highlighted, “Create work table.” If you double-click this line, a
detailed editor opens, as shown on the next slide.

Oracle Data Integrator 11g: Integration and Administration 5 - 20

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Knowledge Module Editor

General
editing

Options
editing

The KM editor has a section to edit general information about an item within the KM, such as
this “Create work table” item in the “LKM SQL to Oracle” knowledge module.
The KM editor also has a section to edit the KM’s options.
Knowledge modules are used later in this course. For additional detailed information about
KMs, see the ODI Knowledge Modules Reference Guide.

Oracle Data Integrator 11g: Integration and Administration 5 - 21

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Editing a Knowledge Module

The last part of organizing a project is knowing how to import and export objects to share
them with other projects.

Oracle Data Integrator 11g: Integration and Administration 5 - 22

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Exporting and Importing Objects

•

Needs:
– Moving objects between projects
– Wanting to send an interface to an off-site colleague
– Sharing ODI objects between repositories
—

•

For example, migrating objects from a development
repository to a maintenance repository

Solution:
– Export to an XML file.
– Import the file into the other project.

The following slides discuss the solution to several problems that can arise.
You cannot drag-and-drop objects between projects. For example, you may want to copy an
interface from one project to another. Similarly, you may want to send an object you have
developed to a colleague at a different site. Or, you may want to share an object between two
repositories—for example, moving an object that was in development to a maintenance
repository.
The solution to these three problems is the same: exporting, and then reimporting.
• First, you export the object to an XML file.
• Then, you reimport the object into the other project. This other project can be in a
different repository, or even at a different site.
You will see how this is done in the next few slides.

Oracle Data Integrator 11g: Integration and Administration 5 - 23

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Exporting and Importing

1. Right-click the object.
2. Select Export.
3. Specify:
– The export
directory
– The export file name
(without .xml)
– Whether to export child objects

4. To export a Work
Repository, use the
menu system.

To export an object, perform the following:
1. Right-click the object. In this example, you export an interface. However, you can export
any object.
2. Select Export from the context menu. The Export window appears.
3. Specify the directory where you want to export the object. You can use the browse
button to select the directory by navigating to it.
4. You must also specify the file name to export to. The .xml extension will automatically
be added to it.
5. Some objects, such as folders, projects, and packages, can contain child objects. If you
do not want these objects in the .xml file, deselect the “Child components export” check
box.
Though almost every object in ODI can be exported by using this method, there are
exceptions. To export a Work Repository, use the relevant option in the File menu in Designer
Navigator.

Oracle Data Integrator 11g: Integration and Administration 5 - 24

Oracle University and (Oracle Corporation) use only.

These eKit materials are to be used ONLY by you for the express purpose SELF STUDY. SHARING THE FILE IS STRICTLY PROHIBITED.

Exporting an Object

1. Right-click the project
or folder.
2. Select Import > Import

Oracle Data Integrator Integation And Administration Student Guide

Navigation menu

Versions of this User Manual:

Views

Navigation