Oracle 11g Datawarehousing Fundamentals in washington DC
George Mason University, Volgenau School of Engineering

TAIT 0420: Data Integration and ETL with Oracle Warehouse Builder

George Mason University partners with Oracle in the Oracle University program. Official Oracle curriculum is utilized in our Oracle 11g Datawarehousing Fundamentals course. Effective 2010, books shipped by Oracle Corporation are in electronic format.


This hands-on training course is designed to teach participants to load data by executing the mappings or the process flows, use Oracle Warehouse Builder 11g, Release 2 features to manage metadata changes, debug mappings, backup metadata, manage security, and tune the ETL mappings for better performance.

In addition, participants are also taught integration of Warehouse Builder with OBI EE, along with the Warehouse Builder architecture and configuration. Participants also learn to retrieve data from different types of sources such as flat files or relational schemas and also to use the different transformation operators to design an ETL task. The usage of Warehouse Builder to define both relational dimensional models and multidimensional models, to deploy a single logical model to multiple physical targets and how to handle slowly changing dimensions are also taught.

In addition, extraction of data from non-Oracle sources using code templates, usage of the Warehouse
Builder ETL and data integration features of the Enterprise ETL Option of the Oracle database are discussed.

All books and materials are included.

Back to top

Audience and Prerequisites

This course is designed for Oracle Developers, Oracle Database Administrators who also have knowledge of Oracle 11g Data Warehousing Fundamentals.

Course Outline Detail

Installing and Setting Up the Warehouse Builder Environment
What Is Oracle Warehouse Builder?
Basic Process Flow of Design and Deployment
Oracle Warehouse Builder Licensing and Connectivity Options
Installing Oracle Warehouse Builder 11.2
Using OWB 11.2 with Database 10g R2
Using the Repository Assistant to Manage Workspaces
Supported operating systems (OS), sources, targets, and optional components

Getting Started with Warehouse Builder
Logging In to OWB Design Center
Overview of the Design Center
OWB Projects
Overview of Objects within a Project
Overview of Objects within an Oracle Module
Organizing Metadata Using Foldering
Locations Navigator and Global Navigator panels
Setting Projects Preferences: Recent Logons

Understanding the Warehouse Builder Architecture
Warehouse Builder Development Cycle
Overview of the Architecture for Design, Deployment, Execution
Overview of Configurations, Control Centers, and Locations
Creating Target Schemas
Registering DB User as an OWB User
Roles and Privileges of Warehouse Builder Users
Registering an Oracle Workflow User

Defining Source Metadata
Data warehouse implementation steps
Difference Between Obtaining Relational and Flat File Source Metadata
Creating Flat File Module
Sampling Simple Delimited File
Sampling Multi-record Flat File
Creating an Oracle Module
Selecting the Tables for Import

Defining ETL Mappings for Staging Data
Purpose of a Staging Area
Define OWB Mappings
Mapping Editor Interface: Grouping, Ungrouping, and Spotlighting
Creating External Tables
Create and Bind process
Levels of Synchronizing Changes
Using the Automapper in the Mapping Editor
Set loading type and target load ordering

Using the Data Transformation Operators
Component Palette
Using a Joiner
Lookup Operator: Handling Multiple Match Rows
Using the Subquery Filter Operator
Using the Set, Sequence, and Splitter Operators
Pivot and Unpivot Operators
Using the Aggregator, Constant, Transformation, and Pre/Post Mapping Operators
Deploying and Executing in Projects Navigator Panel

Cleansing and Match-Merging Name and Address Data
Integrating Data Quality into ETL
Name and Address Data Cleansing
Name and Address Server
Name and Address Software Providers
Settings in the Name and Address Operator
Reviewing a Name and Address Mapping
Consolidating Data Using the Match Merge Operator
Using the Match Merge Operator in a Mapping

Using Process Flows
Process Flow Concepts
Creating a Process Flow Module, a Process Flow Package and a Process Flow
Types of Activities: Fork, And, Mapping, End Activity
Creating Transitions Between Activities
Some More Activities: Manual, SQLPLUS, Email
Generating the Process Flow Package

Deploying and Reporting on ETL Jobs
Logical Versus Physical Implementation
Setting Object Configuration
Deployment Concepts
Invoking the Control Center Manager
Deploy Options and Preferences
Repository Browser
Starting OWB Browser Listener and the Repository Browser
Browsing Design Center and Control Center Reports

Using the Mapping Debugger
Overview of the Mapping Debugger
Initializing a Mapping Debugging Session
Preparing the testing environment and test data
Setting breakpoints and watch points
Evaluating the flow of data to detect mapping errors

Enhancing ETL Performance
Performance Tuning at Various Levels
Performance-Related Parameters in ETL Design
Configuring Mappings for Operating Modes, DML Error Logging, Commit Control, and Default Audit Levels
Enabling Partition Exchange Loading (PEL) for Targets
Performance-Related Parameters in Schema Design
Configuring Indexes, Partitions, Constraints
Enabling Parallelism and Parallel DML
Setting Tablespace Properties and Gathering Schema Statistics

Managing Backups, Development Changes, and Security
Overview of Metadata Loader Utilities (MDL)
Managing Metadata Changes by Using Snapshots
Using Change Manager
Version Management of Design Objects
Graphical UI for Security Management
Object-Level Security
Setting Security Parameters

Integrating with Oracle Business Intelligence Enterprise Edition (OBI EE)
Business Justification: Tools Integration
Integrating with OBI EE and OBI SE
Transferring BI Metadata to OBI EE Server
Setting Up the UDML File Location
Deriving the BI Metadata (OBI EE)
Deploying the BI Module
Converting the UDML File for OBI EE
Oracle BI Admin and Answers Tool

Administrative Tasks in Warehouse Builder
Enterprise ETL License Extends Core In-Database ETL
Multiple Named Configurations: Why and How
Using Multiple Named Configurations
Using Configuration Templates
Steps for Setting Up OWB in a RAC Environment
Creating an OWB Schedule

Managing Metadata
Using Lineage and Impact Analysis Diagrams
Invoking Lineage and Impact Analysis
Using the Change Propagation Dialog
User-Defined Properties, Icons, and Objects
Using Pluggable Mappings
Advanced Activity Types in Process Flows
Native Relational Object Support
Heterogeneous Predefined SQL Transformations

Accessing Non-Oracle Sources
Extensible Framework of OWB 11g Release 2
Benefits of Extensible Code Templates
Location of Seeded Code Templates
Creating New Code Templates
Defining New Integration Platforms in OWB

Designing Mappings with the Oracle Data Integration Enterprise Edition License
Traditional Versus Code Template (CT) Mappings
Execution Units in a CT Mapping
Execution View Versus Logical View
Assigning a Code Template to an Execution Unit
Convert a Classic Mapping to a CT Mapping That Utilizes Data Pump
CT Mappings Deploy to Control Center Agents

Right-Time Data Warehousing with OWB
What Is Meant by Real-Time Data Warehousing
What Refresh Frequency Does OWB Support
Building a Trickle Feed Mapping
Using Advanced Queues in Trickle Feed Mappings
Using CDC Code Templates in Mappings for Change Data Capture
Starting CDC Capture Process

Defining Relational Models
Defining Dimensions Using Wizards and Editors
Defining Dimension Attributes, Levels, and Hierarchies
Binding Dimension Attributes to the Implementation Table
Using the Create Time Dimension Wizard
Defining a Cube
Specifying a Cube's Attributes and Measures
Designing Mappings Using Relational Dimensions and Cubes

More Relational Dimensional Modeling
Initial Versus Incremental Data Warehouse Loads
Updating Data and Metadata
Capturing Changed Data for Refresh
Setting Loading Properties
Choosing the DML Load Type
How OWB Manages Orphans
Support for Cube-Organized Materialized Views
Creating a Type 2 Slowly Changing Dimension

Modeling Multidimensional OLAP Dimensions and Cubes
What Is OLAP
Multidimensional Data Types
Analytic Workspace
Dimensional Modeling Using OWB
OWB Calculated Measures
OWB Calculated Measures


Click here to download the registration form (fax or mail)


Please call for our upcoming class schedule.

4.0 CEUs
40 Hours
Onsite Opportunity

Enhance your organization's competitive edge!

George Mason University's TechAdvance Program can tailor programs to meet your organization's needs. Companies or agencies interested in bringing this program on site should contact TechAdvance at 703-993-1551.

Contact Info.
Online contact form

  George Mason University
Volgenau School of Engineering
  4400 University Drive, MS 4A3
  Fairfax, VA 22030

Telephone: 703-993-1551


Volgenau School of Engineering George Mason University