Basic knowledge of ELT data processing
Course Objectives:
Upon completion of this Course, you should be able to:
Understand the ETL Development Lifecycle
Understand basic Data warehousing Concepts
Understand basic Dimension Modelling
Understand Data Quality Issues in ETL Process
Describe the architecture of Oracle Data Integrator 12c
Install Oracle Data Integrator 12c
Administer ODI Repositories and Agents
Apply ODI Topology concepts for data integration
Define Data Sources
Define Physical and Logical Architecture
Learn to Manage ODI Projects
Describe ODI Model concepts
Create and reverse-engineer models
Design ODI Mappings, Procedures, Packages, and Load Plans to perform ELT data transformations
Explore, audit data, and enforce data quality with ODI
Implement Changed Data Capture with ODI
Enforce data quality constraints in an interface
Manage packages
Understand how to Use ODI Web services and perform integration of ODI with SOA.
Understand how to Extend ODI to include the Big Data Hadoop ecosystem
Course Topics
Overview
Course Scenario
Understanding ETL and Data Integration Platforms
ETL Framework and Components
The Evolution of ETL
Code Generators versus Engines
Database-Centric ETL
Data Integration Platforms
ETL versus EAI
Understanding the Data Integration Tool Market
Functional Capabilities of Data Integration Tools
ODI Value Proposition
Why Oracle Data Integrator?
Traditional ETL to Integration and Data Warehousing
Conventional ETL Design
The ELT Approach to Integration and Data Warehousing
Declarative Design
Traditional ETL versus E-LT Approach
Introduction to Oracle Data Integrator
Oracle Data Integrator Product Architecture
Knowledge Modules
Data Quality
Data Integrity
Name and Address Cleansing Tools
Changed Data Capture
Oracle Data Integrator and Oracle SOA Suite
Oracle Data Integrator in Relation to Oracle Warehouse Builder
Oracle Data Integrator Architecture and Components
Overview of Oracle Data Integrator Repositories
Types of ODI Agents
Stand alone vs J2EE Agents
Overview of ODI Architecture
Knowledge Module Architecture
Oracle Warehouse Builder Integration
Oracle GoldenGate Integration
Architecture for Enterprise-Scale Deployment
About Graphical Modules
Exploring the Oracle DI Studio 12c Interface
The Mapping Editor in Studio
Installing Oracle Data Integrator 12c
Planning the Installation
Installation Options
Standard Installation
Configuring ODI Repositories using RCU
Enterprise Installation
Administering ODI Repositories and Agents
Creating a Wallet to Securely Store Credentials
Administrating the ODI Repositories using Studio
Creating Repository Storage Spaces
Creating and Connecting to the Master Repository
Creating and Connecting to the Work Repository
Managing ODI Agents
Integration of ODI in Enterprise Environment
Using Common Administration Model (CAM)
Methods of Integration of ODI in Enterprise Environment
Configuring ODI Agents using Common Administration Model
ODI Topology Concepts
Overview of ODI Topology
Defining a Topology
Planning the Topology
About Data Servers and Physical Schemas
Mapping Logical and Physical Resources
Defining the Logical Architecture
Defining Agents
Defining the Physical Architecture
Describing the Physical and Logical Architecture
Creating Logical Architecture
Testing a Data Server Connection
Overview of Logical Architecture and Context Views
Creating a Physical Schema
Creating Physical Architecture
Creating a Data Server
Overview of Topology Navigator
Linking the Logical and Physical Architecture
Introduction to ETL Development
The Planning and Design Thread
The Data Flow Thread
Data Delivery Thread
ETL Data Structures
ETL and Data Staging
Designing the Staging Area
Data Structures in the ETL System
Flat Files
XML Data Sets
DTDs, XML Schemas, and XSLT
Relational Tables
Independent DBMS Working Tables
Third Normal Form Entity /Relation Models
Non-relational Data Sources
Dimensional Data Models
Fact Tables
Dimension Tables
Atomic and Aggregate Fact Tables
Surrogate Key Mapping Tables
Impact Analysis
Metadata Capture
Naming Conventions
Auditing Data Transformation Steps
Summary
Oracle ETL-ELT Reference Architecture
Information Architecture Capabilities
Information Provisioning
Data Processing
Data Governance
Data Virtualisation
Information Delivery
Information Access
Information Modelling
Approach to Oracle ETL development
Strategy and Planning Phase
Discover Phase
Analyse Phase
Define Phase
Develop Phase
Deploy Phase
Understanding the Extract, Transform and Integration Process
Data Extraction
The Logical Data Map
Building the Logical Data Map
Approach to Analysing ER Diagrams
Data Content Analysis
Collecting Business Rules in the ETL Process
Integrating Heterogeneous Data Sources
Challenge of Extracting from Disparate Platforms
Processing Fixed Length Flat Files
Processing Delimited Flat Files
Data Transformation
Data Capture
Cleansing and Conforming
Data Cleansing Techniques
Data Conditioning
Data Integration
Data Refining
Data Quality Assessment
Anomalies in Data Fields
Lack of Data Standards
Lack of Data Consolidation
Setting Up a New ODI Project
Overview of ODI Projects
Creating and Maintaining Folders
Creating and Labeling with Markers
Exporting and Importing Objects
Creating a New Project
Understanding Knowledge Modules
Exchanging ODI Objects and Sharing Global Objects
Organizing Projects and Folders
Oracle Data Integrator Model Concepts
Creating Data stores
Organizing Models
Understanding Metadata in ODI
What is a Model?
Creating Models
Creating Keys and References
Understanding Reverse Engineering
Configuring Constraints in ODI
Organizing ODI Models and Creating Data stores
What is a Filter?
What is a Constraint?
Creating a Basic Mapping
Business Rules for Mappings
What is a Mapping?
What is a Join?
What is a Staging Area?
ODI Mapping Concepts
Mapping with Knowledge Modules (KM)
Execution Location
Creating an Intermediate Mapping
What is a Staging Area?
What is a Mapping, a Filter, a Join?
What is a Mapping?
Overview of Integration Process
Business Rules for Mapping
Designing Mappings
Creating Joins
Filtering Data
Multiple Source Data stores
Designing a Mapping
Overview of the Flow
Selecting Knowledge Modules
Disabling Transformations
Specifying the Staging Area
Mapping: Monitoring and Debugging
How to Troubleshoot a Session
Tips for Preventing Errors
Monitoring Mappings
Keys to Reviewing the Generated Code
Working with Errors
Viewing Sessions and Tasks
How to Monitor Execution of a Mapping
Creating Objects with Operator
Using ODI Procedures
What is a Procedure?
Adding Commands
Examples of Procedures
Viewing Results with Operator Navigator
Running a Procedure
Adding Options
Creating Procedures
Using ODI Packages
Variable Steps
Controlling the Execution Path
Error Handling
Review of Package Steps
Executing a Package
What is a Package?
Creating Model, Submodel and Datastore Steps
Creating a Package
Step-by-Step Debugger
Menu Bar Icons
Screen Step Numbering
Control Execution Flow
Specifying Debug Properties
New Functionality
Starting a Session in Debug mode
Managing ODI Scenarios
Generating a Scenario
What is a Scenario?
Scheduling the ODI Scenario
Managing Schedules
Executing a Scenario
Automating Scenario Management
Using Load Plans
Defining the Restart Behavior
What Are Load Plans?
Load Plan Editor
Load Plan Steps
Benefits of Using Load Plans
Handling Failed Load Plans
Enforcing Data Quality with ODI
Why Data Quality? When to Enforce Data Quality?
Data Quality in Source Applications
Enabling Static or Flow Control for a Mapping
Data Quality Business Rules
Data Quality Control in the Integration Process
Data Quality in the Target Applications
Setting the Options, Selecting Which Constraints to Enforce
Reviewing Erroneous Records
Working with Changed Data Capture (CDC)
Journalizing
Oracle GoldenGate Integration
Changed Data Capture in ODI
Viewing Data/Changed data
CDC Strategies and Infrastructure
Why Changed Data Capture? Techniques of Changed Data Capture
CDC Consistency
Creating Change Data Capture (CDC)
Advanced ODI Administration
Integration of ODI with Enterprise Manager
Oracle DI Console Administrative Operations
Create Credential Store for Enterprise Manager
Exploring Oracle DI Console
Testing a Data Server
Testing Physical Agent Connection
Adding a Data Server to a Physical Agent
Creating Templates for Physical Agents
Load Balancing Agents
Performing Operations in DI Console
Exploring Design Time Objects
Eploring Topology Objects
Viewing an Object
Editing an Object
Creating an Object
Searching for an Object
Deleting for an Object
Managing Scenarios and Sessions in ODI Console
Exporting a Scenario
Running a Scenario
Restarting a Session
Cleaning stale Sessions
Administering Repositories in ODI Console
Creating a Repository Connecton
Exporting Repositiries
Exporting Physical Topologies
Oracle Data Integrator and Big Data
Big Data and Hadoop Essentials
Hive Overview
Oracle Big Data Fundamentals
Describe the Oracle approach to Big Data
Oracle Data Integrator and Big Data
Oracle Data Integrator Application Adapter for Hadoop
Loading Unstructured Data from File into Hive
Transforming and Validating Data on Hive
Loading Processed Data in Hive into Oracle