Informatica - Normalizer Transformation

Normalizer Transformation

The Normalizer transformation receives a row that contains multiple-occurring columns and returns a row for each instance of the multiple-occurring data. The transformation processes multiple-occurring columns or multiple-occurring groups of columns in each source row.

The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources.

For example, you might have a relational table that stores four quarters of sales by store. You need to create a row for each sales occurrence. You can configure a Normalizer transformation to return a separate row for each quarter.


The following source rows contain four quarters of sales by store:
Store1 100 300 500 700
Store2 250 450 650 850

Output

Store1 100 1
Store1 300 2
Store1 500 3
Store1 700 4
Store2 250 1
Store2 450 2
Store2 650 3
Store2 850 4


The Normalizer transformation generates a key for each source row.

Types

VSAM Normalizer transformation

A non-reusable transformation that is a Source Qualifier transformation for a COBOL source. The Mapping Designer creates VSAM Normalizer columns from a COBOL source in a mapping. The column attributes are read-only. The VSAM Normalizer receives a multiple-occurring source column through one input port.

Pipeline Normalizer transformation

A transformation that processes multiple-occurring data from relational tables or flat files. You might choose this option when you want to process multiple-occurring data from another transformation in the mapping.



A VSAM Normalizer transformation has one input port for a multiple-occurring column. A pipeline Normalizer transformation has multiple input ports for a multiple-occurring column.
When you create a Normalizer transformation in the Transformation Developer, you create a pipeline Normalizer transformation by default. When you create a pipeline Normalizer transformation, you define the columns based on the data the transformation receives from another type of transformation such as a Source Qualifier transformation.

The Normalizer transformation has one output port for each single-occurring input port.

Generated Column ID (GCID)

The Normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The generated column ID is an index for the instance of the multiple-occurring data. For example, if a column occurs four times in a source record, the Normalizer returns a value of 1, 2, 3, or 4 in the generated column ID based on which instance of the multiple-occurring data occurs in the row.

The Normalizer transformation has at least one generated key column in the output row.

At the end of each session, the Integration Service updates the generated key value in the Normalizer transformation to the last value generated for the session plus one.

-->
You can change the ports on a pipeline Normalizer transformation by editing the columns on the Normalizer tab for relational. To change a VSAM Normalizer transformation, you need to change the COBOL source and recreate the transformation.


Properties

Reset

At the end of a session, resets the value sequence for each generated key value to the value it was before the session.

Restart

Starts the generated key sequence at 1. Each time you run a session, the key sequence value starts at 1 and overrides the sequence value on the Ports tab.


Normalizer tab

The Normalizer tab defines the structure of the source data.

Level: Group columns. Columns in the same group occur beneath a column with a lower level number. When each column is the same level, the transformation contains no column groups.

Occurs: The number of instances of a column or group of columns in the source row. When you create a pipeline Normalizer transformation, you can edit the columns. When you create a VSAM Normalizer transformation, the Normalizer tab is read-only.



VSAM Normalizer Transformation


The VSAM Normalizer transformation is the source qualifier for a COBOL source definition. A COBOL source is a flat file that can contain multiple-occurring data and multiple types of records in the same file.

VSAM (Virtual Storage Access Method) is a file access method for an IBM mainframe operating system. VSAM files organize records in indexed or sequential flat files.

A COBOL source definition can have an OCCURS statement that defines a multiple-occurring column. The COBOL source definition can also contain a REDEFINES statement to define more than one type of record in the file.


Normalizer Tab for a VSAM Normalizer Transformation

POffs: Physical offset. Location of the field in the file. The first byte in the file is zero.
Plen: Physical length. Number of bytes in the field.

Level: Provides column group hierarchy. The higher the level number, the lower the data is in the hierarchy. Columns in the same group occur beneath a column with a lower level number.

Picture: How the data is stored or displayed in the source. Picture 99V99 defines a numeric field with two implied decimals. Picture X(10) indicates ten characters.

Key Type: Type of key constraint to apply to this field. When you configure a field as a primary key, the Integration Service generates unique numeric IDs for this field when running a session with a COBOL source.

Redefines: Indicates that the column REDEFINES another column.