Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)
Part Number B31278-02
Home
Book List
Index
Master Index
Contact Us
Next
View PDF
Contents
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Conventions
Getting Help
Related Publications
What's New
New in Oracle Warehouse Builder 11
g
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