|Oracle® Warehouse Builder Concepts
11g Release 2 (11.2)
Part Number E10581-02
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 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.
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:
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.
Note:You can create and define mappings using OMB*Plus, the scripting interface for OWB as described in Oracle Warehouse Builder API and Scripting Reference.
See Also:These sections in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide:
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.
PL/SQL. OWB 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, OWB 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, OWB 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.
See Also:The following topics in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide:
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:
Note: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 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.
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
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 one of the following types:
See Also:"About Transformation Libraries" in Oracle Warehouse Builder Data Modeling, ETL, and Data Quality Guide
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