Skip Headers
Oracle® Warehouse Builder User's Guide
11g Release 1 (11.1)

B31278-06
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

18 Data Flow Operators

The Mapping Editor provides a set of pre-built mapping operators. These operators enable you to define common transformations that specify how data moves from the source to the target.

This chapter provides details on how to use operators in a mapping to transform data. Some operators have wizards that assist you in designing the mapping. And some operators allow you to start the Expression Builder as an aide to writing SQL expressions.

This chapter includes the following topics:

List of Data Flow Operators

The list of data flow operators is as follows:

Operator Wizards

For operators that require you to make numerous design decisions, wizards guide you in defining the operator. Each wizard begins with a welcome page that provides an overview of the steps you must perform. And each wizard concludes with a summary page listing your selections. Use Next and Back to navigate through the wizard. To close an operator wizard, click Finish on any of the wizard pages.

The following operators have wizards to assist you:

Once you become proficient with defining an operator, you may prefer to disable the wizard and use the Operator Editor instead. To start the Operator Editor, right-click the operator on the Mapping Editor and select Open Details. The Operator Editor displays the same content as the wizard except in a tab format rather than wizard pages.

Whether you are using an operator wizard or the Operator Editor, you must complete the following pages for each operator:

Operator Wizard General Page

Use the General page to specify a name and optional description for the operator. By default, the wizard assigns the operator type as the name. For example, the default name for a new pivot operator is "Pivot".

Operator Wizard Groups Page

Edit group information on the Groups tab.

Each group has a name, direction, and optional description. You can rename groups for most operators but cannot change group direction for any of the operators. A group can have one of these directions: Input, Output, Input/Output.

Depending on the operator, you can add and remove groups from the Groups tab. For example, you add input groups to Joiners and output groups to Splitters.

Operator Wizard Input and Output Pages

The Operator Editor displays a tab for each type of group displayed on the Groups tab. Each of these tabs displays the attribute name, data type, length, precision, scale and optional description.

Depending on the operator, you may be able to add, remove, and edit attributes. The Mapping Editor grays out properties that you cannot edit. For example, if the data type is NUMBER, you can edit the precision and scale but not the length.

Operator Wizard Input Connections

Use the Input Connections page to copy and map attributes into the operator. The attributes you select become mapped members in the input group. The Available Attributes panel displays a list of all the operators in the mapping.

To complete the Input Connections page for an operator:

  1. Select complete groups or individual attributes from the Available Attributes panel.

    To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again.

    Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.

  2. Use the right arrow button between the two panels to move your selections to the Mapped Attributes panel.

    You can use the left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the data flow connection between the source operator and the current operator.

The Expression Builder

Some of the data flow operators require that you create expressions. An expression is a statement or clause that transforms data or specifies a restriction. These expressions are portions of SQL that are used inline as part of a SQL statement. Each expression belongs to a type that is determined by the role of the data flow operator. You can create expressions using Expression Builder, or by typing them into the expression field located in the operator or attribute property windows.

Opening the Expression Builder

You can open the Expression Builder from the <Operator Name> Properties panel of the Mapping Editor for operators such as filters, joiners, and aggregators.

You can open the Expression Builder from the Attribute Properties panel of the Mapping Editor in the operators such as expressions, data generators, splitters, and constants.

To open the Expression Builder:

  1. From the Properties panel of the operator or an attribute, click the Ellipsis button in the Expression field.

    The Expression Builder displays as shown in Figure 18-1.

    Figure 18-1 Expression Builder Interface

    Description of Figure 18-1 follows
    Description of "Figure 18-1 Expression Builder Interface"

  2. Create an expression by:

    • Typing text into the Expression field.

    • Dragging items from the Inputs and Transformations tabs on the left panel and dropping them into the Expression field on the right.

    • Double clicking on items from the Inputs and Transformations tabs on the left panel.

    • Clicking arithmetic operator buttons available under the Expression field.

  3. Click Validate.

    This verifies the accuracy of the Expression syntax.

  4. Click OK to save the expression and close the Expression Builder.

The Expression Builder User Interface

The Expression Builder contains the following parts:

  • In the left panel, the navigation tree displays two tabs:

    • Inputs Tab: A list of input parameters.

    • Transformations Tab: A list of predefined functions and procedures located in the Oracle Library, the Global Shared Library, and a custom Transformation Library.

  • Expression Field: At the top of the right panel is the Expression field. Use this field to type and edit expressions.

  • Arithmetic Operator Buttons: Below the Expression field are buttons for arithmetic operators. Use these buttons to build an expression without typing. The arithmetic operators available vary by the type of data flow operator that is active.

  • Others: A list of available SQL clauses that are appropriate for the active expression type.

    Beginning in Oracle 9i, the CASE function is recommended over the DECODE function because the CASE function generates both SQL and PL/SQL while DECODE is limited to SQL. If you use the DECODE function in an expression, it is promoted to CASE where appropriate during code generation. This enables you to deploy the DECODE functionality in all operating modes (such as setbased or rowbased) and transparently across Oracle Database releases (8.1, 9.0 and higher).

    For example, the function

    DECODE (T1.A, 1, 'ABC', 2, 'DEF', 3, 'GHI', 'JKL')
    

    is converted to the following:

    CASE T1.A WHEN 1 THEN 'ABC'
    WHEN 2 THEN 'DEF'
    WHEN 3 THEN 'GHI'
    ELSE 'JKL'
    
  • Validate Button: Use this button to validate the current expression in the Expression Builder. Validation ensures that all mapping objects referred to by the expression have associated workspace objects. The expressions you create with the Expression Builder are limited to the operator inputs and to any transformations available in a project. This limitation protects the expression from becoming invalid because of changes external to the operator. If the deployment database is different from the design workspace, it may not accept the expression. If this happens, the expression may be valid but incorrect against the database. In this case, expression errors can only be found at deployment time.

  • Validation Results Field: At the bottom of the right panel is the Validation Results field. After you select the Validate button to the right of this field, this field displays the validation results.

Aggregator Operator

The Aggregator operator calculates data aggregations, such as summations and averages, on the input data. It provides an output row set that contains the aggregated data.

Description of aggregator.gif follows
Description of the illustration aggregator.gif

The Aggregator operator has one input group and one output group. For the output group, define a GROUP BY clause that specifies the attributes over which the aggregation is performed. You can optionally specify a HAVING clause that restricts the aggregated data. Each attribute in the output group has the same cardinality. The number of rows in the output row set is less than or equal to the number of input rows.

You can use a single aggregator operator to perform multiple aggregations. Although you can specify a different aggregation function for each attribute in the output group of an aggregator, each aggregator supports only one GROUP BY and one HAVING clause.

Figure 18-2 shows a mapping that uses the Aggregator operator to aggregate the total sales over channels and products. Use the Expression property of the output attribute to specify that the aggregate function to be applied to the attribute TOTAL_SALES is SUM. Use the Group By property of the Aggregator operator to specify that the sales are aggregated over channel ID and product ID. The output of the Aggregator operator is mapped to the target table SALES_FACT.

Figure 18-2 Aggregator Operator in a Mapping

Description of Figure 18-2 follows
Description of "Figure 18-2 Aggregator Operator in a Mapping"

To define an Aggregator operator in a mapping:

  1. Drag and drop an Aggregator operator onto the Mapping Editor canvas.

  2. On the canvas, connect source attributes to the input group of the Aggregator operator.

  3. Right-click the Aggregator operator and select Open Details.

    Warehouse Builder displays the Aggregator Editor.

  4. On the Output Attributes tab, click Add to add an output attribute.

    Warehouse Builder adds an output attribute with the data type NUMBER. You can change both the name and the data type of this attribute.

    In the example displayed in Figure 18-2, you add an output attribute and rename it to TOTAL_SALES.

  5. Click OK to close the Aggregator Editor.

  6. Define expressions for each output attribute by using the Properties Inspector window of the attribute. For detailed instructions, see "Aggregate Function Expression".

    In the example displayed in Figure 18-2, you define the expression as SUM(amount_sold).

  7. Define a Group By clause and an optional Having clause for the operator. For detailed instructions, see "Group By Clause" and "Having Clause".

  8. Map the attributes in the output group of the Aggregator operator to the input group of the target.

Group By Clause

The Group By clause defines how to group the incoming row set to return a single summary row for each group. An ordered list of attributes in the input group specifies how this grouping is performed. The default GROUP BY clause is NONE.

To define the Group By Clause:

  1. Select the Aggregator operator on the Mapping Editor canvas.

    The Aggregator Properties panel displays the properties of the Aggregator operator.

  2. Click the Ellipsis button to the right of the Group By Clause property.

    The Group By Clause dialog box is displayed.

  3. Move the attributes from the Available Attributes list to the GROUP BY Attributes list.

  4. Click OK.

Having Clause

The Having clause is a boolean condition that restricts the groups of rows returned in the output group to those groups for which this condition is true. If this clause is not specified, all summary rows for all groups are returned in the output group.

To define the Having Clause:

  1. Select the Aggregator operator on the mapping canvas.

    The Aggregator Properties panel displays the properties of the Aggregator operator.

  2. Click the Ellipsis button to the right of the Having Clause property.

    The Expression Builder dialog box for the Having Clause displays as shown in Figure 18-3.

    Figure 18-3 Having Clause Dialog Box

    Description of Figure 18-3 follows
    Description of "Figure 18-3 Having Clause Dialog Box"

  3. Create an expression for the Having Clause of the Aggregator operator.

    For example, Figure 18-3 shows a sample Having Clause expression.

  4. Click OK to close the Expression Builder.

  5. Map the attributes you edited from the output group of the Aggregator operator to the attributes in the target.

Aggregate Function Expression

The Expression property of an attribute defines the aggregation functions to be performed on the attribute. For each ungrouped output attribute, select whether the aggregation expression should be a DISTINCT or ALL result. ALL is the default setting. For example,

  • ALL: Select AVG(ALL sal) from emp;

  • DISTINCT: Select AVG(DISTINCT sal) from emp;

A DISTINCT result removes all duplicate rows before the average is calculated.

An ALL result returns an average value on all rows.

If no aggregation function is necessary, specify NONE for the function. Specifying NONE on the attribute aggregation automatically adds the attribute to the resulting GROUP BY function.

To define expressions for output attributes:

  1. In the Aggregator operator on the mapping canvas, select the output attribute for which you want to define an aggregate function.

    The Attribute Properties panel displays the properties of the selected output attribute.

  2. Click the Ellipsis button to the right of the Expression property.

    Figure 18-4 Expression Dialog Box

    Description of Figure 18-4 follows
    Description of "Figure 18-4 Expression Dialog Box"

  3. Select an aggregate function from the Function list.

    The aggregate functions you can select are as follows: AVG, COUNT, MAX, MIN, NONE, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, and VARIANCE.

    In the example displayed in Figure 18-2, you select SUM as the aggregate function.

  4. Select either ALL or DISTINCT as the aggregation expression.

  5. Select the attribute that should be aggregated from the Attribute list.

    In the example displayed in Figure 18-2, you select the attribute amount_sold from the list.

  6. Click OK.

Anydata Cast Operator

Anydata Cast operator allows you to convert an object of type Sys.AnyData to either a primary type or to a user defined type. The Anydata Cast operator accepts an Anydata attribute as a source and transforms the object to the desired type.

Description of anydatacast.gif follows
Description of the illustration anydatacast.gif

The Anydata Cast operator is used with user defined data types and primitive data types. The cast operator acts as a filter. The number of attributes in the output group is n+1 where n is the number of attributes in the input group. This operator has one input group and one output group. The input group is editable. The output group is not editable. In an output group, you can only rename the attributes and change the data type of only the cast target. You cannot change the data type of any other output group attribute.

You can connect attributes to the input group. Each output group gets a copy of the input group attributes, including the Anydata attributes. You must choose an Anydata attribute of the input group as the source of the Cast operation.

If you change the data type to which you are going to cast the Anydata attribute, then you must:

  1. Edit the output group attribute that is the target of the Cast operation

  2. Change the data type of the attribute.

Because the Cast operator is unbound, it will not support any reconciliation operations.

Figure 18-5 displays a mapping that uses an Anydata Cast operator.

Figure 18-5 Anydata Cast in a Mapping

Description of Figure 18-5 follows
Description of "Figure 18-5 Anydata Cast in a Mapping"

To define a Anydata Cast operator in a mapping:

  1. Drop an Anydata Cast operator onto the Mapping Editor canvas.

    The AnyData Cast dialog box is displayed. The tree inside the dialog box has one parent node that will open to display the primary data types (other than Anydata). Each of the other parent nodes will correspond to the modules.

  2. Select the target type for casting and click Finish.

  3. Right-click the ANYDATA CAST operator and select Open Details.

    Warehouse Builder displays the ANYDATA_CAST Editor.

  4. On the Input Attributes tab, click Add and specify the attribute name, data type, and other properties.

  5. Click OK to close the Operator Editor.

  6. Map the attributes of the output group of the Anydata Cast operator to the target.

Deduplicator Operator

The Deduplicator enables you to remove duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.

Description of deduplicator.gif follows
Description of the illustration deduplicator.gif

For example, when you load data from a source table into a dimension, the higher levels within a dimension may be duplicated in the source. Figure 18-6 displays a mapping that uses the Deduplicator operator to remove duplicate values in the source while loading data into the PRODUCTS dimension. The source table contains duplicate values for category ID because more than one products may belong to the same category. The Deduplicator operator removes these duplicates and loads distinct values of category ID into the PRODUCTS dimension.

All rows that are required by the target must pass through the deduplicator. No row set can bypass the deduplicator and hit the target directly.

Figure 18-6 Deduplicator in a Mapping

Description of Figure 18-6 follows
Description of "Figure 18-6 Deduplicator in a Mapping"

To remove duplicates:

  1. Drop the Deduplicator operator onto the Mapping Editor canvas.

  2. Connect the attributes from the source operator to the input/output group of the Deduplicator operator.

  3. Connect the attributes from the Deduplicator operator group to the attributes of the target operator.

Expression Operator

Use the Expression operator to write SQL expressions that define non-procedural algorithms for one output parameter of the operator.

Description of expression.gif follows
Description of the illustration expression.gif

The expression text can contain combinations of input parameter names, variable names, and library functions. Use the Expression operator to transform the column value data of rows within a row set using SQL-type expressions, while preserving the cardinality of the input row set. To create these expressions, open the Attribute properties window for the output attribute and then open the Expression Builder.

By default, the Expression operator contains one input group and one output group.

Figure 18-7 shows a mapping that uses the Expression operator. The transaction table ORACLE_ORDERS contains order details such as product ID, unit price, and quantity sold. The ORDERS_FACT table contains an aggregation of the total sales amount across channels, products, and orders. The Expression operator is used to compute the amount of sale for each product by multiplying the unit price by the quantity sold. The Aggregator operator aggregates the sale amounts over channel code, product ID, and order ID before loading the target table.

Figure 18-7 Expression Operator in a Mapping

Description of Figure 18-7 follows
Description of "Figure 18-7 Expression Operator in a Mapping"

Do not use the expression operator to write aggregation functions. Use the Aggregator operator. See "Aggregator Operator".

To define an Expression operator in a mapping:

  1. Drag and drop an Expression operator onto the Mapping Editor canvas.

  2. Right-click the Expression operator and select Open Details.

    Warehouse Builder displays the Expression Editor.

  3. On the Output Attributes tab, click Add and specify the attribute name, data type, and other properties.

  4. Click OK to close the Operator Editor.

  5. From the Expression operator, select the output attribute.

    The Attribute Properties panel window displays the properties of the selected output attribute.

  6. Click the Ellipsis button to the right of the Expression field.

    The Expression Builder is displayed. Define an expression in the Expression Builder.

    For code generation, the input attributes are replaced by the input attribute names in the expression template.

  7. Connect the Expression output attribute to the appropriate target attribute.

Filter Operator

You can conditionally filter out rows using the Filter operator.

Description of filter.gif follows
Description of the illustration filter.gif

You connect a source operator to the Filter operator, apply a filter condition, and send a subset of rows to the next operator. The Filter operator filters data from a source to a target by placing a WHERE clause in the code represented by the mapping. You specify the filter condition using the Expression Builder. The filter condition can be based on all supported data types and can contain constants.

A Filter operator has only one input/output group that can be connected to both a source and target row set. The resulting row set is a filtered subset of the source row set based on a boolean filter condition expression. All rows that are required at the target must pass through the filter operator. No row set can bypass the filter and be directly inserted in the target.

For a mapping that contains a Filter operator, code that displays the filter condition expression as a WHERE clause for set-based view mode is generated. The filter input names in the original filter condition are replaced by actual column names from the source table, qualified by the source table alias.

Figure 18-8 shows the mapping that uses the Filter operator to move selected data to the target table. The ORACLE_ORDERS table contains orders data. Use the Filter Condition property of the Filter operator to move only the booked orders which were last updated on the current system date into the ORDERS table.

Figure 18-8 Filter in a Mapping

Description of Figure 18-8 follows
Description of "Figure 18-8 Filter in a Mapping"

To define a Filter operator in a mapping:

  1. Drag and drop the Filter operator onto the Mapping Editor canvas.

  2. Connect source attributes to the input/output group of the Filter operator.

  3. Select the Filter operator header.

    The Filter Properties panel of the Mapping Editor displays the properties of the Filter operator.

  4. Click the Ellipsis button to the right of the Filter Condition property.

    Warehouse Builder displays the Expression Builder dialog box for the filter condition.

  5. Define a filter condition expression using the Expression Builder.

  6. Click OK to close the Expression Builder.

  7. Connect the Filter operator outputs to the input/output group in the target.

Adding Self Joins in a Mapping

The Mapping Editor enables you to recursively join a table, view, or other source data operators onto itself.

Also known as tree walking, recursively joining a table back onto itself enables you to retrieve records in a hierarchy. For example, consider a table that contains employee data including the manager for each employee. Using tree walking, you could determine the hierarchy of employees reporting up to a given manager.

To perform tree walking:

  1. Create a mapping and add the desired source data operator such as a table, view, or a materialized view operator, which contains the hierarchal definition.

  2. Connect that source data operator to a Filter operator.

  3. In the filter operator, define the filter condition with CONNECT BY as the first two words. Make sure that you include only the connect by logic in the filter operator. That is, do not include any AND or OR logic in the filter.

Joiner Operator

The Joiner operator joins multiple row sets from different sources with different cardinalities, and produces a single output row set. You can use the Joiner operator to create inner joins, outer joins, equijoins, and non- equijoins. You can also create self joins by using a Filter operator as described in Adding Self Joins in a Mapping.

Description of joiner.gif follows
Description of the illustration joiner.gif

The Joiner operator uses a boolean condition that relates column values in each source row set to at least one other row set. The Joiner operator results in a WHERE clause in the generated SQL query. When run on Oracle 9i, full outer joins are supported. For more information about joins, see Oracle Database SQL Language Reference.

Note:

Operators placed between data sources and a Joiner can generate complex SQL or PL/SQL.

If the input row sets are related through foreign keys, that relationship is used to form a default join condition. You can use this default condition or you can modify it. If the sources are not related through foreign keys, then you must define a join condition.

If two tables in a join query do not have a join condition specified, the Cartesian product of the two tables is returned and each row of one table is combined with each row of the other table.

If the default foreign keys result in duplicate WHERE clauses, the Joiner operator will remove the duplicate clauses. This can happen if the join condition references several foreign keys. For example, if table T1 has a foreign key FK1 pointing to unique key UK1 in table T2 and table T2 has a foreign key FK2 pointing to unique key UK2 in T1, the resulting join condition

T1.A = T2.A AND T1.B = T2.B /*All instances of FK1 -> UK1 are reduced to one WHERE clause*/ AND
T2.B = T1.B AND T2.C = T1.C /*All instances of FK2 -> UK2 are reduced to one E-Business Suite clause*/

is generated by the Joiner operator as

T2.A = T2.A AND T1.B = T2.B AND T2.C = T1.C

If you define a join condition before you map attributes to the input group of the Joiner operator, the generated code treats the join condition as a literal. Since the attributes are not yet mapped to the Joiner operator, the code generator does not recognize these attributes. To avoid this problem, it is recommended that you first map the input groups of the Joiner operator and then define the join condition.

The join condition is defined in a PL/SQL context. For SAP sources, ABAP code can be generated by interpreting the PL/SQL join condition in the ABAP context. ABAP can only join over defined foreign key relationships.

Figure 18-9 shows a mapping that contains a Joiner operator. The two source tables ORACLE_ORDERS and ORACLE_ORDER_LINES are joined to combine the data from these tables into one table. The output of the Joiner operator is passed to the target table DAILY_ORDERS.

Figure 18-9 Joiner in a Mapping

Description of Figure 18-9 follows
Description of "Figure 18-9 Joiner in a Mapping"

To define a Joiner operator in a mapping:

  1. Drag and drop the Joiner operator onto the Mapping Editor canvas.

  2. Connect an output group from the first source to the Joiner input group INGRP1.

    The output attributes are created with data types matching the corresponding input data types.

  3. Connect a group from the second source operator to the INGRP2 group of the Joiner operator.

  4. Select the Joiner operator header.

    The Joiner Properties panel of the Mapping Editor displays the properties of the Joiner operator.

  5. Click the Ellipsis button to the right of the Join Condition property.

    The Expression Builder dialog box is displayed.

  6. Define the join condition.

  7. Click OK to close the Expression Builder.

  8. Map the attributes of the output group of the Joiner operator to the target.

Joiner Restrictions

Do not include aggregation functions in a join condition.

A Joiner can have unlimited number of input groups but only one output group.

The order of input groups in a joiner is used as the join order. The major difference between ANSI join and an Oracle join is that ANSI join must clearly specify the join order, while an Oracle join does not require it.

SELECT ...
      FROM T1 FULL OUTER JOIN T2 ON (T1.A=T2.A) 
                         JOIN T3 ON (T2.A=T3.A);

If you create input groups in another order, such as T1, T3, T2. Warehouse Builder will generate the following:

      SELECT ...
      FROM T1 JOIN T3 ON (1=1) 
              JOIN T2 ON (T1.A=T2.A and T2.A=T3.A);

When T1 and T3 are joined, there is no join condition specified. Warehouse Builder fills in a condition 1=1 (essentially a boolean true) and the two conditions you specified are used to join T2.

The filter condition is applied after join. For example, consider the following join:

Input1.c --- + 
Input2.c --- +---> Joiner 
Input3.c --- +    

with the following conditions:

  • Condition 1: Input1.c (+) = Input2.c (+)

  • Condition 2: Input2.c = Input3.c

  • Condition 3: Input1.c is null

The first two conditions are true joins while the third is a filter condition. If ANSI code is to be generated, the statement is interpreted as

select ... 
from Input1 full outer join Input2 on (Input1.c = Input2.c) 
join Input3 on (Input2.c = Input3.c) 
WHERE Input1.c is not null; 

Specifying a Full Outer Join

If your target warehouse is based on Oracle 9i or a later version, the Joiner operator also supports the full outer join. To specify a full outer join condition, you must place the (+) sign on both sides of a relational operator. The relational operator is not restricted to equality. You can also use other operators such as, >, <, !=, >=, <= .

T1.A (+) = T2.B (+)

The results of the full outer join are as follows:

  • Rows from sources T1 and T2 that satisfy the condition T1.A = T2.B.

  • Rows from source T1 that do not satisfy the condition. Columns corresponding with T2 are populated with nulls.

  • Rows from source T2 that do not satisfy the condition. Columns corresponding with T1 are populated with nulls.

When using the Oracle SQL syntax for partial outer join such as T1.A = T2.B (+), if you place a (+) sign on both sides of the relational operator, it is invalid Oracle SQL syntax. However, any condition with the double (+) sign is translated into ANSI SQL syntax. For example,

SELECT ...
FROM T1 FULL OUTER JOIN T2 ON (T1.A = T2.B);

When using full outer join, keep in mind the following:

  • Do not specify a full outer join condition for versions earlier than Oracle 9i.

  • The ANSI join syntax is generated only if you specify a full outer join condition in the joiner. Otherwise, the following Oracle proprietary join syntax is generated:

    SELECT ...
    FROM T1, T2
    WHERE T1.A = T2.B;
    
  • You can specify both full outer join and join conditions in the same joiner. However, if both conditions are specified for the same sources, the stronger join type is used for generating code. For example, if you specify:

    T1.A(+) = T2.A(+) and T1.B = T2.B
    

    Warehouse Builder will generate a join statement instead of a full outer join because T1.B = T2.B is stronger than the full outer join condition between T1 and T2.

  • You cannot specify a full outer join and partial outer join condition in the same joiner. If you specify a full outer join, then you cannot specify a partial outer join anywhere in the join condition. For example, T1.A (+) = T2.A (+) and T2.B = T3.B (+) is not valid.

Creating Full Outer Join Conditions

In an equijoin, key values from the two tables must match. In a full outer join, key values are matched and nulls are created in the resulting table for key values that cannot be matched. A left or a right outer join retains all rows in the specified table.

In Oracle8i, you create an outer join in SQL using the join condition variable (+):

SELECT ...
FROM A, B
WHERE A.key = B.key (+); 

This example is a left outer join. Rows from table A are included in the joined result even though no rows from table B match them. To create a full outer join in Oracle8i, you must use multiple SQL statements.

The Expression Builder allows the following syntax for a full outer join:

TABLE1.COL1 (+) = TABLE2.COL2 (+) 

This structure is not supported by Oracle8i. Oracle Database is ANSI SQL 1999 compliant. The ANSI SQL 1999 standard includes a solution syntax for performing full outer joins. The code generator translates the preceding expression into an ANSI SQL 1999 full outer join statement, similar to:

SELECT ... 
FROM table1 FULL OUTER JOIN table2 ON (table1.col1 = table2.col2) 

Because the full outer join statement complies to ANSI SQL 1999, it is only valid if the generated code is deployed to an Oracle 9i database. Specifying a full outer join to an Oracle8i database results in a validation error.

A full outer join and a partial outer join can be used together in a single SQL statement, but it must in an AND or an AND/OR condition. If a full outer join and partial outer join are used in the OR condition, an unexpected AND condition will result. For example,

SELECT ... 
FROM table1 FULL OUTER JOIN table2 ON (A = B or C = D) 

is evaluated by Oracle Server as A (+) = B (+) AND C = D.

To use a full outer join in a mapping:

  1. Follow steps one through four for adding a Joiner operator.

  2. Click the Ellipsis button to the right of the Join Condition property to define an expression for the full outer join using the Expression Builder.

  3. Click OK to close the Expression Builder.

Key Lookup Operator

Use the Key Lookup operator to lookup data from a table, view, cube, or dimension. For example, use the Key Lookup operator when you define a mapping that loads a cube or when you define surrogate keys on the dimension. For more information about surrogate identifiers, see "Defining Levels".

Description of key_lookup.gif follows
Description of the illustration key_lookup.gif

The key that you look up can be any unique value. It need not be a primary or unique key, as defined in an RDBMS. The Key Lookup operator reads data from a lookup table using the key input you supply and finds the matching row. This operator returns a row for each input key. You can have multiple Key Lookup operators in the same mapping.

The output of the Key Lookup operator corresponds to the columns in the lookup object. To ensure that only a single lookup row is found for each key input row, use keys in your match condition.

Each output attribute for the key lookup has a property called DEFAULT VALUE. The DEFAULT VALUE property is used instead of NULL in the outgoing row set if no value is found in the lookup table for an input value. The generated code uses the NVL function. The Key Lookup always results in an outer-join statement.

The table, view, or dimension from which the data is being looked up is bound to the Key Lookup operator. You can synchronize a Key Lookup operator with the workspace object to which it is bound. But you cannot synchronize the workspace object with the Key Lookup operator. For more information about synchronizing operators, see "Synchronizing Operators based on Workspace Objects".

Figure 18-10 shows a mapping that is used to load a cube. Data from four source tables is joined using a Joiner operator. But the data in the source tables only contains a channel name. To load the cube, we need the value of the surrogate identifier. A key lookup operator is used to lookup the surrogate identifier of the CHANNELS dimension and then load this value into the cube.

Figure 18-10 Key Lookup in a Mapping

Description of Figure 18-10 follows
Description of "Figure 18-10 Key Lookup in a Mapping"

Using the Key Lookup Operator

You have the following options for using a Key Lookup operator:

  • Define a new Key Lookup operator: Drag a Key Lookup operator from the Palette onto the mapping. The Mapping Editor displays a wizard.

  • Edit an existing Key Lookup operator: Right-click the Key Lookup operator and select Open Details.

Whether you are using the operator wizard or the Operator Editor, complete the following pages:

General

Use the General page to specify a name and optional description for the key lookup operator. By default, the wizard names the operator "Key_Lookup".

Groups

Use the Groups page to specify one input and one output group.In a Key Lookup operator, the input group represents the data from the source that is contained across multiple attributes. The output group represents that data transformed into rows.You can rename and add descriptions to the input and output groups. Since each Key Lookup operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.

Input Connections

Use the Input Connections page to copy and map attributes into the Key Lookup operator. The attributes you select are used to perform a lookup on the lookup object and find matching rows. The left side of the page displays a list of all the operators in the mapping.

Figure 18-11 shows an attribute from the table ORACLE_PRODUCTS table selected as input for the key lookup operator.

Figure 18-11 Input Connections Page of the Key Lookup Operator

Description of Figure 18-11 follows
Description of "Figure 18-11 Input Connections Page of the Key Lookup Operator"

To complete the Input Connections page for a Key Lookup operator:

  1. Select complete groups or individual attributes from the left panel.

    To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again. You can select multiple attributes by pressing the Shift key.

  2. Use the right arrow button in the middle of the page to move your selections to the right side of the wizard page.

    Use the left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the data flow connection between the source operator and the pivot operator.

Lookup

Use the Lookup page to provide details about the object on which the lookup is being performed. This object is referred to as the lookup result. You can perform a lookup on any table, view, or dimension that belongs to the current project.

Figure 18-12 Lookup Page of the Key Lookup Operator

Description of Figure 18-12 follows
Description of "Figure 18-12 Lookup Page of the Key Lookup Operator"

To provide the lookup details, select values for the following:

  • Object that has the lookup result

    Use the Select the object which has the lookup result list to select the object on which you want to perform the lookup. This list displays all the modules in the current project. Expand a module node to display the objects in the module. Select the object that contains the lookup result from this list.

  • Level that has the lookup result

    When you select a dimension as the lookup result, you must specify which level within the dimension contains the lookup result. The Select the level which has the lookup result list displays the levels in the selected dimension. Select the dimension level that contains the lookup result.

  • Lookup Condition

    Specify the condition used to match the input attributes with the records in the lookup result. The Lookup Table key list displays the unique constraints in the lookup result. The Input Attribute list displays the attributes you selected on the Input Connections page. Use these lists to specify the attributes used to match records.

    If you select a dimension level for the lookup, the options displayed are the surrogate and business identifier of the dimension level and the primary key of the database table that stores the dimension data.

Type 2 History Lookup

Use this page only if you selected a Type 2 SCD as the lookup result on the Lookup page. When the lookup result is a Type 2 SCD, you must specify which version of a particular record is to be used as a lookup. The options you can select are as follows:

  • Use the most current record

    This option returns the current record that corresponds to the attribute being looked up using the lookup condition. The current record is the one with the latest timestamp.

  • Specify the historic date as a constant value

    This option returns the record that contains the constant value that is specified using the Date and Time lists.

  • Choose an input attribute that holds the historic value

    This option enables you return records that pertain to a date and time that is contained in one of the input attributes. Use the Input Attribute list to select the attribute that contains the historic value.

No-match Rows

Use the No-match Rows page to indicate the action to be taken when there are no rows that satisfy the lookup condition specified on the Lookup page. Select one of the following options:

  • Return no row

    This option does not return any row when no row in the lookup result satisfies the matching condition.

  • Return a row with the following default values

    This option returns a row that contains default values when the lookup condition is not satisfied by the lookup result. Use the table below this option to specify the default values for each lookup column.

Pivot Operator

The pivot operator enables you to transform a single row of attributes into multiple rows.

Description of pivot_icon.gif follows
Description of the illustration pivot_icon.gif

Use this operator in a mapping when you want to transform data that is contained across attributes instead of rows. This situation can arise when you extract data from non-relational data sources such as data in a crosstab format.

Example: Pivoting Sales Data

The external table SALES_DAT, shown in Figure 18-13, contains data from a flat file. There is a row for each sales representative and separate columns for each month. For more information about external tables, see "Using External Tables".

Table 18-1 shows a sample of the data after a pivot operation is performed. The data that was formerly contained across multiple columns (M1, M2, M3...) is now contained in a single attribute (Monthly_Sales). A single ID row in SALES_DAT corresponds to 12 rows in pivoted data.

Table 18-1 Pivoted Data

REP MONTH MONTHLY_SALES REGION

0675

Jan

10.5

4

0675

Feb

11.4

4

0675

Mar

9.5

4

0675

Apr

8.7

4

0675

May

7.4

4

0675

Jun

7.5

4

0675

Jul

7.8

4

0675

Aug

9.7

4

0675

Sep

NULL

4

0675

Oct

NULL

4

0675

Nov

NULL

4

0675

Dec

NULL

4


To perform the pivot transformation in this example, create a mapping like the one shown in Figure 18-14.

Figure 18-14 Pivot Operator in a Mapping

Description of Figure 18-14 follows
Description of "Figure 18-14 Pivot Operator in a Mapping"

In this mapping, the data is read from the external table once, pivoted, aggregated, and written it to a target in set based mode. It is not necessary to load the data to a target directly after pivoting it. You can use the pivot operator in a series of operators before and after directing data into the target operator. You can place operators such as filter, joiner, and set operation before the pivot operator. Since pivoted data is not a row-by-row operation, you can also execute the mapping in set based mode.

The Row Locator

In the pivot operator, the row locator is an output attribute that you create to correspond to the repeated set of data from the source. When you use the pivot operator, a single input attribute is transformed into multiple rows and generates values for a row locator. In this example, since the source contains attributes for each month, you can create an output attribute named 'MONTH' and designate it as the row locator. Each row from SALES_DAT then yields 12 rows of pivoted data in the output.

Table 18-2 shows the data from the first row from SALES_DAT after the data is pivoted with 'MONTH' as the row indicator.

Table 18-2 Data Pivoted By Row Indicator

REP MONTH MONTHLY_SALES REGION

0675

Jan

10.5

4

0675

Feb

11.4

4

0675

Mar

9.5

4

0675

Apr

8.7

4

0675

May

7.4

4

0675

Jun

7.5

4

0675

Jul

7.8

4

0675

Aug

9.7

4

0675

Sep

NULL

4

0675

Oct

NULL

4

0675

Nov

NULL

4

0675

Dec

NULL

4


Using the Pivot Operator

You have the following options for using a pivot operator:

  • Define a new pivot operator: Use the Pivot Wizard to add a new pivot operator to a mapping. Drag a pivot operator from the Palette onto the mapping. The Mapping Editor displays the Pivot Wizard.

  • Edit an existing pivot operator: Use the Pivot Editor to edit a pivot operator you previously created. Right-click the operator and select Open Details. The Mapping Editor opens the Pivot Editor.

Whether you are using the Pivot Wizard or the Pivot Editor, complete the following pages:

General

Use the General page to specify a name and optional description for the pivot operator. By default, the wizard names the operator "Pivot".

Groups

Use the Groups page to specify one input and one output group.

In a pivot operator, the input group represents the data from the source that is contained across multiple attributes. The output group represents that data transformed into rows.

You can rename and add descriptions to the input and output groups. Since each pivot operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.

Input Connections

Use the Input Connections page to copy and map attributes into the pivot operator. The attributes you select become mapped to the pivot input group. The left side of the page displays a list of all the operators in the mapping.

Figure 18-15 Pivot Operator Input Connections Tab

Description of Figure 18-15 follows
Description of "Figure 18-15 Pivot Operator Input Connections Tab"

To complete the Input Connections page for a Pivot operator:

  1. Select complete groups or individual attributes from the left panel.

    To search for a specific attribute or group by name, type the text in Search for and select Go. To find the next match, select Go again.

    Press the Shift key to select multiple attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.

  2. Use the right arrow button in the middle of the page to move your selections to the right side of the wizard page.

    Use the left arrow to remove groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the data flow connection between the source operator and the pivot operator.

    Figure 18-16 Attributes Copied and Mapped into Pivot In Group

    Description of Figure 18-16 follows
    Description of "Figure 18-16 Attributes Copied and Mapped into Pivot In Group"

Input Attributes

Use the Input Attributes page to modify the attributes you selected in the Input Connections tab or wizard page.

You can perform the following tasks from the Input Attributes page:

  • Add attributes: Use the Add button to add input attributes.

  • Change attribute properties: You can change the attribute name, data type, length, precision, and scale.

  • Add an optional description: Type a description for the input attributes.

  • Designate attribute keys: As an option, use the Key check box to indicate an attribute that uniquely identifies the input group.

Output Attributes

Use the Output Attributes page to create the output attributes for the pivot operator. If you designated any input attributes as keys on the Input Attributes tab or wizard page, those input attributes are displayed as output attributes that you cannot edit or delete.

Figure 18-17 displays the output attributes with MONTH selected as the row locator.

Figure 18-17 Pivot Output Attributes Tab

Description of Figure 18-17 follows
Description of "Figure 18-17 Pivot Output Attributes Tab"

You can perform the following tasks from the pivot Output Attributes Page:

  • Change attribute properties: Except for attributes you designated as keys on the previous tab or wizard page, you can change the attribute name, data type, length, precision, and scale.

  • Add an optional description: Type a description for the output attributes.

  • Designate a row locator: Although you are not required to designate a row locator for the pivot operator, it is recommended. When you identify the row locator on the Output Attributes page or tab, it is easier for you to match your output data to the input data.

    In the pivot operator, the row locator is an output attribute that corresponds to the repeated set of data from the source. For example, if the source data contains separate attributes for each month, create an output attribute 'MONTH' and designate it as the row locator.

Pivot Transform

Use the Pivot Transform page to write expressions for each output attribute.

By default, two rows are displayed. Use Add to specify how many rows of output you want from a single row in the source. For example, if your source contains an attribute for each quarter in a year, you can specify 4 rows of output for each row in the source. If the source data contains an attribute for each month in the year, you can specify 12 rows of output for each row in the source.

Figure 18-18 shows the Pivot Transform tab with the pivot expressions defined for a source with an attribute for each month.

Figure 18-18 Pivot Transform Tab

Description of Figure 18-18 follows
Description of "Figure 18-18 Pivot Transform Tab"

Write pivot expressions based on the following types of output:

  • Row locator: Specify a name for each row where the name is a value you want to load into the table. For example, if the row locator is 'MONTH', type 'Jan' for the first row.

  • Pivoted output data: Select the appropriate expression from the list box. For example, for the row you define as 'Jan', select the expression that returns the set of values for January.

  • Attributes previously specified as keys: Defines the expression for you.

  • Unnecessary data: If the Pivot Transform page contains data that you do not want as output, use the expression 'NULL'. Warehouse Builder outputs a repeated set of rows with no data for attributes you define as 'NULL'.

When using the wizard to create a new pivot operator, click Finish when you want to close the wizard. The Mapping Editor displays the operator you defined.

When using the Pivot Editor to edit an existing pivot operator, click OK when you have finished editing the operator. The Mapping Editor updates the operator with the changes you made.

Post-Mapping Process Operator

Use a Post-Mapping Process operator to define a procedure to be executed after running a PL/SQL mapping. For example, you can use a Post-Mapping Process operator to reenable and build indexes after a mapping completes successfully and loads data into the target.

Description of post_map.gif follows
Description of the illustration post_map.gif

The Post-Mapping Process operator calls a function or procedure after the mapping is executed. The output parameter group provides the connection point for the returned value (if implemented through a function) and the output parameters of the function or procedure. There are no restrictions on the connections of these output attributes

The Post-Mapping Process operator contains groups corresponding to the number and direction of the parameters associated with the selected PL/SQL procedure or function. This list of groups and attributes can only be modified through reconciliation.

You can map constants, data generators, mapping input parameters, and output from a Pre-Mapping Process into a Post-Mapping Process operator. The Post-Mapping Process operator is not valid for an SQL*Loader mapping.

After you add a Post-Mapping Process operator to the Mapping Editor, use the operator properties dialog box to specify run conditions in which to execute the process.

To use a Post-Mapping Process operator in a mapping:

  1. Drag and drop a Post-Mapping Process operator onto the Mapping Editor canvas.

    Warehouse Builder displays the Add Post-Mapping Process dialog box.

  2. Use the Add Post-Mapping Process dialog box to select or create a transformation. For more information about how to use the Add Post-Mapping Process dialog box, see "Adding Operators that Bind to Workspace Objects".

  3. Connect the output attribute of a source operator to the input/output group of the Post-Mapping Process operator.

  4. Set the run conditions for the operator.

To set run conditions for a Post-Mapping Process operator:

  1. From the mapping canvas, select a Post-Mapping Process operator.

    The Post-Mapping Process Properties panel displays the properties of the Post-Mapping Process operator.

  2. Click Post-Mapping Process Run Condition and select one of the following run conditions:

    Always: The process runs regardless of errors from the mapping.

    On Success: The process runs only if the mapping completes without errors.

    On Error: The process runs only if the mapping completes with errors exceeding the number of allowed errors set for the mapping.

    On Warning: The process runs only if the mapping completes with errors that are less than the number of allowed errors set for the mapping.

    If you select On Error or On Warning and the mapping runs in row based mode, you must verify the Maximum Number of Errors set for the mapping. To view the number of allowed errors, right-click the mapping in the Project Explorer, select Configure, and expand Runtime Parameters.

Pre-Mapping Process Operator

Use a Pre-Mapping Process operator to define a procedure to be executed before running a mapping.

Description of pre_map.gif follows
Description of the illustration pre_map.gif

For example, you can use a Pre-Mapping Process operator to truncate tables in a staging area before running a mapping that loads tables to that staging area. You can also use a Pre-Mapping Process operator to disable indexes before running a mapping that loads data to a target. You can then use a Post-Mapping Process operator to reenable and build the indexes after running the mapping that loads data to the target.

The Pre-Mapping Process operator calls a function or procedure whose metadata is defined prior to executing a mapping. The output parameter group provides the connection point for the returned value (if implemented with a function) and the output parameters of the function or procedure. There are no restrictions on the connections of these output attributes.

When you drop a Pre-Mapping Process operator onto the Mapping Editor canvas, a dialog box opens displaying the available libraries, categories, functions, and procedures. After you select a function or procedure from the tree, the operator displays with predefined input and output parameters.

The Pre-Mapping Process operator contains groups corresponding to the number and direction of the parameters associated with the selected PL/SQL procedure or function.

A mapping can only contain one Pre-Mapping Process operator. Only constants, mapping input parameters, and output from a Pre-Mapping Process can be mapped into a Post-Mapping Process operator.

After you add a Pre-Mapping Process operator to the Mapping Editor, use the operator property dialog box to specify conditions in which to execute the mapping.

To use a Pre-Mapping Process operator in a mapping:

  1. Drag and drop a Pre-Mapping Process operator onto the Mapping Editor canvas.

    The Add Pre-Mapping Process dialog box is displayed.

  2. Use the Add Pre-Mapping Process dialog box to select or create a transformation. For more information about how to use this dialog box, see "Adding Operators that Bind to Workspace Objects".

  3. Connect the output attribute of the Pre-Mapping Process operator to the input group of a target operator.

  4. Set the run conditions for the operator.

To set run conditions for a mapping with a Pre-Mapping Process operator:

  1. In the mapping canvas, select the Pre-Mapping Process operator.

    The Pre-Mapping Process Properties panel displays the properties of the Pre-Mapping Process operator.

  2. Click Mapping Run Condition and select one of the following run conditions:

    Always: Runs the mapping after the process completes, regardless of the errors.

    On Success: Runs the mapping only if the process completes without errors.

    On Error: Runs the mapping only if the process completes with errors.

Set Operation Operator

Set operations combine the results of two component queries into a single result.

Description of set.gif follows
Description of the illustration set.gif

While a joiner combines separate rows into one row, set operators combine all data rows in their universal row. In set operators, although the data is added to one output, the column lists are not mixed together to form one combined column list.

The Set Operation operator enables you to use following set operations in a mapping:

By default, the Set Operation operator contains two input groups and one output group. You can add input groups by using the operator editor. The number of attributes in the output group matches the number of attributes in the input group containing the most number of attributes.

To use the Set Operation operator, all sets must have the same number of attributes and the data types of corresponding attributes must match. Corresponding attributes are determined by the order of the attributes within an input group. For example, attribute 1 in input group 1 corresponds to attribute 1 in input group 2.

You must apply the set operation in top-down order. The order of the input groups determines the execution order of the set operation. This order only affects the minus operation. For example, A minus B is not the same as B minus A. The order of the attributes within the first input group determines the structure of a set. For example, {empno, ename} is not the same as {ename, empno}.

Figure 18-19 shows a mapping that uses the Set Operation operator. The data from the two source tables is combined using the Intersect set operation. The output of this operation is mapped to the target. The target table only contains the rows that are common to both the input tables.

Figure 18-19 Set Operation Operator in a Mapping

Description of Figure 18-19 follows
Description of "Figure 18-19 Set Operation Operator in a Mapping"

To use the Set Operation operator in a mapping:

  1. Drag and drop a Set Operation operator onto the Mapping Editor canvas.

  2. Connect source attributes to the Set Operation operator groups.

  3. Select the Set operator header.

    The Set Operation Properties panel displays the properties of the Set operator.

  4. Click the list on the Set Operation property and select an operation from the list.

  5. Connect the Set Operation output group to a target input group.

Synchronizing the Attributes in a Set Operator

The set operator in the Mapping Editor assists you in matching attributes between two data streams. To match attributes from two data streams in a mapping, define the data streams as input groups into the set operator. On the Input Attributes tab, click Synchronize from <Input Group Name>. The synchronize operation rearranges and adds attributes to the target group such that the target group most closely matches the source group. The synchronize operation uses the following rules to find or create a match in the target:

  1. Looks for an existing attribute in the target that matches name and datatype.

  2. Looks for an existing attribute in the target whose description matches the source name, and the datatype matches source datatype.

  3. If (1) and (2) fail, then a new attribute is created with the source name and datatype, and is inserted in the correct matching position. Any unmatched target group attributes are indicated by UNMATCHED in the attribute description.

    To force a target attribute to match a specified source attribute, type the source group attribute as the target attribute description.

Sorter Operator

You can produce a sorted row set using the Sorter operator.

Description of sorter.gif follows
Description of the illustration sorter.gif

The Sorter operator enables you to specify which input attributes are sorted and whether the sorting is performed in ascending or descending order. Warehouse Builder sorts data by placing an ORDER BY clause in the code generated by the mapping.

The Sorter operator has one input/output group. You can use the Sorter operator to sort data from any relational database source. You can place any operator after the Sorter operator.

Order By Clause

The Sorter operator contains the Order By clause. This clause is an ordered list of attributes in the input/output group to specify that sorting is performed in the same order as the ordered attribute list. You can set ascending or descending sorting for each attribute.

Most data in warehouses is loaded in batches. There can be some problems with the loading routines. For example, a batch of orders might contain a single order number multiple times with each order line representing a different state of the order. The order might have gone from status 'CREATED' to 'UPDATED' to 'BOOKED' during the day.

Because a SQL statement does not guarantee any ordering by default, the inserts and updates on the target table can take place in the wrong order. If the 'UPDATED' row is processed last, it becomes the final value for the day although the result should be status 'BOOKED'. Warehouse Builder enables you to solve this problem by creating an ordered extraction query using the Sorter operator. The ORDER BY clause can use the last updated attribute. This will ensure that the records appear in the order in which they were created.

Figure 18-20 shows a mapping that uses the Sorter operator to sort the records from the ORACLE_ORDERS table. Use the Order By Clause property of the Sorter operator to sort the input records on the ORDER_ID and the LAST_UPDATED attributes.

Figure 18-20 Sorter Operator in a Mapping

Description of Figure 18-20 follows
Description of "Figure 18-20 Sorter Operator in a Mapping"

To use the Sorter operator in a mapping:

  1. Drag and drop the Sorter operator onto the Mapping Editor canvas.

  2. Connect a source operator group to the Sorter input/output group as shown in Figure 18-20.

  3. Select the Sorter operator header.

    The Sorter Properties panel displays the properties of the operator.

  4. Click the Ellipsis button in the Order By Clause field.

    The Order By Clause dialog box is displayed.

  5. Select the attributes you want to sort.

    Select an attribute from the Available Attributes list and click the right arrow button. Or, click the double right arrow button to select all of the Available Attributes.

  6. Apply an ORDER BY clause to the attribute.

    Select the attribute in the ORDER BY Attributes list and select ASC (ascending) or DESC (descending) from the ASC/DESC list.

  7. Click OK.

  8. Connect the output of the Sorter operator to the target.

Splitter Operator

You can use the Splitter operator to split data from one source to several targets.

Description of splitter.gif follows
Description of the illustration splitter.gif

The Splitter operator splits a single input row set into several output row sets using a boolean split condition. Each output row set has a cardinality less than or equal to the input cardinality. This is useful when you want to move data to different targets based on a data driven condition. Instead of moving the data through multiple filters, you can use a splitter.

As an option, you can optimize mappings that split data from one source to multiple targets for improved performance. For more information, see "Example: Creating Mappings with Multiple Targets".

The Splitter operator contains one input group and three output groups. The output groups are OUTGRP1, OUTGRP2, and REMAINING_ROWS. You can create additional output groups, if required. You can delete the REMAINING_ROWS output group, but you cannot edit it.

In most cases, the output group REMAINING_ROWS contains all input rows that are not included in any output group. However, when the split condition contains an attribute whose value is null, the corresponding rows are not moved to the REMAINING_ROWS output group.

The Splitter operator contains the split condition. For code generation, the source columns are substituted by the input attribute names in the expression template. The expression is a valid SQL expression that can be used in a WHERE clause.

Figure 18-21 shows the mapping that uses the Splitter operator to split customer data from the source table CUSTOMERS into two separate tables. One table contains only the customer addresses and the other table contains the remaining customer details. Use the Split Condition property of each output group in the Splitter operator to specify which data should be moved to a particular target table.

Figure 18-21 Splitter Operator in a Mapping

Description of Figure 18-21 follows
Description of "Figure 18-21 Splitter Operator in a Mapping"

To use the Splitter operator in a mapping:

  1. Drag and drop the Splitter operator onto the Mapping Editor canvas.

  2. Connect a group from a source operator to the input group of the Splitter operator.

    The output attributes are created with data types matching the corresponding input data types.

  3. Select the output group of the Splitter operator.

    The Group Properties panel displays the properties of the output group.

  4. Click the Ellipsis button to the right of the Split Condition field.

    The Expression Builder dialog box is displayed.

  5. Define the split condition.

    For example, the split condition can be UPPER(INGRP1.OR_CHANNEL) = 'DIRECT'.

  6. Define expressions for the split condition of each output group except the REMAINING ROWS group.

  7. Connect the output groups to the targets.

Example: Creating Mappings with Multiple Targets

When you design a mapping with multiple targets, you have the option to optimize for improved performance. You may decide to not optimize if you require accurate auditing details for the mapping. If you decide to not optimize, separate insert statements for each target are generated.

To optimize a multiple target mapping, you must take additional steps to generate a single insert statement for all targets combined. In this case, a multi_table_insert SQL statement is generated that takes advantage of parallel query and parallel DML services available in versions 9i and higher of the Oracle Database server.

To optimize a mapping with multiple targets:

  1. Define a mapping in an Oracle target module configured to generate Oracle 9i or higher SQL.

    Right-click the target module on the Project Explorer and select Configure. Under Deployment System Type and PL/SQL Generation Mode, select Oracle 9i or higher.

  2. In the Mapping Editor, design a mapping with a single source, a Splitter operator, and multiple targets.

    For the mapping to be optimized, the targets must be tables, not views or materialized views. Each target table must have less than 999 columns. Between the Splitter operator and the targets, do not include any operators that change the cardinality.

    For example, you can place a Filter between the Splitter and the targets as shown in Figure 18-22, but not a Joiner or Aggregator operator. These restrictions only apply if you choose to optimize the mapping.

    Figure 18-22 Example Mapping with Multiple Targets

    Description of Figure 18-22 follows
    Description of "Figure 18-22 Example Mapping with Multiple Targets"

  3. From the Project Explorer, select the mapping and select Design from the menu bar, and select Configure. You can also right-click the mapping you want to configure and select Configure.

    Warehouse Builder displays the configuration properties dialog box for a mapping.

  4. Expand Runtime Parameters and set Default Operating Mode to set based.

  5. Expand Code Generation Options and select Optimized Code.

When you run this mapping and view the generation results, one total SELECT and INSERT count for all targets is returned.

Table Function Operator

Table function operators enable you to manipulate a set of input rows and return another set of rows of the same or different cardinality.

Description of table_function_icon.gif follows
Description of the illustration table_function_icon.gif

While a regular function only works on one row at a time, a table function enables you to apply the same complex PL/SQL logic on a set of rows and increase your performance. Unlike conventional functions, table functions can return a set of output rows that can be queried like a physical table.

The execution of the table function can also be parallelized where the returned rows are streamed directly to the next process without intermediate staging. Rows from a collection returned by a table function can also be pipelined or output one by one, as they are produced, instead of being output in a batch after processing of the entire table function input is completed.

Using table functions can greatly improve performance when loading your data warehouse.

To define a Table Function operator in a mapping:

Before you deploy the mapping containing the Table Function operator, you must manually create the table function in the target warehouse. The Table Function operator is bound to the actual table function object through the code generated by the mapping.

  1. Drag and drop a Table Function operator onto the Mapping Editor canvas.

    A table function operator called TABLEFUNCTION is added to the Mapping Editor canvas.

  2. Connect the appropriate source attributes to the input group of the table function operator.

  3. Right-click the Table Function operator and select Open Details.

    The Table Function Editor is displayed.

  4. From the Groups tab, select Add to add an output group.

    Figure 18-23 shows a mapping that uses a Table Function operator to load data into a table.

    Figure 18-23 Table Function Operator in a Mapping

    Description of Figure 18-23 follows
    Description of "Figure 18-23 Table Function Operator in a Mapping"

Characteristics of Table Functions

Prerequisites for Using the Table Function Operator

Before you can use the Table Function operator in a mapping, create the table function in your target schema, external to Warehouse Builder. The table functions in the database that are supported by the unbound table function operator must meet the following requirements:

Input

  • Ref Cursor returning PLS Record (the fields of the PLS Record) must be supported scalar data types (0..n).

  • There must be at least one input parameter.

Output

  • PLS Record (the fields of the PLS Record should be scalar data types supported by Warehouse Builder.

  • Object Type (the attributes of the Object Type should be supported scalar data types).

  • Supported scalar data types.

  • ROWTYPE

For a Table Function operator in a mapping:

  • You must add one parameter group for each ref cursor type parameter.

  • Multiple scalar parameters can be part of a single scalar type parameter group.

  • The parameter groups and the parameters in a group can be entered in any order.

  • The positioning of the parameters in the table function operator must be the same as the positioning of the parameters in the table function created in your target warehouse.

Table Function Operator Properties

You access the Table Function operator properties using the Properties panel of the Mapping Editor. The Properties panel displays the properties of the object selected on the canvas. For example, when you select the input group of the table function operator, the Properties panel displays the properties of the input parameter group.

Table Function Operator Properties

The Table Function operator has the following properties.

Table Function Name: Represents the name of the table function. The name specified here must match the actual name of the table function.

Table Function is Target: Select this option to indicate that the table function is a target. By default, this property is selected.

Input Parameter Group Properties

The table function operator accepts the following types of input parameters:

  • Input Parameter Type: Valid input parameter types are REF_CURSOR_TYPE or SCALAR_TYPE.

  • REF_CURSOR_TYPE: Returns a PLS Record { 0...N }. The fields of the PLS Record must be a supported scalar data type.

  • SCALAR_TYPE: Supported scalar data types.

  • Parameter Position: Indicates the position of the parameter in the table function signature corresponding to this parameter group. This property is applicable only to REF_CURSOR attribute groups and is used in conjunction with the scalar parameter positions.

Input Parameter Properties

  • Parameter Position: The position of the parameter in the table function signature. This property is only applicable to scalar parameters.

Output Parameter Group Properties

  • Return Table of Scalar: This property specifies whether the return of the table function is a TABLE of SCALAR or not. This information is required because the select list item for TABLE of SCALAR must be Select COLUMN_VALUE while in the other cases it should be an appropriate name.

Output Parameter

  • Type Attribute Name: The name of the field of the PLS Record, attribute of the Object Type, or column of the ROWTYPE. This property is not applicable if the return type is TABLE of SCALAR. This name is used to call the table function.

Transformation Operator

Use the Transformation operator to transform the column value data of rows within a row set using a PL/SQL function, while preserving the cardinality of the input row set.

Description of transform.gif follows
Description of the illustration transform.gif

The Transformation operator must be bound to a function or procedure contained by one of the modules in the workspace. The inputs and outputs of the Transformation operator correspond to the input and output parameters of the bound workspace function or procedure. If the Transformation operator is bound to a function, a result output is added to the operator that corresponds to the result of the function. The bound function or procedure must be generated and deployed before the mapping can be deployed, unless the function or procedure already exists in the target system.

Warehouse Builder provides pre-defined PL/SQL library functions in the runtime schema that can be selected as a bound function when adding a Transformation operator onto a mapping. In addition, you can choose a function or procedure from the Global Shared Library.

The Transformation operator contains the following properties:

To use a Mapping Transformation operator in a mapping:

  1. Drag and drop a Mapping Transformation operator onto the Mapping Editor canvas.

    The Add Mapping Transformation dialog box is displayed.

  2. Use the Add Mapping Transformation dialog box to create a new transformation or select one or more transformations. For more information about these options, see "Adding Operators that Bind to Workspace Objects" beginning.

  3. Connect the source attributes to the inputs of the Mapping Transformation operator.

  4. (Optional step) Right-click one of the inputs and select Open Details.

    The Attribute Properties panel displays the properties of the attribute.

  5. Select an input attribute. If the Procedure property is set to True, then do not connect the input parameter.

  6. Connect the Transformation operator output attributes to the target attributes.

Unpivot Operator

The unpivot operator converts multiple input rows into one output row.

Description of unpivot_icon.gif follows
Description of the illustration unpivot_icon.gif

The unpivot operator enables you to extract from a source once and produce one row from a set of source rows that are grouped by attributes in the source data. Like the pivot operator, the unpivot operator can be placed anywhere in a mapping.

Example: Unpivoting Sales Data

Table 18-3 shows a sample of data from the SALES relational table. In the crosstab format, the 'MONTH' column has 12 possible character values, one for each month of the year. All sales figures are contained in one column, 'MONTHLY_SALES'.

Table 18-3 Data in a Crosstab Format

REP MONTH MONTHLY_SALES REGION

0675

Jan

10.5

4

0676

Jan

9.5

3

0679

Jan

8.7

3

0675

Feb

11.4

4

0676

Feb

10.5

3

0679

Feb

7.4

3

0675

Mar

9.5

4

0676

Mar

10.3

3

0679

Mar

7.5

3

0675

Apr

8.7

4

0676

Apr

7.6

3

0679

Apr

7.8

3


Figure 18-24 depicts data from the relational table 'SALES' after unpivoting the table. The data formerly contained in the 'MONTH' column (Jan, Feb, Mar...) corresponds to12 separate attributes (M1, M2, M3...). The sales figures formerly contained in the 'MONTHLY_SALES' are now distributed across the 12 attributes for each month.

Figure 18-24 Data Unpivoted from Crosstab Format

Description of Figure 18-24 follows
Description of "Figure 18-24 Data Unpivoted from Crosstab Format"

The Row Locator

When you use the Unpivot operator, multiple input rows are transformed into a single row based on the row locator. In the Unpivot operator, the row locator is an attribute that you must select from the source to correspond with a set of output attributes that you define. A row locator is required in an unpivot operator. In this example, the row locator is 'MONTH' from the 'SALES' table and it corresponds to attributes M1, M2, M3... M12 in the unpivoted output.

Using the Unpivot Operator

You have the following options for using an unpivot operator:

  • Define a new Unpivot operator: Drag an Unpivot operator from the Palette onto the mapping. The Mapping Editor displays a wizard.

  • Edit an existing Unpivot operator: Right-click the Unpivot operator and select Open Details. The Mapping Editor opens the Unpivot Editor.

Whether you are using the Unpivot Wizard or the Unpivot Editor, complete the following pages:

General

Use the General page to specify a name and optional description for the Unpivot operator. By default, the wizard names the operator "Unpivot".

Groups

Use the Groups page to specify one input and one output group.

In an Unpivot operator, the input group represents the source data in crosstab format. The output group represents the target data distributed across multiple attributes.

You can rename and add descriptions to the input and output groups. Since each Unpivot operator must have exactly one input and one output group, the wizard prevents you from adding or removing groups or changing group direction.

Input Connections

Use the Input Connections page to select attributes to copy and map into the unpivot operator.

To complete the Input connections page for an Unpivot operator:

  1. Select complete groups or individual attributes from the left panel.

    To search for a specific attribute or group by name, type the text in Search for and click Go. To find the next match, click Go again.

    Hold the Shift key down to select multiple groups or attributes. If you want to select attributes from different groups, you must first combine the groups with a Joiner or Set operator.

  2. Use the left to right arrow button in the middle of the page to move your selections to the right side of the wizard page.

    You can use the right to left arrow to move groups or attributes from the input connections list. Warehouse Builder removes the selection from the input group and removes the data flow connection between the source operator and the unpivot operator.

Input Attributes

Use the Input Attributes page to modify the attributes you selected in the Input Connections tab or wizard page.

You can perform the following tasks from the Unpivot Input Attributes page:

  • Add attributes: Use the Add button to add input attributes.

  • Change attribute properties: You can change the attribute name, data type, length, precision and scale.

  • Add an optional description: Type a description for the input attributes.

  • Designate key attribute(s): You must designate one or more key attributes for unpivot operators. Use the Key check box to indicate the attribute(s) that uniquely identifies the input group. Input rows with the same value in their key attribute(s) produce one unpivoted output row.

Row Locator

Use the Row locator page to select a row locator and assign values to the distinct values contained in the row locator.

Figure 18-25 shows the attribute MONTH selected as the row locator with values such as 'Jan', 'Feb', or 'Mar'.

Figure 18-25 Unpivot Row Locator Page

Description of Figure 18-25 follows
Description of "Figure 18-25 Unpivot Row Locator Page"

To complete the Unpivot Row Locator page:

  1. Select an attribute from the Row locator list box.

    In the Unpivot operator, the row locator is the attribute from the source data that corresponds to a set of output attributes.

  2. Use Add to specify the number of distinct values that exist in the row locator.

  3. For each row locator value, type in the value as it appears in your source dataset.

    For string values, enclose the text in single quotes. For example, if the row locator is 'MONTH', there would be a total of 12 distinct values for that attribute. Click Add to add a row for each distinct value. For row locator values, type values exactly as they appear in the source dataset. For instance, the row locator values as shown in Table 18-3 are 'Jan', 'Feb', and 'Mar.'

Output Attributes

Use the Output Attributes tab to create the output attributes for the Unpivot operator.

Figure 18-26 Unpivot Output Attributes Page

Description of Figure 18-26 follows
Description of "Figure 18-26 Unpivot Output Attributes Page"

If you designated any input attributes as keys on the Input Attributes tab or wizard page, those input attributes are displayed as output attributes that you cannot edit or remove.

You can perform the following tasks from the Unpivot Output Attributes page:

  • Add attributes: Use Add to increase the number of output attributes to accommodate the rows you specified on the Row locator tab or wizard page. If you specified 12 rows, specify 12 output attributes plus attributes for any other input attributes that you did not designate as a key.

  • Change attribute properties: Except for attributes you designated as keys on the Input Attributes tab or wizard page, you can change the attribute name, data type, length, precision, and scale.

  • Add an optional description: Type a description for the output attributes.

Unpivot Transform

Use the Unpivot Transform tab to write expressions for each output attribute.

Figure 18-27 Unpivot Transform Page

Description of Figure 18-27 follows
Description of "Figure 18-27 Unpivot Transform Page"

For attributes you designated as keys, the matching row and expression is defined for you. Warehouse Builder displays the first row as the match for a key attribute. For all other output attributes, specify the matching row and the expression.

  • Matching row: Select the appropriate option from the list box. For example, for the attribute you define as the first month of the year, 'M1', select 'Jan' from the list box.

  • Expression: Select the appropriate expression from the list box. For all the new attributes you created to unpivot the data, select the same input attribute that contains the corresponding data. For example, the unpivot attributes M1, M2, M3... M12 would all share the same expression, INGRP1.MONTHLY_SALES. For all other output attributes, select the corresponding attribute from the list of input attributes.