9
Using Transformations

As you design mappings and process flows, you may want to use specialized transformations to transform data. This chapter describes how to import transformation definitions and how to create custom transformations.

This chapter includes the following topics:

• About Transformations

• Defining Custom Transformations

• Importing PL/SQL

• Editing Transformation Properties

About Transformations

Transformations are PL/SQL functions, procedures, and packages that enable you to transform data. Warehouse Builder provides you a set of pre-defined transformations from the Oracle Library. You can also use the New Transformation Wizard to create custom transformations that define a standalone function, procedure, or package. Use transformations when designing mappings and process flows that define ETL processes.

From the Warehouse Builder navigation tree, expand the Public Transformations node to display the following types of transformations available in Warehouse Builder, as shown in Figure 9-1.

Figure 9-1 Public Transformations Folder

Text description of the illustration publictr.gif

About Custom Transformations

The Custom library stores transformations that can be shared across different warehouse modules in a repository. It contains the following categories:

• Functions: The functions category is available under the Public Transformations node. This category contains any standalone functions. These functions can be defined by the user or imported from a database.

• Procedures: The procedures category is available under the Public Transformations node. This category contains any standalone procedures used as transformations. These procedures can be defined by the user or imported from a database. A procedure transformation takes 0-n input parameters and produces 0-n output parameters.

• Packages: PL/SQL packages can be created or imported in Warehouse Builder. The package body may be modified. The package header, which is the signature for the function or procedure, cannot be modified. The package can be viewed in the transformation library property sheet.

These transformation categories are also available within each warehouse module under the Transformations node. When you create functions and procedures within a warehouse module, you cannot share it across projects in the same repository.

About Pre-Defined Transformations

Warehouse Builder also provides pre-defined categories of transformations that enable you to perform common transformations quickly and easily. These built-in functions and procedures include a set of standard transformations organized into the following categories:

• Administration

• Character

• Conversion

• Date

• Numeric

• OLAP

• Other

• XML

For more information about pre-defined transformations, see the Oracle9i Warehouse Builder Transformation Guide.

Defining Custom Transformations

You can create custom transformations using the New Transformation Wizard.

To define a custom transformation:

1. From the Warehouse Builder navigation tree, expand the Public Transformations node and then the Custom node. You can also expand the Oracle warehouse module node and then the Transformations node.

2. Right-click on the category and select Create Function, Create Procedure, or Create Package.

   Warehouse Builder opens the New Transformation Wizard Welcome page.

3. Click Next.

   Warehouse Builder displays the Name page.

4. Type a name and a description for the new transformation.

5. Select a return type for the function from the drop-down list.

6. Click Next.

   Warehouse Builder displays the Parameters page, as shown in Figure 9-2.

7. Define each parameter for the transformation:
   1. Click Add.

   2. Type a name for the Parameter in the Name column.

   3. Specify the type, the order, whether it is an Input, Output, or Input/Output parameter, and whether the parameter is required.

   Figure 9-2 Transformation Parameter Page

   

   Text description of the illustration transpar.gif

8. Click Next.

   Warehouse Builder displays the Implementation page, as shown in Figure 9-3.

9. Click Code Editor to display the code editor. The code editor has line numbers, find, deploy, and syntax checking.

Figure 9-3 Code Editor for a New Transformation

Text description of the illustration transcod.gif

10. Close the code editor and click Next.

    Warehouse Builder displays the Finish page.

11. Click Finish.

    Warehouse Builder creates the function, procedure, or package and displays it under the corresponding folder under the Public Transformations and Custom nodes in the navigation tree.

Importing PL/SQL

Using the Import Wizard, you can import PL/SQL functions, procedures, and packages into a Warehouse Builder project.

The following steps describe how to import PL/SQL packages from other sources into Warehouse Builder.

To import a PL/SQL function, procedure, or package:

1. From the Warehouse Builder navigation tree, expand the Public Transformations node.

2. Right-click the Custom node and select Import. Or right-click the warehouse module name and select Import.

   Warehouse Builder displays the Database Link Information dialog.

3. Create a new database link to the system from where you are importing the PL/SQL package. Or select a database link from the list.

4. Click OK.

   Warehouse Builder displays the Import Metadata Wizard Welcome page.

5. Click Next.

6. Select PL/SQL Transformation in the Object Type field of the Filter Information page, as shown in Figure 9-4.

Figure 9-4 PL/SQL Transformation Selection

Text description of the illustration refilter.gif

7. Click Next.

   The Import Metadata Wizard displays the Object Selection page, as shown in Figure 9-5.

Figure 9-5 Object Selection Page

Text description of the illustration reobject.gif

8. Select a function, procedure, or package from the Available Objects list. Move the objects to the Selected Objects list by clicking the single arrow button to move a single object or the double arrow button to move multiple objects.

   Check if function is deterministic:

   This hint helps to avoid redundant function calls. If a stored function was called previously with the same arguments, the previous result can be used. The function result should not depend on the state of session variables or schema objects. Otherwise, results might vary across calls. Only DETERMINISTIC functions can be called from a function-based index or a materialized view that has query-rewrite enabled.

   Check if function should be enabled for parallel execution:

   This option declares that a stored function can be used safely in the child sessions of parallel DML evaluations. The state of a main (logon) session is never shared with child sessions. Each child session has its own state, which is initialized when the session begins. The function result should not depend on the state of session (static) variables. Otherwise, results might vary across sessions.

9. Click Next.

   The Import Metadata Wizard displays the Summary and Import page, as shown in Figure 9-6.

Figure 9-6 Summary and Import Page

Text description of the illustration reobjeca.gif

10. Verify the import information. Click Back to revise your selections.

11. Click Finish to import.

    The Import Results dialog displays, as shown in Figure 9-7.

Figure 9-7 Import Results

Text description of the illustration reimpora.gif

12. Click OK proceed with the import. Click Undo to cancel the import process.

    The imported PL/SQL information appears under the Custom node in the navigation tree.

When you use imported PL/SQL:

• You can edit, save, and deploy the imported PL/SQL functions and procedures.

• Wrapped PL/SQL objects are not readable.

• Imported packages can be viewed and modified in the category property sheet.

• You can edit the imported package body but not the imported package specification.

Editing Transformation Properties

You can edit a function, procedure, or package from the Transformation Properties Sheet. Make sure you edit properties consistently. For example, if you change the name of a parameter, then you must also change its name in the implementation code.

To edit a function, procedure, or package:

1. From the Warehouse Builder navigation tree, expand the Public Transformations node, and then the Custom node.

2. Right-click the name of the function, procedure, or package you want to edit and select Properties.

   Warehouse Builder displays the Transformation Properties Sheet. For Packages, you can only edit the name and description of the package. For Functions and Procedures the Properties Sheet displays three tabs: Name, Parameters, and Implementation.

   Name: You can edit the name and description of the function or procedure. For functions, you can also edit the return data type.

   Parameters: You can edit, add, or delete new parameters for a function or procedure. You can also edit and define the attributes of the parameters.

   Implementation: Review the PL/SQL code for the parameter. Click Code Editor to edit the code.