A transformation is any part of a mapping that generates or modifies data. Several common transformations are provided in the Transformations toolbar in Informatica’s Designer tool. You can also create your own transformations.
There are two types of transformations:

• Active transformations can change the number of records passed through them. The number of rows providing data does not have to be the same as the number of rows receiving data. For example, the Filter transformation removes rows that do not meet the specified filter condition.

• Passive transformations never change the record count.

Many transformations use functions. Functions generate or modify data. Transformations act on sets of data, but functions operate on individual data values.
The most commonly used transformations are:

• Source Qualifier - This active transformation object is required in every mapping (except for mappings involving COBOL sources, which use the Normalizer transformation instead). The Source Qualifier transformation extracts data from the source. For relational database sources, this transformation produces SQL; select statements are defined using the Mapping Designer. For flat file sources, this transformation extracts all the data from the file. In addition to extracting data from the source, you can use the Source Qualifier transformation in the following ways:

  • Join data from two or more tables

  • Filter records read

  • Perform an outer join (the default is an inner join)

  • Sort records

  • Select only distinct values

  • Create a custom Select query using SQL

• Normalizer - This active transformation object transforms one source record with many values into many records with unique values.

• Expression - This passive transformation object calculates values in a single row before writing to the target. It uses the following transformation functions: character, conversion, date, logical, special, scientific, numeric, and test. For example, you could use this transformation to adjust employee salaries, concatenate first and last names, or convert strings to numbers.

• Filter - This active transformation object filters out unwanted rows, so that only the desired rows are loaded into the target. It uses the following transformation functions: arithmetic, character, date, special, scientific, numeric, and test. Filter transformations use conditions to evaluate data. If the condition is true, the records pass through to the next transformation object. If the condition is false, the records are not passed to the next transformation object.

• Lookup - This passive transformation object finds a matching record from a relational table that is not a source, and returns the result. It issues a SQL statement to retrieve the lookup records. (You can customize the SQL statement.) If no match is found, it returns NULL.

  • Caching the lookup tables puts the entire record set in memory.

  • Not caching the lookup tables issues a SQL Select statement for every record that passes through the object.

• Stored Procedure - This passive transformation object executes database stored procedures and functions. It uses database routines to do complex transformations that the Informatica suite does not support.

• Update Strategy - Use this active transformation object to specify how much data to retain when moving data from source to target. The Update Strategy transformation determines, for each record, whether to insert, update, delete, or reject the record. This transformation uses the following functions: arithmetic, character, date, special, scientific, numeric, and test. Examples:

  • DD_INSERT or 0 to insert the record

  • DD_UPDATE or 1 to update the record

  • DD_DELETE or 2 to delete the record

  • DD_REJECT or 3 to reject the record and write it to the *.bad file