Contents

Title and Copyright Information

Preface

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

Part I Data Modeling

1 Designing Source and Target Schemas

• Designing Target Schemas
  • Creating Target Modules
  • Designing Relational Target Schemas
  • Designing Dimensional Target Schemas
• Configuring Data Objects
• Validating Data Objects
  • Viewing Validation Results
  • Editing Invalid Objects
• Generating Data Objects
  • Viewing Generation Results and Generated Scripts
  • Saving Generated Scripts to a File

2 Creating Relational Data Objects

• Overview of Data Objects
  • Supported Data Types
  • About Object Class Definition
    • About First Class Objects (FCOs)
    • About Second Class Objects (SCOs)
    • About Third Class and Fourth Class Objects
  • Naming Conventions for Data Objects
  • Using the Data Viewer to View Data Stored in Data Objects
  • About Error Tables
    • Defining Error Tables for Data Objects
    • Error Table Columns
• Defining Tables
  • Creating Table Definitions
    • Name Tab
    • Columns Tab
    • Keys Tab
    • Indexes Tab
    • Partitions Tab
    • Attribute Sets Tab
    • Data Rules Tab
  • Editing Table Definitions
    • Renaming Tables
    • Adding, Modifying, and Deleting Table Columns
    • Adding, Modifying, and Deleting Table Constraints
    • Adding, Modifying, and Deleting Attribute Sets
    • Reordering Columns in a Table
• Defining Views
  • Creating View Definitions
    • Name Tab
    • Columns Tab
    • Query Tab
    • Keys Tab
    • Attribute Sets Tab
    • Data Rules Tab
  • Editing View Definitions
    • Renaming Views
    • Adding, Modifying, and Deleting View Columns
    • Adding, Modifying, and Deleting View Constraints
    • Adding, Modifying, and Deleting Attribute Sets
• Defining Materialized Views
  • Creating Materialized View Definitions
    • Columns Tab
    • Query Tab
    • Keys Tab
    • Indexes Tab
    • Partitions Tab
    • Attribute Sets Tab
    • Data Rules Tab
  • Editing Materialized View Definitions
    • Renaming Materialized Views
    • Adding, Modifying, and Deleting Materialized View Columns
    • Adding, Modifying, and Deleting Materialized View Constraints
    • Adding, Modifying, and Deleting Attribute Sets
• Defining Constraints
  • About Constraints
  • Creating Constraints
    • Defining Primary Key Constraints
    • Defining Foreign Key Constraints
    • Defining Unique Key Constraints
    • Defining Check Constraints
  • Editing Constraints
• Defining Indexes
  • Creating Indexes
• Defining Partitions
  • Range Partitioning
    • Example of Range Partitioning
  • Hash Partitioning
  • Hash by Quantity Partitioning
  • List Partitioning
  • Composite Partitioning
    • About the Subpartition Template
    • Creating Custom Subpartitions
  • Index Partitioning
    • Index Performance Considerations
  • Configuring Partitions
• Defining Attribute Sets
  • Creating Attribute Sets
  • Editing Attribute Sets
• Defining Sequences
  • About Sequences
  • Creating Sequence Definitions
  • Editing Sequence Definitions
    • Name Tab
    • Columns Tab
• Defining User-Defined Types
  • About Object Types
  • Defining Object Types
    • Name Tab
    • Columns Tab
  • Editing Object Types
  • About Varrays
  • Defining Varrays
    • Name Tab
    • Details Tab
  • Editing Varrays
  • About Nested Tables
  • Defining Nested Tables
    • Name Tab
    • Details Tab
  • Editing Nested Tables
• Defining Queues
  • Creating Queue Table Definitions
    • Defining the Payload Type of Queue Tables
  • Editing Queue Tables
  • Creating Advanced Queue Definitions
    • Specifying the Queue Table on which the AQ is Based
  • Editing Advanced Queue Definitions
  • Creating Queue Propagations
    • Selecting a Target Queue for Propagation
  • Editing Queue Propagations
• Configuring Relational Data Objects
  • Configuring Target Modules
    • Deployment System Type
    • Generation Preferences
    • Generation Target Directories
    • Identification
    • Run Time Directories
    • Tablespace Defaults
  • Configuring Tables
    • Error Table
    • Foreign Keys
    • Identification
    • Parallel
    • Performance Parameters
    • Partition Parameters
    • Storage Space
    • Change Data Capture
  • Configuring Materialized Views
    • Materialized View Parameters
    • Materialized View Log Parameters
    • Fast Refresh for Materialized Views
  • Configuring Views
  • Configuring Sequences
  • Configuring Advanced Queues
  • Configuring Queue Tables
  • Configuring Queue Propagations
• Creating Relational Data Objects in Microsoft SQL Server and IBM DB2 UDB
  • Rules for Naming Objects in IBM DB2 UDB
  • Rules for Naming Objects in Microsoft SQL Server

3 Defining Dimensional Objects

• Overview of Dimensional Objects
  • Overview of Dimensions
    • Overview of Surrogate Identifiers
  • Overview of Slowly Changing Dimensions
    • Overview of Defining Type 2 Slowly Changing Dimensions
    • Overview of Hierarchy Versioning
    • Overview of Defining Type 3 Slowly Changing Dimensions (SCDs)
  • Overview of Cubes
  • Orphan Management for Dimensional Objects
    • Orphan Management While Loading Data Into Dimensional Objects
    • Orphan Management While Removing Data From Dimensional Objects
    • Error Tables
• Overview of Implementing Dimensional Objects
  • Relational Implementation of Dimensional Objects
    • Binding
    • Auto Binding
    • Manual Binding
    • Unbinding
  • ROLAP Implementation of Dimensional Objects
  • MOLAP Implementation of Dimensional Objects
    • Analytic Workspace
  • Deployment Options for Dimensional Objects
• Creating Dimensions
  • Dimension Example
  • Creating Dimensions Using the Create Dimension Wizard
    • Name and Description Page
    • Storage Type Page
    • Dimension Attributes Page
    • Levels Page
    • Level Attributes Page
    • Slowly Changing Dimension Page
    • Pre Create Settings Page
    • Dimension Creation Progress Page
    • Summary Page
  • Defaults Used By the Create Dimension Wizard
    • Storage
    • Dimension Attributes
    • Hierarchies
    • Level Attributes
    • Slowly Changing Dimensions
    • Orphan Management Policy
    • Implementation Objects
  • Creating Dimensions Using the Dimension Editor
    • Name Tab
    • Storage Tab
    • Attributes Tab
    • Levels Tab
    • Hierarchies Tab
    • SCD Tab
    • Orphan Tab
    • Specifying the Default Parent for Orphan Rows
    • Physical Bindings Tab
  • Limitations of Deploying Dimensions to the OLAP Catalog
  • Using Control Rows
    • Determining the Number of Rows in a Dimension
• Creating Slowly Changing Dimensions
  • Creating Type 2 Slowly Changing Dimensions Using the Dimension Editor
    • Type 2 Slowly Changing Dimension Dialog Box
  • Updating Type 2 Slowly Changing Dimensions
  • Creating Type 3 Slowly Changing Dimensions Using the Dimension Editor
    • Type 3 Slowly Changing Dimension Dialog Box
• Editing Dimension Definitions
• Configuring Dimensions
  • Specifying How Dimensions are Deployed
• Creating Cubes
  • About Calculated Measures in Cubes
    • Standard Calculation
    • Custom Expression
  • Cube Example
  • Using the Create Cube Wizard to Create Cubes
    • Name and Description Page
    • Storage Type Page
    • Dimensions Page
    • Measures Page
    • Summary Page
  • Defaults Used by the Create Cube Wizard
  • Using the Cube Editor to Create Cubes
    • Name Tab
    • Storage Tab
    • Dimensions Tab
    • Measures Tab
    • Calculated Measure Wizard
    • Aggregation Tab
    • Orphan Tab
    • Physical Bindings Tab
  • Cubes Stored in Analytic Workspaces
    • Ragged Cube Data
    • Defining Aggregations
    • Auto Solving MOLAP Cubes
    • Solving Cube Measures
    • Solving Cubes Independent of Loading
    • Parallel Solving of Cubes
    • Output of a MOLAP Cube Mapping
  • Restriction on Referencing a Non-Leaf Level
• Editing Cube Definitions
• Configuring Cubes
  • Specifying How Cubes are Deployed
• Creating Time Dimensions
  • Creating a Time Dimension Using the Time Dimension Wizard
    • Name and Description Page
    • Storage Page
    • Data Generation Page
    • Levels Page (Calendar Time Dimension Only)
    • Levels Page (Fiscal Time Dimension Only)
    • Pre Create Settings Page
    • Time Dimension Progress Page
    • Summary Page
  • Defaults Used by the Time Dimension Wizard
  • Editing Time Dimension Definitions
    • Name Tab
    • Storage Tab
    • Attributes Tab
    • Levels Tab
    • Hierarchies Tab
  • Modifying the Implementation of Time Dimensions
• Populating Time Dimensions
  • Dynamically Populating Time Dimensions
  • Overlapping Data Populations

Part II Performing ETL

4 Overview of Transforming Data

• About Data Transformation in Oracle Warehouse Builder
• About Mappings
• About Using Operators
  • Types of Operators
    • Source and Target Operators
    • Transformation Operators
    • Pre/Post Processing Operators
    • Pluggable Mapping Operators
    • Real-time Data Warehousing Operators
• About Transformations
  • Types of Transformations
    • Predefined Transformations
    • Custom Transformations
• About Transformation Libraries
  • Types of Transformation Libraries
  • Accessing Transformation Libraries

5 Creating PL/SQL Mappings

• Overview of Oracle Warehouse Builder Mappings
  • Types of Mappings
    • PL/SQL Mappings
    • SQL*Loader Mappings
    • SAP ABAP Mappings
    • Code Template (CT) Mappings
  • About Chunking for PL/SQL Mappings
    • Types of Chunking
    • Methods of Performing Parallel Chunking
• About Operators
• Overview of the Mapping Editor
  • Mapping Editor Canvas
  • Logical View
  • Execution View
  • Execution View Menu and Toolbars
  • Mapping Editor Display Options
• Example: Defining a Simple PL/SQL Mapping
• Steps to Perform Extraction, Transformation, and Loading (ETL) Using Mappings
• Defining Mappings
  • Rules for Naming Mappings
• Adding Operators to Mappings
  • Using the Add Operator Dialog Box to Add Operators
    • Create Unbound Operator with No Attributes
    • Select from Existing Repository Object and Bind
  • Using Pseudocolumns ROWID and ROWNUM in Mappings
• Connecting Operators, Groups, and Attributes
  • Connecting Operators
  • Connecting Groups
  • Connecting Attributes
  • Using the Mapping Connection Dialog Box
    • Attribute Group to Connect
    • Connection Options
    • Messages
    • Connections
• Editing Operators
  • Name Tab
  • Groups Tab
  • Input and Output Tabs
  • Using Display Sets
    • Defining Display Sets
    • Selecting a Display Set
• Setting Mapping Properties
  • Specifying the Order in Which Target Objects in a Mapping Are Loaded
    • Reset to Default
• Configuring Mappings
  • Steps to Configure Mappings
• Synchronizing Operators and Workspace Objects
  • Synchronizing a Mapping Operator with its Associated Workspace Object
    • Synchronizing All Operators in a Mapping
  • Synchronizing a Workspace Object with a Mapping Operator
    • Steps to Synchronize a Workspace Object with a Mapping Operator
  • Advanced Options for Synchronizing
    • Matching Strategies
• Example: Using a Mapping to Load Transaction Data
• Example: Using the Mapping Editor to Create Staging Area Tables
• Using Pluggable Mappings
  • Creating Pluggable Mappings
    • Creating Standalone Pluggable Mappings
    • Signature Groups
    • Input Signature
    • Output Signature
    • Creating Pluggable Mapping Folders
    • Creating User Folders Within Pluggable Mapping Libraries
• Copying Operators Across Mappings and Pluggable Mappings
  • Limitations of Copying Operators, Groups, and Attributes
• Grouping Operators in Mappings and Pluggable Mappings
  • Steps to Group Operators in Mappings and Pluggable Mappings
    • Viewing the Contents of a Folder
  • Steps to Ungroup Operators in Mappings and Pluggable Mappings
  • Spotlighting Selected Operators
• Locating Operators, Groups, and Attributes in Mappings and Pluggable Mappings
  • Steps to Perform a Regular Search
  • Steps to Perform an Advanced Search
  • Advanced Find Dialog Box
• Debugging Mappings
  • General Restrictions in the Mapping Debugger
  • Starting a Debug Session
  • Debug Panels of the Design Center
    • Info Panel
    • Data Panel
  • Defining Test Data
    • Creating New Tables to Use as Test Data
    • Editing the Test Data
    • Cleaning Up Debug Objects in the Runtime Schema
  • 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
  • ReInitializing a Debug Session
  • Scalability

6 Performing ETL Using Dimensional Objects

• Using Dimensions in ETL Mappings
  • Loading Data Into Dimensions
    • Loading Data into Type 1 Dimensions
    • Loading Data into Type 2 Slowly Changing Dimensions (SCDs)
    • Loading Data into Type 3 Slowly Changing Dimensions (SCDs)
  • Example: Loading Data Into Type 2 Slowly Changing Dimensions
  • Extracting Data Stored in Dimensions
    • Extracting Data from Dimensions
    • Extracting Data from Type 2 Slowly Changing Dimensions (SCDs)
    • Extracting Data from Type 3 Slowly Changing Dimensions (SCDs)
  • Removing Data from Dimensions
  • Example: Removing Data from Dimensions
• Using Cubes in ETL Mappings
  • Loading Data Into Cubes

7 Using SQL*Loader, SAP, and Code Template Mappings

• Creating SQL*Loader Mappings to Extract Data from Flat Files
  • Extracting Data from Flat Files
  • Loading Data into a Flat File
  • Creating a New Flat File Target
• Creating SAP Extraction Mappings
  • Defining an SAP Extraction Mapping
    • Adding SAP Tables to the Mapping
    • Setting the Loading Type
    • Setting Configuration Properties for the Mapping
    • Setting the Join Rank
• Retrieving Data from the SAP System
  • Automated System
  • Semiautomated System
  • Manual System
• Creating Code Template (CT) Mappings
  • About Prebuilt Code Templates Shipped with Oracle Warehouse Builder
    • Limitations of Using Certain Prebuilt Code Templates
  • Mapping Operators Only Supported in Oracle Target CT Execution Units
  • Steps to Perform ETL Using Code Template Mappings
  • Creating Template Mapping Modules
  • Creating Mappings Using Code Templates
  • Defining Execution Units
    • Execution View Menu and Toolbars
    • Creating Execution Units
    • Adding Operators to an Execution Unit
    • Adding Operators to Multiple Execution Units
    • Removing Operators from an Execution Unit
    • Removing Execution Units
    • Creating Default Execution Units
    • Default Code Template for An Execution Unit
    • How Oracle Warehouse Builder Displays Code Templates that Can be Associated with Execution Units
  • Starting the Control Center Agent (CCA)
  • Validating Code Template Mappings
  • Generating Code Template Mappings
    • Sample Code Generated for CT Mappings
  • Deploying Code Template Mappings
  • Executing Code Template Mappings
  • Viewing Execution Results for Code Template Mappings
    • Viewing Execution Results by Using the Results Tab
    • Viewing Execution Results by Using the Audit Information Panel
• Setting Options for Code Templates in Code Template Mappings
  • Setting Properties for Bound Operators in CT Mappings
  • Setting Configuration Properties for Code Template Mappings
• Auditing the Execution of Code Template Mappings
  • Steps to Audit the Execution of Code Template Mappings
• Using Code Template Mappings to Perform Change Data Capture (CDC)
  • Types of Change Data Capture (CDC)
  • Change Data Capture Commands
  • Example: Performing Change Data Capture Using Code Templates
    • Steps to Perform Change Data Capture Using CDC CTs
    • Selecting the Objects for Change Data Capture
    • Creating the Mapping that Loads Changes
    • Deploying the Change Data Capture Solution
    • Starting the Change Data Capture Process
    • Adding a Subscriber to the Change Data Capture Process
    • Testing the Change Data Capture Process
    • Performing Change Data Capture Actions in Oracle Warehouse Builder
• Using Control Code Templates
  • Example: Checking Data Constraints Using Control CTs
    • Steps to Log Constraint Violations While Loading Data Into a Target Table
    • Creating the Source Module and Importing Source Objects
    • Creating the Code Template Mapping that Extracts Data, Checks Data Integrity, and Loads Data into an Oracle Target
• Using Oracle Target CTs in Code Template Mappings
  • Example: Using Oracle Target Code Templates
    • Creating the Source Module and Importing Source Objects
    • Creating the Target Module and Target Table
    • Creating the CT Mapping that Transforms Source Data Using Oracle Target CTs
• Moving Data from Heterogeneous Databases to Oracle Database
  • Example: Moving Data from IBM DB2 to Oracle Database Using Integration CTs and Load CTs
    • Steps to Extract Data from IBM DB2, Transform Data, and Load it into an Oracle Database
    • Create the Source Module
    • Create the Target Module and Target Table
    • Create the CT Mapping that Extracts, Transforms, and Loads Data

8 Designing Process Flows

• Overview of Process Flows
  • About Process Flow Modules and Packages
• Example: Creating a Basic Process Flow
• Steps for Defining Process Flows
  • Creating Oracle Workflow Locations
  • Creating Process Flow Modules
    • Creating User Folders Within a Process Flow Module
  • Creating Process Flow Packages
  • Creating Process Flows
• 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 a Namespace
  • Using Bindings
• About Expressions
  • Global Expression Values
• Defining Transition Conditions
• Example: Using Process Flows to Access Flat Files with Variable Names
  • Creating the Process Flow
  • Setting Parameters for the User Defined Activity
    • Method 1: Write a script Within Oracle Warehouse Builder
    • Method 2: Call a script maintained outside of Oracle Warehouse Builder
  • Configuring the User Defined Activity
  • Designing the Mapping
  • Deploying and Executing
  • Subsequent Steps
• Example: Using Process Flows to Transfer Remote Files
  • Defining Locations
  • Creating the Process Flow
  • Setting Parameters for the FTP Activity
    • Example: Writing a Script in Oracle Warehouse Builder for the FTP Activity
    • Using Substitution Variables
  • Configuring the FTP Activity
  • Registering the Process Flow for Deployment

9 Defining Custom Transformations

• About Transforming Data Using Oracle Warehouse Builder
  • Benefits of Using Oracle Warehouse Builder for Transforming Data
• Defining Custom Transformations
  • Defining Functions and Procedures
    • Naming the Custom Transformation
    • Defining the Parameters
    • Specifying the Implementation
  • Defining Table Functions
    • Naming the Table Function
    • Specifying the Return Type
    • Specifying Table Function Input and Output Parameters
    • Specifying Parallelism Options
    • Specifying Data Streaming Options
    • Specifying the Table Function Implementation
  • 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
  • Editing PL/SQL Types
    • Name Tab
    • Attributes Tab
    • Return Type Tab
  • Editing Table Functions
• Importing Transformations
  • Restrictions on Using Imported PL/SQL
• Example: Reusing Existing PL/SQL Code
• Using Functions In Non-Oracle Platforms
  • Creating IBM DB2 and SQL Server Functions
    • Defining IBM DB2 and SQL Server Functions
    • Importing a Function
    • Predefined Generic Heterogeneous Functions
    • Using the Functions in Mappings
• Configuring Functions
  • Configuring Oracle Functions
    • AUTHID
    • Deterministic
    • Parallel Enable
    • Pragma Autonomous Transaction

10 Understanding Performance and Advanced ETL Concepts

• Best Practices for Designing PL/SQL Mappings
  • Set-Based Versus Row-Based Operating Modes
    • Set-Based Mode
    • Row-Based Mode
    • Row-Based (Target Only) Mode
  • About Committing Data in Oracle 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 Oracle Warehouse Builder
• High Performance Data Extraction from Remote Sources

11 Scheduling ETL Jobs

• Overview of Schedules
• Defining Schedules
  • Editing Schedules
    • Start and End Dates and Times
    • Defining Schedules To Repeat
  • Example Schedules
• Applying Schedules to ETL Objects
• Scheduling ETL Jobs in Oracle Enterprise Manager
  • The SQLPLUS_EXEC_TEMPLATE SQL Script
  • The WB_RT_API_EXEC.RUN_TASK Function

12 Deploying to Target Schemas and Executing ETL Logic

• Overview of Deployment and Execution in Oracle Warehouse Builder
  • About Deployment
    • About Deployment Actions
    • About Deployment Status
    • About Deploying Dimensional Objects
    • About Deploying Mappings and Process Flows
    • About Deploying Code Template (CT) Mappings and Web Services
    • About Deploying Schedules
  • About Execution
  • About Configurations
  • About Viewing and Setting Configuration Properties for Different Configurations
• Steps in the Deployment and Execution Process
• Deploying Objects
  • Deploying Objects Using the Control Center Manager
  • Deploying Objects Using the Projects Navigator
  • Deploying Target Systems to a Remote System
  • Reviewing Deployment Results
• Starting ETL Jobs
  • Viewing Execution Results for ETL Jobs
  • Viewing the Data
  • Scheduling ETL Jobs
• Starting ETL Jobs in SQL*Plus
  • Managing Jobs Using SQL Scripts
• Example: Updating a Target Schema

13 Auditing Deployments and Executions

• About Auditing Deployment and Executions
  • About the Repository Browser
  • About the Heterogeneous Repository Browser (HRAB)
    • Differences Between Repository Browser and Heterogeneous Repository Browser
    • Installing the Heterogeneous Repository Browser on Heterogeneous Databases and OC4J Servers
    • Creating Data Stores
  • Types of Auditing
  • List of Heterogeneous Repository Browser Reports
  • Viewing Audit Reports
• Opening the Repository Browser
  • Managing the Repository Browser Listener
  • Accessing the Repository Browser
  • Logging in to a Workspace
    • Connecting to an Oracle Database
    • Connecting to a Heterogeneous Database or OC4J Server
• Design Reports
  • Repository Navigator
  • Object Properties
  • Object Reports
    • Summary Reports
    • Detailed Reports
    • Implementation Reports
    • Impact Analysis Reports
  • Object Lineage
  • Object Impact
• Control Center Reports
  • Deployment Reports
    • Deployment Schedule Report
    • Locations Report
    • Object Summary Report
    • Location Object Summary Report
    • Deployment Report
    • Deployment Error Detail Report
  • Execution Reports
    • Execution Schedule Report
    • Execution Summary Report
    • Execution Report
    • Error Table Execution Report
    • Execution Job Report
    • Trace Report
    • Job File Report
    • Job Start Report
    • Job Error Diagnostic Report
  • Management Reports
    • Service Node Report
    • Location Validation Report
• Common Repository Browser Tasks
  • Identifying Recently-Run Processes
  • Identifying Why a Process Run Failed
  • Comparing Process Runs
  • Discovering Why a Map Run Gave Unexpected Results
  • Identifying Recently-Made Deployments
  • Identifying the Data Objects That Are Deployed to a Specific Location
  • Identifying the Map Runs that Use a Specific Deployed Data Object
  • Discovering the Default Deployment Time Settings of a Deployed Process
  • Rerunning a Process
  • Monitoring a Process Run
  • Terminating a Process Run
  • Removing the Execution Audit Details for a Process
  • Removing Old Deployment Audit details
  • Viewing Error Tables Created as a Result of Data Auditor Execution
  • Unregistering a Location
  • Updating Location Connection Details for a Changed Database Environment
  • Updating Service Node Details in a Changing Oracle RAC Environment

14 Managing Metadata Dependencies

• About the Metadata Dependency Manager
  • Example: Lineage and Impact Analysis (LIA)
  • About Lineage and Impact Analysis and Metadata Dependency Diagrams
• Opening an LIA Diagram
• Managing and Exploring Objects in an LIA Diagram
  • Exploring Object Lineage and Impact in an LIA Diagram
  • Using Find to Search for Objects in an LIA Diagram
  • Using Groups in an LIA Diagram
    • Managing Groups in an LIA Diagram
  • Displaying an Object's Attributes
  • Exporting and Printing LIA Diagrams
• Making Changes to Design Metadata Using Automatic Change Propagation
  • Automated Change Propagation in the Dependency Manager

15 Troubleshooting and Error Handling for ETL Designs

• Inspecting Error Logs in Oracle Warehouse Builder
  • Troubleshooting Validation Errors
  • Troubleshooting Generation Errors
  • Troubleshooting Deployment and Execution Errors
    • Determining the Operators that Caused Errors in Mappings
  • Troubleshooting Name and Address Server Errors
• Using DML Error Logging
  • About DML Error Tables
  • Enabling DML Error Logging
    • DML Error Logging and ETL
  • DML Error Logging Limitations
• Troubleshooting the ETL Process
  • ORA-04063 While Running Hybrid Maps
  • Agent Log Files
  • Error Starting the Control Center Agent (CCA)
  • Error Executing Web Services from the Secure Web Site
  • REP-01012 While Deploying Mappings to a Target Schema
  • Unable to Delete a Location

16 Creating and Consuming Web Services in Oracle Warehouse Builder

• Introduction to Web Services
  • Advantages of Web Services
  • About Web Services in Oracle Warehouse Builder
    • About Defining Web Services
    • About Publishing Web Services
    • About Consuming Web Services
  • About Public Web Services
• Publishing Oracle Warehouse Builder Objects as Web Services
  • Creating Web Service Packages
  • Creating Web Services Based on Oracle Warehouse Builder Objects
    • Naming the Web Service
    • Defining the Web Service Implementation
  • Validating Web Services
  • Generating Web Services
  • Deploying Web Services
    • Deploying Web Services Using the Control Center Manager
    • Deploying Web Services Using the Design Center
• Creating Web Services Based on a URL
  • Naming and Describing a Public Web Service
• Executing Web Services
  • Using the Control Center Manager to Run Web Services
  • Using a Browser to Run Web Services
  • Performing Operations on Web Services Using a Browser
    • Determining If a Web Service or Application Was Deployed to an OC4J Server
    • Executing a Control Center Job
    • Terminating an Execution Job
    • Running Deployed Applications
• Using Web Services as Activities in Process Flows
  • Rules for Using Web Services in Process Flows
  • Steps to Use Web Services in Process Flows
  • Synchronizing Web Service Activities with Their Referenced Web Services
• Using Web Services in Mappings
• Using Secure Sockets Layer (SSL) to Access Web Services Securely
  • J2EE Roles for Control Center Agent Security
  • Setting Up Secure Access on External OC4J Servers
  • Updating the Key Store Password
• Case Study: Using Web Services for Data Integration
  • Example: Publishing Mappings as Web Services
  • Example: Consuming Web Services in Process Flows
    • Modify the LOAD_TOT_SALES_CT_MAP Code Template (CT) Mapping
    • Import the Currency Converter Web Service
    • Create a Process Flow That Consumes the Currency Converter Web Service
  • Example: Integrating Oracle Warehouse Builder Web Services with Oracle BPEL Process Manager

17 Moving Large Volumes of Data Using Transportable Modules

• About Transportable Modules
  • About Transportable Modules and Oracle Database Technology
• Benefits of Using Transportable Modules
• Instructions for Using Transportable Modules
  • Verifying the Requirements for Using Transportable Modules
  • Specifying Locations for Transportable Modules
    • Transportable Module Source Location Information
  • Creating a Transportable Module
    • Describing the Transportable Module
    • Selecting the Source Location
    • Selecting the Target Location
    • Selecting Tablespaces and Schema Objects to Import
    • Available Database Objects
    • Reviewing the Transportable Module Definitions
  • Configuring a Transportable Module
    • Transportable Module Configuration Properties
    • Schema Configuration Properties
    • Target DataFile Configuration Properties
    • Tablespace Configuration Properties
  • Generating and Deploying a Transportable Module
  • Designing Mappings that Access Data through Transportable Modules
• Editing Transportable Modules
  • Name
  • Source Location
  • Tablespaces
  • Target Locations
  • Viewing Tablespace Properties
  • Reimporting Metadata into a Transportable Module

Part III Data Profiling and Data Quality

18 Performing Data Profiling

• Overview of Data Profiling
  • Sources Supported by Oracle Warehouse Builder for Data Profiling
  • Using Oracle Warehouse Builder Data Profiling with Oracle Warehouse Builder ETL
  • Using Oracle Warehouse Builder Data Profiling with Other ETL Solutions
  • About the Data Profile Editor
• Performing Data Profiling
  • Data Profiling Restrictions
  • Prerequisites for Data Profiling
  • Steps to Perform Data Profiling
  • Creating Data Profiles
  • Configuring Data Profiles
    • Steps to Configure Data Profiles
    • Load Configuration Parameters
    • Aggregation Configuration Parameters
    • Pattern Discovery Configuration Parameters
    • Domain Discovery Configuration Parameters
    • Relationship Attribute Count Configuration Parameters
    • Unique Key Discovery Configuration Parameters
    • Functional Dependency Discovery Configuration Parameters
    • Row Relationship Discovery Configuration Parameters
    • Redundant Column Discovery Configuration Parameters
    • Performance Configuration
    • Data Rule Profiling Configuration Parameters
  • Profiling Data
    • Steps to Profile Data
  • Viewing Profile Results
    • Data Profile
    • Profile Object
    • Aggregation
    • Data Type
    • Domain
    • Pattern
    • Unique Key
    • Functional Dependency
    • Referential
    • Data Rule
  • Using Attribute Sets to Profile a Subset of Columns from a Data Object
    • Defining Attribute Sets
    • Creating a Data Profile That Contains the Attribute Set
  • Editing Data Profiles
    • Adding Data Objects to a Data Profile
• Tuning the Data Profiling Process for Better Profiling Performance
  • Tuning the Data Profile for Better Data Profiling Performance
  • Tuning the Oracle Database for Better Data Profiling Performance
    • Multiple Processors
    • Memory
    • I/O System
• Data Watch and Repair (DWR) for Oracle Master Data Management (MDM)
  • Overview of Data Watch and Repair (DWR) for MDM
    • Predefined Data Rules for MDM
  • Prerequisites for Performing Data Watch and Repair (DWR)
  • Steps to Perform Data Watch and Repair (DWR) Using Oracle Warehouse Builder
    • Importing MDM Data Rules
    • Writing Corrected Data and Metadata to the MDM Application

19 Designing and Deriving Data Rules

• Overview of Data Rules
  • Types of Data Rules
  • Data Rules as Objects and Binding Data Rules
• Using Data Rules
  • Managing Data Rules in Folders
  • Deriving Data Rules From Data Profiling Results
    • Steps to Derive Data Rules
  • Creating Data Rules Using the Create Data Rule Wizard
    • Defining the Data Rule
  • Editing Data Rules
  • Applying Data Rules to Data Objects
  • Applying Data Rules within ETL Mappings Manually

20 Monitoring Quality with Data Auditors and Data Rules

• Overview of Data Auditors
• Monitoring Data Quality Using Data Auditors
  • Creating Data Auditors
    • Specifying Actions for Data That Violates Defined Data Rules
    • Editing Data Auditors
  • Configuring Data Auditors
    • Run Time Parameters
    • Data Auditor Parameters
    • Code Generation Options
  • Auditing Data Objects Using Data Auditors
    • Manually Running Data Auditors
    • Scheduling a Data Auditor to Run
    • Data Auditor Execution Results
  • Viewing Data Auditor Error Tables
    • Granting Privileges on Error Tables

21 Data Cleansing and Correction with Data Rules

• Overview of Data Rules in ETL and Automatic Data Correction
• Generating Correction Mappings from Data Profiling Results
  • Prerequisites for Creating Corrections
  • Steps to Create Correction Objects
  • Selecting the Data Rules and Data Types for Corrected Schema Objects
  • Selecting the Objects to Be Corrected
  • Choosing Data Correction and Cleansing Actions
    • Choosing Data Correction Actions
    • Specifying the Cleansing Strategy
  • Viewing the Correction Tables and Mappings
• Cleansing and Transforming Source Data Based on Data Profiling Results
  • Deploying Schema Corrections
  • Deploying Correction Mappings

22 Name and Address Cleansing

• About Name and Address Cleansing in Oracle Warehouse Builder
  • Types of Name and Address Cleansing Available in Oracle Warehouse Builder
  • Example: Correcting Address Information
    • Example Input
    • Example Steps
    • Example Output
  • About Postal Reporting
    • United States Postal Service CASS Certification
    • Canada Post SERP Certification
    • Australia Post AMAS Certification
  • Input Role Descriptions
  • Descriptions of Output Components
    • Pass Through
    • Name
    • Address
    • Extra Vendor
    • Error Status
    • Country-Specific
  • Handling Errors in Name and Address Data
• Using the Name and Address Operator to Cleanse and Correct Name and Address Data
  • Creating a Mapping with a Name and Address Operator
    • Specifying Source Data Details and Setting Parsing Type
    • Specifying Postal Report Details
• Managing the Name and Address Server
  • Configuring the Name and Address Server
  • Starting and Stopping the Name and Address Server

23 Matching, Merging, and Deduplication

• About Matching and Merging in Oracle Warehouse Builder
  • Example: A Basic Mapping with a Match Merge Operator
  • Overview of the Matching and Merging Process
    • Elements of Matching and Merging Records
    • Process for Matching and Merging Records
  • Match Rules
  • Conditional Match Rules
    • Comparison Algorithms
    • Creating Conditional Match Rules
  • Match Rules: Basic Example
    • Example: Matching and Merging Customer Data
    • Example: How Multiple Match Rules Combine
    • Example of Transitive Matching
  • 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
  • Match ID Merge Rule
  • Rank and Rank Record Merge Rules
  • Sequence Merge Rule
  • Min Max and Min Max Record Merge Rules
  • Copy Merge Rule
  • Custom and Custom Record Merge Rules
• Using the Match Merge Operator to Eliminate Duplicate Source Records
  • Steps to Use a Match Merge Operator
  • Considerations When Designing Mappings Containing Match Merge Operators
    • Restrictions on Using the Match Merge Operator
  • Example: Using Two Match Merge Operators for Householding

Part IV Reference

24 Mappings and Process Flows Reference

• Configuring ETL Objects
• Configuring Mappings Reference
  • Runtime Parameters
    • Analyze Table Sample Percentage
    • Bulk Size
    • Chunk Execute Resume Task
    • Chunk Force Resume
    • Chunk Number of Times to Retry
    • Chunk Parallel Level
    • Commit Frequency
    • Default Audit Level
    • Default Operating Mode
    • Default Purge Group
    • Maximum Number of Errors
    • Number of Threads to Process Chunks
  • Code Generation Options
    • ANSI SQL Syntax
    • Commit Control
    • Analyze Table Statements
    • Enable Parallel DML
    • Optimized Code
    • Authid
    • Use Target Load Ordering
    • ERROR TRIGGER
    • Bulk Processing Code
    • Generation Mode
  • Chunking Options
    • Chunking Method
    • Chunk Table
    • Chunk Column
    • Chunk Size
    • Chunk Type
    • SQL Statement
    • SQL Statement Chunk Type
  • SCD Updates
    • Chunking Strategy
  • Sources and Targets Reference
    • Use LCR APIs
    • Database Link
    • Location
    • Conflict Resolution
    • Schema
    • Partition Exchange Loading
    • Hints
    • Constraint Management
    • SQL*Loader Parameters
  • Configuring Code Template (CT) Mappings
    • Use Enclosure Character
    • SQL Loader Data Files
  • Configuring Flat File Operators
  • Flat File Operators as a Target
  • Flat File Operator as a Source
• Configuring Process Flows Reference

25 Source and Target Operators

• List of Source and Target Operators
• Using Oracle Source and Target Operators
  • Setting Properties for Oracle Source and Target Operators
    • Capture Consistency
    • Change Data Capture Filter
    • Enabled
    • Trigger Based Capture
    • Primary Source
    • Loading Types for Oracle Target Operators
    • Loading Types for Flat File Targets
    • Target Load Order
    • Target Filter for Update
    • Target Filter for Delete
    • Match By Constraint
    • Reverting Constraints to Default Values
    • Bound Name
    • Key Name
    • Key Columns
    • Key Type
    • Referenced Keys
    • Error Table Name
    • Roll up Errors
    • Select Only Errors from this Operator
  • Setting Attribute Properties
    • Bound Name
    • Data Type
    • Precision
    • Scale
    • Length
    • Fractional Seconds Precision
    • Load Column When Inserting Row
    • Load Column When Updating Row
    • Match Column When Updating Row
    • Update: Operation
    • Match Column When Deleting Row
    • Chunking Number Column
  • Constant Operator
  • Construct Object Operator
  • Cube Operator
    • Cube Operator Properties
    • Cube Attribute Properties
  • Data Generator Operator
    • Setting a Column to the Data File Record Number
    • Setting a Column to the Current Date
    • Setting a Column to a Unique Sequence Number
  • Dimension Operator
    • Dimension Operator Properties
  • Expand Object Operator
  • External Table Operator
  • Mapping Input Parameter Operator
  • Mapping Output Parameter Operator
  • Materialized View Operator
  • Queue Operator
    • Using a Queue Operator
    • Selecting the Queue
    • Selecting the Source Type for a Queue Operator
    • Selecting the User-Defined or Primary Type for a Queue Operator
    • Selecting the Source Object
    • Specifying the Source Changes to Process
  • Sequence Operator
  • Table Operator
    • Merge Optimization for Table Operators
    • Chunking for Table Operators
    • Creating Temporary Tables While Performing ETL
    • DML Error Logging
    • Data Rules and Loading Tables
  • Varray Iterator Operator
  • View Operator
    • Using the View Operator for Inline Views
• Using Remote and non-Oracle Source and Target Operators
  • Limitations of Using Non-Oracle or Remote Targets
  • Oracle Warehouse Builder Workarounds for Non-Oracle and Remote Targets
• Using Flat File Source and Target Operators
  • Flat File Operator
    • Flat File Source Operators
    • Flat File Target Operators
    • Setting Properties for Flat File Source and Target Operators
    • Loading Types for Flat Files
    • Field Names in the First Row

26 Data Flow Operators

• List of Data Flow Operators
• About Operator Wizards
  • Operator Wizard General Page
  • Operator Wizard Groups Page
  • Operator Wizard Input and Output Pages
  • Operator Wizard Input Connections
• About the Expression Builder
  • Opening the Expression Builder
    • The Expression Builder User Interface
• Aggregator Operator
  • Group By Clause
  • Having Clause
  • Aggregate Function Expression
• Anydata Cast Operator
• Deduplicator Operator
• Expression Operator
• Filter Operator
  • Adding Self Joins in a Mapping
• Joiner Operator
  • Join Conditions
    • Join Conditions and Cartesian Products
    • Default Join Conditions and Foreign Keys
    • Using Join Conditions for Outer Joins
    • Using Join Conditions for Full Outer Joins
    • Grouping Join Conditions
    • Join Conditions and Join Order
    • Filters in Join Conditions
    • Join Conditions and SAP Sources
  • Join Input Roles and Inner and Outer Joins
    • Specifying Inner and Outer Join Types Using Join Input Roles
    • Outer Join Input Roles with More than Two Input Groups
  • Steps to Use a Joiner Operator in a Mapping
• LCR Cast Operator
• LCR Splitter Operator
• Lookup Operator
  • Using the Lookup Operator
    • Name
    • Groups
    • Lookup Tables
    • Input Attributes
    • Output Attributes
    • Lookup Conditions
    • Multiple Match Rows
    • No-match Rows
    • Type 2 History Lookup
• Pivot Operator
  • Example: Pivoting Sales Data
  • The Row Locator
  • Using the Pivot Operator
    • General
    • Groups
    • Input Connections
    • Input Attributes
    • Output Attributes
    • Pivot Transform
• Post-Mapping Process Operator
• Pre-Mapping Process Operator
• Set Operation Operator
  • Synchronizing the Attributes in a Set Operation Operator
• Sorter Operator
  • Order By Clause
• Splitter Operator
  • Example: Creating Mappings with Multiple Targets
• Subquery Filter Operator
• Table Function Operator
  • Prerequisites for Using the Table Function Operator
    • Input
    • Output
  • Table Function Operator Properties
    • Table Function Operator Properties
    • Input Parameter Properties
    • Output Parameter Group Properties
    • Output Parameter
• Transformation Operator
• Unpivot Operator
  • Example: Unpivoting Sales Data
  • The Row Locator
  • Using the Unpivot Operator
    • General
    • Groups
    • Input Connections
    • Input Attributes
    • Row Locator
    • Output Attributes
    • Unpivot Transform

27 Activities in Process Flows

• Using Activities in Process Flows
  • Activities That Represent Objects
  • Utility Activities
  • Control Activities
  • Operating System Activities
    • Setting a Security Constraint
    • Setting a Proxy Command and Parameters
• AND
• Assign
• Data Auditor Monitor
• Enterprise Java Bean
  • Example: Using an Enterprise Java Bean Activity to Leverage Existing Business Logic from EJBs
  • Example: Using an Enterprise Java Bean Activity to Load Data From one DB2 Table to Another
  • Restrictions on Using an Enterprise Java Bean Activity
• Email
• End
• End Loop
• File Exists
• FORK
• For Loop
• FTP
  • Writing a Script Within Oracle Warehouse Builder
  • Using Substitution Variables
  • Calling a Script Outside of Oracle Warehouse Builder
• Java Class
  • Example of Using a Java Class Activity in a Process Flow
  • Example of Customizing the Java Class Activity Executable
• Manual
• Mapping
• Notification
  • Notification Message Substitution
• OMBPlus
• OR
• Route
• Set Status
• SQL*PLUS
  • Using SQL*PLUS Activities in Process Flows
  • Using Substitution Variables
  • SQL *Plus Command
• Start
• Subprocess
• Transform
• User Defined
• Wait
• While Loop
• Web Service

28 Oracle Warehouse BuilderTransformations Reference

• Predefined Transformations in the Public Oracle Predefined Library
  • Administrative Transformations
    • WB_ABORT
    • WB_COMPILE_PLSQL
    • WB_DISABLE_ALL_CONSTRAINTS
    • WB_DISABLE_ALL_TRIGGERS
    • WB_DISABLE_CONSTRAINT
    • WB_DISABLE_TRIGGER
    • WB_ENABLE_ALL_CONSTRAINTS
    • WB_ENABLE_ALL_TRIGGERS
    • WB_ENABLE_CONSTRAINT
    • WB_ENABLE_TRIGGER
    • WB_TRUNCATE_TABLE
  • Character Transformations
    • WB_LOOKUP_CHAR (number)
    • WB_LOOKUP_CHAR (varchar2)
    • WB_IS_SPACE
  • Control Center Transformations
    • WB_RT_GET_ELAPSED_TIME
    • WB_RT_GET_JOB_METRICS
    • WB_RT_GET_LAST_EXECUTION_TIME
    • WB_RT_GET_MAP_RUN_AUDIT
    • WB_RT_GET_NUMBER_OF_ERRORS
    • WB_RT_GET_NUMBER_OF_WARNINGS
    • WB_RT_GET_PARENT_AUDIT_ID
    • WB_RT_GET_RETURN_CODE
    • WB_RT_GET_START_TIME
  • Conversion Transformations
  • Date Transformations
    • WB_CAL_MONTH_NAME
    • WB_CAL_MONTH_OF_YEAR
    • WB_CAL_MONTH_SHORT_NAME
    • WB_CAL_QTR
    • WB_CAL_WEEK_OF_YEAR
    • WB_CAL_YEAR
    • WB_CAL_YEAR_NAME
    • WB_DATE_FROM_JULIAN
    • WB_DAY_NAME
    • WB_DAY_OF_MONTH
    • WB_DAY_OF_WEEK
    • WB_DAY_OF_YEAR
    • WB_DAY_SHORT_NAME
    • WB_DECADE
    • WB_HOUR12
    • WB_HOUR12MI_SS
    • WB_HOUR24
    • WB_HOUR24MI_SS
    • WB_IS_DATE
    • WB_JULIAN_FROM_DATE
    • WB_MI_SS
    • WB_WEEK_OF_MONTH
  • Number Transformations
    • WB_LOOKUP_NUM (on a number)
    • WB_LOOKUP_NUM (on a varchar2)
    • WB_IS_NUMBER
  • OLAP Transformations
    • WB_OLAP_AW_PRECOMPUTE
    • WB_OLAP_LOAD_CUBE
    • WB_OLAP_LOAD_DIMENSION
    • WB_OLAP_LOAD_DIMENSION_GENUK
  • Other Transformations
  • Spatial Transformations
  • Streams Transformations
    • REPLICATE
  • XML Transformations
    • WB_XML_LOAD
    • WB_XML_LOAD_F

29 Oracle Warehouse Builder Code Template Tools and Substitution Methods Reference

• Code Template Tools Supported in Oracle Warehouse Builder
• Code Template Tools Not Supported in Oracle Warehouse Builder
• Code Template Substitution Methods Supported in Oracle Warehouse Builder
• Code Template Substitution Methods Not Supported in Oracle Warehouse Builder

Index