Home Solutions Oracle Warehouse Builder Get optimal test management information. Certified solutions.
Visit our brand websites for specific solutions

Versioning, automated deployment and Life Cycle Management for
Oracle OWB / ODI

Mainframe modernization and agility, using Life Cycle Management for

Oracle Warehouse Builder

Oracle Warehouse Builder (OWB) is a comprehensive tool for ETL (extract, transform and load), relational and dimensional modeling, data quality, data auditing, and full lifecycle management of data and metadata. With the Oracle Database as its metadata repository and transformation engine, OWB provides superior performance, security, and scalability.

OWB helps you with the design of your relational targets, mappings and process flows, with the deployment of relational database or dimensional objects and ETL mappings (generation of PL/SQL scripts). A mapping is a Warehouse Builder entity that describes the sequence of operations required to extract data from sources, transform the data, and load the data into one or more targets. Mappings provide a visual representation of the flow of the data and the operations performed on the data. Warehouse Builder has 4 types of mappings: PL/SQL mappings, SQL*Loader mappings, SAP ABAP mappings and Code Template mappings.

In Oracle Warehouse Builder, you can execute two types of deployed objects: mappings and process flows. After you deploy process flows or mappings to your target system, they are available for execution from within the Control Center Manager. Additionally, process flows can be executed through Oracle Workflow.

The Warehouse Builder mappings can be executed individually each time you want to load or reload data or you can execute a process flow. The process flow will automate the order and dependency of the mappings execution.

In Warehouse Builder, you specify how to transform the data by designing mappings in the Mapping Editor.

Oracle Warehouse Builder (OWB) Mapping editor example

A mapping is a Warehouse Builder entity that describes the sequence of operations required to extract data from sources, transform the data, and load the data into one or more targets. Mappings provide a visual representation of the flow of the data and the operations performed on the data. Warehouse Builder has 4 types of mappings: PL/SQL mappings, SQL*Loader mappings, SAP ABAP mappings and Code Template mappings.

Oracle Warehouse Builder (OWB): Deployment Concepts

Oracle Warehouse Builder deployment process
Oracle Warehouse Builder deployment process

Deployment is the process of creating physical objects in a target location according to the logical objects in an OWB 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 already 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 runtime.
  • Transfer the PL/SQL, XPDL, SQL*Loader, or ABAP script from the Design Center to the Control Center.

Oracle Warehouse Builder and Application lifecycle Management

IKAN ALM Application Lifecycle Management

Oracle Warehouse Builder provides a number of lifecycle features such as a Repository and related security, Impact analysis, data lineage and change propagation, the ability to handle multiple deployment configurations and scripting.

A standard ALM practice in Oracle Warehouse Builder is to create a repository and one or more target schemas for each environment, where each environment has his own project definition, distinct target schemas per environment. Code is promoted by using MDL (Metadata Loader) export functionality and/or OMB*Plus scripting.

OMB*Plus, which is based on Tcl (Tool Command language) allows you to access all Repository features, allows you to create, change and deploy objects and mappings and supports import and Export between repositories.

However this approach demands a good OWB skills level and related experience. Such a process is complex and required good and constant communication between all involved parties, from developers over QA people and operations.

And with direct access to not only developing and testing platforms but also to the production environment, a great responsibility rests on each involved stakeholder, especially developers.

Not to mention ALM good practices, where separation of duties and a repeatable process is key. Last but not least, a standard environment doesn’t just consist of OWB, but can also involve other tasks or processes. An example of such other tasks are all the database related tasks.

Professional Application lifecycle Management for OWB

IKAN ALM with VCR4OWB offers a complete, automated solution for OWB and non-OWB related Application Lifecycle Management (ALM).

VCR4OWB, Versioning for Oracle Warehouse Builder (OWB)

VCR4OWB, OWB independent versioning

First of all VCR4OWB allows you to version the OWB and non-OWB objects into CVS, an open Source Versioning Repository. A developer can select individual objects (mapping, table, ...) or he can select a whole list of objects (mappings, modules, project) at the same time. VCROWB, offers concurrent versioning at object level (which is the lowest entity level of OWB) and uses "standard" xml files as format. Intelligent diff- parsing helps to avoid useless version changes, like just a timestamp change. VCR4OWB complies with the OWB standards and makes use of the OWB Experts technology.

VCR4OWB consists of a JAVA class with five (5) functions:


    Does a checkout of the given VCR-module/object into a given path (sandbox) and writes a list of all elements checked out into a given text file.


    Does a commit of all elements named in a given text file from a given path (sandbox) into a given VCR-module/object.


    Similar to CHECKOUT but without the VCR-information. So elements from foreign projects can be mixed into the default project.


    Does a delete of all elements in a given text file in the given path (sandbox) and then commit to set them "removed" in the VCR.


    Does a VCR-status command in the given path (sandbox) and shows an html-list of all elements in the path to give a visual compare of the situation in OWB and helps solving conflicts.

    This JAVA class can be used in OWB with the "Experts technology" to completely integrate this VCR-function in the development environment of OWB. Sample "Experts" are delivered on how to use it or a customer can develop or write his own Expert.

IKAN ALM, Lifecycle, Build, Deploy and approval management

IKAN ALM for Oracle Warehouse Builder sample Workflow (OWB build and Deploy with IKAN ALM and dynamic 
IKAN ALM for Oracle Warehouse Builder sample Workflow (OWB build and Deploy with IKAN ALM and "dynamic Tcl").

Once all OWB and non-OWB objects are versioned and available, IKAN ALM can help you to Build and Deploy your application. Deployment can happen over a customizable Lifecycle: you can define as many test levels as you want, whereby each level can consist out of one or more physical environments.

You need at least one Build and one Test level. In addition you can also define pre- and post-approvals to each level. IKAN ALM has as benefit that it can integrate the OWB processes with other processes or tools in order to come to one global “release” embedding OWB and non-OWB elements. Example of non-OWB processes are the database related tasks.



The objects are retrieved from the VCR and imported into the local OWB instance (build machine). Depending on the objects in OWB, a Tcl-script is created dynamically, which describes all steps and dependencies for the later OWB-deploy (writing PL/SQL into the database). This script is parameter driven, so it can be called later with different parameters for the rollout to several environments.

Deploy to Test and Deploy to production

Since all necessary files and scripts are packaged with the build, the deploy run straight in two steps: first an import of the metadata into the deploy OWB-repository is done and then executing the Tcl-script for deploying everything into the target database happens.

The dynamic Tcl-creation is handled by a separate process. The only task left is to import the OWB metadata and triggering the creation and execution of Tcl-scripts.

In the current release, only full builds are supported. In the future we will provide the possibility to do also incremental builds and deploys.

IKAN ALM and VCR4OWB Benefits

  • Platform independent web application
  • Ergonomic user interface for intuitive operation
  • Detailed, customized process design of all server, build, and Deploy tasks
  • Extensive reporting database with open interfaces
  • IKAN ALM support various version control tools (Eg, CVS, IBM ClearCase, MS Visual SourcesSafe, PVCS, SubVersion ...) . Currently only CVS is supported for OWB.
  • Generic interface for integration of URL-based tracking systems
  • Full integration into existing work environments and tools
  • Full command line interface
  • JAVA interface VCR4OWB for the integration of OWB Experts
  • Build-controlled processes, independent of the versioning tool
  • Reuse of existing build and Deploy scripts possible
  • Complex, individually configurable role model for the control of any inspection and approval stages to regulate the access skills
  • Reliable feedbacks on mail and web interfaces
  • Distributed, incremental, multi-platform build and Deploy
  • Definition of project dependencies
  • Support for multiple, parallel life cycles per project (Eg for release developments, production, maintenance and emergency operations, etc.)
  • Support for parallel or sequential rollout (if necessary at the same time)
  • Easy roll back to earlier release versions
  • Integration with existing security systems and policies (E.g., Open Directory, LDAP, etc.)

For more information

If you need additional information or would like to request a demo, please contact us.

More info on VCR4OWB and dynamic Tcl script:

Dynamic Tcl script: copyright © Oracle Consulting, Germany

Next: CA EndevorNext page