9 Deployment and Execution

This section discusses the concepts of deploying design objects to a target schema, and executing the ETL logic defined in the deployed objects.

This section contains these topics:

About Deploying and Executing Design Objects

After you design your ETL and data quality processes, you deploy and execute the resulting design objects to implement the design in the target schema. The Control Center Manager provides a comprehensive deployment console for viewing and managing all aspects of deployment and execution. It provides access to the information stored in the active Control Center.

The following topics provide overviews of deploying and executing design objects:

See Also:

"Deploying to Target Schemas and Executing ETL Logic" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

Deployment Concepts

Deployment is the process of creating physical objects in a target location according to the logical objects in an Oracle Warehouse Builder workspace.

For example, when you create a table using the Design Center, the metadata for this table is stored in the workspace. If the table described in your design does not exist in the database schema referenced by the specified location, then you must create the table by deploying it. Similarly, after you design a PL/SQL mapping, you must generate code for it (which creates a PL/SQL package implementing the mapping logic), then deploy the generated code to the specified location, which loads the generated PL/SQL package to the referenced schema. You can deploy objects from within the Design Center, or use the Control Center Manager. You can also use OMB*Plus commands to deploy objects.

As soon as you define a new object in the Design Center, the object is listed in the Control Center Manager under its deployment location.

Deploying a mapping or a process flow includes these steps:

  • Generate the PL/SQL, SQL*Loader, or ABAP script, if necessary.

  • Register the required locations and deploy any required connectors. This ensures that the details of the physical locations and their connectors are available at run time.

  • Transfer the PL/SQL, XPDL, SQL*Loader, or ABAP script from the Design Center to the Control Center. (XPDL refers to XML Process Definition Language, a format standardized by the Workflow Management Coalition.)


  • After deploying a mapping or a process flow, you must explicitly start the scripts, as described in "Starting ETL Jobs" Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

  • Workspace users can deploy only those objects for which they have the COMPILE privilege. By default, his privilege is granted on all objects in the workspace. However, the workspace owner may have instituted a different security policy.

Deployment Status: Viewing Results

After you deploy an object, Oracle Warehouse Builder assigns a deployment status to it. You can view the deployment status in the Control Center Manager.

The status represents the result of the deployment as follows:

  • Not Deployed: Indicates that the object has not yet been deployed to the target schema.

  • Success: Indicates that the object has been successfully deployed to the target schema.

  • Warning: Indicates that some warnings were generated during the deployment of the object. Double-click the status to view details about the warning.

  • Failed: Indicates that deployment of the object failed. Double-click the job in the Control Center jobs window to view information about why the deployment failed. The Job Log in the Design Center also captures details that you can view.

About Executing Design Objects

Execution is the process of starting the ETL logic defined in the deployed objects. For example, you define a mapping that sources data from a table, performs transformations on the source data, and loads it into the target table. When you deploy this mapping, the PL/SQL code generated for this mapping is stored in the target schema. When you execute this mapping, the job for the ETL logic is started and the data is picked up from the source table, transformed, and loaded into the target table.

Overview of Deployment and Execution Procedures

During the lifecycle of a data system, you typically takes these steps in the deployment process to create your system and perform the execution process to move data into your system:

Step 1   Select a named configuration

The Control Center for the selected configuration specifies the execution environment for the objects.

Step 2   Deploy objects to the target location

You can deploy them individually, in stages, or all.

Step 3   Review the results of the deployment

If an object fails to deploy, then fix the problem and try again.

Step 4   Start the ETL process for the target location

Execute the mappings or process flows that contain the ETL logic for the objects.

Step 5   Revise the design of target objects, if needed

Accommodate user requests, changes to the source data, and so forth.

Step 6   Set the deployment action

Set the modified objects to Upgrade or Replace.

Runtime Auditing for Deployments and Executions

Whenever you deploy and execute a design object, auditing information is generated and stored for you to view and manage.

The auditing information is specific to the type of object:

  • PL/SQL run time auditing is the auditing information for Oracle Warehouse Builder objects deployed using PL/SQL scripts. Data objects, mappings, and process flows use PL/SQL deployments.

  • Heterogeneous run time auditing refers to auditing information about objects that are deployed to an OC4J server or a heterogeneous database such as DB2 or SQL Server. This type of auditing includes auditing from multiple Control Center Agent (CCA). This type of auditing also includes information about Code Templates, Web services, and Code Template mappings.

You view and manage the audit information through these methods:

  • Control Center Manager. The Control Center Jobs panel displays a list of the deployment and executions jobs. When you double-click a job, you can see the job details.

  • Repository Browser or Heterogeneous Repository Browser. You can access audit information The Heterogeneous Repository Browser enables you to access auditing information on systems that do not have Oracle Warehouse Builder installed.

  • Public Views. As an alternative to using the Repository Browser, you can access the same information through the public views. To do the same, start a SQL*Plus session and query the public views. Refer to Oracle Warehouse Builder Sources and Targets Guide for a list of public views.

See Also:

"Auditing Deployments and Executions" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide

About Scheduling Design Objects to Execute

Oracle Warehouse Builder provides these scheduling options:

  • Integrated job scheduling in Oracle Warehouse Builder (This feature is available if you have the Enterprise ETL option)

  • Scheduling with Oracle Enterprise Manager to run ETL jobs

  • Integration with third party schedulers

Integration with Third-party Schedulers

Integration with third-party schedulers depends on the features of the third party scheduler. For example, mappings and process flows in Oracle Warehouse Builder are PL/SQL packages, and if there is a way in a third party scheduler to invoke a PL/SQL package, then refer to the documentation for that third party scheduler. You can also expose a mapping or a process flow as a Web Service if you are using a product like Oracle BPEL that can invoke Web services as part of orchestrating complex processes.