Skip Headers
Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)

Part Number E10581-01
Go to Documentation Home
Go to Book List
Book List
Go to Table of Contents
Go to Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Go to next page
View PDF

6 Data Transformation

This section discusses basic concepts related to design and implementation of data extraction, transformation and loading (ETL) mappings in Oracle Warehouse Builder.

This section contains the following topics:

Data Transformation with OWB Mappings

Data transformation is the term for converting data from a source data format into a destination data format. Data transformations typically require two steps: a) data mapping (from source to target) to capture any transformations that must occur, and b) code generation to create the actual transformation process. After you import your source data and define the target, you decide how to transform the source data into the output desired for the target. The Mapping Editor in OWB guides you on how to transform the data by designing mappings. A mapping describes the sequence of operations required to extract data from sources, transform the data, and load the data into one or more targets.

Transformations are PL/SQL functions, procedures, packages, and types that enable you to transform data. You use transformations when designing mappings and process flows that define ETL processes.

Data Flow and Transformation-Code Generation in Mappings

Mappings provide a visual representation of the flow of the data and the operations performed on the data. Based on the ETL logic that you define in a mapping, OWB generates the code required to implement your design. OWB can generate code for the following languages:


You can create and define mappings using OMB*Plus, the scripting interface for OWB as described in Oracle Warehouse Builder API and Scripting Reference.

Mapping Operators

The mapping operator is the basic design element for a mapping. As you design a mapping, you select operators from the Mapping Editor palette, and you can visually drag them onto the work area or canvas. Operators handle how to represent sources and targets in the data flow. Operators also define how to transform the data from source to target. The operators you select affect how you will design the mapping.

Based on the operators you select, OWB assigns the mapping to one of the following Mapping Generation Languages:

Each of these languages require you to adhere to certain rules when designing a mapping.

Pluggable Mappings

A pluggable mapping is a reusable grouping of mapping operators that works as a single operator. Pluggable mappings are similar to functions in programming languages such as SQL*Plus and C, and enable you to re-use the ETL logic contained within.

Once defined, a pluggable mapping appears as a single mapping operator, nested inside a mapping. You can use a pluggable mapping more than once in the same mapping, or in other mappings. You can include pluggable mappings within other pluggable mappings.

Like any operator, a pluggable mapping has a signature, which consists of input and output attributes that enable you to connect it to other operators in various mappings. The signature is similar to the input and output requirements of a function in a programming language.

A pluggable mapping can be either reusable or embedded:


The use of pluggable mappings requires the Oracle Warehouse Builder Enterprise ETL Option. Refer to Oracle Database Licensing Information for details about this option.

See Also:

"Using Pluggable Mappings" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide for procedures

Transformations for Designing Mappings

Transformations are PL/SQL functions, procedures, table functions, and packages that enable you to transform data. You use transformations when designing mappings and process flows that define ETL processes.

Transformations are organized as follows:

Predefined Transformations and Custom Transformations

OWB provides a set of predefined transformations that enable you to perform common transformation operations. These predefined transformations are part of the Oracle Library that consists of built-in and seeded functions and procedures. You can directly use these predefined transformations to transform your data.

A custom transformation is one that is created by the user. Custom transformations can use predefined transformations as part of their definition. You can also import PL/SQL packages. Although you can modify the package body of an imported package, you cannot modify the package header, which is the signature for the function or procedure.

See Also:

"About Transformations" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide

Transformation Libraries

A transformation library consists of a set of reusable transformations. Each time you create a project, OWB creates a Transformation Library containing transformation operations for that project. This library contains the standard Oracle Library and an additional library for each Oracle module defined within the project.

Transformation libraries are available under the Public Transformations node of the Global Navigator in the Design Center.

Transformation libraries are one of the following types:

  • Oracle Library, a collection of predefined functions from which you can define procedures for your Global Shared Library.

  • Global Shared Library, a collection of reusable transformations created by the user.

See Also:

"About Transformation Libraries" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide

Table Functions

OWB provides the ability to define table function operators in mappings. Use table function operators to represent a table function in a mapping. Table function operators enable you to manipulate a set of input rows and return another set of rows of the same or different cardinality. Using table functions can greatly improve performance when loading your data warehouse.

See Also:

"Table Function Operator" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide