Introduction to TRANSFORMATIONS

                                                           

A transformation is a repository object that generates, modifies, or passes data. You configure logic in a transformation that the Integration Service uses to transform data. The Designer provides a set of transformations that perform specific functions.

For example, an Aggregator transformation performs calculations on groups of data.Transformations in a mapping represent the operations the Integration Service performs on the data. Data passes through transformation ports that we link in a mapping or mapplet.

Types of Transformations:

Active: An active transformation can change the number of rows that pass through it, such as a Filter transformation that removes rows that do not meet the filter condition.

Passive: A passive transformation does not change the number of rows that pass through it, such as an Expression transformation that performs a calculation on data and passes all rows through the transformation.

Connected: A connected transformation is connected to other transformations in the mapping.

Unconnected: An unconnected transformation is not connected to other transformations in the mapping. An unconnected transformation is called within another transformation, and returns a value to that transformation.

Reusable: Reusable transformations can be used in multiple mappings. These are Created in Transformation Developer tool. Or promote a non-reusable transformation from the Mapping Designer.

• we can create most transformations as a non-reusable or reusable.
• External Procedure transformation can be created as a reusable Transformation only.
• Source Qualifier is not reusable.

Non reusable: Non-reusable transformations exist within a single mapping. These are created in Mapping Designer tool.

Single-Group Transformation: Transformations that have one input and one Output group.

Multi-Group Transformations: Transformations that have multiple input Groups, multiple output groups, or both. A group is the representation of a row of Data entering or leaving a transformation. Example: Union, Router, Joiner, HTTP etc.

 Working with Ports

After we create a transformation, we need to add and configure ports using the Ports tab. Ports are equivalent to columns in Informatica.

Creating Ports:

We can create a new port in the following ways:

• Drag a port from another transformation. When we drag a port from another transformation the Designer creates a port with the same properties, and it links the two ports. Click Layout > Copy Columns to enable copying ports.
• Click the Add button on the Ports tab. The Designer creates an empty port you can configure.

Using Default Values for Ports

All transformations use default values that determine how the Integration Service handles input null values and output transformation errors.

•  Input port: The system default value for null input ports is NULL. It displays as a blank in the transformation. If an input value is NULL, the Integration Service leaves it as NULL.
•  Output port: The system default value for output transformation errors is ERROR. The default value appears in the transformation as ERROR(`transformation error'). If a transformation error occurs, the Integration Service skips the row. The Integration Service notes all input rows skipped by the ERROR function in the session log file.
• Input/output port: The system default value for null input is the same as input ports, NULL. The system default value appears as a blank in the transformation. The default value for output transformation errors is the same as output ports.

Note: Variable ports can’t have default values. The Integration Service initializes variable ports according to the datatype.

Note: The Integration Service ignores user-defined default values for unconnected transformations.

User-defined default values

Constant value: Use any constant (numeric or text), including NULL.

Example: 0, 9999, ‘Unknown Value’, NULL

Constant expression: We can include a transformation function with constant parameters. Example: 500 * 1.75, TO_DATE('January 1, 1998, 12:05 AM'), ERROR ('Null not allowed')

ERROR: Generate a transformation error. Write the row and a message in the session log or row error log. The Integration Service writes the row to session log or row error log based on session configuration.

Use the ERROR function as the default value when we do not want null values to pass into a transformation. For example, we might want to skip a row when the input value of DEPT_NAME is NULL. You could use the following expression as the default

value:ERROR('Error. DEPT is NULL')

ABORT: Abort the session. Session aborts when the Integration Service encounters a null input value. The Integration Service does not increase the error count or write rows to the reject file.

Example: ABORT(‘DEPT is NULL')

TRACING LEVELS

When we configure a transformation, we can set the amount of detail the Integration Service writes in the session log.

• We set tracing level in Properties tab of a transformation.

• Change the tracing level to a Verbose setting only when we need to debug a Transformation that is not behaving as expected.
• To add a slight performance boost, we can also set the tracing level to Terse.