Oracle® Warehouse BuilderUser's Guide

10g Release 2 (10.2.0.2)

B28223-05

April 2009

Oracle Warehouse Builder User's Guide 10g Release 2 (10.2.0.2)

B28223-05

Copyright © 2000, 2009, Oracle and/or its affiliates. All rights reserved.

This software and related documentation are provided under a license agreement containing restrictions on use and disclosure and are protected by intellectual property laws. Except as expressly permitted in your license agreement or allowed by law, you may not use, copy, reproduce, translate, broadcast, modify, license, transmit, distribute, exhibit, perform, publish, or display any part, in any form, or by any means. Reverse engineering, disassembly, or decompilation of this software, unless required by law for interoperability, is prohibited.

The information contained herein is subject to change without notice and is not warranted to be error-free. If you find any errors, please report them to us in writing.

If this software or related documentation is delivered to the U.S. Government or anyone licensing it on behalf of the U.S. Government, the following notice is applicable:

U.S. GOVERNMENT RIGHTS Programs, software, databases, and related documentation and technical data delivered to U.S. Government customers are "commercial computer software" or "commercial technical data" pursuant to the applicable Federal Acquisition Regulation and agency-specific supplemental regulations. As such, the use, duplication, disclosure, modification, and adaptation shall be subject to the restrictions and license terms set forth in the applicable Government contract, and, to the extent applicable by the terms of the Government contract, the additional rights set forth in FAR 52.227-19, Commercial Computer Software License (December 2007). Oracle USA, Inc., 500 Oracle Parkway, Redwood City, CA 94065.

This software is developed for general use in a variety of information management applications. It is not developed or intended for use in any inherently dangerous applications, including applications which may create a risk of personal injury. If you use this software in dangerous applications, then you shall be responsible to take all appropriate fail-safe, backup, redundancy, and other measures to ensure the safe use of this software. Oracle Corporation and its affiliates disclaim any liability for any damages caused by use of this software in dangerous applications.

Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.

This software and documentation may provide access to or information on content, products, and services from third parties. Oracle Corporation and its affiliates are not responsible for and expressly disclaim all warranties of any kind with respect to third-party content, products, and services. Oracle Corporation and its affiliates will not be responsible for any loss, costs, or damages incurred due to your access to or use of third-party content, products, or services.

This program contains Batik version 1.6.

Apache License

Version 2.0, January 2004

http://www.apache.org/licenses/

For additional information about the terms and conditions, search for "Apache License" in Oracle Warehouse Builder online help.

iii

Contents

Preface ........................................................................................................................................................... xxxix

Audience................................................................................................................................................. xxxixDocumentation Accessibility ............................................................................................................... xxxixConventions ................................................................................................................................................. xlGetting Help................................................................................................................................................. xlRelated Publications................................................................................................................................... xli

What's New..................................................................................................................................................... xliii

New in Oracle Warehouse Builder 10g Release 2 (10.2.0.2) ............................................................... xliii

Part I Introduction and Concepts

1 Overview

About Oracle Warehouse Builder ......................................................................................................... 1-1Data Consolidation and Integration................................................................................................ 1-1

2 Creating an Oracle Data Warehouse

Understanding the Basic Concepts ....................................................................................................... 2-1General Steps for Creating an Oracle Data Warehouse .................................................................... 2-2About Locations........................................................................................................................................ 2-7About Modules ......................................................................................................................................... 2-8

3 Setting Up Warehouse Builder

Organizing Design Objects into Projects and Collections............................................................... 3-1Defining Collections................................................................................................................................ 3-2

Creating a Collection ......................................................................................................................... 3-2Name and Description Page...................................................................................................... 3-2Contents Page .............................................................................................................................. 3-2Summary Page............................................................................................................................. 3-3

Editing Collection Definitions.......................................................................................................... 3-3Name Tab ..................................................................................................................................... 3-3Contents Tab................................................................................................................................ 3-3

Setting Preferences................................................................................................................................... 3-4Appearance Preferences.................................................................................................................... 3-4

iv

Control Center Monitor Preferences ............................................................................................... 3-4Data Profiling Preferences ................................................................................................................ 3-5Deployment Preferences ................................................................................................................... 3-6Environment Preferences .................................................................................................................. 3-7Generation/Validation Preferences ................................................................................................ 3-7Logging Preferences........................................................................................................................... 3-8Naming Preferences........................................................................................................................... 3-8

About Naming Modes................................................................................................................ 3-9Security Preferences........................................................................................................................ 3-10

Creating Additional Configurations ................................................................................................. 3-11Creating a New Configuration...................................................................................................... 3-11Activating a Configuration ............................................................................................................ 3-11

About Connectors.................................................................................................................................. 3-12About Validation ................................................................................................................................... 3-12

4 Designing Target Schemas

Creating Oracle Data Objects ................................................................................................................ 4-1About Data Objects ............................................................................................................................ 4-1Naming Conventions for Data Objects ........................................................................................... 4-4Supported Data Types....................................................................................................................... 4-4

About the Data Object Editor ................................................................................................................ 4-7Data Object Editor Components ........................................................................................................... 4-7

Title Bar................................................................................................................................................ 4-8Menu Bar ............................................................................................................................................. 4-9

Diagram........................................................................................................................................ 4-9Object ......................................................................................................................................... 4-10Edit ............................................................................................................................................. 4-10View ........................................................................................................................................... 4-11Window ..................................................................................................................................... 4-11Help............................................................................................................................................ 4-12

Toolbar.............................................................................................................................................. 4-12Explorer ............................................................................................................................................ 4-12Palette................................................................................................................................................ 4-13Configuration................................................................................................................................... 4-13Bird's Eye View................................................................................................................................ 4-13Canvas............................................................................................................................................... 4-14

Canvas Tabs .............................................................................................................................. 4-14Canvas Icons ............................................................................................................................. 4-15Performing Operations on a Data Object Using the Canvas ............................................. 4-15

Object Details ................................................................................................................................... 4-16Generation........................................................................................................................................ 4-17Indicator Bar..................................................................................................................................... 4-17

Data Viewer ............................................................................................................................................ 4-17Using the Data Object Editor .............................................................................................................. 4-18

Creating a Data Object Using the Menu Bar ............................................................................... 4-19Creating a Data Object Using the Canvas ................................................................................... 4-19Creating a Data Object Using the Data Object Editor Palette................................................... 4-20

v

Add a New or Existing Data Object Dialog ................................................................................ 4-20Configuring Data Objects.................................................................................................................... 4-21About Attribute Sets ............................................................................................................................. 4-21About Constraints ................................................................................................................................. 4-22About Indexes ........................................................................................................................................ 4-23

Creating Indexes.............................................................................................................................. 4-23About Partitions..................................................................................................................................... 4-24About Dimensional Objects................................................................................................................ 4-25

Defining Dimensional Objects....................................................................................................... 4-26Implementing Dimensional Objects ............................................................................................. 4-26

Relational Implementation of Dimensional Objects ........................................................... 4-26Binding ............................................................................................................................... 4-27

ROLAP Implementation of Dimensional Objects ............................................................... 4-27MOLAP Implementation of Dimensional Objects .............................................................. 4-28

Analytic Workspace ........................................................................................................ 4-28OLAP Catalog ................................................................................................................... 4-28

Deploying Dimensional Objects ................................................................................................... 4-29Loading Dimensional Objects ....................................................................................................... 4-30

About Dimensions ................................................................................................................................ 4-30Rules for Dimension Objects ......................................................................................................... 4-30Defining a Dimension..................................................................................................................... 4-31

Defining Dimension Attributes.............................................................................................. 4-31Defining Levels......................................................................................................................... 4-31

Surrogate Identifiers......................................................................................................... 4-31Business Identifiers........................................................................................................... 4-32Parent Identifier ................................................................................................................ 4-32

Defining Level Attributes ....................................................................................................... 4-32Defining Hierarchies ............................................................................................................... 4-32Dimension Roles ...................................................................................................................... 4-33Level Relationships.................................................................................................................. 4-33Dimension Example................................................................................................................. 4-34Value-based Hierarchies ......................................................................................................... 4-34

Implementing a Dimension ........................................................................................................... 4-35Relational and ROLAP Implementation of a Dimension................................................... 4-35

Star Schema........................................................................................................................ 4-35Snowflake Schema ............................................................................................................ 4-36

Binding ...................................................................................................................................... 4-36MOLAP Implementation ........................................................................................................ 4-38

About Slowly Changing Dimensions ............................................................................................... 4-38About Type 1 SCDs......................................................................................................................... 4-39About Type 2 SCDs......................................................................................................................... 4-39

Defining a Type 2 SCD............................................................................................................ 4-40About Type 3 SCDs......................................................................................................................... 4-40

Defining a Type 3 SCD............................................................................................................ 4-40About Cubes........................................................................................................................................... 4-41

Defining a Cube............................................................................................................................... 4-42Cube Measures ......................................................................................................................... 4-42

vi

Cube Dimensionality............................................................................................................... 4-42Cube Example........................................................................................................................... 4-43

Implementing a Cube ..................................................................................................................... 4-43Relational and ROLAP Implementation of a Cube............................................................. 4-43Binding ...................................................................................................................................... 4-44MOLAP Implementation of a Cube ...................................................................................... 4-45

Solve Dependency Order of Cube ................................................................................................ 4-45About Time Dimensions ...................................................................................................................... 4-45

Best Practices for Creating a Time Dimension............................................................................ 4-46Defining a Time Dimension........................................................................................................... 4-46

Levels ......................................................................................................................................... 4-46Dimension Attributes .............................................................................................................. 4-47Level Attributes........................................................................................................................ 4-47Hierarchies ................................................................................................................................ 4-48

Implementing a Time Dimension ................................................................................................. 4-49Using a Time Dimension in a Cube Mapping............................................................................. 4-49Populating a Time Dimension....................................................................................................... 4-49

Overlapping Data Populations .............................................................................................. 4-50

5 Identifying Data Sources and Importing Metadata

About Source Data and Metadata ......................................................................................................... 5-1General Steps for Importing Metadata from Sources ....................................................................... 5-1

Example: Importing Metadata from Flat Files............................................................................... 5-2Supported Sources and Targets ............................................................................................................. 5-2Integrating with Business Intelligence Tools ..................................................................................... 5-3

Introduction to Business Intelligence Objects in Warehouse Builder ........................................ 5-4Introduction to Business Definitions............................................................................................... 5-4About Business Definitions .............................................................................................................. 5-5About Business Presentations .......................................................................................................... 5-5

6 Creating Mappings

About Mappings and Operators............................................................................................................ 6-1Instructions for Defining Mappings .................................................................................................... 6-2

Instructions for Using Flat File Sources or Targets in a Mapping .............................................. 6-3Creating a Mapping ................................................................................................................................. 6-4

About the Mapping Editor ............................................................................................................... 6-5Mapping Editor Windows ................................................................................................................ 6-7

Explorer ........................................................................................................................................ 6-7Properties Inspector.................................................................................................................... 6-7Palette ........................................................................................................................................... 6-7Bird Eye View.............................................................................................................................. 6-7Data Viewer ................................................................................................................................. 6-7Generation.................................................................................................................................... 6-7

Mapping Editor Toolbars.................................................................................................................. 6-8Mapping Editor Display Options .................................................................................................... 6-8Types of Operators............................................................................................................................. 6-8

Oracle Source/Target Operators .............................................................................................. 6-9

vii

Data Flow Operators .................................................................................................................. 6-9Pre/Post Processing Operators.............................................................................................. 6-10Pluggable Mapping Operators............................................................................................... 6-11

Adding Operators.................................................................................................................................. 6-11Adding Operators that Bind to Repository Objects ................................................................... 6-12

Add Operator Dialog............................................................................................................... 6-13Create Unbound Operator with No Attributes ................................................................... 6-13Select from Existing Repository Object and Bind................................................................ 6-14

Editing Operators .................................................................................................................................. 6-14Name Tab ......................................................................................................................................... 6-14Groups Tab....................................................................................................................................... 6-15Input and Output Tabs................................................................................................................... 6-15Mapping Naming Conventions .................................................................................................... 6-16Using Display Sets .......................................................................................................................... 6-17

Defining Display Sets .............................................................................................................. 6-17Selecting a Display Set ............................................................................................................ 6-18

Connecting Operators........................................................................................................................... 6-18Connecting Attributes .................................................................................................................... 6-19Connecting Groups ......................................................................................................................... 6-20

Example: Using the Mapping Editor to Create Staging Area Tables ............................... 6-20Using the Connect Operators Dialog .................................................................................... 6-21Copy Source Attributes to Target Group and Match ......................................................... 6-22Match by Position of Source and Target Attributes............................................................ 6-22Match by Name of Source and Target Attributes ............................................................... 6-23

Using Pluggable Mappings ................................................................................................................. 6-23Pluggable Mapping Folders .......................................................................................................... 6-24Creating a Pluggable Mapping ..................................................................................................... 6-24

Standalone Pluggable Mapping............................................................................................. 6-24Pluggable Mapping Folder ..................................................................................................... 6-24Signature Groups ..................................................................................................................... 6-25Input Signature......................................................................................................................... 6-25Output Signature ..................................................................................................................... 6-25

Pluggable Mapping Implementation ........................................................................................... 6-26Pluggable Mapping Usage............................................................................................................. 6-26

Pluggable Mapping Usage Signature ................................................................................... 6-26Types of Pluggable Mapping Usage ..................................................................................... 6-26

Pluggable Mapping Editor............................................................................................................. 6-27Setting Mapping Properties ................................................................................................................ 6-27

Target Load Order .......................................................................................................................... 6-27Reset to Default ........................................................................................................................ 6-28

Setting Operator, Group, and Attribute Properties ........................................................................ 6-28Synchronizing Operators and Repository Objects ......................................................................... 6-29

Synchronizing An Operator .......................................................................................................... 6-30Synchronizing From a Repository Object to an Operator......................................................... 6-30

Synchronizing Operators based on Repository Objects..................................................... 6-31Synchronizing from an Operator to a Repository Object.......................................................... 6-32Advanced Options for Synchronizing ........................................................................................ 6-32

viii

Matching Strategies ................................................................................................................ 6-33Debugging a Mapping ......................................................................................................................... 6-34

Starting a Debug Session................................................................................................................ 6-34The Debug Panels of the Mapping Editor ................................................................................... 6-35

Debug Info Panel...................................................................................................................... 6-35Debug Data Panel .................................................................................................................... 6-35

Defining Test Data .......................................................................................................................... 6-35Creating New Tables to Use as Test Data ............................................................................ 6-36Editing the Test Data ............................................................................................................... 6-36

Setting Breakpoints ......................................................................................................................... 6-37Setting Watches ............................................................................................................................... 6-37Running the Mapping .................................................................................................................... 6-37

Selecting the First Source and Path to Debug...................................................................... 6-38Debugging Mappings with Correlated Commit ................................................................. 6-38Setting a Starting Point............................................................................................................ 6-39Debugging Pluggable Submap Operators ........................................................................... 6-39

Re-Initializing a Debug Session .................................................................................................... 6-39Scalability ......................................................................................................................................... 6-40

7 Designing Process Flows

About Process Flows ................................................................................................................................ 7-1About Process Flow Modules and Packages.................................................................................. 7-2

Instructions for Defining Process Flows ............................................................................................. 7-2Creating Process Flow Modules....................................................................................................... 7-3Creating Process Flow Packages...................................................................................................... 7-3Creating Process Flows ..................................................................................................................... 7-3

About the Process Flow Editor............................................................................................................... 7-4Process Flow Editor Windows ......................................................................................................... 7-6Displaying the Process Flow Editor ................................................................................................ 7-6Navigating the Process Flow Editor................................................................................................ 7-7About Activities.................................................................................................................................. 7-8

Adding Activities ........................................................................................................................ 7-9Parameters for Activities......................................................................................................... 7-10

Creating and Using Activity Templates ...................................................................................... 7-11Name and Description Page................................................................................................... 7-11Parameters Page ....................................................................................................................... 7-12Using Activity Templates ....................................................................................................... 7-13

About Transitions............................................................................................................................ 7-14Rules for Valid Transitions ..................................................................................................... 7-15Connecting Activities .............................................................................................................. 7-15Configuring Activities............................................................................................................. 7-16Using parameters and variables ............................................................................................ 7-16Using Namespace .................................................................................................................... 7-17Using Bindings ......................................................................................................................... 7-17

Expressions....................................................................................................................................... 7-17Global Expression Values ....................................................................................................... 7-17

Defining Transition Conditions .................................................................................................... 7-18

ix

Evaluation Security Context .......................................................................................................... 7-19

8 Understanding Performance and Advanced ETL Concepts

Best Practices for Designing PL/SQL Mappings................................................................................ 8-1Set Based Versus Row Based Operating Modes............................................................................ 8-4

Set based....................................................................................................................................... 8-5Row based .................................................................................................................................... 8-5Row based (Target Only) ........................................................................................................... 8-6

About Committing Data in Warehouse Builder............................................................................ 8-7Committing Data Based on Mapping Design ................................................................................ 8-7

Committing Data from a Single Source to Multiple Targets ................................................ 8-7Automatic Commit versus Automatic Correlated Commit ................................................. 8-8Embedding Commit Logic into the Mapping......................................................................... 8-9

Committing Data Independently of Mapping Design .............................................................. 8-10Running Multiple Mappings Before Committing Data............................................................. 8-10

Committing Data at Runtime................................................................................................. 8-11Committing Mappings Using the Process Flow Editor...................................................... 8-12

Ensuring Referential Integrity in PL/SQL Mappings ............................................................... 8-13Best Practices for Designing SQL*Loader Mappings .................................................................... 8-14

Using Conventional Loading to Ensure Referential Integrity in SQL*Loader Mappings.... 8-14Maintaining Relationships Between Master and Detail Records...................................... 8-15Extracting and Loading Master-Detail Records .................................................................. 8-16Error Handling Suggestions................................................................................................... 8-19Subsequent Operations ........................................................................................................... 8-20

Using Direct Path Loading to Ensure Referential Integrity in SQL*Loader Mappings........ 8-21Improved Performance Through Partition Exchange Loading .................................................... 8-24

About Partition Exchange Loading .............................................................................................. 8-25Configuring a Mapping for PEL ................................................................................................... 8-25Direct and Indirect PEL.................................................................................................................. 8-26

Using Indirect PEL................................................................................................................... 8-26Example: Using Direct PEL to Publish Fact Tables............................................................. 8-27

Using PEL Effectively ..................................................................................................................... 8-28Configuring Targets in a Mapping............................................................................................... 8-29

Step 1: Create All Partitions.................................................................................................... 8-29Step 2: Create All Indexes Using the LOCAL Option ........................................................ 8-31Step 3: Primary/Unique Keys Use "USING INDEX" Option............................................ 8-31

Restrictions for Using PEL in Warehouse Builder ..................................................................... 8-31High Performance Data Extraction from Remote Sources ............................................................ 8-31

9 Using Oracle Warehouse Builder Transformations

About Transformations ........................................................................................................................... 9-1Types of Transformations ................................................................................................................. 9-1

Predefined Transformations...................................................................................................... 9-1Custom Transformations ........................................................................................................... 9-2

Transforming Data Using Warehouse Builder .............................................................................. 9-3Benefits of Using Warehouse Builder for Transforming Data .................................................... 9-3

x

About Transformation Libraries............................................................................................................ 9-3Types of Transformation Libraries .................................................................................................. 9-3Accessing Transformation Libraries................................................................................................ 9-4

Defining Custom Transformations ....................................................................................................... 9-5Defining Functions and Procedures ................................................................................................ 9-7

Name and Description Page...................................................................................................... 9-7Parameters Page .......................................................................................................................... 9-7Implementation Page ................................................................................................................ 9-8Summary Page............................................................................................................................. 9-8

Defining PL/SQL Types ................................................................................................................... 9-8About PL/SQL Types................................................................................................................. 9-8Usage Scenario for PL/SQL Types........................................................................................... 9-9Creating PL/SQL Types ......................................................................................................... 9-10Name and Description Page................................................................................................... 9-11Attributes Page......................................................................................................................... 9-11Return Type Page..................................................................................................................... 9-11Summary Page.......................................................................................................................... 9-12

Editing Transformation Properties .................................................................................................... 9-12Editing Function or Procedure Definitions ................................................................................. 9-12

Name Tab .................................................................................................................................. 9-13Parameters Tab......................................................................................................................... 9-13Implementation Tab ................................................................................................................ 9-13

Editing PL/SQL Types ................................................................................................................... 9-13Name Tab .................................................................................................................................. 9-14Attributes Tab........................................................................................................................... 9-14Return Type Tab....................................................................................................................... 9-14

Importing PL/SQL ................................................................................................................................. 9-14

10 Understanding Data Quality Management

About the Data Quality Management Process ................................................................................ 10-1Phases in the Data Quality Life Cycle .......................................................................................... 10-2

Quality Assessment ................................................................................................................. 10-2Quality Design.......................................................................................................................... 10-2Quality Transformation .......................................................................................................... 10-3Quality Monitoring.................................................................................................................. 10-3

About Data Profiling ............................................................................................................................ 10-3Uses of Data Profiling..................................................................................................................... 10-3Types of Data Profiling................................................................................................................... 10-4

Attribute Analysis ................................................................................................................... 10-4Functional Dependency .......................................................................................................... 10-5Referential Analysis................................................................................................................. 10-6Data Rule Profiling .................................................................................................................. 10-7

How to Perform Data Profiling..................................................................................................... 10-7Import or Select the Metadata................................................................................................ 10-8Create a Data Profile................................................................................................................ 10-8Profile the Data......................................................................................................................... 10-8View Profile Results and Derive Data Rules........................................................................ 10-9

xi

Generating Corrections ........................................................................................................... 10-9Define and Edit Data Rules Manually .................................................................................. 10-9Generate, Deploy, and Execute.............................................................................................. 10-9

About Six Sigma .............................................................................................................................. 10-9What is Six Sigma?................................................................................................................... 10-9Six Sigma Metrics for Data Profiling................................................................................... 10-10

About Data Quality............................................................................................................................. 10-11About the Match-Merge Operator.............................................................................................. 10-11About the Name and Address Operator ................................................................................... 10-11Example: Correcting Address Information ............................................................................... 10-12

Example Input ........................................................................................................................ 10-12Example Steps......................................................................................................................... 10-13Example Output ..................................................................................................................... 10-14

Handling Errors in Name and Address Data ........................................................................... 10-15About Postal Reporting................................................................................................................ 10-15

United States Postal Service CASS Certification ............................................................... 10-16Canada Post SERP Certification........................................................................................... 10-16Australia Post AMAS Certification ..................................................................................... 10-16

About Data Rules ................................................................................................................................ 10-16About Quality Monitoring ................................................................................................................ 10-17

About Data Auditors .................................................................................................................... 10-17

11 Deploying to Target Schemas and Executing ETL Logic

About Deployment ............................................................................................................................... 11-1What is a Control Center? .............................................................................................................. 11-2Configuring the Physical Details of Deployment....................................................................... 11-2Deployment Actions ....................................................................................................................... 11-3The Deployment Process................................................................................................................ 11-3

About Schedules.................................................................................................................................... 11-3Process for Defining and Using Schedules ...................................................................................... 11-4Deploying Objects ................................................................................................................................ 11-5Starting ETL Jobs................................................................................................................................... 11-5

Viewing the Data............................................................................................................................. 11-6

Part II Data Modeling Reference

12 Reference for Using Oracle Data Objects

Using the Data Object Editor to Edit Oracle Data Objects ........................................................... 12-1Using Constraints .................................................................................................................................. 12-2

Creating Constraints ....................................................................................................................... 12-2Creating Primary Key Constraints ........................................................................................ 12-2Creating Foreign Key Constraints ......................................................................................... 12-3Creating Unique Key Constraints ......................................................................................... 12-3Creating Check Constraints.................................................................................................... 12-4

Editing Constraints ......................................................................................................................... 12-4Using Partitions ..................................................................................................................................... 12-4

xii

Range Partitioning .......................................................................................................................... 12-5Example of Range Partitioning .............................................................................................. 12-6

Hash Partitioning ............................................................................................................................ 12-6Hash By Quantity Partitioning...................................................................................................... 12-7List Partitioning............................................................................................................................... 12-7

Example of List Partitioning................................................................................................... 12-8Composite Partitioning .................................................................................................................. 12-9

About the Subpartition Template........................................................................................ 12-10Creating Custom Subpartitions ........................................................................................... 12-10

Index Partitioning ......................................................................................................................... 12-11Index Performance Considerations ..................................................................................... 12-12

Configuring Partitions.................................................................................................................. 12-12Using Tables ......................................................................................................................................... 12-13

Creating Table Definitions........................................................................................................... 12-13Name Tab ................................................................................................................................ 12-14Columns Tab........................................................................................................................... 12-14Constraints Tab ...................................................................................................................... 12-15Indexes Tab ............................................................................................................................. 12-15Partitions Tab.......................................................................................................................... 12-15Attribute Sets Tab .................................................................................................................. 12-15Data Rules Tab........................................................................................................................ 12-15

Editing Table Definitions ............................................................................................................. 12-16Renaming a Table................................................................................................................... 12-16Adding, Modifying, and Removing Table Columns........................................................ 12-16Adding, Modifying, and Deleting Table Constraints....................................................... 12-16Adding, Editing, and Deleting Attribute Sets ................................................................... 12-16Reordering Columns in a Table ........................................................................................... 12-16

Using Views.......................................................................................................................................... 12-17About Views .................................................................................................................................. 12-17Creating View Definitions ........................................................................................................... 12-17

Name Tab ................................................................................................................................ 12-18Columns Tab........................................................................................................................... 12-18Query Tab................................................................................................................................ 12-18Constraints Tab ...................................................................................................................... 12-18Attribute Sets Tab .................................................................................................................. 12-19Data Rules Tab........................................................................................................................ 12-19Data Viewer Tab..................................................................................................................... 12-19

Editing View Definitions.............................................................................................................. 12-19Renaming a View ................................................................................................................... 12-19Adding, Editing, and Removing View Columns .............................................................. 12-19Adding, Editing, and Deleting View Constraints ............................................................. 12-19Adding, Editing, and Removing Attribute Sets ................................................................ 12-19

Using Materialized Views ................................................................................................................. 12-20About Materialized Views ........................................................................................................... 12-20Creating Materialized View Definitions.................................................................................... 12-20

Name Tab ................................................................................................................................ 12-21Columns Tab........................................................................................................................... 12-21

xiii

Query Tab................................................................................................................................ 12-21Constraints Tab ...................................................................................................................... 12-21Indexes Tab ............................................................................................................................. 12-21Partitions Tab.......................................................................................................................... 12-21Attribute Sets Tab .................................................................................................................. 12-22Data Rules Tab........................................................................................................................ 12-22

Editing Materialized View Definitions ...................................................................................... 12-22Renaming Materialized Views............................................................................................. 12-22Adding, Editing, and Deleting Materialized View Columns.......................................... 12-22Adding, Editing, and Deleting Materialized View Constraints...................................... 12-22Adding, Editing, and Deleting Attribute Sets ................................................................... 12-22

Using Attribute Sets............................................................................................................................ 12-22Creating Attribute Sets ................................................................................................................. 12-23Editing Attribute Sets ................................................................................................................... 12-24

Using Sequences.................................................................................................................................. 12-25About Sequences ........................................................................................................................... 12-25Creating a Sequence Definition................................................................................................... 12-25Editing Sequence Definitions ...................................................................................................... 12-25

Name Tab ................................................................................................................................ 12-26Columns Tab........................................................................................................................... 12-26

Editing Sequence Column Descriptions .................................................................................... 12-26Using User Defined Types................................................................................................................. 12-26

About Object Types ...................................................................................................................... 12-26Creating Object Types .................................................................................................................. 12-27

Name Tab ................................................................................................................................ 12-27Columns Tab........................................................................................................................... 12-27

Editing Object Types..................................................................................................................... 12-28About Varrays ............................................................................................................................... 12-28Creating Varrays ........................................................................................................................... 12-29

Name Tab ................................................................................................................................ 12-29Details Tab .............................................................................................................................. 12-29

Editing Varrays.............................................................................................................................. 12-29About Nested Tables .................................................................................................................... 12-29Creating Nested Tables ................................................................................................................ 12-30

Name Tab ................................................................................................................................ 12-30Details Tab .............................................................................................................................. 12-30

Editing Nested Tables................................................................................................................... 12-30Configuring Data Objects.................................................................................................................. 12-31

Configuring Warehouse Builder Design Objects ..................................................................... 12-31Configuring Target Modules....................................................................................................... 12-32

Identification........................................................................................................................... 12-32Tablespace Defaults ............................................................................................................... 12-33Generation Preferences ......................................................................................................... 12-33Deployment System Type..................................................................................................... 12-33Run Time Directories............................................................................................................. 12-33Generation Target Directories.............................................................................................. 12-33

Configuring Tables ....................................................................................................................... 12-34

xiv

Configuring Materialized Views ................................................................................................ 12-35Materialized View Parameters............................................................................................. 12-36Fast Refresh for Materialized Views ................................................................................... 12-37

Configuring Views........................................................................................................................ 12-38Configuring Sequences................................................................................................................. 12-38

13 Defining Dimensional Objects

Creating Dimensions ............................................................................................................................ 13-1Using the Create Dimension Wizard ........................................................................................... 13-2

Name and Description Page................................................................................................... 13-2Storage Type Page.................................................................................................................... 13-2Dimension Attributes Page..................................................................................................... 13-3Levels Page................................................................................................................................ 13-4Level Attributes Page .............................................................................................................. 13-5Slowly Changing Dimension Page........................................................................................ 13-6Pre Create Settings Page ......................................................................................................... 13-6Dimension Creation Progress Page....................................................................................... 13-7Summary Page.......................................................................................................................... 13-7Defaults Used By the Create Dimension Wizard ................................................................ 13-7

Storage ................................................................................................................................ 13-7Dimension Attributes....................................................................................................... 13-8Hierarchies......................................................................................................................... 13-8Level Attributes................................................................................................................. 13-8Slowly Changing Dimensions......................................................................................... 13-8Implementation Objects................................................................................................... 13-8

Using the Data Object Editor......................................................................................................... 13-9Name Tab ................................................................................................................................ 13-10Storage Tab.............................................................................................................................. 13-10Attributes Tab......................................................................................................................... 13-11Levels Tab ............................................................................................................................... 13-11Hierarchies Tab ...................................................................................................................... 13-12SCD Tab................................................................................................................................... 13-13Data Viewer Tab..................................................................................................................... 13-14Binding Attributes ................................................................................................................. 13-14

Creating Slowly Changing Dimensions Using the Data Object Editor ................................... 13-15Creating a Type 2 SCD ................................................................................................................. 13-15

Type 2 Slowly Changing Dimension Dialog...................................................................... 13-16Creating a Type 3 SCD ................................................................................................................. 13-17

Type 3 Slowly Changing Dimension Dialog...................................................................... 13-17Editing Dimension Definitions.................................................................................................... 13-19

Configuring Dimensions ................................................................................................................... 13-19Deployment Options for Dimensions ........................................................................................ 13-19

Deployment Options for Different Dimension Implementations................................... 13-20Creating Cubes..................................................................................................................................... 13-20

Using the Create Cube Wizard ................................................................................................... 13-20Name and Description Page................................................................................................. 13-20Storage Type Page.................................................................................................................. 13-21

xv

Dimensions Page.................................................................................................................... 13-21Measures Page........................................................................................................................ 13-22Summary Page........................................................................................................................ 13-22Defaults Used by the Create Cube Wizard ........................................................................ 13-23

Using the Data Object Editor....................................................................................................... 13-23Name Tab ................................................................................................................................ 13-24Storage Tab ............................................................................................................................. 13-24Dimensions Tab...................................................................................................................... 13-25

Advanced Dialog ............................................................................................................ 13-25Measures Tab.......................................................................................................................... 13-26Calculated Measure Wizard ................................................................................................. 13-27

Select Calculated Measure Type................................................................................... 13-27Define Calculated Measure Details.............................................................................. 13-30Reviewing the Summary Information ......................................................................... 13-30

Aggregation Tab..................................................................................................................... 13-30Precomputing ROLAP Cubes ...................................................................................... 13-31

Data Viewer Tab..................................................................................................................... 13-31Binding Cube Attributes ....................................................................................................... 13-31

Cubes Stored in Analytic Workspaces ....................................................................................... 13-32Ragged Cube Data in Warehouse Builder.......................................................................... 13-32Defining Aggregations .......................................................................................................... 13-32Auto Solving MOLAP Cubes ............................................................................................... 13-32Solving Cube Measures......................................................................................................... 13-33Solving Cubes Independent of Loading ............................................................................. 13-33Parallel Solving of Cubes ...................................................................................................... 13-34Output of a MOLAP Cube Mapping .................................................................................. 13-34

Editing Cube Definitions.............................................................................................................. 13-34Configuring Cubes.............................................................................................................................. 13-35Creating Time Dimensions................................................................................................................ 13-35

Creating a Time Dimension Using the Time Dimension Wizard .......................................... 13-36Name and Description Page................................................................................................. 13-36Storage Page............................................................................................................................ 13-36Data Generation Page............................................................................................................ 13-37Levels Page (Calendar Time Dimension Only) ................................................................. 13-37Levels Page (Fiscal Time Dimension Only) ....................................................................... 13-38Pre Create Settings Page ....................................................................................................... 13-38Time Dimension Progress Page ........................................................................................... 13-38Summary Page........................................................................................................................ 13-38Defaults Used by the Time Dimension Wizard................................................................. 13-39

Editing Time Dimension Definitions ......................................................................................... 13-39Name Tab ................................................................................................................................ 13-39Storage Tab ............................................................................................................................. 13-40Attributes Tab......................................................................................................................... 13-40Levels Tab ............................................................................................................................... 13-41Hierarchies Tab ...................................................................................................................... 13-41

xvi

14 Defining Flat Files and External Tables

About Flat Files in Warehouse Builder ............................................................................................. 14-1Flat Files as Sources ........................................................................................................................ 14-1

Importing ASCII Files into the Repository........................................................................... 14-1Adding Existing Binary Files to the Repository .................................................................. 14-2About External Tables ............................................................................................................. 14-2External Table Operators versus Flat File Operators.......................................................... 14-3

Flat Files as Targets ......................................................................................................................... 14-3Creating Flat File Modules............................................................................................................. 14-3

Describing the Flat File Module............................................................................................. 14-4Defining Locations for Flat File Modules ............................................................................. 14-4Connection Information Page ................................................................................................ 14-4Edit File System Location Dialog........................................................................................... 14-5

Using the Create Flat File Wizard ...................................................................................................... 14-5Describing a New Flat File............................................................................................................. 14-5Defining File Properties for a New Flat File................................................................................ 14-6

Record Organization ............................................................................................................... 14-6Logical Record Definition ....................................................................................................... 14-6Number of Rows to Skip......................................................................................................... 14-7Field Format.............................................................................................................................. 14-7

Defining the Record Type for a New Flat File ............................................................................ 14-7Defining Field Properties for a New Flat File ............................................................................. 14-8

SQL*Loader Properties............................................................................................................ 14-8SQL Properties.......................................................................................................................... 14-9

Using the Import Metadata Wizard for Flat Files ......................................................................... 14-10Using the Flat File Sample Wizard .................................................................................................. 14-12

Describing the Flat File................................................................................................................. 14-13Selecting the Record Organization ............................................................................................. 14-14

Specifying Logical Records................................................................................................... 14-14Selecting the File Format .............................................................................................................. 14-15Selecting the File Layout .............................................................................................................. 14-16Selecting Record Types (Multiple Record Type Files Only) ................................................... 14-17

Example: Flat File with Multiple Record Types ................................................................ 14-17Defining Multiple Record Organization in a Delimited File ........................................... 14-18Defining Multiple Record Organization in a Fixed-Length File ..................................... 14-19

Specifying Field Lengths (Fixed-Length Files Only)................................................................ 14-20Specifying Field Lengths for Multiple Record Files.......................................................... 14-21

Specifying Field Properties .......................................................................................................... 14-21SQL*Loader Properties.......................................................................................................... 14-22SQL Properties........................................................................................................................ 14-23

Updating a File Definition ................................................................................................................ 14-24Name Tab ....................................................................................................................................... 14-24General Tab .................................................................................................................................... 14-24Record Tab ..................................................................................................................................... 14-25Structure Tab.................................................................................................................................. 14-26

Using External Tables ............................................................