Transformations

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

Creating an Expression Transformation

For data mappings that use ETL_CUSTOMER_MASTER, ETL_CUST_SUBSCRIPTION, or ETL_CUST_ATTRIBUTES as the target table, you need to create an Expression Transformation with output columns that generate Connect-specific data values that are not likely to be stored in the source tables or flat files. Within the Expression Transformation table, create the following hard coded expressions and output ports:

Port Name	Datatype	Prec	Scale	O	Expression
PROCESS_TYPE	String	10	0	X	'Customer'
COMPANY_ID	Integer	10	0	X	100

You can map these ports directly into the corresponding columns in the target tables. However, if a Normalizer is used in the mapping, the ports must be first mapped into the Normalizer transformation. From the output ports in the Normalizer transformation the data can be mapped to the target.

Creating a Normalizer Transformation

For mappings that require a single source record with many values to be transformed into many records with unique values (that is, a single mapping with multiple attributes to the ETL_CUST_ATTRIBUTES target or a single mapping with multiple Conversations to the ETL_CUST_SUBSCRIPTION target), a Normalizer transformation is required.

If a Normalizer is used in the mapping, all data ports must pass through the Normalizer before data is written to the target.

While all data fields passed from the source are specified in this transformation before populating the target, you need to specify the number of times the Normalized data columns will occur in the mapping.

In the example below, the Normalizer is created to accommodate seven attributes in a single mapping. The mapping writes data to the ETL_CUST_ATTRIBUTES table.

Column Name	Level	Occurs	Datatype	Prec	Scale
ATTRIBUTE_NAME	0	7	String	40	0
ATTRIBUTE_VALUE	0	7	String	80	0

See Informatica’s User Guide and Transformation Language Reference for more information on transformations.