1/35

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)

1 Introduction to Oracle Warehouse Builder

• Overview of Oracle Warehouse Builder
  • Data Consolidation and Integration

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
  • Deleting Objects in Warehouse Builder
    • Restoring Deleted Objects from the Recycle Bin
• 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

Part I Sources and Targets

4 Identifying Data Sources and Importing Metadata

• About Source Data and Metadata
• Supported Sources and Targets
• General Steps for Importing Metadata from Sources
  • About Locations
    • Creating Locations
    • Granting Privileges to a Target Location
    • Registering and Unregistering Locations
    • Deleting Locations
  • About Connectors
  • About Modules
    • Creating Modules
  • Example: Importing Metadata from Flat Files
• Using the Import Metadata Wizard
  • Importing Definitions from a Database
    • Filter Information Page
    • Object Selection Page
    • Summary and Import Page
    • Import Results Page
• 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

5 Importing Design Definitions from Third Party Design Tools

• Using Design Definitions from Oracle Designer 6i/9i
  • Using Designer 6i/9i as a Metadata Source
• Example: Importing from CA Erwin

6 Integrating with Applications

• 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

7 Retrieving Data From SAP Applications

• Why SAP Connector
• Supported SAP Versions
• Overview of SAP Objects
  • SAP Object Types
  • SAP Business Domains
• Overview of the Warehouse Builder-SAP Interaction
• Implementing an SAP Data Retrieval Mechanism
• Connecting to an SAP System
  • Required Files For SAP Connector
  • Troubleshooting Connection Errors
  • Creating SAP Module Definitions
    • Connecting to an SAP System
• Importing Metadata from SAP Tables
  • Importing SAP Metadata Definitions
    • Filtering SAP Metadata
    • Selecting Objects for Metadata Import
    • Reviewing Import Summary
    • Reimporting SAP Tables
  • Analyzing Metadata Details
• 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
  • Semi Automated System
  • Manual System

8 Flat Files as Sources or Targets

• About Flat Files
  • Flat Files as Sources
    • Importing ASCII Files into the Workspace
    • Adding Existing Binary Files to the Workspace
    • About External Tables
    • External Table Operators versus Flat File Operators
  • Flat Files as Targets
  • Creating Flat File Modules
    • Describing the Flat File Module
    • Defining Locations for Flat File Modules
    • Connection Information Page
    • Edit File System Location Dialog Box
• Using the Create Flat File Wizard
  • Describing a New Flat File
  • Defining File Properties for a New Flat File
    • Record Organization
    • Logical Record Definition
    • Number of Rows to Skip
    • Field Format
  • Defining the Record Type for a New Flat File
  • Defining Field Properties for a New Flat File
    • SQL*Loader Properties
    • SQL Properties
• Importing Definitions from Flat Files
• Using the Flat File Sample Wizard
  • Describing the Flat File
  • Selecting the Record Organization
    • Specifying Logical Records
  • Selecting the File Format
  • Selecting the File Layout
  • Selecting Record Types (Multiple Record Type Files Only)
    • Example: Flat File with Multiple Record Types
    • Defining Multiple Record Organization in a Delimited File
    • Defining Multiple Record Organization in a Fixed-Length File
  • Specifying Field Lengths (Fixed-Length Files Only)
    • Specifying Field Lengths for Multiple Record Files
  • Specifying Field Properties
    • SQL*Loader Properties
    • SQL Properties
• Updating a File Definition
  • Name Tab
  • General Tab
  • Record Tab
  • Structure Tab
• Using External Tables
  • Creating a New External Table Definition
    • Name Page
    • File Selection Page
    • Locations Page
  • Synchronizing an External Table Definition with a Record in a File
  • Editing External Table Definitions
    • Name Tab
    • Columns Tab
    • File Tab
    • Locations Tab
    • Data Rules Tab
    • Access Parameters Tab
  • Configuring External Tables
    • Access Specification
    • Reject
    • Parallel
    • Data Characteristics
    • Field Editing
    • Identification
    • Data Files

9 Using Microsoft Products as Sources

• Using Excel Spreadsheets as Sources
  • Case Study
  • Troubleshooting
• Using SQL Server as a Source
  • 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

10 Integrating Metadata Using the Transfer Wizard

• Using the Transfer Wizard
  • Integrating with the Meta Integration Model Bridges (MIMB)
    • Download the Meta Integration Model Bridge
  • Importing Metadata
    • Metadata Source and Target Identification Page
    • Transfer Parameter Identification Page
    • Summary Page
    • Importing the MDL File
  • Transfer Considerations
  • Importing Metadata from an OMG CWM-Standard System

11 Integrating with Business Intelligence Tools

• Integrating with Business Intelligence Tools
  • Introduction to Business Intelligence Objects in Warehouse Builder
  • Introduction to Business Definitions
    • About Business Definitions in Warehouse Builder
• Using Business Definitions
  • Creating Business Definitions
    • Naming the Business Definition Module
    • Setting the Connection Information
    • Reviewing the Summary Information
  • About Item Folders
  • Editing an Item Folder
    • Name Tab
    • Source Items Tab
    • Items Tab
    • Joins Tab
    • Conditions Tab
  • Creating an Item Folder
    • Naming and Describing the Type of Item Folder
    • Selecting Source Items
    • Selecting the Join
    • Reviewing the Summary
  • Creating a Business Area
    • Naming the Business Area
    • Selecting the Item Folders
    • Reviewing the Summary
  • Editing a Business Area
    • Editing the Business Area Name
    • Reviewing Item Folders in a Business Area
  • Creating a Drill Path
    • Naming the Drill Path
    • Specifying Drill Levels
    • Specifying the Join
    • Reviewing the Summary
  • Editing a Drill Path
    • Editing the Drill Path Name
    • Reviewing the Drill Levels in the Drill Path
  • Creating Lists of Values
    • Naming the List of Values
    • Defining Items in a List of Values
    • Referencing Items in a List of Values
    • Reviewing the Summary
  • Editing Lists of Values
    • Editing the List of Values Name
    • Editing Items in the List of Values
    • Editing Referencing Items
    • Advanced Options for List of Values
  • Creating Alternative Sort Orders
    • Naming the Alternative Sort Order
    • Defining Item for the Alternative Sort Order
    • Defining Order Item for the Alternative Sort Order
    • Referencing Items for the Alternative Sort Order
    • Referencing Selection Panel for the Alternative Sort Order
    • Reviewing the Summary
  • Editing Alternative Sort Orders
    • Editing the Alternative Sort Order Name
    • Editing the Defining Item
    • Editing the Defining Order Item
    • Editing the Referencing Order Items
    • Advanced Options
  • Creating Drills to Detail
    • Create Drill to Detail
  • Editing Drills to Detail
  • Creating Registered Functions
    • Naming the Registered Function
    • Specifying the Function Parameters
    • Reviewing the Summary
  • Editing Registered Functions
    • Renaming a Registered Function
    • Modifying the Parameters of a Registered Function
  • Deriving Business Intelligence Objects
    • Selecting Source Objects
    • Selecting a Target for the Derived Objects
    • Specifying Derivation Rules
    • Reviewing the Pre Derivation Rules
    • Reviewing Derivation Progress
    • Finishing the Derivation
• Using the Data Object Editor with Business Intelligence Objects
  • Creating Business Areas Using the Data Object Editor
  • Adding Item Folders to a Business Area
  • Creating Item Folder Using the Data Object Editor
  • Adding Items to An Item Folder
  • Synchronizing Item Folders
    • Synchronize Item Folder Dialog Box
  • Creating Joins Using the Data Object Editor
• Configuring Business Intelligence Objects
  • Configuration Parameters for Business Definition Modules
  • Configuration Parameters for Item Folders
  • Configuration Parameters for Registered Functions
• Accessing Business Intelligence Objects Using Oracle BI Discoverer
  • Using Business Definitions in Oracle BI Discoverer

Part II ETL and Data Quality

12 Designing Target Schemas

• Designing the Target Schema
  • Designing a Relational Target Schema
  • Designing a Dimensional Target Schema
• Configuring Data Objects
• Validating Data Objects
  • Editing Invalid Objects
• Generating Data Objects
  • Viewing Generated Scripts
  • Saving Generated Scripts to a File

13 Defining Oracle Data Objects

• 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
• Using the Data Object Editor to Edit Oracle Data Objects
• Using Constraints
  • About Constraints
  • Creating Constraints
    • Creating Primary Key Constraints
    • Creating Foreign Key Constraints
    • Creating Unique Key Constraints
    • Creating Check Constraints
  • Editing Constraints
• Using Indexes
  • Creating Indexes
• Using Partitions
  • Range Partitioning
    • Example of Range Partitioning
  • Hash Partitioning
  • Hash By Quantity Partitioning
  • List Partitioning
    • Example of List Partitioning
  • Composite Partitioning
    • About the Subpartition Template
    • Creating Custom Subpartitions
  • Index Partitioning
    • Index Performance Considerations
  • Configuring Partitions
• Using Tables
  • Creating Table Definitions
    • Name Tab
    • Columns Tab
    • Constraints Tab
    • Indexes Tab
    • Partitions Tab
    • Attribute Sets Tab
    • Data Rules Tab
  • Editing Table Definitions
    • Renaming a Table
    • Adding, Modifying, and Removing Table Columns
    • Adding, Modifying, and Deleting Table Constraints
    • Adding, Editing, and Deleting Attribute Sets
    • Reordering Columns in a Table
• Using Views
  • About Views
  • Creating View Definitions
    • Name Tab
    • Columns Tab
    • Query Tab
    • Constraints Tab
    • Attribute Sets Tab
    • Data Rules Tab
    • Data Viewer Tab
  • Editing View Definitions
    • Renaming a View
    • Adding, Editing, and Removing View Columns
    • Adding, Editing, and Deleting View Constraints
    • Adding, Editing, and Removing Attribute Sets
• Using Materialized Views
  • About Materialized Views
  • Creating Materialized View Definitions
    • Name Tab
    • Columns Tab
    • Query Tab
    • Constraints Tab
    • Indexes Tab
    • Partitions Tab
    • Attribute Sets Tab
    • Data Rules Tab
  • Editing Materialized View Definitions
    • Renaming Materialized Views
    • Adding, Editing, and Deleting Materialized View Columns
    • Adding, Editing, and Deleting Materialized View Constraints
    • Adding, Editing, and Deleting Attribute Sets
• Using Attribute Sets
  • Creating Attribute Sets
  • Editing Attribute Sets
• Using Sequences
  • About Sequences
  • Creating a Sequence Definition
  • Editing Sequence Definitions
    • Name Tab
    • Columns Tab
  • Editing Sequence Column Descriptions
• Using User-Defined Types
  • About Object Types
  • Creating Object Types
    • Name Tab
    • Columns Tab
  • Editing Object Types
  • About Varrays
  • Creating Varrays
    • Name Tab
    • Details Tab
  • Editing Varrays
  • About Nested Tables
  • Creating Nested Tables
    • Name Tab
    • Details Tab
  • Editing Nested Tables
• Configuring Data Objects
  • Configuring Design Objects
  • Configuring Target Modules
    • Identification
    • Tablespace Defaults
    • Generation Preferences
    • Deployment System Type
    • Run Time Directories
    • Generation Target Directories
  • Configuring Tables
  • Configuring Materialized Views
    • Materialized View Parameters
    • Fast Refresh for Materialized Views
  • Configuring Views
  • Configuring Sequences

14 Defining Dimensional Objects

• 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
• 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
  • Using the Data Object Editor
    • Name Tab
    • Storage Tab
    • Attributes Tab
    • Levels Tab
    • Hierarchies Tab
    • SCD Tab
    • Data Viewer Tab
    • Binding Attributes
• Creating Slowly Changing Dimensions Using the Data Object Editor
  • Creating a Type 2 SCD
    • Type 2 Slowly Changing Dimension Dialog Box
  • Creating a Type 3 SCD
    • Type 3 Slowly Changing Dimension Dialog Box
  • Editing Dimension Definitions
• Configuring Dimensions
  • Deployment Options for Dimensions
    • Deployment Options for Different Dimension Implementations
• Creating Cubes
  • Using the Create Cube Wizard
    • Name and Description Page
    • Storage Type Page
    • Dimensions Page
    • Measures Page
    • Summary Page
    • Defaults Used by the Create Cube Wizard
  • Using the Data Object Editor
    • Name Tab
    • Storage Tab
    • Dimensions Tab
    • Measures Tab
    • Calculated Measure Wizard
    • Aggregation Tab
    • Data Viewer Tab
    • Binding Cube Attributes
  • 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
  • Editing Cube Definitions
• Configuring Cubes
• 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

15 Data Transformation

• About Data Transformation in Warehouse Builder
• About Mappings
• About Operators
  • Types of Operators
    • Oracle Source/Target Operators
    • Data Flow Operators
    • Pre/Post Processing Operators
    • Pluggable Mapping Operators
• About Transformations
  • Types of Transformations
    • Predefined Transformations
    • Custom Transformations
• About Transformation Libraries
  • Types of Transformation Libraries
  • Accessing Transformation Libraries

16 Creating Mappings

• 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
• 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
• Example: Using a Mapping to Load Transaction Data
• 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

17 Source and Target Operators

• Using Source and Target Operators
• List of Source and Target Operators
• Using Oracle Source and Target Operators
  • Setting Properties for Oracle Source and Target Operators
    • 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
  • Constant Operator
  • Construct Object Operator
  • Cube Operator
    • Cube Operator 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
    • Dimension Operator as a Source
    • Dimension Operator as a Target
  • External Table Operator
  • Expand Object Operator
  • Mapping Input Parameter Operator
  • Mapping Output Parameter Operator
  • Materialized View Operator
  • Sequence Operator
  • Table Operator
    • Merge Optimization for Table Operators
    • Creating Temporary Tables While Performing ETL
  • Varray Iterator Operator
  • View Operator
• Using Remote and non-Oracle Source and Target Operators
  • Limitations of Using non-Oracle or Remote Targets
  • Warehouse Builder Workarounds for non-Oracle and Remote Targets
• Using Flat File Source and Target Operators
  • Setting Properties for Flat File Source and Target Operators
  • Loading Types for Flat Files
  • Field Names in the First Row
  • Flat File Operator
    • Flat File Source Operators
    • Flat File Target Operators

18 Data Flow Operators

• List of Data Flow Operators
• Operator Wizards
  • Operator Wizard General Page
  • Operator Wizard Groups Page
  • Operator Wizard Input and Output Pages
  • Operator Wizard Input Connections
• 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
  • Joiner Restrictions
    • Specifying a Full Outer Join
  • Creating Full Outer Join Conditions
• Key Lookup Operator
  • Using the Key Lookup Operator
    • General
    • Groups
    • Input Connections
    • Lookup
    • Type 2 History Lookup
    • No-match Rows
• 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 Operator
• Sorter Operator
  • Order By Clause
• Splitter Operator
  • Example: Creating Mappings with Multiple Targets
• Table Function Operator
  • Prerequisites for Using the Table Function Operator
    • Input
    • Output
  • Table Function Operator Properties
    • Table Function Operator Properties
    • Input Parameter Group 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

19 Oracle Warehouse Builder Transformations

• 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
• 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
• Importing PL/SQL
  • Restrictions on Using Imported PL/SQL
• Example: Reusing Existing PL/SQL Code

20 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
• Example: Using Process Flows to Access 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
• Example: Using Process Flows to Transfer 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

21 Activities in Process Flows

• Using Activities in Process Flows
  • Activities that Represent Objects
  • Utility Activities
  • Control Activities
  • OS Activities
    • Setting a Security Constraint
    • Setting a Proxy Command and Parameters
• AND
• Assign
• Data Auditor
• Email
• End
• End Loop
• File Exists
• FORK
• For Loop
• FTP
  • Writing a Script Within Warehouse Builder
  • Using Substitution Variables
  • Calling a Script Outside of Warehouse Builder
• Manual
• Mapping
• Notification
  • Notification Message Substitution
• OR
• Route
• Set Status
• Sqlplus
  • Using Activities in Process Flows
  • Using Substitution Variables
  • SQL *Plus Command
• Start
• Subprocess
• Transform
• User Defined
• Wait
• While Loop

22 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
• Configuring ETL Objects
• Configuring Mappings Reference
  • Procedure to Configure Mappings
  • Runtime Parameters
    • Bulk Size
    • Analyze Table Sample Percentage
    • Commit Frequency
    • Maximum Number of Errors
    • Default Operating Mode
    • Default Audit Level
    • Default Purge Group
  • 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
  • Sources and Targets Reference
    • Use LCR APIs
    • Database Link
    • Location
    • Conflict Resolution
    • Schema
    • Partition Exchange Loading
    • Hints
    • Constraint Management
    • SQL*Loader Parameters
  • Configuring Flat File Operators
  • Flat File Operators as a Target
  • Flat File Operator as a Source
• Configuring Process Flows Reference

23 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 the Data Profile Editor
  • About Six Sigma
    • What is Six Sigma?
    • Six Sigma Metrics for Data Profiling
• About Data Correction and Augmentation
  • Automatic Data Correction Based on Data Profiling Results
    • Types of Corrections for Source Data
    • About Performing Data Correction
  • Data Correction and Augmentation Using Operators
• About Data Rules
  • Types of Data Rules
  • Implementation of Data Rules
• About Quality Monitoring
  • About Data Auditors
• Performing Data Profiling
  • Data Profiling Restrictions
  • Steps to Perform Data Profiling
  • Import or Select the Metadata
  • Create a Data Profile
    • Using Attribute Sets to Profile a Subset of Columns from a Data Object
  • Profile the Data
    • Steps to Profile Data
    • Configuring Data Profiles
  • View Profile Results
    • Data Profile
    • Profile Object
    • Aggregation
    • Data Type
    • Domain
    • Pattern
    • Unique Key
    • Functional Dependency
    • Referential
    • Data Rule
  • Derive Data Rules
    • Steps to Derive Data Rules
  • Generate Corrections
    • Steps to Automate Data Correction
    • Steps to Create Corrections
    • Selecting Data Rules and Data Types for Corrected Objects
    • Selecting the Objects to be Corrected
    • Choosing Data Correction and Cleansing Actions
    • Steps to Deploy Correction Objects
    • Viewing the Correction Tables and Mappings
  • Define and Edit Data Rules Manually
  • Generate, Deploy, and Execute
  • Editing Data Profiles
    • Adding Data Objects to a Data Profile
  • Reference for Setting Data Profiling Configuration Parameters
    • Load Configuration
    • Aggregation Configuration
    • Pattern Discovery Configuration
    • Domain Discovery Configuration
    • Relationship Attribute Count Configuration
    • Unique Key Discovery Configuration
    • Functional Dependency Discovery Configuration
    • Row Relationship Discovery Configuration
    • Redundant Column Discovery Configuration
    • Data Rule Profiling Configuration
• 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
    • Defining the Data Rule
  • Editing Data Rules
  • Applying Data Rules to Objects
• Monitoring Data Quality Using Data Auditors
  • Creating Data Auditors
    • Specifying Data Correction Actions
  • Editing Data Auditors
  • Auditing Data Objects Using Data Auditors
    • Manually Running Data Auditors
    • Automatically Running Data Auditors
    • Data Auditor Execution Results
  • Configuring Data Auditors
    • Run Time Parameters
    • Data Auditor Parameters
    • Code Generation Options
  • Viewing Data Auditor Error Tables
    • Granting Privileges on Error Tables
• Setting Data Watch and Repair 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 Warehouse Builder
    • Importing MDM Data Rules
    • Writing Corrected Data and Metadata to the MDM Application

24 Data Quality Operators

• About the Match-Merge Operator
  • Understanding Matching Concepts
    • Example of Matching and Merging Customer Data
    • Example of Multiple Match Rules
    • Example of Transitive Matching
  • Restrictions on Using the Match-Merge Operator
  • Overview of the Matching and Merging Process
    • Requirements for Matching and Merging Records
    • Process for 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
  • 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
• About the Name and Address Operator
  • 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 Match-Merge Operator to Eliminate Duplicate Source Records
  • Steps to Use a Match-Merge Operator
  • Designing Mappings with a Match-Merge Operator
  • Using Two Match-Merge Operators
• Using the Name and Address Operator to Cleanse Source 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

25 Deploying to Target Schemas and Executing ETL Logic

• About Deployment and Execution in Warehouse Builder
  • About Deployment
    • Deployment Actions
    • Deployment Status
  • About Execution
• 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
  • Deploying to Additional Locations
  • Runtime Preferences
    • SQL Loader Runtime Preferences
    • SQL*Plus Runtime Preferences
    • Other Runtime Preferences
• Example: Updating a Target Schema

26 Scheduling ETL Objects

• About Schedules
• Process for Defining and Using Schedules
  • Editing a Schedule
    • Start and End Dates and Times
    • Defining Schedules To Repeat
  • Example Schedules
• Editing a Schedule
  • Start and End Dates and Times
  • Defining Schedules To Repeat
    • By Month
    • By Week Number
    • By Year Day
    • By Month Day
    • By Day
    • By Hour
    • By Minute
    • By Second
    • By Set Position

27 Auditing Deployments and Executions

• About the Repository Browser
  • Viewing Audit Reports
• Opening the Repository Browser
  • Starting and Stopping the Repository Browser Listener
  • Starting the Repository Browser
  • Logging in to a Workspace
• The Design Center
  • Repository Navigator
  • Object Reports
    • Summary Reports
    • Detailed Reports
    • Implementation Reports
    • Impact Analysis Reports
  • Object Properties
  • Object Lineage
  • Object Impact
• Control Center Reports
  • Deployment Reports
    • Deployment Schedule Report
    • Locations Report
    • Object Summary Report
    • Deployment Report
    • Deployment Error Detail Report
  • Execution Reports
    • Execution Schedule Report
    • Execution Summary Report
    • Error Table Execution Report
    • Trace Report
    • Execution Job Report
    • Job File Report
    • Job Start Report
    • Execution 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
  • Aborting 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 RAC Environment

28 Troubleshooting and Error Handling for ETL Designs

• Inspecting Error Logs in Warehouse Builder
• 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
• Using Pseudocolumns ROWID and ROWNUM in Mappings

Index