Creating a Source Transformation in a Data Source Object

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:

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.


Top of page

x
Procedure: How to Create a Source Transformation in a Data Source Object
  1. From the data flow workspace, double-click a data source object, or right-click it and select Source Transformations.

    The Source Transformations window displays all of the columns in the data source.

    Source Transformations

    Note: The columns of the synonym are shaded because they cannot be edited here. Only source transformations can be edited in this window.

  2. Click the Insert Transforms button.

    The Transformation Calculator opens.

  3. Type a name and format for the virtual 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 pop-up notification to advise you of the change.

  4. Use the Columns/Variables and Functions tabs, and the calculator buttons, to build the expression for the virtual column. 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.

    The new column appears in the Columns list.

  6. To verify that the transformation is working properly, click the Test Transforms Test Transforms button button.

    The Test Transformations window opens, showing the result of the source transformation.

    Test Transformations window

  7. Click OK to close the Source Transformations window.

Top of page

x
Procedure: How to Create a Transformation That Uses a Variable

From the Source Transformations window:

  1. Click the Insert Transforms Insert Transforms button button.

    The Transformation Calculator opens.

  2. Type a name and type or select a format for the virtual 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 pop-up notification to advise you of the change.

  3. Double-click the variable you want assigned to the column from the Columns/Variables tab. You can also type in the variable, which must be enclosed in single quotation marks (‘), as shown in the following image.

    Transformation calculator

  4. Click OK.

The variable appears in the Expression column in the Source Transformations window, as shown in the following image.

Source Transformations

For more information about using variables, see Using Variables in a Flow.


Top of page

x
Procedure: How to Create a Relational Expression in the Transformation Calculator

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:

  1. Click the Insert Transforms Insert Transforms button button.

    The Transformation Calculator opens.

  2. Click the Relational Expression tab.
  3. Type in a name for the column.

    Note: Since the transformation will always evaluate to either 1 (true) or 0 (false), the format will be automatically set to I1.

  4. Optionally, type in a Title for the column.
  5. Click the Add New Row button above the Expression field.

    A blank line is added to the Expression field, as shown in the following image.

    Column selection

  6. Select a Column from the drop-down menu.
  7. Select a Relation from the drop-down menu.
  8. Select a Type from the drop-down menu.

    The following options are available:

    • Value
    • Field
    • Variable

    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.

  9. Type in a value or click the ellipsis (...) to select an available value.

    The Value(s) Selection dialog box opens, as shown in the following image.

    Value Selection window

  10. Select one (or more) Available Values and move it to the Selected Value(s) pane using the arrow. Click OK.

    The Transformer Calculator should look like the image below.

    Relational Express fields

  11. Click OK to close the Transformation Calculator.

The relational expression appears as a column in the Source Transformations window, as shown in the following image.

Source Transformations Window

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.


Top of page

x
Reference: Source Transformations Window

To access the Source Transformations window from the data flow workspace, double-click a source, or right-click a source and select Source Transformations.

Source Transformations window

The Source Transformations window has the following fields and options:

Source Columns

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

Name

Is the name of the column, and also shows a symbol that indicates regular columns, key columns, index columns, and virtual columns.

Format

Is the format of the column.

Expression

Is the expression for the column.

Description

Is the description of the column.

Find button Find

Allows you to search for a column.

Refresh

Refreshes the columns in the data source.

Sample data

Shows a sample of the data from the data source.

Insert Transforms button Insert transforms

Opens the Transformation Calculator to create a transformation.

Delete button Delete transforms

Deletes the selected transformation.

Edit Transforms button Edit transforms

Opens the Transformation Calculator to edit a transformation.

Test Transforms button Test transforms

Tests the transformations.

Up and down arrows

Moves the selected transformation up or down within the Source Transformations list.



x
Reference: Column Header Context Menu

To access the column header context menu, right-click any column header.

The column header context menu has the following options:

Find

Allows you to search for a column

Best Fit

Resizes the column to fit its data.

Customize

Allows you to select what information is displayed for each column.

Paste Transformations

Pastes transformations from memory into the transformation list.


Top of page

x
Reference: Source Transformations Context Menu

To access the source transformations context menu, right-click any column of a transformation.

Context menu

The transformations context menu has the following options:

Edit

Opens the transformation calculator for the selected transformation. This is the default action.

Delete

Deletes the transformation.

Test Transform(s)

Tests all of the transformations. This can also be done by clicking the Test Transforms button.

Error Details

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.

Copy Transforms

Copies the selected transformations to memory, so that they can be pasted into this, or another, flow.

Paste Transforms

Pastes transformations from memory into the transformation list.

Note: Copying and pasting transforms does not use the Windows clipboard.


Top of page

x
Reference: Transformation Calculator

To access the Transformation Calculator, click the Insert button in the Transformations window.

Transformation calculator

The Transformation Calculator has the following fields and options:

Name

Is the name of the column.

Format

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.

Title

Is the title of a new target table created by a data flow. The title will be included in the synonym that is created.

Missing

Indicates if missing (null) values are allowed in this field. The following options are available:

  • OFF. The result is never missing.
  • ON. The result is missing only if all missable fields are missing.
  • ON ALL. The result is missing if any missable fields are missing.

Expression tab

Displays the expression window.

Relational Expression tab

Displays the Relational Expression Builder window.

Columns/Variables tab

Displays available columns and variables in a hierarchical tree or in a grid.

Functions tab

Displays a list of functions that are available for your transformations.

Function Assist button

Allows you to specify parameters for the function through a dialog box when creating or editing a transformation.

SQL Optimization Button SQL Optimization button

Opens the Filter SQL Optimization Report, which allows you to specify settings for, and run, the report.

Check Expression button

Verifies the validity of the expression.

Sample Data button

Produces sample data for the expression.

Find button Find button

Allows you to search for a function or variable by name.

Calculator buttons

Insert numbers and operators.

The following operators are available:

| (single concatenation bar)

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
.
.
.
|| (double concatenation bar)

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.

IF

Establishes a conditional test.

THEN

Specifies the action to perform if the result of a conditional test is TRUE.

ELSE

Specifies the action to perform if the result of a conditional test is FALSE.

LT

Returns the value TRUE if the value on the left is less than the value on the right.

NOT

Returns the value TRUE if the operand is false.

LE

Returns the value TRUE if the value on the left is less than, or equal to the value on the right.

EQ

Returns the value TRUE if the value on the left is equal to the value on the right.

AND

Returns the value TRUE if both operands are true.

GT

Returns the value TRUE if the value on the left is greater than the value on the right.

GE

Returns the value TRUE if the value on the left is greater than, or equal to the value on the right.

NE

Returns the value TRUE if the value on the left is not equal to the value on the right.

OR

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.

a->A

Converts selected text to uppercase.

A->a

Converts selected text to lowercase.

Date

Opens the Date Editor window, which lets you use the current date or specify a date from the calendar.

Datetime

Opens the Date Editor window, which lets you use the current date and time or specify a date and time from the calendar.


Top of page

x
Order of Evaluation

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:


Top of page

x
Changing the Order of Evaluation

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