Skip Headers
Oracle9
i
Warehouse Builder User's Guide
Release 9.2
Part Number B10996-01
Index
Contents
Title and Copyright Information
Send Us Your Comments
Preface
Purpose
Audience
How This Guide Is Organized
New in Release 9.2
Added in Release 2 (9.0.4)
Conventions
Related Publications
Documentation Accessibility
Accessibility of Links to External Web Sites in Documentation
Contacting Oracle
1 Overview of Oracle9
i
Warehouse Builder
Product Architecture and Capabilities
The Design Component
The Runtime Component
How Warehouse Builder Achieves Its Goal
Warehouse Builder Components
Warehouse Builder Objects
Warehouse Builder Deployment Targets
Benefits of Using Warehouse Builder
2 Getting Started with Warehouse Builder
Overview of Creating a Business Intelligence System
Step 1: Creating a Project
Step 2: Defining Source and Target Modules
Step 3: Defining Data Movement and Transformation
Step 4: Validating and Generating
Step 5: Deploying and Executing
Accessing the Warehouse Builder Repository
Multiple User Account System
Security Registration Service
Security Requirements
Starting Warehouse Builder
For Windows NT/2000/XP Users
For UNIX Users
Closing Warehouse Builder
Supporting Multiple Users
Read/Write Mode
Read-Only Mode
Synchronization
Introducing the Console
The Project Navigation Tree
The Toolbar
Setting Preferences
General Preferences
Naming Preferences
Message Log Preferences
Utilities Preferences
Browser Preferences
Clipboard/Recycle Preferences
Creating and Editing Warehouse Builder Objects
Creating Projects
Using the Warehouse Builder Wizards
Using Object Editors
Using Property Sheets
Creating User-Defined Properties
Finding Objects in a Project
Managing Metadata
Metadata Reporting
Metadata Import and Export
Metadata Exchange
Part I Defining Source and Target Objects
3 Defining Oracle Data Objects
Creating Warehouse Modules
Defining Locations
Defining Connectors
About Data Objects
Using Tables
Creating a Table Definition
Using the Table Editor
Editing Table Definitions
Renaming a Table
Adding Columns
Reordering Columns in a Table
Editing Constraints
Deleting Constraints
Adding Attribute Sets
Using External Tables
About External Tables
Creating a New External Table Definition
Using the External Table Editor
Reconciling an External Table Definition with a Record in a File
Editing External Table Definitions
Using Views
About Views
Creating a View Definition
Editing a View Definition
Using Materialized Views
About Materialized Views
Creating a Materialized View Definition
Editing a Materialized View Definition
Using Sequences
About Sequences
Creating a Sequence Definition
Using the Sequence Editor
Editing Sequence Definitions
Renaming a Sequence
Editing Sequence Column Descriptions
Using Advanced Queues
About Advanced Queues
Creating Definitions for Advanced Queues
Reimporting Advanced Queue Definitions
Viewing Advanced Queue Properties
Creating Advanced Queues
Using Dimensions
Rules for Dimension Objects
Creating a Dimension Definition
Editing Dimension Definitions
Using Cubes
Creating Cube Definitions
Editing a Cube Definition
Importing Metadata into Target Modules
4 Importing Data Definitions
Creating Source Modules
Configuring Connections for Database Sources
About Database Sources
Creating a Database Source Module
Importing Definitions from a Database
Re-Importing Definitions from an Oracle Database
Updating Oracle Database Source Definitions
Using Oracle Designer 6i/9
i
Sources
Using Designer 6i/9i as a Metadata Source
About Flat File Modules
Creating Flat File Modules
About Flat File Sources and Targets
Using the Import Metadata Wizard Flat Files
Using the Flat File Sample Wizard
Describing the Flat File
Selecting the Record Organization
Selecting the File Layout
Selecting the File Format
Selecting Record Types (Multiple Record Type Files Only)
Specifying Field Lengths (Fixed-Length Files Only)
Specifying Field Properties
Updating a File Definition
5 Configuring Data Objects
Creating Indexes and Partitions
About Indexes
Creating Indexes
Creating Bitmap Indexes
Configuring Indexes
Renaming Indexes
About Partitions
Creating Partitions
Configuring Warehouse Builder Design Objects
Configuring Target Modules
Configuring Tables
Configuring External Tables
Configuring Advanced Queues
Configuring Dimensions
Configuring Cubes
Configuring Materialized Views
Materialized View Parameters
Fast Refresh for Materialized Views
Configuring Views
Configuring Sequences
Part II Designing ETL Objects
6 Designing Mappings
About Mappings
About Oracle Warehouse Modules
Procedure for Defining Mappings
Creating a Mapping
About the Mapping Editor
About Operators
Adding Operators
Adding Bindable Operators
Editing Operators
Mapping Naming Conventions
Using Display Sets
Compressing Mappings
Connecting Operators
Connecting Attributes
Connecting Groups
Setting Operator Properties
Source and Target Operator Properties
Attribute Properties
Flat File Operators Properties
Reconciling Operators and Repository Objects
Inbound Reconciliation
Outbound Reconciliation
Match Strategies
7 Debugging Mappings
About Debug Mode in the Mapping Editor
Starting a Debug Session
Defining Test Data
Setting Breakpoints
Setting Watches
Running the Mapping
Re-Initializing a Debug Session
Scalability
Restrictions
8 Using Mapping Operators
Using the Expression Builder
Opening the Expression Builder
Aggregator Operator
Constant Operator
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
Deduplicator Operator
Expression Operator
Filter Operator
Joiner Operator
Joiner Restrictions
Specifying a Full Outer Join
Creating Full Outer Join Conditions
Key Lookup Operator
Mapping Advanced Queue Operator
Creating AQ Mappings
Advanced Queue Operator Properties
AQ Prerequisites for Mapping Execution
Mapping Flat File Operator
Flat File Source and Target Operators
Extracting a Master-Detail Structure from Flat Files
Mapping Input Parameter Operator
Mapping Output Parameter Operator
Mapping Sequence Operator
Match-Merge Operator
Example: Matching and Merging Customer Data
Designing Mappings with a Match-Merge Operator
Using the Match-Merge Operator
Understanding Matching Concepts
Refining Data from Match-Merge Operators
Name and Address Operator
About the Name and Address Operator
Example: Following a Record Through the Name and Address Operator
Using the Name and Address Operator in a Mapping
Postal Reporting
Pivot Operator
Example: Pivoting Sales Data
The Row Locator
Using the Pivot Operator
Post-Mapping Process Operator
Pre-Mapping Process Operator
Set Operation Operator
Sorter Operator
Splitter Operator
Example: Creating Mappings with Multiple Targets
Table Function Operator
Transformation Operator
Unpivot Operator
Example: Unpivoting Sales Data
The Row Locator
Using the Unpivot Operator
9 Using Transformations
About Transformations
About Custom Transformations
About Pre-Defined Transformations
Defining Custom Transformations
Importing PL/SQL
Editing Transformation Properties
10 Designing Process Flows
About Process Flows
About Process Flow Modules
About Process Flow Packages
Procedure for Defining Process Flows
Creating Process Flow Modules
Creating Process Flow Packages
Creating Process Flow Definitions
About the Process Flow Editor
Navigating in the Process Flow Editor
About Activities
About Transitions
Using Activities in Process Flows
Process Flow Editor Menu and Toolbar
Menu Bar
Toolbar
11 Configuring ETL Objects
Configuring Mappings Reference
Procedure for Configuring Mappings
Runtime Parameters Reference
Code Generation Options Reference
Sources and Targets Reference
Configuring Flat File Operators
Flat File Operators as a Target
Flat File Operator as a Source
Strategies for Configuring PL/SQL Mappings
Selecting a Default Operating Mode
Committing Data from a Single Source to Multiple Targets
Avoiding Invalid Designs for PL/SQL Mappings
Using Partition Exchange Loading
About Partition Exchange Loading
Configuring a Mapping for PEL
Direct vs. Indirect PEL
Using PEL Effectively
Configuring Targets in a Mapping
Restrictions for Using PEL in Warehouse Builder
Configuring Process Flows
Part III Deploying and Executing
12 Validating Objects
About Validation
Validating Objects
Viewing the Validation Results
The Validation Results Navigation Tree
The Validation Messages
Editing Invalid Objects
Viewing Generated Scripts
Generating Scripts
Viewing the Generation Results
Viewing the Scripts
Saving the Scripts
13 Deploying Target Systems
About Deployment
Deploying and Upgrading Target Systems
Defining Runtime Repository Connections
Deploying Objects
Using the Deployment Manager
Opening the Deployment Manager
About the Deployment Manager
Registering Locations
Database Connections
Location Registration
Selecting Objects for Deployment
Viewing Deployment History
Completing the Deployment
Saving Deployment Scripts
Executing Deployed Objects
Executing Mappings and Process Flows
Scheduling Mappings and Process Flows
14 Auditing Deployment and Execution
Why Audit Deployments and Executions?
About the Runtime Audit Browser
Starting the Client Browser Version
Starting the Oracle Portal Version
Viewing Runtime Repository Reports
Viewing Deployment Reports
Viewing Execution Reports
Purging Audit Data
Runtime Audit Browser Administration
Managing Available Repositories
Administering Roles
Maintaining the Administrator List
Available Runtime Audit Reports
Part IV Managing Metadata
15 Importing and Exporting with the Metadata Loader (MDL)
Overview of Import and Export Using Metadata Loader
Importing and Exporting Metadata Using the Metadata Loader
Required Access Privileges for MDL
About Metadata Loader Results
Exporting Metadata
Before Exporting Metadata
About the Metadata Export Utility
Exporting Metadata using Warehouse Builder Client
Metadata Export File Format
Archiving a Project
Importing Metadata
Before Importing Metadata
About the Metadata Import Utility
Validation Rules Governing Import
Importing Metadata using Warehouse Builder Client
Restoring a Project
Using the Metadata Loader Command Line Utility
Creating MDL Parameter Files at the Command Line
Exporting Metadata Using the Command Line Utility
Importing Metadata Using the Command Line Utility
Splitter for Exporting and Importing Warehouse Builder Mappings
16 Metadata Change Management
About Metadata Snapshots
Snapshot Types
Full and Signature Snapshots
Cascade and No Cascade Snapshots
Snapshot Combinations
Creating Snapshots
Updating Snapshots
Metadata Change Management Window
Compare Snapshots
Restore Snapshots
Delete Snapshots
Using Metadata Snapshots
History Management
Change Management
Snapshot Usage Suggestions
Using Snapshots in Conjunction with Other Warehouse Builder Functionality
Snapshot Views for Data Warehouse Designers and Managers
17 Metadata Browsing and Reporting
About Warehouse Builder Design Browser
Using Warehouse Builder Design Browser
Starting Warehouse Builder Design Browser
Navigating Warehouse Builder Browser
Browsing Favorites
Customizing Your Favorites Page
Viewing Warehouse Builder Reports
Viewing Reports in the Warehouse Builder Client
Viewing Reports in Warehouse Builder Browser
Warehouse Builder Metadata Reports
18 Managing Warehouse Builder Browser
Warehouse Builder Browser Overview
About Oracle9
i
AS Portal
Portlets for Managing Warehouse Builder Browser
Adding Portlets
Administering Warehouse Builder Browser
Register a Warehouse Builder Repository
Register a Custom Report
Resource Management
Managing Preferences
Managing the Dependency Index
Other Administration Tasks
Configuring the Warehouse Builder Client
Creating Custom Reports
Creating a Custom Report in Oracle Portal
Part V Integrating and Extending Warehouse Builder
19 Extending Warehouse Builder Functionality
About Oracle Metabase (OMB) Plus
User-Defined Properties
Managing User Defined Properties
Viewing User Defined Properties
Transferring UPDs to Other Repositories
Managing Security with PL/SQL
Maintaining Repository Users
Plug-in Interface for the Security PL/SQL Package Specification
Definitions of the Constants in the Package Specification
Implementing the PL/SQL Interface
Implementing Sample Security Policies
The Warehouse Builder Security Architecture
Using a Customizable Security Authorization Framework
Development Cycle Based Security
Reactive Security and Audit-based Security
Data Stewardship
20 Data Quality: Name and Address Cleansing
Input Roles
Output Components
Name and Address Operator Supported Countries
Country Postal Certifications
United States Postal Service CASS Certification
Canada Post SERP Certification
Australia Post AMAS Certification
Configuring the Name and Address Server
Starting and Stopping the Name and Address Server
Best Practices for Using the Name and Address Operator in a Mapping
Errors in Name and Address Data
Using Status Codes
Using Discrete Input Roles
Separating Good Records from Bad Records with the Splitter Operator
21 Using SAP R/3 Data in Warehouse Builder
About the Warehouse Builder SAP Integrator
About SAP Business Areas
SAP Table Types
Required Files
Defining SAP Metadata Objects
Creating SAP Module Definitions
Importing SAP Metadata Definitions
Updating SAP Source Modules
Defining the ETL Process for SAP Objects
Defining Mappings Containing SAP Objects
Configuring Code Generation for SAP Objects
Generating SAP Definitions
Loading SAP Data into the Repository
Deploying PL/SQL Scripts for Transparent Tables
22 Integrating Warehouse Builder Metadata with Other BI Products
Overview of Warehouse Builder Integration Features
Defining Collections
Creating a Collection
Editing a Collection
Business Intelligence Integration Using the Transfer Wizard
Integrating with the Meta Integration Model Bridges (MIMB)
Importing Metadata into Warehouse Builder
Exporting Metadata from Warehouse Builder
Online Analytical Processing (OLAP) with Warehouse Builder
Enabling OLAP with Warehouse Builder
Creating the OLAP Metadata
Defining Collections in Warehouse Builder
Deploying OLAP Metadata Using the Warehouse Builder Transfer Wizard
Loading the Analytic Workspace
Debugging the Cloning in a Mapping or Process Flow
Notes on the Warehouse Builder OLAP Bridge (9.0.5)
Execution Modes for the Warehouse Builder OLAP Bridge
Customizing the Bridge
Cloning Behavior (Incremental Loading, Precalculated Aggregation)
Warehouse Builder Bridges: Transfer Parameters and Considerations
Transfer Parameters
Transfer Considerations
Importing Metadata from an Object Management Group CWM Standard System
Importing Metadata from Computer Associates ERwin 3.5.1
Importing Metadata from Powersoft PowerDesigner 6.0
Importing Metadata from Oracle9
i
OLAP Server
Exporting Metadata to Oracle Discoverer 4
i
and 9
i
Importing Transferred Data into Discoverer
Exporting Metadata to an Object Management Group CWM Standard System
Exporting Metadata to Oracle Express
Exporting Metadata to Oracle9
i
OLAP Server
Part VI Appendix
A Keyboard Shortcuts
General Windows Keys
Tree View Control Keys
Accelerator Keys Set for Warehouse Builder
Menu Commands and Access Keys
Mapping Editor Keyboard Operations
Auto-Mapping Mnemonic Key Assignments
B Reserved Words
Reserved Words
C Using the XML Toolkit
Overview
Retrieving Data From Sources
Storing Data in Targets
Using Runtime Controls
Calling the XML Toolkit
Two Entrances
Typical Control Files
XML Documents Stored in Files
XML Documents Stored as Other Objects
Control File Element Names and Attributes
Document Type Definition for the Control File
Reference Materials
D Warehouse Builder Public Views
Warehouse Builder Design Repository Public Views
General Model Views
Data Model Views
Implementation Model Views
Flat Files Views
Collections Views
Function Model Views
Configuration Model Views
Deployment Model Views
Mapping Model Views
Process Flow Model Views
Warehouse Builder Runtime Repository Public Views
Deployment Auditing Views
Execution Auditing Views
E Public Objects
About Object Class Definitions
About First Class Objects
About Second Class Objects
About Third and Fourth Class Objects
Warehouse Builder Class Definition Objects
Object Ownership Tree
Glossary
Index
Copyright © 2001, 2003 Oracle Corporation.
All Rights Reserved.
Index
