90

Normalizer Transformation

The Normalizer is an active transformation. It is used to convert a single row into multiple rows. When the Normalizer transformation receives a row that contains multiple-occurring data, it returns a row for each instance of the multiple-occurring data.

If in a single row, there is repeating data in multiple columns, then it can be split into multiple rows. Sometimes we have data in multiple occurring columns.

For example, a relational source includes four fields with flat sales data. We can configure a Normalizer transformation to generate a separate output row for each flat.

When the Normalizer returns multiple rows from an incoming row, it returns duplicate data for single-occurring incoming columns.

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.

Here are the following properties of Normalizer transformation in the Properties panel, such as:

• Normalized Fields Tab: Define the multiple-occurring fields and specify additional fields that you want to use in the mapping.
• Field Mapping Tab: Connect the incoming fields to the normalized fields.

We need the appropriate license to use the Normalizer transformation.

The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources. It can process multiple record types from a COBOL source that contains a REDEFINES clause.

Normalizer Transformation Types

Here are the two types of Normalizer transformation, such as:

• 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.
  We create the columns manually and edit them in the Transformation Developer or Mapping Designer. The pipeline Normalizer transformation represents multiple-occurring columns with one input port for each source column occurrence.

Example

We create the following table that represents the student marks records of different classes, such as:

Step 1: Create the source table “stud_source” and target table “stud_target” using the script and import them in Informatica.

Student Name	Class 7	Class 8	Class 9	Class 10
Joy	60	65	75	80
Edward	65	70	80	90

Step 2: Create a mapping having source stud_source and target table stud_target.

Step 3: From the transformation menu create a new transformation

1. Select normalizer as transformation.
2. Enter the name nrm_stud.
3. And click on the Create

Step 4: The transformation will be created, then click on the Done button.

Step 5: Double click on the normalizer transformation, then

1. Select the normalizer tab.
2. Click on icon to create two columns.
3. Enter column names.
4. Set number of occurrences to 4 for marks and 0 for student name.
5. Click on the OK

Columns will be generated in the transformation. We will see 4 number of marks column as we set the number of occurrences to 4.

Step 6: Then in the mapping

1. Link the four-column of source qualifier of the four class to the normalizer columns, respectively.
2. Link the student name column to the normalizer column.
3. Link student_name & marks columns from normalizer to the target table.

Save the mapping and execute it after creating session and workflow. The class score column is repeating in four columns. For each class score of the student, a separate row will be created by using the Normalizer transformation.

The output of the above mapping will look like the following:

Student Name	Class	Score
Joy	7	60
Joy	8	65
Joy	9	75
Joy	10	80
Edward	7	65
Edward	8	70
Edward	9	80
Edward	10	90

The source data had repeating columns, namely class7, class 8, class 9, and class 10. We have rearranged the data to fit into a single column of class, and for one source record, four records are created in the target by using Normalizer.

In this way, we can normalize data and create multiple records for a single source of data.

---