| Oracle® Warehouse Builder Transformation Guide 10g Release 1 (10.1) Part Number B12151-02 |
|
|
View PDF |
| Oracle® Warehouse Builder Transformation Guide 10g Release 1 (10.1) Part Number B12151-02 |
|
|
View PDF |
Transforming data is one of the main functions of an Extract, Transformation, and Loading (ETL) tool. Data transformations can range from single-value-based arithmetic calculations to multiple data transformations, such as data manipulation and alterations. Oracle Warehouse Builder includes several pre-defined transformations, as well as a library of predefined functions and procedures, to transform data. Warehouse Builder also uses SQL and PL/SQL as the transformation languages, which enables it to fully utilize the power of the Oracle database engine.
This guide describes the functions and procedures that characterize Warehouse Builder transformations. Because this guide is not intended as a basic user manual, you must have an understanding of how to create mappings and how to add transformations and operators to mappings. Additionally, concepts such as the Global Shared Library are not discussed in this manual.
This chapter includes the following topics:
For more information, see the Oracle Warehouse Builder User's Guide.
Oracle offers a comprehensive ETL solution, and one of the major components in this solution is the ETL tool, Oracle Warehouse Builder. Warehouse Builder provides components that enable you to model and create an ETL process. Its intuitive user interface (UI) enables you to design and define objects that are stored in an open repository. After completing the initial design, you can deploy this design to the runtime platform. Because the runtime platform is the Oracle database, Warehouse Builder is used as a tool that generates code rather than an ETL engine-based tool. Warehouse Builder also provides reporting tools to run reports on your repository. And it also enables you to integrate with other Oracle query tools.
Because SQL and PL/SQL are versatile and proven languages widely used by many information professionals, the time and expense of developing an alternative transformation language is eliminated by using Warehouse Builder. With Warehouse Builder, you can create solutions using existing knowledge and a proven, open, and standard technology.
The ETL processes designed with Warehouse Builder can be translated into PL/SQL packages. These PL/SQL packages are deployed to the Oracle database and stored as packages available for execution.
You can also use PL/SQL to transform data moving from sources to targets. To enable faster development of warehousing solutions, Warehouse Builder provides custom procedures and functions written in PL/SQL. Warehouse Builder enables you to reuse PL/SQL as well as to write your own PL/SQL transformations. Because the final process runs on the Oracle database, Warehouse Builder supports all constructs supported by the Oracle database.
You can also use the Warehouse Builder Mapping Editor to design data transformations using SQL code components. For example, activities such as joining disparate data sources or splitting data streams into multiple output streams can be implemented as SQL components. This enables Warehouse Builder to generate efficient SQL code to move data from source to target.
Oracle Corporation strives to comply with industry-accepted standards and participates actively in SQL standards committees. Industry-accepted committees include the American National Standards Institute (ANSI) and the International Standards Organization (ISO), which is affiliated with the International Electrotechnical Commission (IEC). Both ANSI and the ISO/IEC have accepted SQL as the standard language for relational databases. When a new SQL standard is simultaneously published by these organizations, the names of these standards conform to conventions used by the organization. Although naming conventions may differ, it should be noted that the standards are technically identical.
The latest SQL standard was adopted in July 1999 and is often called SQL:99. The formal names of this standard are:
ANSI X3.135-1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
ISO/IEC 9075:1999, "Database Language SQL", Parts 1 ("Framework"), 2 ("Foundation"), and 5 ("Bindings")
SQL is a data sublanguage that provides an interface to a relational database such as Oracle. All SQL statements are instructions to the database. SQL benefits different types of users including application programmers, database administrators, managers, and end users.
SQL language:
Processes sets of data as groups rather than as individual units.
Provides automatic navigation to the data.
Uses standalone statements that are complex and powerful.
Flow-control statements were not originally part of SQL, but they can be found in the recently accepted optional part of SQL, ISO/IEC 9075-5: 1996. Flow-control statements are commonly known as "persistent stored modules" (PSM), and Oracle's PL/SQL extension to SQL is similar to PSM.
SQL enables you work with data at the logical level. The implementation details are only required when you want to manipulate the data. For example, to retrieve a set of rows from a table, you can define a condition to filter the rows. All rows satisfying the condition are retrieved in a single step and can be passed as a unit to the user, to another SQL statement, or to an application. You do not have to filter the rows one by one, or manually store and retrieve the data. All SQL statements use the optimizer, a part of Oracle that determines the most efficient means of accessing the specified data. Oracle also provides techniques that you can use to improve the optimizer performance.
SQL provides statements for different tasks including:
Querying data
Inserting, updating, and deleting rows in a table
Creating, replacing, altering, and dropping objects
Controlling access to the database and its objects
Guaranteeing database consistency and integrity
SQL unifies all of the preceeding tasks in one consistent language.
All major relational database management systems support SQL and all programs written in SQL can be moved from one database to another with very little modification.
This means that all the SQL knowledge in your organization is fully portable to Warehouse Builder. Warehouse Builder enables you to import and maintain any existing complex custom code. You can later use these custom transformations in Warehouse Builder mappings.
