C Oracle Warehouse Builder to Oracle Data Integrator Migration Utility Patch

This appendix provides information about the new and enhanced features that are provided in ODI 12.1.2 patch number 17053768 "Oracle Warehouse Builder to Oracle Data Integrator Migration Utility Patch".

The ODI 12.1.2 patch number 17053768 is available for search and download through My Oracle Support. To access My Oracle Support, click the following URL:

http://www.oracle.com/pls/topic/lookup?ctx=acc&id=info

This appendix includes the following sections:

New Features

This section provides information about the new features that are available in ODI 12.1.2 patch number 17053768. This section includes the following topics:

Pivot Component

A pivot component is a projector component (see: "Projector Components") that lets you transform data that is contained in multiple input rows into a single output row. The pivot component lets you extract data from a source once and produce one row from a set of source rows that are grouped by attributes in the source data. The pivot component can be placed anywhere in the data flow of a mapping.

Example: Pivoting Sales Data

SALES shows a sample of data from the SALES relational table. The QUARTER attribute has 4 possible character values, one for each quarter of the year. All the sales figures are contained in one attribute, SALES.

Table C-1 SALES

YEAR QUARTER SALES

2010

Q1

10.5

2010

Q2

11.4

2010

Q3

9.5

2010

Q4

8.7

2011

Q1

9.5

2011

Q2

10.5

2011

Q3

10.3

2011

Q4

7.6

PIVOTED DATA depicts data from the relational table SALES after unpivoting the table. The data that was formerly contained in the QUARTER attribute (Q1, Q2, Q3, and Q4) corresponds to 4 separate attributes (Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales). The sales figures formerly contained in the SALES attribute are distributed across the 4 attributes for each quarter.

Table C-2 PIVOTED DATA

Year Q1_Sales Q2_Sales Q3_Sales Q4_Sales

2010

10.5

11.4

9.5

8.7

2011

9.5

10.5

10.3

7.6

The Row Locator

When you use the pivot component, multiple input rows are transformed into a single row based on the row locator. The row locator is an attribute that you must select from the source to correspond with the set of output attributes that you define. It is necessary to specify a row locator to perform the unpivot operation.

In this example, the row locator is the attribute QUARTER from the SALES table and it corresponds to the attributes Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales attributes in the pivoted output data.

Using the Pivot Component

To use a pivot component in a mapping:

  1. Drag and drop the source datastore into the logical diagram.

  2. Drag and drop a Pivot component from the component palette into the logical diagram.

  3. From the source datastore drag and drop the appropriate attributes on the pivot component. In this example, the YEAR attribute.

    Note:

    Do not drag the row locator attribute or the attributes that contain the data values that correspond to the output attributes. In this example, QUARTER is the row locator attribute and SALES is the attribute that contain the data values (sales figures) that correspond to the Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales output attributes.

  4. Select the pivot component. The properties of the unpivot component are displayed in the Property Inspector.

  5. Enter a name and description for the pivot component.

  6. Type in the expression or use the Expression Editor to specify the row locator. In this example, since the QUARTER attribute in the SALES table is the row locator, the expression will be SALES.QUARTER.

  7. Under Row Locator Values, click the + sign to add the row locator values. In this example, the possible values for the row locator attribute QUARTER are Q1, Q2, Q3, and Q4.

  8. Under Attributes, add output attributes to correspond to each input row. If required, you can add new attributes or rename the listed attributes.

    In this example, add 4 new attributes, Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales that will correspond to 4 input rows Q1, Q2, Q3, and Q4 respectively.

  9. If required, change the expression for each attribute to pick up the sales figures from the source and select a matching row for each attribute.

    In this example, set the expressions for each attribute to SALES.SALES and set the matching rows to Q1, Q2, Q3, and Q4 respectively.

  10. Drag and drop the target datastore into the logical diagram.

  11. Connect the pivot component to the target datastore by dragging a link from the output (right) connector of the pivot component to the input (left) connector of the target datastore.

  12. Drag and drop the appropriate attributes of the pivot component on to the target datastore. In this example, YEAR, Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales.

  13. Go to the physical diagram and assign new KMs if you want to.

    Save and execute the mapping to perform the pivot operation.

Unpivot Component

An unpivot component is a projector component (see: "Projector Components") that lets you transform data that is contained across attributes into multiple rows.

The unpivot component does the reverse of what the pivot component does. Similar to the pivot component, an unpivot component can be placed anywhere in the flow of a mapping.

The unpivot component is specifically useful in situations when you extract data from non-relational data sources such as a flat file, which contains data across attributes rather than rows.

Example: Unpivoting Sales Data

The external table, QUARTERLY_SALES_DATA, shown in Table C-3, contains data from a flat file. There is a row for each year and separate attributes for sales in each quarter.

Table C-3 QUARTERLY_SALES_DATA

Year Q1_Sales Q2_Sales Q3_Sales Q4_Sales

2010

10.5

11.4

9.5

8.7

2011

9.5

10.5

10.3

7.6

Table C-4 shows a sample of the data after an unpivot operation is performed. The data that was formerly contained across multiple attributes (Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales) is now contained in a single attribute (SALES). The unpivot component breaks the data in a single attribute (Q1_Sales) into two attributes (QUARTER and SALES). A single row in QUARTERLY_SALES_DATA corresponds to 4 rows (one for sales in each quarter) in the unpivoted data.

Table C-4 UNPIVOTED DATA

YEAR QUARTER SALES

2010

Q1

10.5

2010

Q2

11.4

2010

Q3

9.5

2010

Q4

8.7

2011

Q1

9.5

2011

Q2

10.5

2011

Q3

10.3

2011

Q4

7.6

The Row Locator

The row locator is an output attribute that corresponds to the repeated set of data from the source. The unpivot component transforms a single input attribute into multiple rows and generates values for a row locator. The other attributes that correspond to the data from the source are referred as value locators. In this example, the attribute QUARTER is the row locator and the attribute SALES is the value locator.

Note:

To use the unpivot component, you are required to create the row locator and the value locator attributes for the unpivot component.

Using the Unpivot Component

To use an unpivot component in a mapping:

  1. Drag and drop the source data store into the logical diagram.

  2. Drag and drop an Unpivot component from the component palette into the logical diagram.

  3. From the source datastore drag and drop the appropriate attributes on the unpivot component. In this example, the YEAR attribute.

    Note:

    Do not drag the attributes that contain the data that corresponds to the value locator. In this example, Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales.

  4. Select the unpivot component. The properties of the unpivot component are displayed in the Property Inspector.

  5. Enter a name and description for the unpivot component.

  6. Create the row locator and value locator attributes using the Attribute Editor. In this example, you need to create two attributes named QUARTER and SALES.

    Note:

    Do not forget to define the appropriate data types and constraints (if required) for the attributes.

  7. In the Property Inspector, under UNPIVOT, select the row locator attribute from the Row Locator drop-down list. In this example, QUARTER.

    Now that the row locator is selected, the other attributes can act as value locators. In this example, SALES.

  8. Under UNPIVOT TRANSFORMS, click + to add transform rules for each output attribute. Edit the default values of the transform rules and specify the appropriate expressions to create the required logic.

    In this example, you need to add 4 transform rules, one for each quarter. The transform rules define the values that will be populated in the row locator attribute QUARTER and the value locator attribute SALES. The QUARTER attribute must be populated with constant values (Q1, Q2, Q3, and Q4), while the SALES attribute must be populated with the values from source datastore attributes (Q1_Sales, Q2_Sales, Q3_Sales, and Q4_Sales).

  9. Leave the INCLUDE NULLS check box selected to generate rows with no data for the attributes that are defined as NULL.

  10. Drag and drop the target datastore into the logical diagram.

  11. Connect the unpivot component to the target datastore by dragging a link from the output (right) connector of the unpivot component to the input (left) connector of the target datastore.

  12. Drag and drop the appropriate attributes of the unpivot component on to the target datastore. In this example, YEAR, QUARTER, and SALES.

  13. Go to the physical diagram and assign new KMs if you want to.

  14. Click Save and then execute the mapping to perform the unpivot operation.

Table Function Component

A table function component is a projector component (see: "Projector Components") that represents a table function in a mapping. Table function components enable you to manipulate a set of input rows and return another set of output rows of the same or different cardinality. The set of output rows can be queried like a physical table. A table function component can be placed anywhere in a mapping, as a source, a target, or a data flow component.

A table function component can have multiple input connector points and one output connector point. The input connector point attributes act as the input parameters for the table function, while the output connector point attributes are used to store the return values.

For each input connector, you can define the parameter type, REF_CURSOR or SCALAR, depending on the type of attributes the input connector point will hold.

To use a table function component in a mapping:

  1. Create a table function in the database if it does not exist.

  2. Right-click the Mappings node and select New Mapping.

  3. Drag and drop the source datastore into the logical diagram.

  4. Drag and drop a table function component from the component palette into the logical diagram. A table function component is created with no input connector points and one default output connector point.

  5. Click the table function component. The properties of the table function component are displayed in the Property Inspector.

  6. In the property inspector, go to the Attributes tab.

  7. Type the name of the table function in the Name field. If the table function is in a different schema, type the function name as SCHEMA_NAME.FUNCTION_NAME.

  8. Go to the Connector Points tab and click the + sign to add new input connector points. Do not forget to set the appropriate parameter type for each input connector.

    Note:

    Each REF_CURSOR attribute must be held by a separate input connector point with its parameter type set to REF_CURSOR. Multiple SCALAR attributes can be held by a single input connector point with its parameter type set to SCALAR.

  9. Go to the Attributes tab and add attributes for the input connector points (created in previous step) and the output connector point. The input connector point attributes act as the input parameters for the table function, while the output connector point attributes are used to store the return values.

  10. Drag and drop the required attributes from the source datastore on the appropriate attributes for the input connector points of the table function component. A connection between the source datastore and the table function component is created.

  11. Drag and drop the target datastore into the logical diagram.

  12. Drag and drop the output attributes of the table function component on the attributes of the target datastore.

  13. Go to the physical diagram of the mapping and ensure that the table function component is in the correct execution unit. If it is not, move the table function to the correct execution unit.

  14. Assign new KMs if you want to.

  15. Save and then execute the mapping.

Subquery Filter Component

A subquery filter component is a projector component (see: "Projector Components") that lets 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 datastore contains employee data and the DEPT datastore contains department data. You can use a subquery to fetch a set of records from the DEPT datastore and then filter rows from the EMP datastore by using one of the subquery conditions.

A subquery filter component has two input connector points and one output connector point. The two input connector points are Driver Input connector point and Subquery Filter Input connector point. The Driver Input connector point is where the main datastore is set, which drives the whole query. The Subquery Filter Input connector point is where the datastore that is used in the sub-query is set. In the example, EMP is the Driver Input connector point and DEPT is the Subquery Filter Input connector point.

To filter rows using a subquery filter component:

  1. Drag and drop a subquery filter component from the component palette into the logical diagram.

  2. Connect the subquery filter component with the source datastores and the target datastore.

  3. Drag and drop the input attributes from the source datastores on the subquery filter component.

  4. Drag and drop the output attributes of the subquery filter component on the target datastore.

  5. Go to the Connector Points tab and select the input datastores for the driver input connector point and the subquery filter input connector point.

  6. Click the subquery filter component. The properties of the subquery filter component are displayed in the Property Inspector.

  7. Go to the Attributes tab. The output connector point attributes are listed. Set the expressions for the driver input connector point and the subquery filter connector point.

    Note:

    You are required to set an expression for the subquery filter input connector point only if the subquery filter input role is set to one of the following:

    IN, NOT IN, =, >, <, >=, <=, !=, <>, ^=

  8. Go to the Condition tab.

  9. Type an expression in the Subquery Filter Condition field. It is necessary to specify a subquery filter condition if the subquery filter input role is set to EXISTS or NOT EXISTS.

  10. Select a subquery filter input role from the Subquery Filter Input Role drop-down list.

  11. Select a group comparison condition from the Group Comparison Condition drop-down list. A group comparison condition can be used only with the following subquery input roles:

    =, >, <, >=, <=, !=, <>, ^=

  12. Save and then execute the mapping.

Enhanced Features

This section provides information about the existing features that have been enhanced in ODI 12.1.2 patch number 17053768. This section includes the following topics:

Lookup Component Enhancements

This section provides information about the enhancements done to the Lookup component. Two new properties, Multiple Match Rows and No-Match Rows are added to the Lookup properties. These properties appear under Match Row Rules section of the Lookup properties page.

Multiple Match Rows

The Lookup Type property has been replaced with Multiple Match Rows.

The Multiple Match Rows property defines which row from the lookup result must be selected as the lookup result if the lookup returns multiple results. Multiple rows are returned when the lookup condition specified matches multiple records.

You can select one of the following options to specify the action to perform when multiple rows are returned by the lookup operation:

  • Error: multiple rows cause mapping to fail

    This option indicates that when the lookup operation returns multiple rows, the mapping execution fails.

  • All Rows (number of result rows may differ from the number of input rows)

    This option indicates that when the lookup operation returns multiple rows, all the rows should be returned as the lookup result.

  • Select any single row

    This option indicates that when the lookup operation returns multiple rows, any one row from the returned rows must be selected as the lookup result.

  • Select first single row

    This option indicates that when the lookup operation returns multiple rows, the first row from the returned rows must be selected as the lookup result.

  • Select nth single row

    This option indicates that when the lookup operation returns multiple rows, the nth row from the result rows must be selected as the lookup result. When you select this option, the Nth Row Number field appears, where you can specify the value of n.

Lookup Attributes Order:

Use the Lookup Attributes Default Value & Order By table to specify how the result set that contains multiple rows should be ordered. Ensure that the attributes are listed in the same order (from top to bottom) in which you want the result set to be ordered. For example, to implement an ordering such as ORDER BY attr2, attr3, and then attr1, the attributes should be listed in the same order. You can use the arrows to change the position of the attributes to specify the order.

No-Match Rows

The No-Match Rows property indicates the action to be performed when there are no rows that satisfy the lookup condition. You can select one of the following options to specify the action to perform when no rows are returned by the lookup operation:

  • Return no row

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

  • Return a row with the following default values

    This option returns a row that contains default values when no row in the lookup results satisfies the lookup condition. Use the Lookup Attributes Default Value & Order By: table below this option to specify the default values for each lookup attribute.

Sequence Enhancements

Sequence in Oracle Data Integrator is enhanced to support the CURRVAL operator. The expression editor now displays the NEXTVAL and CURRVAL operators for each sequence that is listed in the ODI objects panel as shown in Figure C-1.

Figure C-1 Expression editor enhancement: SEQUENCE

Description of Figure C-1 follows
Description of "Figure C-1 Expression editor enhancement: SEQUENCE"