6 Deploying to Target Schemas and Executing ETL Logic

This chapter contains the following topics:

About Deployment
Deploying Objects
Starting ETL Jobs

About Deployment

Deployment is the process of creating physical objects in a target location from the logical objects in a Warehouse Builder repository.

Deploying a mapping or a process flow includes these steps:

Generate the PL/SQL, SQL*Loader, or ABAP script, if necessary.
Copy the script from the Design Center to the Control Center. Also copy SQL*Loader control files to the Control Center.
Deploy any new connectors; that is, create the database links and database directories between locations.

After deploying a mapping or a process flow, you must explicitly start the scripts.

You can deploy only those objects for which you have the COMPILE privilege. By default, you have this privilege on all objects in the repository. However, the repository owner may have instituted a different security policy.

You can deploy directly from the Design Center navigation tree or using the Control Center Manager.

Note:

Always maintain objects using Warehouse Builder. Do not modify the deployed, physical objects manually in SQL. Otherwise, the logical objects and the physical objects will not be synchronized, which may cause unpredictable results.

Note:

Whenever you deploy an object, Warehouse Builder automatically saves all changes to all design objects to the repository. You can choose to display a warning message by selecting Prompt for commit on the Preferences dialog box.

What is a Control Center?

A Control Center stores detailed information about every deployment, which you can access either by object or by job, including:

The current deployment status of each object
A history of all deployment attempts for each object
A history of all ETL start attempts for each mapping and process flow
A complete log of messages from all deployment jobs

A Control Center is implemented as a schema in the same database as the target location. Each repository has a default Control Center, which was created in the schema of the repository owner. For example, the REP_OWNER repository owner has a schema named REP_OWNER that stores the metadata from both the Design Center and the default Control Center.

You can use the default Control Center to deploy to the local system, or you can create additional Control Centers for deploying to different systems. Only one Control Center is active at any time.

The Control Center Manager offers a comprehensive deployment console that enables you to view and manage all aspects of deployment. It provides access to the information stored in the active Control Center.

You can also access deployment data reports using the Repository Browser, as described in the Warehouse Builder Online Help.

To create a new Control Center:

In the Locations Navigator, right-click Control Centers and select New Control Center.

The Create Control Center dialog box is displayed.
Complete the dialog box. Click the Help button for additional details.

You can also create a Control Center using the Create Configuration wizard.

To make a Control Center active:

Create or edit a configuration so that it uses the Control Center.
Activate that configuration.

Configuring the Physical Details of Deployment

Warehouse Builder separates the logical design of the objects from the physical details of the deployment. It creates this separation by storing the physical details in configuration parameters. An object called a configuration stores all of the configuration settings. You can create a different configuration for each deployment location, with different settings for the object parameters in each one.

Before deployment, ensure that you check the configuration of the target objects, the mappings, and the modules.

For an object to be deployable:

Its target location must be fully defined, valid, and selected for the object's module.
Its Deployable parameter must be selected, which it is by default.
It must validate and generate without errors.

Deployment Actions

When you define a new object in the Design Center, the object is listed in the Control Center Manager. Each object has a default deployment action, which you can display. The default is set by the previous action and varies depending on the type of object. You can override the default by choosing a different deployment action in the Control Center Manager.

These are the deployment actions:

Create: Creates the object in the target location. If an object with that name already exists, then an error may occur.
Upgrade: Modifies the object without losing data, if possible. You can undo and redo an upgrade. This action is not available for some object types, such as schedules.
Drop: Deletes the object from the target location.
Replace: Deletes and re-creates the object. This action is quicker than the Upgrade action, but it deletes all data.

The Deployment Process

During the life cycle of a data system, you typically perform these steps in the deployment process:

Select a configuration with the object settings and the Control Center that you want to use.
Deploy objects to the target location. You can deploy them individually, in stages, or all at once.
Review the results of the deployment. If an object fails to deploy, then fix the problem and try again.
Start the ETL process.
Revise the design of target objects to accommodate user requests, changes to the source data, and so forth.
Set the deployment action on the modified objects to Upgrade or Replace.
Repeat these steps.

Note:

Warehouse Builder automatically saves all changes to the repository before deployment.

Deploying Objects

Deployment is the process of creating physical objects in a target location from the metadata using your generated code. You can deploy an object from the Projects Navigator or using the Control Center Manager. Warehouse Builder automatically validates and generates the object.

Deployment from the Projects Navigator is restricted to the default action, which may be set to Create, Replace, Drop, or Update. To override the default action, use the Control Center Manager, which provides full control over the deployment process.

To deploy from the Projects Navigator:

Select the object and click the Deploy icon on the toolbar.

Status messages appear at the bottom of the Design Center window. For notification that deployment is complete, select Show Deployment Completion Messages in your preferences before deploying.

To open the Control Center Manager:

Open a project.
From the Tools menu, select Control Center Manager.

Starting ETL Jobs

ETL is the process of extracting data from its source location, transforming it as defined in a mapping, and loading it into target objects. When you start ETL, you submit it as a job to the Warehouse Builder job queue. The job can start immediately or at a scheduled time, if you use a scheduler such as the one in Oracle Enterprise Manager. Similar to deployment, you can start ETL from the Projects Navigator or using the Control Center Manager. You can also start ETL using tools outside of Warehouse Builder that execute SQL scripts.

To start ETL from the Projects Navigator:

Select a mapping or a process flow, then, from the File menu, select Start.

Viewing the Data

After completing ETL, you can check any data object in Warehouse Builder to verify that the results are as you expected.

To view the data:

In the Projects Navigator, right-click the object and select Data. The Data Viewer will open with the contents of the object.