In this section: How to:
Reference: |
You can create a source transformation in a data source to add intermediate calculations to the request. Source transformations are performed on a data source object as the records are read, before any filtering or aggregation is performed. This is often useful for complex calculations that are composed of multiple expressions. Creating a source transformation is also helpful in performing data type conversions when multiple steps are required, and if you want to aggregate or filter a calculated column.
Source transformations are only performed for a particular data flow.
A source transformation can contain an expression or a constant.
Note:
If different transformations are required in each instance, you should create multiple synonyms with different synonym names for these sources and use them in the data flow.
In addition to source transformations, you can use the Relational Expression tab of the Transformation Calculator to create a relational expression transformation that can be used in a filter.
The Source Transformations window displays all of the columns in the data source.
Note: The columns of the synonym are shaded because they cannot be edited here. Only source transformations can be edited in this window.
The Transformation Calculator opens.
Note: If you do not supply a format, the DMC will automatically provide the format based on the expression. Similarly, if you supply the wrong format, the DMC will replace it with the correct format using a pop-up notification to advise you of the change.
Note: When you create expressions, keep in mind their order of evaluation. For details, see Order of Evaluation.
The new column appears in the Columns list.
The Test Transformations window opens, showing the result of the source transformation.
From the Source Transformations window:
The Transformation Calculator opens.
Note: If you do not supply a format, the DMC will automatically provide the format based on the expression. Similarly, if you supply the wrong format, the DMC will replace it with the correct format using a pop-up notification to advise you of the change.
The variable appears in the Expression column in the Source Transformations window, as shown in the following image.
For more information about using variables, see Using Variables in a Flow.
The Relational Expression tab of the Transformation Calculator allows you to build an expression that can be used as a filter.
From the Source Transformations window:
The Transformation Calculator opens.
Note: Since the transformation will always evaluate to either 1 (true) or 0 (false), the format will be automatically set to I1.
A blank line is added to the Expression field, as shown in the following image.
The following options are available:
Note: This field is not available if the Relation is set to IS MISSING or IS-NOT MISSING. If the Relation is set to DB_FILE or NOT DB_FILE, Type must be a value.
The Value(s) Selection dialog box opens, as shown in the following image.
The Transformer Calculator should look like the image below.
The relational expression appears as a column in the Source Transformations window, as shown in the following image.
Note: Using a source transformation column in a filter or as a selected column may disable automatic passthru, so it should be used with care.
To access the Source Transformations window from the data flow workspace, double-click a source, or right-click a source and select Source Transformations.
The Source Transformations window has the following fields and options:
Lists the columns in the data source. The first section contains the column order.
The columns symbols are:
Symbol |
Description |
---|---|
|
Key column |
|
Regular column |
|
Index column |
|
Virtual column Note: The symbol for columns in the synonym is gray, while the background for a source transformation (virtual column in the flow) is white. |
|
Source Transformation |
Is the name of the column, and also shows a symbol that indicates regular columns, key columns, index columns, and virtual columns.
Is the format of the column.
Is the expression for the column.
Is the description of the column.
Allows you to search for a column.
Refreshes the columns in the data source.
Shows a sample of the data from the data source.
Opens the Transformation Calculator to create a transformation.
Deletes the selected transformation.
Opens the Transformation Calculator to edit a transformation.
Tests the transformations.
Moves the selected transformation up or down within the Source Transformations list.
To access the column header context menu, right-click any column header.
The column header context menu has the following options:
Allows you to search for a column
Resizes the column to fit its data.
Allows you to select what information is displayed for each column.
Pastes transformations from memory into the transformation list.
To access the source transformations context menu, right-click any column of a transformation.
The transformations context menu has the following options:
Opens the transformation calculator for the selected transformation. This is the default action.
Deletes the transformation.
Tests all of the transformations. This can also be done by clicking the Test Transforms button.
This option is only available if the selected transformation contains an error, which is displayed in red text. Selecting this option opens a window with the error code and a short explanation of the error.
Copies the selected transformations to memory, so that they can be pasted into this, or another, flow.
Pastes transformations from memory into the transformation list.
Note: Copying and pasting transforms does not use the Windows clipboard.
To access the Transformation Calculator, click the Insert button in the Transformations window.
The Transformation Calculator has the following fields and options:
Is the name of the column.
Is the format of the column.
Note: If you do not supply a format, the DMC will automatically provide the format based on the expression. Similarly, if you supply the wrong format, the DMC will replace it with the correct format using a notification to advise you of the change.
Is the title of a new target table created by a data flow. The title will be included in the synonym that is created.
Indicates if missing (null) values are allowed in this field. The following options are available:
Displays the expression window.
Displays the Relational Expression Builder window.
Displays available columns and variables in a hierarchical tree or in a grid.
Displays a list of functions that are available for your transformations.
Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.
Opens the Filter SQL Optimization Report, which allows you to specify settings for, and run, the report.
Verifies the validity of the expression.
Produces sample data for the expression.
Allows you to search for a function or variable by name.
Insert numbers and operators.
The following operators are available:
Concatenates two values, retaining any trailing blanks after the first one. For example, if FIRST_NAME and LAST_NAME were both in A15 format, the expression
FULL_NAME = FIRST_NAME | LAST_NAME
would produce a column like the following:
MICHAEL SMITHSON JANE JONES . . .
Concatenates two values, suppressing any trailing blanks in the first. For example, to construct the full name and insert a comma, the syntax
FULL_NAME = LAST_NAME || (', ' | FIRST_NAME)
would produce a column like the following:
SMITHSON, MICHAEL JONES, JANE . . .
The concatenation in the parentheses is done first (preserving the blank space after the comma), and the result is then concatenated to LAST_NAME, suppressing the trailing blanks of LAST_NAME.
Establishes a conditional test.
Specifies the action to perform if the result of a conditional test is TRUE.
Specifies the action to perform if the result of a conditional test is FALSE.
Returns the value TRUE if the value on the left is less than the value on the right.
Returns the value TRUE if the operand is false.
Returns the value TRUE if the value on the left is less than, or equal to the value on the right.
Returns the value TRUE if the value on the left is equal to the value on the right.
Returns the value TRUE if both operands are true.
Returns the value TRUE if the value on the left is greater than the value on the right.
Returns the value TRUE if the value on the left is greater than, or equal to the value on the right.
Returns the value TRUE if the value on the left is not equal to the value on the right.
Returns the value TRUE if either operand is true.
Raises a value to the specified power.
Adds parentheses.
Inserts two single quotation marks. Enter alphanumeric test values between these.
Converts selected text to uppercase.
Converts selected text to lowercase.
Opens the Date Editor window, which lets you use the current date or specify a date from the calendar.
Opens the Date Editor window, which lets you use the current date and time or specify a date and time from the calendar.
The order of evaluation can affect the result of an expression in a transformation. DataMigrator evaluates numeric and Boolean operators from left to right in the following order:
The following expression gives an incorrect result because UNIT_SOLD is first multiplied by UNIT_COST, and then the result is subtracted from RETAIL_PRICE:
RETAIL_PRICE - UNIT_COST * UNIT_SOLD
You can change the order of evaluation and improve readability by enclosing expressions in parentheses. An expression in parentheses is evaluated first. The correct expression for the preceding example is:
(RETAIL_PRICE - UNIT_COST) * UNIT_SOLD
iWay Software |