DataMigrator/Reporting Server > Designing a Data Flow > Creating a Source Transformation in a Data Source Object

Creating a Source Transformation in a Data Source Object

Topics:

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 you are adding a source transformation to a hierarchical file (for example, FOCUS/FDS), the WITH attribute may be required. The WITH attribute may also be required if you add a source transformation that references a constant.
  • Use of some source transformations disables Automatic Passthru. For more information, see Using Automatic Passthru.
  • You can create a virtual column that appears whenever a data source is used by adding it to the data source itself via the navigation pane. For more information, see Adding Virtual Columns (DEFINE) in a Synonym.
  • When re-using a synonym in a data flow, such as for a recursive join, the transformations must be the same in all instances of the synonym in the flow.

    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.

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 click Source Transformations.

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

    Source Transformations dialog box

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

  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 button Test Transforms button.

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

  7. Click OK to close the Source Transformations dialog box.

Procedure: How to Create a Transformation That Uses a Variable

From the Source Transformations dialog box:

  1. Click the Insert Transforms 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 dialog box, as shown in the following image.

Source Transformations dialog box

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

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 dialog box:

  1. Click the Insert Transforms 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.

  6. Click the drop-down menu in the Field column, as shown in the following image.
    Transformations Calculator dialog box
  7. Select a Column from the drop-down menu.
  8. Select a Relation from the drop-down menu.
  9. 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.

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

    The Value(s) Selection dialog box opens.

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

    The Transformation Calculator should look like the image below.

    Transformation Calculator valie
  12. Click OK to close the Transformation Calculator.

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

Source transformations dialog box

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.

Reference: Source Transformations Dialog Box

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

Source Transformations dialog box

The Source Transformations dialog box 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

Column button

Regular column

Index 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
Display Name (Title)

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.

Reference: Column Header Shortcut Menu

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

The column header shortcut 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 Transforms

Pastes transformations from memory into the transformation list.

Reference: Source Transformations Shortcut Menu

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

The source transformations shortcut menu has the following options:

Edit Transformation (Advanced)

Opens the Transformation Calculator for the selected transformation. This is the default action.

Insert Transformation

Displays a shortcut menu with a list of available functions that can be added as a transformation. Selecting one of the functions opens the Function Assist dialog box, which allows you to specify parameters for that function.

Delete

Deletes the transformation.

Test Transformations

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 dialog box with the error code and a short explanation of the error.

Copy Transformations

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

Paste Transformations

Pastes transformations from memory into the transformation list.

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

Reference: Transformation Calculator

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

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 dialog box.

Relational Expression tab

Displays the Relational Expression Builder dialog box.

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.

SQL Optimization Button SQL Optimization button

Opens the SQL APT Status 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.

Function Assist button

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

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 dialog box, which lets you use the current date or specify a date from the calendar.

Datetime

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

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:

  • Operations enclosed in parentheses
  • Exponentiation
  • Multiplication and division
  • Addition and subtraction
  • EQ, NE, LE, LT, GE, GT, NOT
  • AND
  • OR

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

 WebFOCUS

Feedback