Contents

Title and Copyright Information

Preface

• Audience
• Documentation Accessibility
• Conventions
• Getting Help
• Related Publications

What's New

• New in Oracle Warehouse Builder 11g Release 1 (11.1)

Part I Introduction and Concepts

1 Introduction to Oracle Warehouse Builder

• Overview of Oracle Warehouse Builder
  • Data Consolidation and Integration
• Product Options and Licensing
  • Warehouse Builder Core Functionality
  • Warehouse Builder Enterprise ETL Option
  • Warehouse Builder Data Quality Option
  • Warehouse Builder Connector - E-Business Suite
  • Warehouse Builder Connector - PeopleSoft
  • Warehouse Builder Connector - SAP R/3 Connector
  • Warehouse Builder Connector - Siebel

2 Getting Started with Oracle Warehouse Builder

• Understanding the Basic Concepts
• Implementing a Data Integration Solution
  • Before You Begin
  • Preparing the Warehouse Builder Design Center
  • Importing the Source Metadata
  • Profiling Data and Ensuring Data Quality
  • Designing the Target Schema
  • Designing ETL Logic
  • Deploying the Design and Executing the Data Integration Solution
  • Monitoring and Reporting on the Data Warehouse

3 Setting Up Warehouse Builder

• Organizing Design Objects into Projects
• Setting Preferences
  • Appearance Preferences
  • Control Center Monitor Preferences
  • Data Profiling Preferences
  • Deployment Preferences
  • Environment Preferences
  • Generation/Validation Preferences
  • Logging Preferences
  • Naming Preferences
    • About Naming Modes
  • Security Preferences
• Defining Collections
  • Creating a Collection
    • Name and Description Page
    • Contents Page
    • Summary Page
  • Editing Collection Definitions
    • Name Tab
    • Contents Tab
• Alternative Interfaces

4 Identifying Data Sources and Importing Metadata

• About Source Data and Metadata
• Supported Sources and Targets
• General Steps for Importing Metadata from Sources
  • Example: Importing Metadata from Flat Files
  • About Modules
    • Creating Modules
• Using the Import Metadata Wizard
  • Importing Definitions from a Database
    • Filter Information Page
    • Object Selection Page
    • Summary and Import Page
    • Import Results Page
  • Importing Definitions from Flat Files
• Reimporting Definitions from an Oracle Database
  • Advanced Import Options
    • Advanced Import Options for Views and External Tables
    • Advanced Import Options for Tables
    • Advanced Import Options for Object Types
    • Advanced Import Options for SQL Collections
  • Updating Oracle Database Source Definitions
• Integrating with E-Business Suite
  • Importing E-Business Suite Metadata Definitions
    • Filtering E-Business Suite Metadata
    • Selecting the Objects
    • Reviewing Import Summary
• Integrating with PeopleSoft
  • Importing PeopleSoft Metadata Definitions
    • Filtering PeopleSoft Metadata
    • Selecting the Objects
    • Reviewing Import Summary
• Integrating with Siebel
  • Importing Siebel Metadata Definitions
    • Creating a Siebel Source Module
    • Importing Siebel Metadata
• Integrating with SAP R/3
  • About SAP Business Domains
  • SAP Table Types
  • Required Files For SAP Connector
  • Creating SAP Module Definitions
    • Connecting to an SAP Source Application
  • Importing SAP Metadata Definitions
    • Filtering SAP Metadata
    • Selecting the Objects
    • Reviewing Import Summary
    • Reimporting SAP Objects
  • Updating SAP Source Modules
  • Defining the ETL Process for SAP Objects
    • Defining Mappings Containing SAP Objects
    • Configuring Code Generation for SAP Objects
    • Generating SAP Definitions
  • Loading SAP Data into the Workspace
    • Deploying and Executing an SAP Mapping
    • Deploying PL/SQL Scripts for Transparent Tables
• Integrating with Business Intelligence Tools
  • Introduction to Business Intelligence Objects in Warehouse Builder
  • Introduction to Business Definitions
  • About Business Definitions

5 Understanding Data Quality Management

• About the Data Quality Management Process
  • Phases in the Data Quality Lifecycle
    • Quality Assessment
    • Quality Design
    • Quality Transformation
    • Quality Monitoring
• About Data Profiling
  • Benefits of Data Profiling
  • Types of Data Profiling
    • Attribute Analysis
    • Functional Dependency
    • Referential Analysis
    • Data Rule Profiling
  • About Six Sigma
    • What is Six Sigma?
    • Six Sigma Metrics for Data Profiling
• About Data Correction and Augmentation
  • About the Match-Merge Operator
  • Example of Matching and Merging Customer Data
  • Overview of the Matching and Merging Process
    • Matching and Merging Records
  • Match Rules
  • Conditional Match Rules
    • Comparison Algorithms
    • Creating Conditional Match Rules
  • Weight Match Rules
    • Example of Weight Match Rules
    • Creating Weight Match Rules
  • Person Match Rules
    • Person Roles
    • Person Details
    • Creating Person Match Rules
  • Firm Match Rules
    • Firm Roles
    • Firm Details
    • Creating Firm Match Rules
  • Address Match Rules
    • Address Roles
    • Address Details
    • Creating Address Match Rules
  • Custom Match Rules
    • Creating Custom Match Rules
  • Merge Rules
  • Using a Match-Merge Operator
  • About the Name and Address Operator
  • Example: Correcting Address Information
    • Example Input
    • Example Steps
    • Example Output
  • Handling Errors in Name and Address Data
  • About Postal Reporting
    • United States Postal Service CASS Certification
    • Canada Post SERP Certification
    • Australia Post AMAS Certification
• About Data Rules
• About Quality Monitoring
  • About Data Auditors
• Performing Data Profiling
  • Import or Select the Metadata
  • Create a Data Profile
  • Profile the Data
    • Configuring Data Profiles
    • Steps to Profile Data
  • View Profile Results
  • Derive Data Rules
  • Generate Corrections
  • Define and Edit Data Rules Manually
  • Generate, Deploy, and Execute
• Tuning the Data Profiling Process
  • Tuning the Data Profile for Better Data Profiling Performance
  • Tuning the Oracle Database for Better Data Profiling Performance
    • Multiple Processors
    • Memory
    • I/O System
• Using Data Rules
  • Creating Data Rules
  • Applying Data Rules to Objects
• Monitoring Data Quality Using Data Auditors
  • Creating Data Auditors
  • Auditing Data Objects Using Data Auditors
    • Manually Running Data Auditors
    • Automatically Running Data Auditors
    • Data Auditor Execution Results

6 Designing Target Schemas

• About Data Objects
  • Supported Data Types
  • Naming Conventions for Data Objects
• About the Data Object Editor
  • Data Viewer
  • Using the Data Object Editor to Create Data Objects
    • Creating Data Objects Using the Menu Bar
    • Creating a Data Object Using the Canvas
    • Creating a Data Object Using the Data Object Editor Palette
• About Dimensional Objects
  • Defining Dimensional Objects
  • Implementing Dimensional Objects
    • Relational Implementation of Dimensional Objects
    • ROLAP Implementation of Dimensional Objects
    • MOLAP Implementation of Dimensional Objects
  • Deploying Dimensional Objects
  • Loading Dimensional Objects
• About Dimensions
  • Rules for Dimension Objects
  • Limitations of Deploying Dimensions to the OLAP Catalog
  • Defining a Dimension
    • Defining Dimension Attributes
    • Defining Levels
    • Defining Level Attributes
    • Defining Hierarchies
    • Dimension Roles
    • Level Relationships
    • Dimension Example
    • Control Rows
    • Value-based Hierarchies
  • Implementing a Dimension
    • Relational and ROLAP Implementation of a Dimension
    • Binding
    • MOLAP Implementation
• About Slowly Changing Dimensions
  • About Type 1 Slowly Changing Dimensions
  • About Type 2 Slowly Changing Dimensions
    • Defining a Type 2 Slowly Changing Dimension
    • Updating Type 2 Slowly Changing Dimensions
  • About Type 3 Slowly Changing Dimensions
    • Defining a Type 3 Slowly Changing Dimension
• About Time Dimensions
  • Best Practices for Creating a Time Dimension
  • Defining a Time Dimension
    • Levels
    • Dimension Attributes
    • Level Attributes
    • Hierarchies
  • Implementing a Time Dimension
  • Using a Time Dimension in a Cube Mapping
  • Populating a Time Dimension
    • Overlapping Data Populations
• About Cubes
  • Defining a Cube
    • Cube Measures
    • Cube Dimensionality
    • Cube Example
  • Implementing a Cube
    • Relational and ROLAP Implementation of a Cube
    • Binding
    • MOLAP Implementation of a Cube
  • Solve Dependency Order of Cube
• Designing the Target Schema
  • Designing a Relational Target Schema
  • Designing a Dimensional Target Schema
• Creating Oracle Data Objects
  • Creating Relational Data Objects
  • Creating Dimensions
  • Creating Time Dimensions
  • Creating Cubes
• Configuring Data Objects
• Validating Data Objects
  • Editing Invalid Objects
• Generating Data Objects
  • Viewing Generated Scripts
  • Saving Generated Scripts to a File
• Deriving Business Intelligence Metadata

7 Creating Mappings

• About Mappings and Operators
• Instructions for Defining Mappings
  • Instructions for Using Flat File Sources or Targets in a Mapping
• Creating a Mapping
  • About the Mapping Editor
  • Mapping Editor Windows
    • Explorer
    • Properties Inspector
    • Palette
    • Bird's Eye View
    • Data Viewer
    • Generation
  • Mapping Editor Toolbars
  • Mapping Editor Display Options
  • Types of Operators
    • Oracle Source/Target Operators
    • Data Flow Operators
    • Pre/Post Processing Operators
    • Pluggable Mapping Operators
• Adding Operators
  • Adding Operators that Bind to Workspace Objects
    • Add Operator Dialog Box
    • Create Unbound Operator with No Attributes
    • Select from Existing Repository Object and Bind
• Editing Operators
  • Name Tab
  • Groups Tab
  • Input and Output Tabs
  • Mapping Naming Conventions
  • Using Display Sets
    • Defining Display Sets
    • Selecting a Display Set
• Connecting Operators
  • Connecting Attributes
  • Connecting Groups
    • Example: Using the Mapping Editor to Create Staging Area Tables
    • Using the Connect Operators Dialog Box
    • Copy Source Attributes to Target Group and Match
    • Match by Position of Source and Target Attributes
    • Match by Name of Source and Target Attributes
• Using Pluggable Mappings
  • Creating a Pluggable Mapping
    • Standalone Pluggable Mapping
    • Pluggable Mapping Folders
    • Signature Groups
    • Input Signature
    • Output Signature
  • Pluggable Mapping Editor
• Setting Mapping Properties
  • Target Load Order
    • Reset to Default
• Setting Operator, Group, and Attribute Properties
• Synchronizing Operators and Workspace Objects
  • Synchronizing An Operator
  • Synchronizing From a Workspace Object to an Operator
    • Synchronizing Operators based on Workspace Objects
  • Synchronizing from an Operator to a Workspace Object
  • Advanced Options for Synchronizing
    • Matching Strategies
• Using DML Error Logging
  • About Error Tables
    • Error Tables and DML Error Logging
    • Error Tables and Data Rules
    • Using Error Tables for DML Error Logging and Data Rules
  • Enabling DML Error Logging
    • DML Error Logging and ETL
  • DML Error Logging Limitations
• Debugging a Mapping
  • Starting a Debug Session
  • The Debug Panels of the Mapping Editor
    • Debug Info Panel
    • Debug Data Panel
  • Defining Test Data
    • Creating New Tables to Use as Test Data
    • Editing the Test Data
  • Setting Breakpoints
  • Setting Watches
  • Running the Mapping
    • Selecting the First Source and Path to Debug
    • Debugging Mappings with Correlated Commit
    • Setting a Starting Point
    • Debugging Pluggable Submap Operators
  • Re-Initializing a Debug Session
  • Scalability

8 Designing Process Flows

• About Process Flows
  • About Process Flow Modules and Packages
• Instructions for Defining Process Flows
  • Creating Process Flow Modules
  • Creating Process Flow Packages
  • Creating Process Flows
• About the Process Flow Editor
  • Standard Editor Components
  • Process Flow Editor Windows
  • Opening the Process Flow Editor
  • Navigating the Process Flow Editor
• Adding Activities to Process Flows
  • About Activities
  • Adding Activities
  • Parameters for Activities
• Creating and Using Activity Templates
  • Name and Description Page
  • Parameters Page
  • Using Activity Templates
• About Transitions
  • Rules for Valid Transitions
  • Connecting Activities
  • Configuring Activities
  • Using Parameters and Variables
  • Using Namespace
  • Using Bindings
• Expressions
  • Global Expression Values
• Defining Transition Conditions

9 Understanding Performance and Advanced ETL Concepts

• Best Practices for Designing PL/SQL Mappings
  • Set Based Versus Row Based Operating Modes
    • Set Based
    • Row Based
    • Row Based (Target Only)
  • About Committing Data in Warehouse Builder
  • Committing Data Based on Mapping Design
    • Committing Data from a Single Source to Multiple Targets
    • Automatic Commit versus Automatic Correlated Commit
    • Embedding Commit Logic into the Mapping
  • Committing Data Independently of Mapping Design
  • Running Multiple Mappings Before Committing Data
    • Committing Data at Runtime
    • Committing Mappings through the Process Flow Editor
  • Ensuring Referential Integrity in PL/SQL Mappings
• Best Practices for Designing SQL*Loader Mappings
  • Using Conventional Loading to Ensure Referential Integrity in SQL*Loader Mappings
    • Maintaining Relationships Between Master and Detail Records
    • Extracting and Loading Master-Detail Records
    • Error Handling Suggestions
    • Subsequent Operations
  • Using Direct Path Loading to Ensure Referential Integrity in SQL*Loader Mappings
• Improved Performance through Partition Exchange Loading
  • About Partition Exchange Loading
  • Configuring a Mapping for PEL
  • Direct and Indirect PEL
    • Using Indirect PEL
    • Example: Using Direct PEL to Publish Fact Tables
  • Using PEL Effectively
  • Configuring Targets in a Mapping
    • Step 1: Create All Partitions
    • Step 2: Create All Indexes Using the LOCAL Option
    • Step 3: Primary/Unique Keys Use "USING INDEX" Option
  • Restrictions for Using PEL in Warehouse Builder
• High Performance Data Extraction from Remote Sources

10 Introducing Oracle Warehouse Builder Transformations

• About Transforming Data Using Warehouse Builder
  • Benefits of Using Warehouse Builder for Transforming Data
• About Transformations
  • Types of Transformations
    • Predefined Transformations
    • Custom Transformations
• About Transformation Libraries
  • Types of Transformation Libraries
  • Accessing Transformation Libraries
• Defining Custom Transformations
  • Defining Functions and Procedures
    • Name and Description Page
    • Parameters Page
    • Implementation Page
    • Summary Page
  • Defining PL/SQL Types
    • About PL/SQL Types
    • Usage Scenario for PL/SQL Types
    • Creating PL/SQL Types
    • Name and Description Page
    • Attributes Page
    • Return Type Page
    • Summary Page
• Editing Custom Transformations
  • Editing Function or Procedure Definitions
    • Name Tab
    • Parameters Tab
    • Implementation Tab
  • Editing PL/SQL Types
    • Name Tab
    • Attributes Tab
    • Return Type Tab
• Importing PL/SQL
  • Restrictions on Using Imported PL/SQL

11 Deploying to Target Schemas and Executing ETL Logic

• About Deployment and Execution in Warehouse Builder
  • About Deployment
    • Deployment Actions
    • Deployment Status
  • About Execution
  • About the Warehouse Builder Implementation Environment
  • About Control Centers
    • Creating a Control Center
    • Activating a Control Center
  • About Locations
    • Creating Locations
    • Registering and Unregistering Locations
    • Deleting Locations
  • About Connectors
• The Deployment and Execution Process
  • Deploying Objects
  • Deploying Business Definitions to Oracle Discoverer
    • Deploying Business Definitions Directly to Oracle Discoverer
    • Deploying Business Definitions to Earlier Versions of Oracle Discoverer
    • Deploying Business Definitions Using the Core Functionality
  • Reviewing the Deployment Results
  • Starting ETL Jobs
    • Viewing the Data
  • Scheduling ETL Jobs
• Configuring the Physical Details of Deployment
  • About Configurations
    • Creating New Configurations
    • Activating Configurations
  • Creating Additional Configurations
    • Scenario Requiring Multiple Configurations
    • Setting Configuration Properties for a Named Configuration
    • Deploying a Design to Multiple Target Systems
    • Benefit of Creating Additional Configurations
• About Schedules
• Process for Defining and Using Schedules
  • Example Schedules

Part II Example Cases

12 Loading Data Stored in a Microsoft Excel File

• Case Study
• Troubleshooting

13 Connecting to SQL Server and Importing Metadata

• Creating an ODBC Data Source
• Configuring the Oracle Database Server
  • Creating a Heterogeneous Service Configuration File
  • Editing the listener.ora file
• Adding the SQL Server as a Source in Warehouse Builder
• What's Next
• Troubleshooting

14 Loading Transaction Data

15 The Fastest Way to Load Data from Flat Files

• SQL *Loader
  • When To Use SQL*Loader
• External Tables
  • Benefits of Using External Tables
  • When To Use External Tables
• Solution 1: Using SQL*Loader
• Solution 2: Using External Tables

16 Importing from CA ERwin and Other Third-Party Design Tools

17 Reusing Existing PL/SQL Code

18 Sourcing from Flat Files with Variable Names

• Creating the Process Flow
• Setting Parameters for the External Process Activity
  • Method 1: Write a script within Warehouse Builder
  • Method 2: Call a script maintained outside of Warehouse Builder
• Configuring the External Process Activity
• Designing the Mapping
• Deploying and Executing
• Subsequent Steps
  • Creating a Schedule

19 Transferring Remote Files

• Creating the Process Flow
• Setting Parameters for the FTP Activity
  • Example: Writing a Script in Warehouse Builder for the FTP Activity
  • Using Substitution Variables
• Configuring the FTP Activity
• Registering the Process Flow for Deployment
• Defining Locations

20 Inspecting Error Logs in Warehouse Builder

21 Updating the Target Schema

22 Managing Multiple Versions of a BI Implementation

• Approach
  • Initial Phase
    • Case Study
  • Mature Phase
    • Case Study

Index