11 Deploying to Target Schemas and Executing ETL Logic

This chapter contains the following topics:

About Deployment
About Schedules
Process for Defining and Using Schedules

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, as described in "Starting the ETL Process".

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 Chapter 30, "Auditing Deployments and Executions".

To create a new Control Center:

In the Connection Explorer, right-click Control Centers and choose New.

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.

Refer to "Creating a New Configuration".
Activate that configuration.

Refer to "Activating a 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, be sure to 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

As soon as 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 result.
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 recreates the object. This action is quicker than Upgrade, but it deletes all data.

The Deployment Process

During the life cycle of a data system, you typically will take 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.

About Schedules

Use schedules to plan when and how often to execute operations that you designed within Warehouse Builder. You can apply schedules to mappings and process flows that you want to execute in an Oracle Database, version 10g or higher.

When you are in the development phase of using Warehouse Builder, you may not want to schedule mappings and process flows but rather start and stop them immediately from a Control Center as described in Chapter 29, "Deploying Target Systems".

You can define schedules to execute once or to execute repeatedly based on an interval you define in the user interface. For each schedule you define, Warehouse Builder generates codes that follows the iCal calendaring standards.

Schedules are defined in the context of projects and contained in schedule modules under the Schedules node on the Project Explorer as shown in Figure 11-1.

Figure 11-1 Schedules on the Project Explorer

This illustration is described in the surrounding text.

Description of "Figure 11-1 Schedules on the Project Explorer"

For every new project you create, Warehouse Builder creates a default schedule module, MY_CAL_MODULE. Create schedules under the default module or create a new module by right- clicking the Schedules node and selecting New...

Deploying Warehouse Builder Schedules to Oracle Workflow

To successfully deploy Warehouse Builder schedules to Oracle Workflow, ensure access to the correct version of Oracle Workflow as described in the Oracle Warehouse Builder Installation and Administration Guide.

Ensure that the target location has the 'CREATE SYNONYM' system privilege. If the Evaluation Location is specified or the deployment location references a different database instance from Control Center schema, then the deployment location must have the 'CREATE DATABASE LINK' system privilege.

Process for Defining and Using Schedules

To create a module to contain schedules, right-click the Schedules node and select New.
To create a schedule, right-click a schedule module and select New.

Warehouse Builder launches the Schedule Wizard.
On the Name and Description page, type a name for the schedule that is 24 characters or less.

The rules for most Warehouse Builder objects is that physical names can be 1 to 30 alphanumeric characters and business names can be 1 to 2000 alphanumeric characters.
Follow the instructions in the Schedule Wizard.

Use the wizard to specify values for Start and End Dates and Times, Frequency Unit, and Repeat Every. When you complete the wizard, Warehouse Builder saves the schedule under the schedule module you selected.
On the Project Explorer, right-click the schedule you created with the wizard and select Open Editor.

Warehouse Builder displays the schedule editor. Review your selections and view the list of calculated execution times. For complex schedules, you can now enter values for the By Clauses.
To apply a schedule to a mapping or process flow, right-click the object in the Project Explorer and select Configure. In the Referred Calendar field, select the Ellipsis button to view a list of existing schedules.

For any mapping or process flow you want to schedule, the physical name must be 25 characters or less and the business name must be 1995 characters or less. This restriction enables Warehouse Builder to append to the mapping name the suffix _job and other internal characters required for deployment and execution.
Deploy the schedule.

Recall that when you deploy a mapping, for example, you also need to deploy any associated mappings and process flows and any new target data objects. Likewise, you should also deploy any associated schedules.

When properly deployed with its associated objects, the target schema executes the mapping or process flow based on the schedule you created.

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 Project Explorer or using the Control Center Manager. Warehouse Builder automatically validates and generates the object.

Deployment from the Project Explorer 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 Project Explorer:

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.
Choose Control Center Manager from the Tools menu.

If this menu choice is not available, then check that the appropriate configuration and Control Center are active. Refer to "Creating Additional Configurations".

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. Like deployment, you can start ETL from the Project Explorer 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 Project Explorer:

Select a mapping or a process flow, then choose Start from the Design menu.

Viewing the Data

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

To view the data:

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