6 Data Transformation

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.

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.

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"

• "Transformation Libraries"

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.