Preface

Oracle9i Warehouse Builder is a comprehensive toolset designed for practitioners who develop and implement data warehouses. This guide describes how to use Warehouse Builder to:

• Create a definition of a data warehouse.

• Configure the definitions for a physical instance of the logical warehouse.

• Validate the set of definitions and their configurations.

• Generate a set of scripts to create and populate the physical database instance.

• Generate data transformation scripts.

• Deploy and initially load the physical instance.

• Maintain the physical instance by conditionally refreshing it with generated scripts.

Audience

This guide is intended for data warehouse practitioners including:

• Warehouse Architects, Designers, and Developers

• Data Analysts and those who develop extract, transform, and load routines

• Developers of large-scale products based on data warehouses

• Warehouse Administrators

• System Administrators

• Other MIS professionals

In order to use the information in this guide, you must know SQL and PL/SQL. You need to be comfortable with concepts of Relational Database Management Systems and Data Warehouse design. For information on data warehousing, refer to the Oracle8i/9i Data Warehousing Guide. Also, you need to be familiar with Oracle's relational database software products such as Oracle8i/9i, SQL*Plus, SQL*Loader, Oracle Enterprise Manager, and Oracle Workflow.

How This Guide Is Organized

The Oracle9i Warehouse Builder User's Guide contains the following chapters and appendixes.

Chapter 1, "Overview", introduces you to Oracle9i Warehouse Builder and describes the design and implementation process for a data warehouse.

Chapter 2, "Getting Started on a Project", introduces the Warehouse Builder repository and how to log on to the repository using the Warehouse Builder client. It also describes how to start a project, and how Warehouse Builder handles multiple users on the same module.

Chapter 3, "Defining Relational Targets", describe how to define relational model targets and how to edit their properties.

Chapter 4, "Defining Dimensional Targets" describes how to define dimensional model targets and how to edit their properties.

Chapter 5, "Defining Source Modules" shows you how to create definitions for data sources using Warehouse Builder wizards. The chapter describes how to import the definitions from Oracle and non-Oracle database systems, and Designer repositories. It shows you how to import data from a flat file source and create formats for the files. This chapter also shows you how to modify definitions, generate and display diagrams for the source objects, and print diagrams.

Chapter 6, "Defining Source to Target Mappings" introduces the concepts of mapping data sources to data targets. It also describes how to create logical definitions that map source operator attributes to target operator attributes.

Chapter 7, "Using Mapping Operators and Transformations" describes the various tools available for transforming data as it moves from the source to the target. The chapter describes the Oracle Transformation Library, the Global Shared (transformation) Libraries, Expression Builder, custom transformations, and SQL operations such as filters, splitters, and aggregation functions.

Chapter 8, "Configuring and Generating Mappings" describes how to configure mapping operators, attributes, and physical properties. It also describes how to define code generation strategies for optimal performance during deployment.

Chapter 9, "Configuring, Generating, and Deploying", describes how to configure and validate the definitions for a specific instance. The chapter then describes how to generate and deploy the scripts that extract, transform, and load data into your data warehouse. These scripts create and populate the data warehouse with dimensions, facts, views, and materialized views.

Chapter 10, "Managing Loads and Updates", describes how to load your data warehouse. It then describes how to register job scripts with Oracle Enterprise Manager to schedule periodic loading into the warehouse.

Chapter 11, "Administration", describes administrative tasks on your warehouse using Warehouse Builder utilities. It describes how to create and drop Warehouse Builder repositories, how to install or drop a Warehouse Builder Runtime Catalog, how to display audit statistics and error messages stored in the Runtime tables, and how to export definitions from a Warehouse Builder repository and import definitions from a Warehouse Builder Metadata file.

Chapter 12, "Metadata Reporting Using Warehouse Builder Browser", describes how to use Oracle Portal to view reports and create your own reports on metadata.

Appendix A, "Keyboard Shortcuts" lists keyboard shortcuts for Warehouse Builder commands.

Appendix B, "Reserved Words", lists the words reserved for Warehouse Builder.

Appendix C, "Mapping User Interface" provides a reference for the Mapping Editor.

Appendix D, "Performance Enhancements" describes performance improvements.

Appendix E, "Batch Services API" describes how to use the Warehouse Builder API commands in batch mode.

Appendix F, "Using the XML Toolkit", describes how to retrieve and store data from XML documents in a target schema.

Appendix G, "OWB Public View Tables Management", lists the Warehouse Builder Public Views that can be accessed through SQL. These views can be used to extend the reporting capabilities of Warehouse Builder.

Appendix H, "Warehouse Builder Bridges: Transfer Parameters and Considerations" describes paramenters required for importing and exporting metadata.

Conventions

The SQL*Plus interface to Oracle8i may be referred to as SQL. This interface is the Oracle8i/9i implementation of the SQL standard ANSI X3.135-1992, ISO 9075:1992, commonly referred to as the ANSI/ISO SQL standard or SQL92.

In the examples, an implied carriage return occurs at the end of each line, unless otherwise noted. You must press the Return key at the end of a line of input.

The following conventions are also used in this manual:

Convention	Meaning
. . .	Vertical ellipsis points in an example mean that information not directly related to the example has been omitted.
...	Horizontal ellipsis points in statements or commands mean that parts of the statement or command not directly related to the example have been omitted.
boldface text	Boldface type in text refers to interface buttons and links.
[]	Brackets enclose optional clauses from which you can choose one or none.

Related Documents

For more information, see the following manuals:

• Oracle 9i Warehouse Builder Installation Guide

• Oracle9i Warehouse Builder Release Notes

• Oracle8i/9i Data Warehousing Guide

Oracle provides additional information sources, including other documentation, training, and support services that can enhance your understanding and knowledge of Oracle9i Warehouse Builder.

• For more information on Oracle9i Warehouse Builder technical support, contact Oracle World Wide Support services at:

  http://www.oracle.com/support

• For the latest information on, and downloads of, software and documentation updates to Oracle9i Warehouse Builder, visit MetaLink at:

  http://metalink.oracle.com

• You can order other Oracle documentation at:

  http://oraclestore.oracle.com

Documentation Accessibility

Our goal is to make Oracle products, services, and supporting documentation accessible, with good usability, to the disabled community. To that end, our documentation includes features that make information available to users of assistive technology. This documentation is available in HTML format, and contains markup to facilitate access by the disabled community. Standards will continue to evolve over time, and Oracle Corporation is actively engaged with other market-leading technology vendors to address technical obstacles so that our documentation can be accessible to all of our customers. For additional information, visit the Oracle Accessibility Program Web site at:

http://www.oracle.com/accessibility/