1
Overview

Oracle9i Warehouse Builder is a comprehensive and powerful tool that enables you to define and maintain a large scale logical model of a data warehouse and to deploy a physical instance of that model. This chapter describes the process of using Warehouse Builder to design, implement, and load a data warehouse. This guide provides instructions on how to use Warehouse Builder to:

• Create a set of definitions for a logical warehouse schema, its data sources, and the operations that extract data from a source, transform the data, and load the data into a warehouse.

• Configure a physical instance of the logical warehouse, validate the set of definitions for the instance, generate a set of scripts that create and load a physical instance, and deploy a physical instance to an Oracle8i/9i database system.

• Update and refresh the data warehouse, and set up Oracle Enterprise Manager and Oracle Workflow to manage the warehouse operation.

The Development Phases

There are three development phases in the data warehouse building process:

1. Definition Phase

   During this phase, you create logical definitions that describe a warehouse and its sources. These define:

   • The warehouse schema

   • Data sources and targets

   • Mappings of the extract, transform, and load (ETL) operations

2. Generation Phase

   A physical instance of the warehouse is defined by configuring a set of logical definitions. The configured logical definitions are validated and then used to generate a set of scripts that create and manage the physical instance. The scripts include:

   • Data definition language (DDL) code to create the warehouse and intermediate schema objects.

   • PL/SQL, SQL*Loader, and Tcl code to extract data, map and transform the data, and then load it into the physical instance.

   Warehouse Builder runs the DDL scripts that deploy the logical definitions as physical objects to create the physical instance. The Tcl scripts can also be deployed to a file system to use in conjunction with Oracle Workflow and Oracle Enterprise Manager to schedule and manage load and refresh jobs.

3. Load and Manage Phase

   During this phase, the infrastructure for the load and refresh jobs is implemented:

   • Job dependencies can be defined in the Oracle Workflow process.

   • Oracle Enterprise Manager can be used to schedule mappings, jobs, or an Oracle Workflow process.

Refer to the Oracle Data Warehousing Guide for information on data warehouse design and data warehousing strategies.

The Definition Phase

During this phase, you create numerous logical definitions to describe your data warehouse. These logical definitions describe a data warehouse schema, your data sources, optional intermediate staging schemas, and the target warehouse.

After you identify the different sources of data for your warehouse, you create a source module. A module contains logical definitions for the repository objects. You then use the Import Metadata Wizard to import definitions of data sources from:

• Database Sources including Oracle Designer repositories

• Flat File Sources

• Pre-packaged Applications

You then create a complete set of definitions for the target schema of the data warehouse. These definitions are stored in Warehouse Modules. The source and target definitions are used in the design, development, and implementation of routines that extract, transform, and load data into target warehouse schemas.

Finally, you create logical definitions that describe how to extract, transform, and load the data. These definitions are called Mappings and are stored in the warehouse module that defines the target warehouse.

The Generation Phase

After defining the logical warehouse, you configure Warehouse Builder to create and load a physical instance of the warehouse. Warehouse Builder then validates the definitions for the physical instance and generates the scripts to create objects for the instance. This phase consists of the following steps:

1. Configuring the Logical Definitions

   The configuration parameters determine how an object is deployed, the processing characteristics of selected objects, the location of deployed scripts, the physical properties of warehouse objects, and other properties.

2. Validating the Logical Definitions

   After you configure a physical instance, validate the set of definitions to detect script errors such as invalid foreign key references, invalid object references in mappings, and data type mismatches.

3. Generating the Deployment Scripts

   After you have a validated set of configured definitions, generate the scripts.

4. Deploying the warehouse

   After generating the scripts, you can deploy the database objects (database links, tables, dimensions, facts, materialized views, synonyms, and PL/SQL packages) to one or more instances. This creates the empty warehouse.

The Load and Manage Phase

After you deploy scripts, you can register them as jobs with Oracle Enterprise Manager or another scheduling tool. You can use a dependency management tool such as Oracle Workflow to run multiple job processes with dependencies. Schedule these Workflow processes to load or update the warehouse. The Warehouse Builder Workflow Queue Listener monitors the processes and ensures that dependencies are handled in the correct order. After the processes have completed, view the results using the Warehouse Builder Runtime Audit Viewer.

Warehouse Builder enables you to complete several administrative tasks using Warehouse Builder Utilities including the Runtime Assistant, the Run Time Audit Viewer, the Metadata Loader, and the Warehouse Builder Browser reporting tool.