After selecting a data source and target, you can create mappings to transform your data in the following ways:
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:
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.
While you can map to an existing flat file, there are some considerations that should be taken into account.
The Transformations window opens, as shown in the following image.
The mapping will appear in the grid under the Expressions tab.
The mapping will appear in the grid under the Expressions tab, with the Selected Column appearing in the Transform Expression box.
The Transformation Calculator opens.
Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation
Note: Clicking a column heading will sort the column grid on that heading.
The new mapping will appear in the grid under the Expressions tab.
Note: The transformations will occur in the order in which they appear in the Expressions tab. If you need to change the order, select a transformation and use the up and down arrows.
The Transformations window opens.
The Transformation Calculator opens.
Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation.
The new mapping will appear in the grid under the Expressions tab.
The new mapping is now available as a column in the Transformation Calculator, and can be used to create a complex expression.
Tip: Since calculations are performed in the order they appear, make sure that your temporary column appears before any column in the Transform Rules box that uses it to calculate a value.
The DB_LOOKUP function allows you to build data flows that perform lookups into multiple tables using target transformations instead of explicit joins. This approach can simplify flows that require lookup values from numerous tables. For information about using the DB_LOOKUP function to create source transformations, see Using Lookups to Create Multi-Table Flows Without Joins.
The Transformations window opens.
The Transformation Calculator opens.
The prototype for DB_LOOKUP appears in the Expression window and the DB_LOOKUP dialog box opens.
The Select Synonym dialog box opens.
Note: If you have Automatically add join conditions checked in the Data Management section of the Options dialog box, 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.
When you have selected the columns, the DB_LOOKUP dialog box will look like this:
The expression is completed in the Transformation Calculator window.
The Transformation Calculator should now look like this.
The target transformation is added to the Transformations window.
From the Transformations window:
The Transformation Calculator opens.
The variable appears in the Expression column in the Transformations window.
For more information about using variables, see Using Variables in a Flow.
From the Transformations window:
or
Select the column name in the Expressions tab and click the Edit transforms button.
The Transformation Calculator opens.
Note: You can use the Function Assist button to change the parameters of a transformation that uses the DB_LOOKUP function.
You can also double-click the Expression field in the Expressions tab, and enter the expression directly.
If the format of the target column is smaller than the format of the source column, the transformation mapping will fail. For example, moving a column formatted as A24 into a column formatted as A10 will fail because you cannot move 24 characters into a 10-character field. To solve this problem, you would need to use edit or substring in a transform expression.
The Transformations window provides an option for automatically adjusting the transformation to reconcile different format sizes. Adjust will also convert integer, alphanumeric, and datetime source columns so that they match the target column.
In the Transformations window:
The mapping appears in the Expressions list.
The Transform Expression now shows the use of substring to move the correct number of characters into the target field. For example, only as many characters as will fit in the target column will be moved.
The Transformations window opens and now shows a Description column. Here you can enter a comment for the transformation that is stored in the flow, as shown in the following image.
From the Transformations window:
The Test Transformation window opens with sample data for the transformations.
To access the Transformations dialog box from the data flow workspace, double-click a target, or right-click a target and select Target Transformations.
The Transformations window contains the following fields and options:
Allows you to search for a column.
Refreshes the columns in the target.
Maps all columns with identical names.
Lists the columns in the data target.
Note: Clicking a column heading will sort the column grid on that heading.
Lists the columns in the data source. Next to each column name, there is a symbol that indicates regular columns, key columns, index columns, and virtual columns.
In both Target and Selected columns, the symbols are:
Symbol |
Description |
---|---|
|
Key column |
|
Regular column |
|
Index column |
|
Virtual columns (in Target columns only). These are grayed and cannot be selected for mapping. |
Establishes the relationship between the selected columns as equal.
Inserts a new mapping and opens the Transformation Calculator.
Deletes a selected mapping.
Opens the Transformation Calculator to edit the selected mapping.
Adjusts the transformation expression to reconcile different format sizes.
Lists the transformation mappings. To access the context menu with available options for the transformations, right-click a transformation in this tab. For more information, see Source Transformations Context Menu.
Lists transformations that screen records as they are loaded into the data target. If a record does not meet the criteria, it is rejected.
Retrieves, by default, 50 records and 4096 characters per row from the source, and applies all available transformations. This option enables you to review the data being moved and to ensure that the syntax of each transformation is correct. No duplicate processing is performed. For example, if a key value is duplicated, a unique constraint violation may occur. Similarly, if a value is missing for a column described as not null, a constraint violation will occur at run time.
Note: You can set the number of rows retrieved from the Options dialog box under the Run Options branch of the User Preferences tree. The default is 50 rows.
If there are any errors, the Server Messages window displays an error message and marks the incorrect transformation in red.
Move the selected mapping to change the order of the transformations.
Error details for transformations can be viewed in the DMC. If an error is made, the transform will appear in red, as shown in the following image.
To see the error message details, click the error message number. A window opens explaining the error, as seen in the following image.
Note: This method of viewing error details also applies to target transformations.
How to: |
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.
The Transformation Calculator opens with a name and format already provided.
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 DataMigrator log as invalid records. These records can also be logged to a file for review. For more information, see Transformation Calculator.
The validation rule appears in the grid below the Validates tab.
iWay Software |