26 Data Flow Operators

List of Data Flow Operators

The list of data flow operators is as follows:

• "Aggregator Operator"

• "Anydata Cast Operator"

• "Deduplicator Operator"

• "Expression Operator"

• "Filter Operator"

• "Joiner Operator"

• "LCR Cast Operator"

• "LCR Splitter Operator"

• "Lookup Operator"

• "Pivot Operator"

• "Post-Mapping Process Operator"

• "Pre-Mapping Process Operator"

• "Set Operation Operator"

• "Sorter Operator"

• "Splitter Operator"

• "Subquery Filter Operator"

• "Table Function Operator"

• "Transformation Operator"

• "Unpivot Operator"

About 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:

• "Lookup Operator"

• Match Merge operator, see "Using the Match Merge Operator to Eliminate Duplicate Source Records"

• Name and Address operator, see "Using the Name and Address Operator to Cleanse and Correct Name and Address Data"

• "Queue Operator"

• "Pivot Operator"

• "Unpivot Operator"

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"

• "Operator Wizard Groups Page"

• "Operator Wizard Input and Output Pages"

• "Operator Wizard Input Connections"

About 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 Property Inspector of the operator or operator attributes.

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.

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 26-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 26-2 Aggregator Operator in a Mapping

Description of "Figure 26-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.

   Oracle Warehouse Builder displays the Aggregator Editor.

4. On the Output Attributes tab, create the output attributes that store the aggregated data.

   To create an output attribute, click the empty cell under the Attribute column and enter the attribute name. The default data type assigned to the attribute is NUMBER. You can change the data type and other parameters related to the data type such as length, precision, and so on.

   If the output attribute refers to an input attribute (from the Input group), the Group By Clause is automatically set.

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

5. Define an expression for each output attribute. You can directly enter the expression in the Expression column associated with the attribute. Or, click the Ellipsis button to the right of the Expression field to display the Expression Builder. For detailed instructions on using the Expression Builder, see "Aggregate Function Expression".

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

6. Click OK to close the Aggregator Editor.

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.

Anydata Cast Operator

Anydata Cast operator enables 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.

The Anydata Cast operator is used with user-defined data types and primitive data types. This 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 Anydata Cast operator is unbound, it does not support any synchronization operations.

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

Figure 26-5 Anydata Cast in a Mapping

Description of "Figure 26-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 opens to display the primary data types (other than Anydata). Each of the other parent nodes corresponds to the modules.

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

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

   Oracle 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 operator enables you to remove duplicate data in a source by placing a DISTINCT clause in the select code represented by the mapping.

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.

All attributes from a source rowset must pass through the Deduplicator operator. You cannot map part of the output from a source rowset and part of the output from the Deduplicator operator to the same target table.

Figure 26-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.

Figure 26-6 Deduplicator in a Mapping

Description of "Figure 26-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 I/O 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 nonprocedural algorithms for one output parameter of the operator.

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 26-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 26-7 Expression Operator in a Mapping

Description of "Figure 26-7 Expression Operator in a Mapping"

Do not use the Expression operator to write aggregation functions. Use the Aggregator operator. See "Aggregator Operator" for more information about the Aggregator operator.

Also, do not use a line-end or double-dash comment (--) after an expression in an expression operator if the expression is connected to an aggregation operator, as this causes a deployment compile error. This is because an aggregation operator in this scenario adds parenthesis, and the commenting removes one of them, creating unbalanced parenthesis.

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.

   Oracle Warehouse Builder displays the Expression Editor.

3. On the Output Attributes tab, create an output attribute by clicking on a blank cell under the Attribute column. The default data type assigned is NUMERIC. You can modify the data type and other parameters associated with the data type.

4. Define the expression used for the output attribute.

   Enter the expression directly in the Expression field of the output attribute. Or click the Ellipsis button to the right of the Expression field to display the Expression builder dialog box that enables you to specify the expression.

5. Click OK to close the Expression Editor.

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

Filter Operator

You can conditionally filter out rows using the Filter operator.

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 SQL statement or a IF statement of the generated PL/SQL code. 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 I/O 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 26-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 26-8 Filter in a Mapping

Description of "Figure 26-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 I/O group of the Filter operator.

3. Select the Filter operator header.

   The Property Inspector displays the properties of the Filter operator.

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

   Oracle 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 I/O group in the target.

Joiner Operator

The Joiner operator joins multiple row sets from different sources with different cardinalities, and produces a single output row set.

In general, a Joiner operator corresponds to a SQL JOIN clause. For more information about SQL joins, see Oracle Database SQL Language Reference.

The Joiner operator uses a boolean condition called the join 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 Oracle9i or higher, ANSI full outer joins are supported.

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".

For all Joiners, you must set a join condition, as described in "Join Conditions".

For Joiners that define outer joins, you must use one of two methods to specify the outer join type:

• Insert Oracle SQL outer join operators (+) in the join condition, as described in "Join Conditions".

• Set joiner input roles on the input groups, as described in "Join Input Roles and Inner and Outer Joins".

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 WHERE clause */

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

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

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

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

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

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

T1.A(+) = T2.A(+) AND T2.A = T3.A

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

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; 

T1 JOIN T2

T1 FULL OUTER JOIN T2

T1 LEFT OUTER JOIN T2

T1 RIGHT OUTER JOIN T2

T1 RIGHT OUTER JOIN T2

T1 LEFT OUTER JOIN T2 
   LEFT OUTER JOIN T3

T1 RIGHT OUTER JOIN T2 
   RIGHT OUTER JOIN T3

Steps to Use a Joiner Operator in a Mapping

To add a Joiner operator to a mapping:

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

2. You must join more than two sources, right-click the Joiner operator and select Open Details.

   Oracle Warehouse Builder displays the Joiner Editor.

   On the Groups tab, create more input groups as needed.

   To create an input group, click the empty cell under the Group column and enter the new group name. The default direction assigned to the new group is Input.

   Click OK after adding your input groups.

3. For each input group, connect an output group from a source to the desired Joiner input group.

   Output attributes are created with data types matching the corresponding input data types.

4. If necessary, change the Input Role for each source.

   See "Join Input Roles and Inner and Outer Joins".

5. Select the Joiner operator header.

   The Property Inspector displays the properties of the Joiner operator.

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

   The Expression Builder dialog box is displayed.

7. Define the join condition.

   See "Join Conditions".

8. Click OK to close the Expression Builder.

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

Example: Using the Joiner Operator

Figure 26-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 26-9 Joiner in a Mapping

Description of "Figure 26-9 Joiner in a Mapping"

LCR Cast Operator

Use the LCR Cast operator to expand an LCR (Logical Change Record) object into its constituent columns. It enables you to update the target object with insert, update, or delete operations contained in the LCR. Typically, the LCR Cast operator is used just after a Queue operator in a real-time mapping that publishes source changes to target objects.

The LCR Cast operator must be bound to the table for which it stores change records. It contains one input group and one output group, both of which are non-editable. You cannot add groups to this operator, but you can rename the existing input or output group. The input group contains an attribute Event, of type SYS.LCR$_ROW_RECORD, that stores the LCRs. You must connect a SYS.ANYDATA attribute to the input group. The output group contains the columns of the tables to which the LCR Cast operator is bound.

To add an LCR Cast operator to a mapping:

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

   Oracle Warehouse Builder displays the Add LCR Cast dialog box.

2. Select the table to which the LCR Cast operator must be bound.

3. Connect the output attribute of a source operator to the input group of the LCR Cast operator.

4. Connect the attributes of the output group in the LCR Cast operator to the target table to which you want to publish the changes contained in the LCR.

Figure 26-10 describes an example of using the LCR Cast operator in a mapping. The Queue operator ADVANCED_QUEUE represents the advanced queue that stores the change records for the source table. The LCR Cast operator is bound to the target table into which the changed records from the source should be transferred.

The AQ contains a payload that is represented by the PAYLOAD attribute. The payload stores change data in the form of LCRs. Map the PAYLOAD attribute the LCR Cast operator to expand the LCR into the columns contained in the target table that is bound to the LCR Cast operator. You then map the output attributes of the LCR Cast operator to the target table ORDERS.

Figure 26-10 Mapping that Uses an LCR Cast Operator

Description of "Figure 26-10 Mapping that Uses an LCR Cast Operator"

LCR Splitter Operator

Use the LCR Splitter operator to direct changes to different tables along data flow paths.

The LCR Splitter contains one input group and one output group. Both groups contain one attribute called Event of type SYS.LCR$_ROW_RECORD. The input group represents the represents the LCR object. You cannot add input or output groups.

Lookup Operator

Use the Lookup operator to lookup data from a table, view, cube, or dimension. For example, use the Lookup operator when you define a mapping that loads a cube or when you define surrogate keys on the dimension.

You can use the same Lookup operator to lookup data from multiple objects.

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 Lookup operator reads data from a lookup table using the key input you supply and returns exactly one matching row. This operator returns a row for each input key. You can have multiple Lookup operators in the same mapping.

The output of the Lookup operator corresponds to the columns in the lookup object. In case multiple records are returned by the lookup operation, you can specify which of these records is selected.

The Lookup Wizard contains one input group and one output group. You can create additional input and output groups. The attributes in each input group must be connected from the same data source. Each output group is bound to one lookup object. Each lookup uses attributes from only one input group as search values. That is, each output group is associated with only one input group. The tooltip for each output group displays the input group associated with it and the lookup condition used.

Since an output group is bound to an object, its attributes are the columns in the object. You can create additional output attributes, that are derived from the object columns, by using the Expression property of the output attribute.

Each output attribute for the 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 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 Lookup operator. You can synchronize a Lookup operator with the workspace object to which it is bound. But you cannot synchronize the workspace object with the Lookup operator. For more information about synchronizing operators, see "Synchronizing Operators and Workspace Objects".

Points to Keep In Mind while Using the Lookup Operator

The Lookup operator returns only one row. When the result of the lookup returns multiple rows, you must specify which of the rows must be used as the return value. You can select either the first row or the last row from the returned rows.

Since you are selecting the first or last row, the order in which the lookup results are returned is important. Since SQL queries do not guarantee repeatable order, you must specify the appropriate ORDER BY clause to sort lookup results. To eliminate non-determinism, ensure that you specify a detailed ordering clause that ensures that the desired record is picked as the lookup result.

Figure 26-11 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 Lookup operator is used to lookup the surrogate identifier of the CHANNELS dimension and then load this value into the cube.

Figure 26-11 Lookup Operator in a Mapping

Description of "Figure 26-11 Lookup Operator in a Mapping"

Pivot Operator

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

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 contains data from a flat file. There is a row for each sales representative and separate columns for each month.

See Also:

Oracle Warehouse Builder Sources and Targets Guide for more information about external tables.

Figure 26-12 displays the flat file SALES_DAT.

Figure 26-12 SALES_DAT

Description of "Figure 26-12 SALES_DAT"

Table 26-3 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 26-3 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 26-13.

Figure 26-13 Pivot Operator in a Mapping

Description of "Figure 26-13 Pivot Operator in a Mapping"

In this mapping that performs the pivot transformation, 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 run the mapping in set-based mode.

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 Pivot 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"

• "Groups"

• "Input Connections"

• "Input Attributes"

• "Output Attributes"

• "Pivot Transform"

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 26-14 shows a group from the external table SALES_DAT selected as input for the Pivot operator.

Figure 26-14 Pivot Operator Input Connections Tab

Description of "Figure 26-14 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. To select attributes from different groups, then 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. Oracle Warehouse Builder removes the selection from the input group and removes the data flow connection between the source operator and the Pivot operator.

   Figure 26-15 shows a group from SALES_DAT copied and mapped into the PIVOTSALES operator.

   Figure 26-15 Attributes Copied and Mapped into Pivot In Group

   
   Description of "Figure 26-15 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: Enter the attribute name and other attribute details in an empty row on this page.

• 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, then those input attributes are displayed as output attributes that you cannot edit or delete.

Figure 26-16 displays the output attributes with MONTH selected as the row locator.

Figure 26-16 Pivot Output Attributes Tab

Description of "Figure 26-16 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, then you can specify 12 rows of output for each row in the source.

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

Figure 26-17 Pivot Transform Tab

Description of "Figure 26-17 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'. Oracle Warehouse Builder outputs a repeated set of rows with no data for attributes you define as 'NULL'.

When using the wizard to create a 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 run 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.

The Post-Mapping Process operator calls a function or procedure after the mapping is run. 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 synchronization with workspace objects.

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 run 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.

   Oracle 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 "Using the Add Operator Dialog Box to Add Operators".

3. Connect the output attribute of a source operator to the I/O 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 Property Inspector 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 enabled errors set for the mapping.

   On Warning: The process runs only if the mapping completes with errors that are less than the number of enabled 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 enabled errors, right-click the mapping in the Projects Navigator, select Configure, and expand Runtime Parameters.

Pre-Mapping Process Operator

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

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 before executing a mapping. The output attribute group provides the return value (if implemented as a function) and the output parameters of the function or procedure. You can connect these attributes to any other operators downstream, and they do not have the connection restrictions that apply to the Post-Mapping Process operator.

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 the attributes that correspond to the selected function or procedure.

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.

After you add a Pre-Mapping Process operator to the Mapping Editor, use the Property Inspector to specify the Run condition of 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 "Using the Add Operator Dialog Box to Add Operators".

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 Property Inspector 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.

While a Joiner operator combines separate rows into one row, Set Operation operators combine all data rows into one output rowset using one of the various set operation conditions. In Set Operation operators, although the data is added to one output, the column lists are not mixed to form one combined column list.

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

• Union (default)

• Union All

• Intersect

• Minus

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 different from B minus A. The order of the attributes within the first input group determines the structure of a set. For example, {empno, ename} is different from {ename, empno}.

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 Operation operator header.

   The Property Inspector displays the properties of the Set Operation 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.

Sorter Operator

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

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

The Sorter operator has one I/O 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 I/O 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, then it becomes the final value for the day although the result should be status 'BOOKED'. Oracle 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 ensures that the records appear in the order in which they were created.

Figure 26-18 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 26-18 Sorter Operator in a Mapping

Description of "Figure 26-18 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 I/O group.

3. Select the Sorter operator header.

   The Property Inspector 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.

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 26-19 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 26-19 Splitter Operator in a Mapping

Description of "Figure 26-19 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 Property Inspector 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.

Subquery Filter Operator

The Subquery Filter operator enables you to filter rows based on the results of a subquery. The conditions that you can use to filter rows are EXISTS, NOT EXISTS, IN, and NOT IN.

For example, the EMP table contains employee data. You can use a subquery to fetch a set of records from another table and then filter rows from the EMP table by using one of the conditions EXISTS, NOT EXISTS, IN, or NOT IN.

The Subquery Filter operator contains one input group INGRP1 and one I/O group INOUTGRP1. INGRP1 is mapped from the object that represents the subquery used to filter source data. The default condition used for filtering data is EXISTS, which is indicated by an "E" displayed to the left of INGRP1. You can change this condition based on your requirement. The group INOUTGRP1 is mapped from the source data set that must be filtered. The filtered data is available as an output of this group.

To create a mapping with a Subquery Filter operator:

1. Drag and drop a Subquery Filter operator onto the mapping canvas.

2. Connect the source attributes to filter to the I/O group INOUTGRP1 of the Subquery Filter operator.

3. Connect the required attributes from the object to use as a subquery to the group INGRP1 of the Subquery Filter operator.

4. Select the Subquery Filter operator header.

   The Property Inspector displays the properties of the Subquery Filter operator.

   If the Property Inspector is not visible in the Design Center, select Property Inspector from the View menu.

5. In Subquery Filter Input Role field, select the condition to use to filter input rows. The available options are: Exists, In, Not Exists, or Not In.

6. If a filter condition is required, click the Ellipsis button on the Subquery Filter Condition field to display the Expression Builder. Use this interface to specify the filter condition used to compare the input rowset and the rowset returned by the subquery.

   A filter condition is not required if the input role is IN or NOT IN. For filter roles EXISTS and NOT EXISTS, you must specify a filter condition that relates the source query to EXISTS filtering subquery.

7. If the input role is IN or NOT IN, edit the properties of each input attribute and select the matching attribute from the I/O group of the subquery. This relates an input attribute from the source input query to each subquery group attribute, thus relating the source query to the filtering subquery.

8. Connect the I/O group of the Subquery Filter operator to the target.

Figure 26-21 displays a simple example of a Subquery Filter operator. In this mapping, orders data relating to customers whose credit limit is above a certain value is loaded into a target table called CUST_TGT. Order data is stored in the ORDERS and ORDERS_ITEMS tables. A Joiner operator is used to combine orders data from these tables. Use a Subquery Filter operator to filter order data based on the results of a subquery on the CUSTOMERS table.

The attributes, in the CUSTOMERS table, required for comparison are mapped to the input group of the Subquery Filter operator. The orders data, represented by the result of the Joiner operator, are mapped to the Input/Output group of the Subquery Filter operator. The Subquery Filter Condition property of the Subquery Filter operator is set to represent the condition used compare rows. In this example, the following condition was specified for the Subquery Filter Condition:

INGRP1.CUSTOMER_ID  =  INOUTGRP1.CUSTOMER_ID  AND  INGRP1.CREDIT_LIMIT  >= 75000

Figure 26-21 Subquery Filter Operator in a Mapping

Description of "Figure 26-21 Subquery Filter Operator in a Mapping"

Following is the code generated by Oracle Warehouse Builder for the Subquery Filter operator used in the mapping displayed in Figure 26-21.

SELECT
 "ORDERS"."ORDER_ID" "ORDER_ID",
  "ORDERS"."CUSTOMER_ID" "CUSTOMER_ID",
  "ORDERS"."ORDER_DATE" "ORDER_DATE"
FROM
     "OE"."ORDERS"@"ORA11@OE_SRC_LOCATION"  "ORDERS"   
   JOIN 
     "OE"."ORDER_ITEMS"@"ORA11@OE_SRC_LOCATION"  "ORDER_ITEMS" ON 
          ( ( "ORDERS"."ORDER_ID" = "ORDER_ITEMS"."ORDER_ID" ) )
WHERE 
  (EXISTS 
    (SELECT  1 
     FROM "OE"."CUSTOMERS"@"ORA11@OE_SRC_LOCATION"  "CUSTOMERS"
     WHERE 
       ( "CUSTOMERS"."CUSTOMER_ID"  =  "ORDERS"."CUSTOMER_ID" )  AND
          ( "CUSTOMERS"."CREDIT_LIMIT"  >= 75000)
    )
  );

Table Function Operator

Use Table Function operators to represent a table function in a mapping. Table function operators enable you to manipulate a set of input rows and return another set of rows of the same or different cardinality.

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.

A Table Function operator contains one input group and one output group.

To define a Table Function operator in a mapping:

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

   The Add Table Function Operator dialog box is displayed.

2. Use an existing table function to transform data by choosing Select from existing repository object and bind and then selecting the table function from the tree.

   The Table Function operator is added to the canvas. The input group INGRP1 contains the input parameters defined for the table function.

3. Map the operator that contains the input to the used by the table function to the parameters in the input group.

   Typically one or more of the input parameters are collection types. In such cases, if the source object does not contain collection types, use a Construct Object operator to create a collection type using the individual source attributes.

4. Map the Return group of the Table Function operator to the operator representing the transformation target.

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

Figure 26-22 Table Function Operator in a Mapping

Description of "Figure 26-22 Table Function Operator in a Mapping"

Characteristics of Table Functions

• They do not support the passing of parameters by name.

• If the return type is TABLE of PLS Record, the name you select must match the name of PLS Record field. It is possible to select only one subset of the fields of the PLS Record in the select list.

• If the return type is TABLE of T1%ROWTYPE, the name you select must match the name of the columns of the table T1.

• If the return type is TABLE of Object Type, the name you select list must match the name of Object Type attribute.

• If the return type is TABLE of Scalar (like TABLE of NUMBER), only Select COLUMN_VALUE can be used to retrieve the scalar values returned by 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.

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, then 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 exists in the target system.

Oracle Warehouse Builder provides pre-defined PL/SQL library functions in the run time 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 public Oracle Custom library.

The Transformation operator contains the following properties:

• Function Call: The text template for the function call that is generated by the code generator with the attribute names listed as the calling parameters. For the actual call, the attribute names are replaced with the actual source or target columns that are connected to the attributes.

• Function Name: The name of the function or procedure, to which this operator is bound.

• Procedure: A boolean value indicating, if true, that the bound transformation is a procedure rather than a function with no returned value.

• Data Type: Indicates the data type of the input, output, or result parameter of the bound function that corresponds to the given attribute. If the output of a mapping transformation is of CHAR data type, then an RTRIM is applied on the result before moving the data to a target. This ensures that no extra spaces are contained in the output result.

• Default Value: The default value (blank if none) for the given attribute.

• Optional Input: A boolean value indicating, if true, that the given attribute is optional. If the attribute is optional, then it need not be connected in the mapping.

• Function Return: A boolean value indicating, if true, that the given output attribute is the result attribute for the function. The result attribute is a named result. Use this property if another output is a named result, or if you change the name of the result output.

To use a Transformation operator in a mapping:

1. Drag and drop a 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 transformation or select one or more transformations. For more information about these options, see "Using the Add Operator Dialog Box to Add Operators".

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

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

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

Unpivot Operator

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

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 26-5 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 26-5 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 26-23 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 26-23 Data Unpivoted from Crosstab Format

Description of "Figure 26-23 Data Unpivoted from Crosstab Format"

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"

• "Groups"

• "Input Connections"

• "Input Attributes"

• "Row Locator"

• "Output Attributes"

• "Unpivot Transform"

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. To select attributes from different groups, then 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. Oracle 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: Enter the attribute name and other attribute details in an empty row on the page.

• 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 26-24 shows the attribute MONTH selected as the row locator with values such as 'Jan', 'Feb', or 'Mar'.

Figure 26-24 Unpivot Row Locator Page

Description of "Figure 26-24 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 data set.

   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 data set. For instance, the row locator values as shown in Table 26-5 are 'Jan', 'Feb', and 'Mar.'

Output Attributes

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

Figure 26-25 displays the Output Attributes tab.

Figure 26-25 Unpivot Output Attributes Page

Description of "Figure 26-25 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: To increase the number of output attributes to accommodate the rows you specified on the Row locator tab or wizard page, enter the attribute details in an empty cell of the page. If you specified 12 rows, then 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 26-26 displays the Unpivot Transform tab.

Figure 26-26 Unpivot Transform Page

Description of "Figure 26-26 Unpivot Transform Page"

For attributes you designated as keys, the matching row and expression is defined for you. Oracle 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.