Contents

Title and Copyright Information

Send Us Your Comments

Preface

Audience

How This Guide Is Organized

Conventions

Related Documents

1 Overview

The Development Phases

The Definition Phase

The Generation Phase

The Load and Manage Phase

2 Getting Started on a Project

Logging on to a Repository

About the Warehouse Builder Console

Console Toolbar

Navigation Tree Views

Project Tree

Transformation Tree

Administration Tree

Utilities

Displaying Utility Properties

Adding a Utility

Updating a Utility

Removing a Utility

Recycle Bin and Clipboard

Recycle Bin

Clipboard

Clipboard/Recycle Preferences

Working with Projects

About Warehouse Builder Projects

Creating a Project

Module Contents

About Multi-User Access

Read/Write Mode

Read-Only Mode

Synchronization

Creating Objects

Using the Warehouse Builder Wizards

Object Names

Logical Name Mode

Physical Name Mode

Setting the Name Mode

Editing Objects

Object Editors

Property Sheets

Finding Objects

Ending a Warehouse Builder Session

Committing Your Work

Exiting Warehouse Builder

3 Defining Relational Targets

Creating a Warehouse Target Module

Displaying the Warehouse Module

Creating Table Definitions

Creating a Table Definition

Updating Table Definitions

Using the Table Editor

Using the Table Property Sheet

Creating Attribute Sets

Importing Definitions

Creating Materialized View Definitions

Creating a Materialized View Definition

Updating a Materialized View Definition

Creating Conventional View Definitions

Creating a View Definition

Updating a View Definition

Defining a Sequence Object

4 Defining Dimensional Targets

About Star Schemas

About Dimension Tables

About Fact Tables

About Materialized Views

About Conventional Views

Creating Dimension Definitions

Rules for Dimension Objects

About Levels and Hierarchies

About Unique Key Constraints

About Mixed Levels of Aggregation

Creating a Dimension Definition

Updating Dimension Definitions

Using the Dimension Editor

Using the Property Sheets

Creating a Time Dimension Definition

Creating Fact Table Definitions

Creating a Definition for a Fact Table

Updating a Fact Table Definition

Using the Fact Editor

Importing Definitions

Adding Transformations

About Transformations

About Transformation Parameters

About Oracle Transformation Libraries

Global Shared Library

Oracle Library

Accessing Transformation Libraries

Creating Transformation Libraries

Defining Custom Transformations

Editing Transformation Properties

Importing PL/SQL Packages

Defining Business Areas

5 Defining Source Modules

Creating Source Modules

Configuring Connection Information for Database Sources

Creating a Database Source Module

Creating a Source Module for an Oracle Designer Repository

Creating a Source Module for Designer 6i

Creating a Flat File Source Module

Create a Source Module for SAP Definitions

Updating a Source Module

Importing Definitions from Database Sources

Importing Definitions from a Database

Re-Importing Definitions from an Oracle Database

Updating Oracle Database Source Definitions

Update a Source Definition

Update the Connection

Diagram a Source Definition

Creating Definitions for Flat File Sources

About the Flat File Sample Wizard

Creating a Definition for a Fixed-Length File

Defining Multiple Records in a Fixed-Length File

Specifying Logical Records

Creating a Definition for a Delimited File

Defining Multiple Record Types in a Delimited File

Updating a File Definition

Importing Definitions for Pure Extract and Pure Integrate

Importing Definitions for SAP Data Sources

Business Components

Import Metadata from SAP sources

6 Defining Source to Target Mappings

Understanding Warehouse Builder Mappings

About Mapping Operators

About Operator Properties

About Attributes and Attribute Groups

About Display Sets

About Binding Mapping Operators

Defining Mappings

About the Mapping Editor

Creating a Mapping

Displaying the Mapping Editor

Selecting Data Operators

Selecting a Flat File Operator

Selecting an SAP Source

Selecting a Data Flow Operator

Connecting Mapping Operators

Connecting Attributes

Connecting Attribute Groups

Editing Mapping Operator Attributes

Adding or Removing Operator Attribute Groups

Editing Operator Attributes

Adding or Removing Attributes

Renaming Attributes

7 Using Mapping Operators and Transformations

Using Expression Builder

Adding Flow Operators to a Mapping

Adding Mapping Sequences

Adding Data Generators

Setting a Column to the Data File Record Number

Setting a Column to the Current Date

Setting a Column to a Unique Sequence Number

Adding Constants

Using Key Lookups

Using Set Operations

Joining Data Sources

Creating Full Outer Join Conditions

Splitting Data

Removing Redundant Data

Aggregating Data

Filtering Data

Ordering Data

Cleansing Name and Address Data

Adding a Pre-Mapping Process

Adding a Post-Mapping Process

Adding Mapping Input Parameters

Adding Mapping Output Parameters

Adding External Processes

Adding Transformations

Adding Expressions

8 Configuring and Generating Mappings

Reconciling Mapping Operators with Repository Objects

Using Inbound Reconciliation

Using Outbound Reconciliation

Configuring a Mapping

Configuring Mapping Table Operators

Configuring Flat File Operators

Configuring Transformation Operators

Configuring Mapping Operator Attributes

Setting Attribute Properties

Setting Loading Properties

Configuring Physical Properties for a Mapping

Setting Deployable

Setting the Step Type

Setting Runtime Parameters

Setting Sources and Targets

Configuring Flat File Physical Properties

Configuring SAP File Physical Properties

Setting the Step Type

Setting the Runtime Parameters

Validating and Generating a Mapping

Validating a Mapping

Generating a Mapping

Viewing the Generated Code for a Mapping

Viewing Intermediate Results

Viewing Code from the Module Editor

9 Configuring, Generating, and Deploying

Configuring a Physical Instance

Setting up Configuration Properties

Guidelines for Configuring Warehouse Modules

Guidelines for Configuring Dimensions, Facts, and Tables

Guidelines for Configuring Materialized Views

Guideline for Configuring Sequences

Guidelines for Configuring Views

Creating Database Links, Indexes, and Partitions

Creating Database Links

Creating Partitions

Defining Hash Partitions

Defining Range Partitions

Defining a Range with Hash Subpartitions

Configuring Partition Exchange Loading (PEL)

Validating Definitions and Generating Scripts

Validating Definitions

Valid and Invalid Definitions

Generating Scripts

Verifying the Generation Results

Database Links

Dimensions

Views and Materialized Views

Mappings

Sequences

Tables

Post-Generation Tasks

Schema Objects Tab

Transforms Tab

Deploying Scripts

Deploying SAP Definitions

Deploying ABAP Scripts for Non-Transparent Tables

Deploying ABAP Scripts Manually

Deploying PL/SQL Scripts for Transparent Tables

Upgrading the Warehouse

Generating and Executing Upgrade Scripts

10 Managing Loads and Updates

Registering Tcl Scripts

Creating an Oracle Workflow

Deploying Scripts to the Workflow Server

Defining the Workflow Process

Scheduling a Workflow

Viewing the Job

Changing Job Parameters

Viewing the Results

About the Runtime Audit Viewer

Viewing the Job Audit

Viewing the Job Instance Audit

Viewing the Task Audit

Viewing the Task Details Audit

Using the Warehouse Builder Runtime Audit Viewer

Viewing Specific Results

Refreshing the Audit Viewer

Purging Runtime Entries

11 Administration

About the Warehouse Builder Metadata Loader

Metadata Export Utility

Export File

Exporting Metadata

Metadata Import Utility

Importing Metadata

Metadata Loader Command Line Utility

Directive Keywords for the Export Utility

Export a Project

Directive Keywords for the Import Utility

Import Selected Modules

Validation Rules Governing Import

Splitter for Exporting and Importing OWB Mappings

About Batch Services

Using the Batch Services Command Line Syntax

Running Multiple Command Line Operations

Locating and Reviewing Log Files

About the Warehouse Builder Transfer Wizard

Warehouse Builder Transfer Wizard Overview

Transfer Considerations

Importing Metadata into Warehouse Builder

Exporting Metadata from Warehouse Builder

About the Archive/Restore Utility

Setting up Preferences

Archiving a Project

Restoring a Project

Using Discoverer Workbooks for Metadata and Runtime Reporting

Installing the EUL Template and Workbooks

Prerequisites

Setting Database Connections

Creating Specific .eex Files

Importing .eex Files into Discoverer

Accessing the EUL within Discoverer Administration

Accessing Workbooks using Discoverer User Edition

Navigating Workbooks

Definition Workbook

QA Workbooks

Runtime Workbooks

Improving Performance of Workbooks

12 Metadata Reporting Using Warehouse Builder Browser

Accessing Reports from the Warehouse Builder Client

Setting up Browser Preferences

Opening the Reports

Accessing Reports from the Warehouse Builder Browser

Starting the Warehouse Builder Browser

Adding Portlets to the Warehouse Builder Browser

Available Warehouse Builder Browser Portlets

Using Warehouse Builder Browser to View Reports

About Warehouse Builder Reports

Available Warehouse Builder Reports

Summary Reports

Detailed Reports

Implementation Reports

Lineage and Impact Analysis Reports

Lineage and Impact Analysis Diagrams

Using Warehouse Builder Browser

Navigating the Warehouse Builder Browser

General Page Information

Current Item Information

Properties Tab

Contents Tab

Related Tab

Reports Tab

Links Tab

Browsing Favorites

General Page Information

Filter

Navigation Favorites

Reports Favorites

Customizing Your Favorites Page

Creating Custom Reports

Creating a Custom Report

Registering a Custom Report

Adding a Custom Report to a Role

Viewing a Custom Report

Creating Other Custom Reports

Using the Administration Pages

Registering a Warehouse Builder Repository

Administering Database Links

Unregistering a Repository

Registering a Custom Report

Purging Stale User Information

Resource Management

Managing Portlet Access

Managing Repositories

Managing Custom Reports

Understanding Roles

Managing Preferences

Saving Preferences

Loading Preferences

Managing the Dependency Index

Setting the Refresh Options

Refreshing the Dependency Index on Demand

A Keyboard Shortcuts

Keyboard Command Access

General Windows Keys

Tree View Control Keys

Accelerator Keys Set for Warehouse Builder

Menu Commands and Access Keys

Auto-Mapping Mnemonic Key Assignments

B Reserved Words

Reserved Words

C Mapping User Interface

Mapping Editor

Mapping Menu Bar

Mapping Editor Toolbar

Toolbox

Operator Property Inspector

Using the Mouse in the Mapping Editor

Left Mouse Click Operations

Tool Tips

Right Mouse Click Operations

Keyboard Operations

Other Dialogs

Add Operator Dialog

Add/Remove Attributes Dialog

Inbound and Outbound Reconcile Dialog

Physical Properties Inspector

D Performance Enhancements

Overview

Overhead-Free INSERT

Fast Index Creation

Fast Constraint Maintenance

Error Handling

Minimum Target Locking

Parallel Direct-Path INSERT

When to Use Partition Exchange Loading

Using Partition Exchange Loading

Configuring the Mapping

Configuring the Target

Create All Partitions

Create All Indexes Using the LOCAL option

Specify the USING INDEX Option for Primary/Unique Keys

Restrictions

Restriction 1: Allow One Date Partition Key

Restriction 2: Allow Natural Calendar System Only

Restriction 3: All Data Partitions Must be in the Same Tablespace

Restriction 4: All Index Partitions Must be in the Same Tablespace

Performance Comparisons

The Internal Workings

Function Example 1: GET_PN

Function Example 2: GET_VC

E Batch Services API

Repository

Open Repository Connection

Close Repository Connection

Projects

Modules

Validation

Generation

Deployment

Deployment into a File System

Deployment in a Database

Metadata Import

Metadata Export

F Using the XML Toolkit

Retrieving Data From Sources

Storing Data in Targets

Using Runtime Controls

How to Call the XML Toolkit

Two Entrances

Typical Control Files

XML Documents Stored in Files

The XML Document

The Target Table

The Control File

About the dateFormat Attribute

A Warehouse Builder Transformation

The Target Table

The Control File

The Style Sheet

A Warehouse Builder Transformation

Multiple Targets

XML Documents Stored as Other Objects

Document Stored as a CLOB

Document Stored as an Object-Based Advanced Queue

Document Stored at a URL

Control File Element Names and Attributes

Source Elements

Target Elements

Runtime Control

Document Type Definition for the Control File

Reference Materials

G OWB Public View Tables Management

Data Model

Implementation Model

Flat File/Record Model

Impact Analysis/Data Lineage Model

Classification Model

Expression & Function Model

Configuration & Deployment Model

Transformation/Mapping Model

Runtime Public Views

H Warehouse Builder Bridges: Transfer Parameters and Considerations

Transfer Parameters

Transfer Considerations

Importing Metadata from an Object Management Group Common Warehouse Metamodel Standard System

Importing Metadata from Computer Associates ERwin 3.5.1

Importing Metadata from Powersoft PowerDesigner 6.0

Importing Metadata from Oracle9i OLAP server

Exporting Metadata to Oracle Discoverer 3.1 and 4i

Configuring Warehouse Builder for Dimensional Reuse

Defining Dimensions and Facts in Warehouse Builder

Defining the Dummy Tables

Hiding Data Prior to Transfer

Importing Transferred Data into Discoverer

Dimensional Reuse Naming Conventions in Discoverer

Exporting Metadata to an Object Management Group CWM Standard System

Exporting Metadata to Oracle Express

OSA Configuration

Exporting Metadata to Oracle9i OLAP server

Glossary

Index