Oracle Data Integrator 12c: Integration and Administration Ed 3

Introduction to Integration and Administration

• Course Objectives
• Lesson Objectives
• Course Agenda
• Lesson Agenda
• Why Oracle Data Integrator?
• Conventional Integration Process: ETL
• Extract Load Transform (E-LT)
• ODI Architecture and Components
• ODI Architecture
• ODI Components: Overview
• Using ODI Studio
• Designer Navigator (Work Repository)
• Operator Navigator (Work Repository)
• Topology Navigator (Master Repository)
• Security Navigator (Master Repository)
• What Is an Agent?
• ODI Agents
• Three Types of Agents: Java EE, Standalone, Collocated Standalone
• Using the Three Types of Agents
• Standalone Agent: Example
• ODI Console
• Enterprise Manager FMW Console
• Management Pack for ODI for Enterprise Manager Cloud Control
• Management Pack for ODI for EM CC ODI: Home Page
• Agenda
• ODI Repositories
• Master and Work Repositories
• Repository Setup: Example
• Repository Setup: Multiple Master Repositories
• Components: Global View
• Possible ODI Methodology
• Checklist of Practice Activities
• Starting Oracle Data Integrator
• Using Online Help
• Quiz
• Summary
• Practice 1-1 Overview: Logging In and Using Help

Administering ODI Repositories

• Objectives
• Agenda
• Initial Repository Administration Tasks
• Steps to Set Up the ODI Repositories
• 1. Run Repository Creation Utility
• 1a. Create Schemas
• 1b. Create Passwords and Tablespaces
• 2. Connect to the Master/Work Repository 3. Create a Wallet
• Connecting to the Master/Work Repository
• Exporting the Master Repository
• Importing the Master Repository
• Creating a Work Repository – 1
• Creating a Work Repository – 2
• Changing the Work Repository Password
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 2-1 Overview: Creating and Connecting to ODI Master and Work
• Repositories

ODI Topology Concepts

• Objectives
• Agenda
• What Is Topology?
• What Is in the Topology?
• Agenda
• What Is a Data Server?
• Data Servers: Examples
• Important Guideline 1
• What Is a Physical Schema?
• Physical Schemas: Properties
• Technology Terminology Among Vendors
• Important Guideline 2
• Agenda
• Infrastructure for Two Production Sites: Example
• ODI Design: Physical Architecture of the Two Production Sites
• Logical Schemas and Contexts
• What Is a Logical Schema?
• Important Guideline 3
• Logical Versus Physical Architecture
• Design Time Versus Run Time
• What Is a Context?
• A Context Maps a Logical to a Physical Schema
• Important Guideline 4: Defining Contexts
• Mapping Logical and Physical Resources
• Agenda
• ODI Physical Agents
• Creating a Physical Agent
• ODI Agent Parameters
• Launching a Standalone Agent: Examples
• Stopping the ODI Agent
• Deploying and Configuring a Java EE Agent
• Load Balancing: Example
• Important Guideline 5
• Infrastructure with Agents: Example
• Defining Agents: Example
• Special Case: Fragmentation Problem
• Special Case: Important Guideline 6
• Special Case: Defining the Physical Architecture
• Special Case: Infrastructure
• Special Case: Physical Architecture in ODI
• Agenda
• Planning the Topology
• Matrix of Logical and Physical Mappings
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 3-1 Overview: Configuring a Standalone Agent by Using the Common
• Administration Model

Physical and Logical Architecture

• Objectives
• Agenda
• What Topology Navigator Contains
• Topology Navigator: Overview
• Review: Context Connects Logical to Physical
• Objects You Create in the Practice
• Defining a Context
• Agenda
• Physical Architecture View
• Prerequisites for Connecting to a Server
• Important Note
• Creating a Data Server
• Creating a Data Server: JDBC
• JDBC Driver
• JDBC URL
• Creating a Data Server: JNDI
• Testing a Data Server Connection
• Creating a Physical Schema
• Agenda
• Logical Architecture and Context Views
• Creating a Logical Schema
• Creating a Logical Agent
• Editing a Context to Link Logical and Physical Agents
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 4-1 Overview: Working with Topology

Setting Up a New ODI Project

• Objectives
• Agenda
• What Is a Project?
• Oracle Data Integrator Projects: Overview
• How to Use ODI Projects in Your Work
• Creating a New Project
• Agenda
• What Is a Folder?
• Creating a New Folder
• Organizing Projects and Folders
• Agenda
• What Is a Knowledge Module?
• Types of Knowledge Modules
• Which Knowledge Modules Are Needed?
• Knowledge Modules: Examples
• Importing Knowledge Modules
• Replacing Existing KMs
• Knowledge Module Editor
• Editing a Knowledge Module
• Agenda
• Exporting and Importing
• Exporting an Object
• Importing an Object
• ID Numbers: Overview
• Import Types
• Choosing an Import Mode
• Import Report
• Agenda
• What Is a Marker?
• Tagging Objects with Markers
• Removing Markers
• Marker Groups
• Project and Global Markers
• Creating a Marker Group
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 5-1 Overview: Setting Up a New ODI Project

Oracle Data Integrator Model Concepts

• Objectives
• What Is a Model?
• Agenda
• Relational Model
• Relational Model: Tables and Columns
• Relational Model: Keys
• Relational Model: Foreign Keys
• Relational Model: Constraints
• Relational Model: Indexes
• Relational Model Support in ODI
• Additional Metadata in ODI
• FlexFields
• Agenda
• What Is Reverse-Engineering?
• Methods for DBMS Reverse-Engineering
• Other Methods for Reverse-Engineering
• Standard Versus Customized Reverse-Engineering
• Reverse-Engineering Life Cycle
• Agenda
• Creating a Model by Reverse-Engineering
• Step 1: Creating and Naming a New Model
• Step 2: Defining a Reverse-Engineering Strategy
• Step 3: Starting the Reverse-Engineering Process
• Using RKM for Customized Reverse-Engineering
• Selective Reverse-Engineering
• Step 4: Fleshing Out Models
• Shortcuts
• Smart Export and Import
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 6-1 Overview: Creating Models by Reverse-Engineering

Organizing ODI Models and Creating ODI Datastores

• Objectives
• Agenda
• What Is a Model Folder?
• Creating a Model Folder
• What Is a Submodel?
• Creating a Submodel
• Organizing Datastores into Submodels
• Setting Up Automatic Distribution
• Agenda
• Creating Datastores
• Creating a Datastore in a Model
• Adding Columns to a Datastore
• Agenda
• What Is a Constraint in ODI?
• Constraints in ODI
• Creating a Mandatory Column
• Agenda
• Creating a Key
• Checking a Key
• Creating a Reference
• Creating a Simple Reference
• Creating a Complex Reference
• Checking a Reference
• Agenda
• Creating a Condition
• Checking a Condition
• Agenda
• Audit/Explore: When and Why
• Audit/Explore Process: Overview
• Agenda
• Displaying the of a Datastore
• Viewing the Distribution of Values
• Analyzing the of a Datastore
• Agenda
• Defining Business Rules in ODI
• From Business Rules to Constraints
• Deducing Constraints from Data Analysis
• Testing a Constraint
• Auditing a Model or Datastore
• Reviewing Erroneous Records
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 7-1 Overview: Checking Data Quality in the Model

ODI Mapping Concepts

• Objectives
• Agenda
• What Is a Mapping?
• Business Rules for Mappings
• Where Are the Rules Defined?
• Agenda
• What Is an Expression?
• What Is a Join?
• What Is a Filter?
• What Is a Lookup?
• What Is a Set?
• What Are Some of the Others?
• New with Patch: Pivot and Unpivot
• Agenda
• How Does ODI Implement Business Rules?
• Business Problem
• Implementing the Rules
• Integration Process
• Process Details
• Process Implementation: Example 1
• Process Implementation: Example 2
• Process Implementation: Example 3
• Agenda
• What Is a Staging Area?
• Execution Location
• Agenda
• From Business Rules to Processes
• Knowledge Modules
• What Is a Knowledge Module?
• Code Generation
• KM Types Used in Mappings
• Agenda
• Purpose of a Mapping
• What Is an Expression?
• Creating a One-to-One Mapping
• Creating and Naming a Mapping
• Defining the Target Datastore
• Multiple Targets
• Defining the Source Datastore
• Connecting the Ports to Make the Map
• Defining the Expressions
• Valid Expression Types
• Saving the Mapping
• Running the Mapping
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 8-1Overview: Mapping: Simple Transformations

Designing Mappings

• Objectives
• Agenda
• Multiple-Source Datastores
• Creating a Join Manually
• Advanced Joins
• Types of Joins
• Setting Up a Join
• Creating Lookups
• Using Lookups
• Agenda
• Filters in ODI
• Defining a Filter Manually
• Setting Up a Filter
• Agenda
• Physical Mapping Diagram
• Flow in the Physical Diagram
• What Defines the Flow?
• Scenario
• Basic Process
• Agenda
• Purpose of a Staging Area
• Placing the Staging Area
• Important Note
• Specifying the Staging Area
• Agenda
• Options for Expressions
• Setting Options for Expressions
• Disabling an Expression
• Enabling a Mapping for Inserts or Updates
• Agenda
• Execution Location and Syntax
• Why Change the Execution Location?
• Changing the Execution Location
• ODI Mapping Execution Simulation
• Agenda
• Which KMs for Which Flow?
• Knowledge Modules: Additional Information
• Identifying IKMs and LKMs
• IKMs and LKMs: Strategies and Methods
• Specifying an LKM
• Specifying an IKM
• Common KM Options
• Flow: Example 1
• Flow: Example 2
• Flow: Example 3
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 9-1 Overview: Mapping: Complex Transformations
• Practice 9-2 Overview: Mapping: Implementing Lookup

Mappings: Monitoring and Troubleshooting

• Objectives
• Agenda
• Operator Navigator: Viewing the Log
• Using the Operator Navigator
• Hierarchy: Sessions, Steps, Tasks
• Viewing Details of Sessions, Steps, and Tasks
• Monitoring Execution of a Mapping
• Troubleshooting a Session
• 1. Identifying the Error
• 2. Reviewing the Code
• 3. Fixing the Code and Restarting the Session
• 4. Fixing the Mapping
• Keys to Reviewing the Generated Code
• Agenda
• Common Errors and Symptoms
• Important Note
• Tips for Preventing Errors
• Using the Attributes Panel for Quick Edits
• Quiz
• Summary
• Checklist of Practice Activities

Practice 10-1 Overview: Creating ODI Mapping: Exporting a Flat File to a Relational

• Table

Designing Mappings: Advanced Topics 1

• Objectives
• Agenda
• Business Rules in Mappings
• Business Rule Elements
• More Elements
• Expression Editor
• Agenda
• Using a Variable in Code
• Binding Versus Substitution
• Case Sensitivity
• Agenda
• Defining a Dataset
• Using Set-Based Operators
• Example of SET: UNION
• Agenda
• Types of Sequences
• Support for Native Sequences
• Creating a Native Sequence
• Referring to Sequences
• Sequences Updated by Agent
• Using Standard Sequences in Mappings Correctly
• Using ODI Standard Sequences in Mappings
• Populating Native Identity Attributes
• Sequences: Best Practices
• Automatic Temporary Index Management
• Tracking Variables and Sequences
• How Variable and Sequence Tracking Works
• Variable Actions
• Definition Tab of Session Step or Session Task
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 11-1: Using Native Sequences with ODI Mapping
• Practice 11-2: Using Temporary Indexes
• Practice 11-3: Using Sets with ODI Mapping

Designing Mappings: Advanced Topics 2

• Objectives
• Agenda
• Partitioning
• Definition in Datastore After Reverse-Engineering
• Using Partitioning in a Mapping
• Agenda
• Reusable Mappings
• Using Reusable Mappings: Example
• Derived Select (Subselect) for Reusable Mappings
• Agenda
• What Is a User Function?
• Why Use User Functions?
• Properties of User Functions
• Using User Functions
• Creating a User Function
• Defining an Implementation
• Syntax and Implementations
• User Functions at Design Time
• User Functions at Run Time
• Note: Functions in Execution Log
• Agenda
• Using Substitution Methods
• Substitution Methods: Examples
• Agenda
• Description of KM Steps
• Details of the Steps
• Setting KM Options
• Developing Your Own KM: Guidelines
• Complex File Technology
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 12-1: Creating and Using Reusable Mappings
• Practice 12-2: Developing a New Knowledge Module

Using ODI Procedures

• Objectives
• Agenda
• What Is a Procedure?
• Procedure: Examples
• Creating Procedures: Overview
• Agenda
• Creating a New Procedure
• Agenda
• Creating a Command
• Arranging Tasks in Order
• Which Parameters Should Be Set?
• Valid Types of Commands
• More Elements
• Why Use a Source Command?
• Agenda
• Types of Options
• Creating a New Option
• Making a Command Optional
• Using an Option Value in a Command
• Agenda
• Procedure Execution
• Using the Operator Navigator to View Results
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 13-1: Creating an ODI Procedure

Using ODI Packages

• Objectives
• Agenda
• What Is a Package?
• Creating a Package
• Agenda
• Creating and Naming a Package
• Package Diagram
• Package Diagram Toolbar
• Agenda
• Package Steps
• Creating a Package Step
• What Is an ODI Tool?
• Creating an ODI Tool Step
• Tool Steps: Best Practices
• Agenda
• Sequencing Steps
• A Simple Package
• Sequencing Package Steps
• Agenda
• Executing a Package
• Agenda
• Basic Step Types
• Advanced Step Types
• Agenda
• Creating Model, Submodel, and Datastore Steps
• Models, Submodels, and Datastore Steps
• Agenda
• Creating a Variable Step
• Variable Steps
• Agenda
• Controlling Execution
• Error Handling
• Creating a Loop
• The Advanced Tab
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 14-1: Creating an ODI Package
• Practice 14-2: Using ODI Packages with Variables and User Functions

Step-by-Step Debugger

• Objectives
• Agenda
• Overview
• Agenda
• Process Overview
• Starting a Session in Debug Mode
• Specifying Debug Properties
• Control Execution Flow
• Screen Step Numbering
• Agenda
• New Functionalities
• Benefits for End Users
• Agenda
• Debug Toolbar
• Toolbar: Current Cursor
• Toolbar: Get Data
• Toolbar: Step Into
• Toolbar: Run to Task End
• Toolbar: Run to Next Task
• Toolbar: Run to Step End
• Toolbar: Run to Next Step
• Toolbar: Pause
• Toolbar: Resume
• Summary
• Checklist of Practice Activities
• Practice 15-1: Debugging Mappings

Managing ODI Scenarios

• Objectives
• Agenda
• What Is a Scenario?
• Properties of Scenarios
• Agenda
• Scenario-Related Tasks
• Generating a Scenario
• Regenerating a Scenario
• Generation Versus Regeneration
• Executing a Scenario from the GUI
• Executing a Scenario from a Command Line
• Executing a Scenario from a Package
• Exporting a Scenario
• Agenda
• Preparing Scenarios for Deployment
• Automating Scenario Management
• Scheduling the ODI Scenario
• Scheduling ODI Scenario with External Scheduler
• Managing Schedules
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 16-1: Creating and Scheduling an ODI Scenario

Using Load Plans

• Objectives
• Should You Organize Executions with Load Plans?
• What Are Load Plans?
• Load Plan Editor
• Load Plan Steps
• Defining the Restart Behavior
• Are Load Plans Substitutes or Packages or Scenarios?
• Benefits of Using Load Plans
• Handling Failed Load Plans
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 17-1 Overview: Using Load Plans

Enforcing Data Quality with ODI

• Objectives
• Agenda
• Why Data Quality?
• When to Enforce Data Quality
• Data Quality in Source Applications
• Data Quality Control in the Integration Process
• Data Quality in the Target Applications
• Agenda
• Data Quality Business Rules
• From Business Rules to Constraints
• Agenda
• Data Quality System: Overview
• Static and Flow Controls: Differences
• Data Quality Control: Properties
• Synchronous Control
• What Is a Constraint?
• What Can Be Checked?
• Enforcing Data Quality in a Mapping
• Agenda
• Setting Up Static or Flow Control
• Enabling Static or Flow Control
• Agenda
• Setting the Physical Options
• Setting the Logical Options
• Agenda
• Selecting Which Constraints to Enforce
• Selecting Which Constraints to Check
• Differences Between Control Types
• Agenda
• Reviewing Erroneous Records
• EnterpriseDataQuality Tool
• Using the EDQ Tool
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 18-1: Enforcing Data Quality with ODI Mappings

Working with Changed Data Capture (CDC)

• Objectives
• Why Changed Data Capture?
• CDC Techniques
• Changed Data Capture in ODI
• Journalizing Components
• CDC Infrastructure in ODI
• Simple Versus Consistent Set Journalizing
• Limitations of Simple CDC Journalizing: Example
• Consistent CDC Journalizing
• Consistent CDC: Infrastructure
• Setting Up Journalizing
• Setting CDC Parameters: Example
• Adding a Subscriber: Example
• Starting Journal: Example
• Journalizing Status
• Viewing Data/Changed Data: Example
• Using Changed Data
• Oracle GoldenGate Integration
• Oracle GoldenGate Integration in ODI 12c
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 19-1: Implementing Changed Data Capture

Advanced ODI Administration

• Objectives
• Agenda
• Introduction to ODI Security Navigator
• Security Concepts: Overview
• Defining Security Policies
• Creating Profiles
• Using Generic and Nongeneric Profiles
• Built-in Profiles
• Creating Users
• Assigning a Profile to a User
• Assigning an Authorization by Profile or User
• Defining Password Policies
• Setting User Preferences
• ODI Security Integration: Overview
• Implementing External Authentication (OPSS)
• Implementing External Authentication (OPSS): Switching the Authentication
• Mode
• Implementing External Password Storage
• Agenda
• Types of ODI Reports
• Generating Topology Reports
• Generated Topology Report: Example
• Version Comparison Report: Example
• Generating Object Reports
• Agenda
• Integration of ODI with Enterprise Manager
• Java EE Agent and Enterprise Manager Configuration with WebLogic
• Domain: Overview
• Using ODI Console: Example
• Quiz
• Summary
• Checklist of Practice Activities
• Practice 20-1: Setting Up ODI Security
• Practice 20-2: Integration with Enterprise Manager and Using ODI Console

Integrating ODI with Subversion

• Objectives
• Agenda
• ODI-VCS Integration
• ODI-VCS Integration Features
• ODI: Selecting the Required VCS
• ODI Repository Change
• Agenda
• SVN Connections
• Creating an SVN Connection
• Configuring ODI Subversion Settings
• Configuring the Subversion Repository with ODI
• Creating a Default Subversion Project Structure
• Configuring the Subversion Repository with ODI: Options
• SVN Configuration Options
• Creating the Default Project Structure
• Disconnection from ODI (Required)
• Agenda
• Adding ODI Objects to SVN: Options
• Adding a Single Non-Versioned Object to SVN: 1
• Adding a Single Non-Versioned Object to SVN: 2
• Adding Multiple Non-Versioned Objects to SVN: 1
• Adding Multiple Non-Versioned Objects to SVN: 2
• Creating a New Version for an Object
• Adding a Version Comment
• Agenda
• Creating Full or Partial Tags in the Subversion Repository
• Creating a Full Tag
• Full Tag Parameters
• Creating a Partial Tag
• Partial Tag: Adding a Subset of ODI Objects
• Agenda
• Version History
• Comparing Versions
• Version Search Criteria
• Version Compare Results
• Display Color Options
• Display Version Tree
• Agenda
• Performing a Branch Merge
• Merge Summary Report
• Merge Results Window
• Resolving Merge Conflicts
• Merge Conflict Resolution
• Quiz
• Summary
• Practice 21-1: Subversion ODI Setup

Integrating Big Data with ODI

• Objectives
• Agenda
• Big Data
• Google BigTable, a Big Data Pioneer
• Emergence of Apache Hadoop
• Agenda
• Apache Hadoop
• Hadoop Ecosystem
• Apache HBASE
• Apache Hive
• Apache Pig
• Apache Spark
• Apache Sqoop
• Apache Oozie
• Agenda
• Big Data Transformation with Data Integrator
• Agenda
• Hadoop Data Integration: Overview
• Big Data Knowledge Modules Matrix:1
• Big Data Knowledge Modules Matrix: 2
• Big Data Knowledge Modules Matrix: 3
• Big Data Knowledge Modules Matrix: 4
• Big Data Knowledge Modules Matrix: 5
• ODI: Hadoop Integration Process
• ODI: Further Processing After Hadoop Analysis
• ODI: Code Generation
• Agenda
• Apache Oozie and ODI Integration
• ODI to Oozie Workflow Tasks
• Agenda
• ODI Support for Unstructured Data
• ODI Support for Complex Data
• Summary
• Practice 22-1: Configuring the Hadoop ecosystem