Skip Headers
Oracle® Warehouse Builder User's Guide
10
g
Release 2 (10.2.0.2)
Part Number B28223-05
Home
Book List
Index
Contact Us
Next
View PDF
Contents
List of Examples
List of Figures
List of Tables
Title and Copyright Information
Preface
Audience
Documentation Accessibility
Conventions
Getting Help
Related Publications
What's New
New in Oracle Warehouse Builder 10
g
Release 2 (10.2.0.2)
Part I Introduction and Concepts
1
Overview
About Oracle Warehouse Builder
Data Consolidation and Integration
2
Creating an Oracle Data Warehouse
Understanding the Basic Concepts
General Steps for Creating an Oracle Data Warehouse
About Locations
About Modules
3
Setting Up Warehouse Builder
Organizing Design Objects into Projects and Collections
Defining Collections
Creating a Collection
Name and Description Page
Contents Page
Summary Page
Editing Collection Definitions
Name Tab
Contents Tab
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
Creating Additional Configurations
Creating a New Configuration
Activating a Configuration
About Connectors
About Validation
4
Designing Target Schemas
Creating Oracle Data Objects
About Data Objects
Naming Conventions for Data Objects
Supported Data Types
About the Data Object Editor
Data Object Editor Components
Title Bar
Menu Bar
Diagram
Object
Edit
View
Window
Help
Toolbar
Explorer
Palette
Configuration
Bird's Eye View
Canvas
Canvas Tabs
Canvas Icons
Performing Operations on a Data Object Using the Canvas
Object Details
Generation
Indicator Bar
Data Viewer
Using the Data Object Editor
Creating a Data Object Using the Menu Bar
Creating a Data Object Using the Canvas
Creating a Data Object Using the Data Object Editor Palette
Add a New or Existing Data Object Dialog
Configuring Data Objects
About Attribute Sets
About Constraints
About Indexes
Creating Indexes
About Partitions
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
Defining a Dimension
Defining Dimension Attributes
Defining Levels
Defining Level Attributes
Defining Hierarchies
Dimension Roles
Level Relationships
Dimension Example
Value-based Hierarchies
Implementing a Dimension
Relational and ROLAP Implementation of a Dimension
Binding
MOLAP Implementation
About Slowly Changing Dimensions
About Type 1 SCDs
About Type 2 SCDs
Defining a Type 2 SCD
About Type 3 SCDs
Defining a Type 3 SCD
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
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
5
Identifying Data Sources and Importing Metadata
About Source Data and Metadata
General Steps for Importing Metadata from Sources
Example: Importing Metadata from Flat Files
Supported Sources and Targets
Integrating with Business Intelligence Tools
Introduction to Business Intelligence Objects in Warehouse Builder
Introduction to Business Definitions
About Business Definitions
About Business Presentations
6
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 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 Repository Objects
Add Operator Dialog
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
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
Pluggable Mapping Folders
Creating a Pluggable Mapping
Standalone Pluggable Mapping
Pluggable Mapping Folder
Signature Groups
Input Signature
Output Signature
Pluggable Mapping Implementation
Pluggable Mapping Usage
Pluggable Mapping Usage Signature
Types of Pluggable Mapping Usage
Pluggable Mapping Editor
Setting Mapping Properties
Target Load Order
Reset to Default
Setting Operator, Group, and Attribute Properties
Synchronizing Operators and Repository Objects
Synchronizing An Operator
Synchronizing From a Repository Object to an Operator
Synchronizing Operators based on Repository Objects
Synchronizing from an Operator to a Repository Object
Advanced Options for Synchronizing
Matching Strategies
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
7
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
Process Flow Editor Windows
Displaying the Process Flow Editor
Navigating the Process Flow Editor
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
Evaluation Security Context
8
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 Using 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
9
Using Oracle Warehouse Builder Transformations
About Transformations
Types of Transformations
Predefined Transformations
Custom Transformations
Transforming Data Using Warehouse Builder
Benefits of Using Warehouse Builder for Transforming Data
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 Transformation Properties
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
10
Understanding Data Quality Management
About the Data Quality Management Process
Phases in the Data Quality Life Cycle
Quality Assessment
Quality Design
Quality Transformation
Quality Monitoring
About Data Profiling
Uses of Data Profiling
Types of Data Profiling
Attribute Analysis
Functional Dependency
Referential Analysis
Data Rule Profiling
How to Perform Data Profiling
Import or Select the Metadata
Create a Data Profile
Profile the Data
View Profile Results and Derive Data Rules
Generating Corrections
Define and Edit Data Rules Manually
Generate, Deploy, and Execute
About Six Sigma
What is Six Sigma?
Six Sigma Metrics for Data Profiling
About Data Quality
About the 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
11
Deploying to Target Schemas and Executing ETL Logic
About Deployment
What is a Control Center?
Configuring the Physical Details of Deployment
Deployment Actions
The Deployment Process
About Schedules
Process for Defining and Using Schedules
Deploying Objects
Starting ETL Jobs
Viewing the Data
Part II Data Modeling Reference
12
Reference for Using Oracle Data Objects
Using the Data Object Editor to Edit Oracle Data Objects
Using Constraints
Creating Constraints
Creating Primary Key Constraints
Creating Foreign Key Constraints
Creating Unique Key Constraints
Creating Check Constraints
Editing Constraints
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 Warehouse Builder 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
13
Defining Dimensional Objects
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
Creating a Type 3 SCD
Type 3 Slowly Changing Dimension Dialog
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 in Warehouse Builder
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
14
Defining Flat Files and External Tables
About Flat Files in Warehouse Builder
Flat Files as Sources
Importing ASCII Files into the Repository
Adding Existing Binary Files to the Repository
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
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
Using the Import Metadata Wizard for 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
15
Defining Business Intelligence Objects
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
Deploying Business Definitions
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
Creating Joins Using the Data Object Editor
Using Business Presentations
Creating Business Presentation Modules
Naming the Business Presentation Module
Specifying the Deployment Location
Reviewing the Summary
Editing Business Presentation Modules
Renaming the Business Presentation Module
Modifying the Data Location
Creating Presentation Templates
Naming the Presentation Template
Selecting the Type
Selecting the Items to Include
Defining the Layout
Reviewing the Summary
Editing Presentation Templates
Renaming the Presentation Template
Modifying the Type of Report Template
Modifying the Report Items
Modifying the Report Layout
Configuring Business Intelligence Objects
Configuration Parameters for Business Definition Modules
Configuration Parameters for Item Folders
Configuration Parameters for Registered Functions
Configuration Parameters for Business Presentation Modules
Configuration Parameters for Presentation Templates
Accessing Business Intelligence Objects Using Oracle BI Discoverer and Oracle BI Beans
Using Business Definitions in Oracle BI Discoverer
Using Business Presentations in Oracle BI Beans
16
Importing Data Definitions
Using the Import Metadata Wizard
Importing Definitions from a Database
Filter Information Page
Object Selection Page
Summary and Import Page
Import Results Page
Re-Importing Definitions from an Oracle Database
Advanced Import Options
Updating Oracle Database Source Definitions
Using Oracle Designer 6
i
/9
i
Sources
Using Designer 6
i
/9
i
as a Metadata Source
17
Integrating Metadata Through the Warehouse Builder Transfer Wizard
Using the Oracle Warehouse Builder Transfer Wizard
Integrating with the Meta Integration Model Bridges (MIMB)
Download the Meta Integration Model Bridge
Importing Metadata into Warehouse Builder
Metadata Source and Target Identification Page
Transfer Parameter Identification Page
Summary Page
Importing the MDL File into Warehouse Builder
Transfer Considerations
Importing Metadata from an Object Management Group CWM Standard System
18
Importing Data From Third Party Applications
Integrating with E-Business Suite
Creating an E-Business Suite Module
Connecting to an E-Business Suite Database
Importing E-Business Suite Metadata Definitions
Filtering E-Business Suite Metadata
Selecting the Objects
Reviewing Import Summary
Integrating with PeopleSoft Data
Creating a PeopleSoft Module
Connecting to PeopleSoft Database
Importing PeopleSoft Metadata Definitions
Filtering PeopleSoft Metadata
Selecting the Objects
Reviewing Import Summary
Extracting 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
SAP Function Modules
Data Rerieval Mechanisms
Implementing an SAP Data Retrieval Mechanism
Connecting to an SAP System
Required Files For SAP Connector
Troubleshooting Connection Errors
Creating SAP Module Definitions
Importing Metadata from SAP Tables
Importing SAP Metadata Definitions
Analyzing Metadata Details
Creating SAP Extraction Mappings
Defining an SAP Extraction Mapping
Retrieving Data from the SAP System
Automated System
Semi Automated System
Manual System
19
Validating Data Objects
Validating Objects
Viewing the Validation Results
Validation Results Navigation Tree
Validation Messages
Editing Invalid Objects
Viewing Generated Scripts
Generating Scripts
Viewing the Generation Results
Viewing the Scripts
Saving the Scripts
Part III Data Quality Reference
20
Ensuring Data Quality
Steps to Perform Data Profiling
Using Data Profiles
Creating Data Profiles
Naming the Data Profile
Selecting Objects
Reviewing the Summary
Editing Data Profiles
Data Locations Tab
Adding Data Objects to a Data Profile
Add Profile Tables Dialog
Using the Data Profile Editor
Menu Bar
Toolbars
Object Tree
Property Inspector
Monitor Panel
Profile Results Canvas
Data Drill Panel
Data Rule Panel
Configuring Data Profiles
Configuration Parameters for Data Profiles
Profiling the Data
Data Profile Setup Dialog
Viewing the Results
Data Profile
Profile Object
Aggregation
Data Type
Domain
Pattern
Unique Key
Functional Dependency
Referential
Data Rule
Deriving Data Rules
Correcting Schemas and Cleansing Data
Creating Corrections
Select Target Module
Select Objects
Select Data Rules and Data Types
Data Rules Validation
Verify and Accept Corrected Tables
Choose Data Correction Actions
Summary
Viewing Correction Tables and Mappings
Using Data Rules
Types of Data Rules
Creating Data Rule Folders
Create Data Rule Folder
Creating Data Rules
Naming the Data Rule
Defining the Rule
Summary Page
Editing Data Rules
Applying Data Rules
Select Rule
Name and Description
Bind Rule Parameters
Summary
Tuning the Data Profiling Process
Tuning Warehouse Builder for Better Data Profiling Performance
Tuning the Oracle Database for Better Data Profiling Performance
Multiple Processors
Memory
I/O System
Using Data Auditors
Creating Data Auditors
Naming the Data Auditor
Selecting Objects
Choosing Actions
Summary Page
Editing Data Auditors
Reconciling Objects
Auditing Objects Using Data Auditors
Manually Running Data Auditors
Automatically Running Data Auditors
Configuring Data Auditors
Run Time Parameters
Data Auditor
Code Generation Options
21
Data Quality Operators
Using the Match-Merge Operator
Example of Matching and Merging Customer Data
Understanding Matching Concepts
Example of Multiple Match Rules
Example of Transitive Matching
Designing Mappings with a Match-Merge Operator
Using Two Match-Merge Operators
Match-Merge Wizard and Editor: Name
Match-Merge Wizard and Editor: Groups
Match-Merge Wizard and Editor: Input Connections
Match-Merge Wizard and Editor: Input Attributes
Match-Merge Wizard and Editor: Merge Output
Match-Merge Wizard and Editor: Cross-Reference Output
Match-Merge Wizard and Editor: Match Bins
Match-Merge Wizard and Editor: Match Rules
Descriptions of Match Rules
Conditional Match Rule
Defining a Conditional Match Rule
Algorithms for Conditional Match Rules
Weight Match Rule
Using the Weight Match Rule
Example of a Weight Match Rule
Person Match Rule
Defining a Person Match Rule
Firm Match Rule
Defining a Firm Match Rule
Address Match Rule
Defining an Address Match Rule
Custom Match Rule
Custom Match Rule Editor
Defining a Custom Match Rule
Merge Rules Page
Descriptions of 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
Custom Merge Rule Editor
Using the Name and Address Operator in a Mapping
Name and Address Wizard and Editor: General
Name and Address Wizard and Editor: Definitions
Name and Address Wizard and Editor: Groups
Name and Address Wizard and Editor: Input Connections
Name and Address Wizard and Editor: Input Attributes
Input Role Descriptions
Name and Address Wizard and Editor: Output Attributes
Output Attribute Components Dialog Box
Descriptions of Output Components
Pass Through
Name
Address
Extra Vendor
Error Status
Country-Specific
Name and Address Wizard and Editor: Postal Reporting Page
About Postal Reporting
United States Postal Service CASS Certification
Canada Post SERP Certification
Australia Post AMAS Certification
Managing the Name and Address Server
Configuring the Name and Address Server
Starting and Stopping the Name and Address Server
Part IV ETL Design Reference
22
Using Activities in Process Flows
Using Activities in Process Flows
Oracle Warehouse Builder Specific Activities
Utility Activities
Control Activities
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
23
Moving Large Volumes of Data
About Transportable Modules
About Transportable Modules and Oracle Database Technology
Benefits of Using Transportable Modules
Instructions for Using Transportable Modules
Roles and Privileges Required 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
24
ETL Objects Configuration
Configuring Mappings Reference
Procedure for Configuring 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
25
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
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
26
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
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
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
27
Transformations
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
ASCII
CHR
CONCAT
INITCAP
INSTR, INSTR2, INSTR4, INSTRB, INSTRC
LENGTH, LENGTH2, LENGTH4, LENGTHB, LENGTHC
LOWER
LPAD
LTRIM
NLSSORT
NLS_INITCAP
NLS_LOWER
NLS_UPPER
REPLACE
REGEXP_INSTR
REGEXP_REPLACE
REGEXP_SUBSTR
RPAD
RTRIM
SOUNDEX
SUBSTR, SUBSTR2, SUBSTR4, SUBSTRB, SUBSTRC
TRANSLATE
TRIM
UPPER
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
ASCIISTR
COMPOSE
CONVERT
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_MULTI_BYTE
TO_NCHAR
TO_NCLOB
TO_NUMBER
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
UNISTR
Date Transformations
ADD_MONTHS
CURRENT_DATE
DBTIMEZONE
FROM_TZ
LAST_DAY
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
ROUND (date)
SESSIONTIMEZONE
SYSDATE
SYSTIMESTAMP
SYS_EXTRACT_UTC
TRUNC (date)
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
ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WB_LOOKUP_NUM (on a number)
WB_LOOKUP_NUM (on a varchar2)
WB_IS_NUMBER
WIDTH_BUCKET
OLAP Transformations
WB_OLAP_AW_PRECOMPUTE
WB_OLAP_LOAD_CUBE
WB_OLAP_LOAD_DIMENSION
WB_OLAP_LOAD_DIMENSION_GENUK
Other Transformations
DEPTH
DUMP
EMPTY_BLOB, EMPTY_CLOB
NLS_CHARSET_DECL_LEN
NLS_CHARSET_ID
NLS_CHARSET_NAME
NULLIF
NVL
NVL2
ORA_HASH
PATH
SYS_CONTEXT
SYS_GUID
SYS_TYPEID
UID
USER
USERENV
VSIZE
Spatial Transformations
SDO_AGGR_CENTROID
SDO_AGGR_CONVEXHULL
SDO_AGGR_MBR
SDO_AGGR_UNION
Streams Transformations
REPLICATE
XML Transformations
EXISTSNODE
EXTRACT
EXTRACTVALUE
SYS_XMLAGG
SYS_XMLGEN
WB_XML_LOAD
WB_XML_LOAD_F
XMLCONCAT
XMLSEQUENCE
XMLTRANSFORM
Part V Deployment and Execution Reference
28
Scheduling ETL Objects
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
Example Schedules
29
Deploying Target Systems
Creating Target Locations
Selecting a Target Location Type
Granting Privileges to the Target Location
Registering Locations
Deploying From the Design Center Project Explorer
Opening Control Center Manager
Deploying From Control Center Manager
Reviewing the Deployment Results
Deploying to Additional Locations
Starting the ETL Process
Scheduling ETL Jobs
Runtime Preferences
30
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 Repository
The Design Center
Repository Navigator
Object Reports
Summary Reports
Detailed Reports
Implementation Reports
Lineage and Impact Analysis Reports and Diagrams
Object Properties
Object Lineage
Object Impact Analysis
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 Warehouse Builder 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
Unregistering a Location
Updating Location Connection Details for a Changed Database Environment
Updating Service Node Details in a Changing RAC Environment
Part VI Reference for Managing Metadata
31
Managing Metadata Dependencies
Introduction to the Metadata Dependency Manager
Usage Scenario
What are Lineage and Impact Analysis Diagrams?
Generating an LIA Diagram
Modifying the Display of an LIA Diagram
Using Groups in an LIA Diagram
Displaying an Object's Attributes
Modifying Objects in the Dependency Manager
Metadata Dependency Manager
Menu Bar
Analysis
Edit
View
Window
Toolbars
Bird's Eye View
DM Tree
Property Inspector
Canvas
Propagate Change Dialog Box
32
Managing Metadata Changes
About Metadata Snapshots
Creating Snapshots
Opening the Create Snapshot Wizard
Adding Components to a Snapshot
Opening the Add to Snapshot Wizard
Managing Snapshots
Managing Snapshot Access Privileges
Comparing Snapshots
Comparing Two Snapshots
Comparing a Repository Object with a Snapshot Component
Converting a Full Snapshot to a Signature Snapshot
Restoring Repository Objects From Snapshots
Exporting and Importing Snapshots
Deleting Snapshots
33
Importing and Exporting with the Metadata Loader (MDL)
Overview of Import and Export Using Metadata Loader
Metadata Loader Utilities
Metadata Export Utility
Metadata Import Utility
Uses of Metadata Loader
Accessing the Metadata Loader
Multiple Session Concurrency and MDL
Metadata Loader Log File
About Metadata Loader Results
Using Metadata Loader with Warehouse Builder Design Center
Exporting Metadata Using Warehouse Builder Design Center
Metadata Export Dialog
Export Advanced Options Dialog
Importing Metadata Using Warehouse Builder Design Center
Metadata Import Dialog
Import Advanced Options Dialog
File Summary Dialog
Combining Import Modes and Matching Criteria
Import Different Base Languages
Validation Rules Governing Import
Upgrading Metadata from Previous Versions
Metadata Upgrade Dialog
Changes to Repository Objects After Upgrading to Oracle Warehouse Builder 10
g
Release 2
Checking for Warnings and Error Messages
Using Metadata Loader with OMB Plus
34
Extending the Warehouse Builder Repository
Extending the Warehouse Builder Repository With User Defined Objects
About Oracle Metabase (OMB) Plus
Using OMB Plus Scripts to Specify UDOs and UDPs
Naming Conventions for UDOs and UDPs
Adding New Properties to Warehouse Builder Objects
Creating UDPs: An Example
Adding UDOs to the Repository
Writing Scripts to Define UDOs
Creating UDOs: An Example
Associating UDOs with Objects
Working with UDOs and UDPs
Propagating UDOs and UDPs to Other Repositories
Creating New Icons for Objects in Warehouse Builder
Creating Icon Sets
Create Icon Set Dialog
Assigning New Icon Sets to Warehouse Builder Objects
Assigning a New Icon Set to an Object
Assigning New Icon Sets to Activities in Process Flow
Assigning New Icon Sets to Tasks in Expert Editor
Part VII Additional Usages
35
Implementing Best Practices
What Are Experts?
User's View of an Expert
Developer's View of an Expert
How Are Experts Different From Process Flows?
How to Create Experts
Creating an Expert Object
Adding Tasks to the Expert Canvas
Adding Nested Experts to the Expert Canvas
Adding Transitions to the Expert Canvas
Passing Data Values Among Tasks
Binding Input Parameters
Using Constants
Using Variables
Validating, Generating, and Starting Experts
Creating a Development Environment
Publishing Experts
Running an Expert From a Batch File
36
Keyboard Access
General Windows Keys
Tree View Control Keys
Accelerator Keys Set for Warehouse Builder Shortcut Keys
Menu Commands and Access Keys
Mapping Editor Keyboard Operations
Connect Operators Mnemonic Key Assignments
Cube Shortcut Keys
Tables, Views, Materialized Views, and User Defined Types (Object Types, Varrays, Nested Tables) Shortcut Keys
Dimension Shortcut Keys
Business Definitions Shortcut Keys
Mappings Shortcut Keys
Pluggable Mappings Editor Shortcut Key
37
Reserved Words
Reserved Words
Index
Scripting on this page enhances content navigation, but does not change the content in any way.