Mapping Transformations

After selecting a data source and target, you can create mappings to transform your data in the following ways:

• Map the columns of the source, or combinations of columns in the source, to columns in the data target.
• Convert the extract data into the form necessary for the data target.
• Create a temporary column for mapping.
  Note: You can create a temporary column within the mapping step to add intermediate calculations that are not part of the original SQL command or target data. This is often useful for complex calculations that are actually composed of multiple expressions. Creating a temporary column makes the transformation easier to understand and support. It is also helpful in performing data type conversions when multiple steps are required.

There are three methods for mapping columns:

• Automapping. Automatically maps columns with the same name.
• Simple mapping. Maps one column in the source to one in the target.
• Complex mapping. Builds expressions.
  Note: The target column format should be at least as large as the source column format, but you can adjust the transformation to accommodate different format sizes.

Considerations for Mapping to an Existing Fixed Format File

Automatically Map All Columns

Procedure
1. On the Home tab, in the Tools group, click Options.
2. Expand Data Flow and select Data Flow Designer from the User Preferences tree.
3. Select the Automatically map all columns checkbox in the On Add Target section.
4. Click OK.

Map Transformations

Procedure
1. From the data flow workspace, double-click a target, or right-click it and click Target Transformations.

   The Transformations dialog opens, as shown in the following image.

   

2. To map columns with identical names, click the AutoMap button .

   The mapping will appear in the grid under the Expressions tab.

3. To map columns with different names, select one column from each grid and click the equal sign.

   The mapping will appear in the grid under the Expressions tab, with the Selected Column appearing in the Transform Expression box.

4. To create a complex mapping for a target column, double-click a column to move it to the Expressions tab.
5. Click the Edit Transforms button.

   The Transformation Calculator opens.

6. Create an expression using the Columns/Variables and Functions tabs, and the calculator buttons. For details on using the Transformation Calculator, see Transformation Calculator. For information about using functions, see Using Functions. For information about using variables, see Using Variables in a Flow.
   Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation.
7. Click OK.

The new mapping will appear in the grid under the Expressions tab.

Create a Temporary Column for Mapping

Procedure
1. In the data flow workspace, double-click a target, or right-click it and click Target Transformations.

   The Transformations dialog opens.

2. Click the Insert Intermediate Transforms button.

   The Transformation Calculator opens.

3. Type a name and type or select a format for the column.
4. Create an expression using the Columns/Variables and Functions tabs and the calculator buttons. For details on using the Transformation Calculator, see Transformation Calculator. For information about using functions, see Using Functions. For information about using variables, see Using Variables in a Flow.
   Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation.
5. Click OK.

Create a Target Transformation Using Relational Lookups

Procedure
1. From the data flow workspace, double-click a target object, or right-click it and click Target Transformations.

   The Transformations dialog opens.

2. Double-click the column that will use the lookup. The column is moved to the grid under the Expressions tab.
3. With the column selected in the Expressions grid, click the Edit Transforms button.

   The Transformation Calculator opens.

4. Click the Functions tab, expand the Data Source and Decoding - Simplified folder, and double-click the DB_LOOKUP function.

   The prototype for DB_LOOKUP appears in the Expression window and the DB_LOOKUP dialog opens.

5. Select a synonym to use for the lookup by clicking the ellipsis (...) after the Lookup Synonym field.

   The Select Synonym dialog opens.

6. Select a synonym and click Select.
7. From the Lookup dialog, click the Add/Delete button.
8. A lookup is similar to a join in that it uses columns from two different tables. Select a column from the Source and Lookup Fields lists, and click the equal sign (=).
   Note: If you have Automatically add join conditions checked in the Data Management section of the Options dialog, the Lookup is added automatically, since both data sources have a column with the same name and format, and that column is a key column in the lookup table.
9. Select the value to return from the lookup from the Return Field dropdown menu.
10. Select the original column name and click OK. Note that in this case, the format of the column is A20.
11. Click OK.

    The expression is completed in the Transformation Calculator dialog.

    The Transformation Calculator should now look like this.

    

12. Click OK.

    The target transformation is added to the Transformations dialog.

13. Click OK to close the Transformations dialog.

Edit a Transformation Mapping

Procedure
1. Double-click the column number or name in the Expressions tab.

   or

   Select the column name in the Expressions tab and click the Edit Transforms button.

   The Transformation Calculator opens.

2. Modify the expression.
   Note: You can use the Function Assist button to change the parameters of a transformation that uses the DB_LOOKUP function.
3. When you are finished with your changes click OK.

Adjust a Transformation Mapping

Procedure
1. Map the source and target columns by selecting one from each list and clicking the equal sign.

   The mapping appears in the Expressions list.

2. With the expression highlighted, click the Adjust Expression button .

Add Comments to a Transformation

Procedure
1. Right-click the title bar under the Expression tab in the Transformations dialog.
2. Point to Customize from the shortcut menu and then click Description.

Test Mappings

Procedure
1. Click the Test Transforms button.

   The Test Transformations dialog opens with sample data for the transformations.

2. Click the Close button.

Transformations dialog

View Error Details

Procedure
1. Right-click the error and select Error Details.
2. The Error(s) Details dialog opens.

   To see the error message details, click the error message number. A window opens explaining the error, as seen in the following image.

   

3. Click OK.
   Note: This method of viewing error details also applies to target transformations.

Validating Records

You can create a business rule that screens records as they are loaded into the data target. If a record does not meet the criteria, it is rejected.

If a value meets the criteria of the rule, a value of 1 is assigned to the record and the record is loaded. If the value does not meet the criteria of the record, the value of 0 is assigned to the record and the record is rejected.

Validate Records

Procedure
1. In the Transformations dialog, select the Validates tab.
2. Click the Insert Intermediate Transforms button .

   The Transformation Calculator opens with a name and format already provided.

3. Optionally, enter a name for the validation rule in the Column field.
4. Create an expression for the validation rule using the Columns, Functions, and Variables tabs, and the calculator buttons.

   If the expression evaluates to TRUE then the row is loaded in the target table. If the expression evaluates to FALSE then the row is rejected as invalid.

   Note that the validation is performed after any filters or aggregation. For example, if you enter QUANTITY GT 10, only records where the sum of the values of QUANTITY for the group by fields is greater than 10 will be loaded.

   The count of rejected rows is reported in the ibi Data Migrator log as invalid records. These records can also be logged to a file for review. For more information, see Transformation Calculator.

5. Click OK.