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 Oracle Warehouse Builder 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 Oracle Warehouse Builder 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, Oracle Warehouse Builder generates the code required to implement your design. Oracle Warehouse Builder can generate code for the following languages:

  • PL/SQL: PL/SQL stands for Procedural Language/Standard Query Language. It extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more powerful than SQL.

  • SQL*Loader: SQL*Loader is an Oracle tool for loading data from files into Oracle Database tables. It is the most efficient way to load large volumes of data from flat files.

  • SAP ABAP: ABAP is a programming language for developing applications for the SAP R/3 system, a business application subsystem.

  • Code Templates (CT mappings): For Code Template (CT) mappings, Oracle Warehouse Builder generates data extraction or other mapping code based on the contents of a Code Template.


You can create and define mappings using OMB*Plus, the scripting interface for Oracle Warehouse Builder 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 design the mapping.

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

  • PL/SQL. Oracle Warehouse Builder generates PL/SQL code for all mappings that do not contain either a flat file operator as a source, or a SAP/R3 source. Design considerations for PL/SQL mappings depend on whether you specify a row-based or set-based operating mode.

  • SQL*Loader. When you define a flat file operator as a source, Oracle Warehouse Builder generates SQL*Loader code. To design a SQL*Loader mapping correctly, follow the guidelines described in "Using Flat File Source and Target Operators" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

  • ABAP (SAP-based script). When you define a SAP/R3 source, Oracle Warehouse Builder generates ABAP code. For mapping design considerations for SAP sources, see "Creating SQL*Loader, SAP, and Code Template Mappings" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide.

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 reuse the ETL logic contained within.

When defined, a pluggable mapping appears as a single mapping operator, nested inside a mapping. You can use a pluggable mapping multiple times 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:

  • Reusable pluggable mapping: A pluggable mapping is reusable if the metadata it references can exist outside of the mapping within which it is contained.

  • Embedded pluggable mapping: A pluggable mapping is embedded if the metadata it references is owned only by the mapping or pluggable mapping in which it is contained.


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

Oracle Warehouse Builder 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.

Transformation Libraries

A transformation library consists of a set of reusable transformations. Each time you create a project, Oracle Warehouse Builder 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

Oracle Warehouse Builder 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