Normalizer Transformation in Informatica with EXAMPLE

What is Normalizer Transformation?

Normalizer is an active transformation, used to convert a single row into multiple rows and vice versa. It is a smart way of representing your data in more organized manner.

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

Student Name Class 9 Score Class 10 Score Class 11 Score Class 12 Score
Student 1 50 60 65 80
Student 2 70 64 83 77

In this case, the class score column is repeating in four columns. Using normalizer, we can split these in the following data set.

Student Name Class Score
Student 1 9 50
Student 1 10 60
Student 1 11 65
Student 1 12 80
Student 2 9 70
Student 2 10 64
Student 2 11 83
Student 2 12 77

Step 1) Create source table “sales_source” and target table “sales_target” using the script and import them in Informatica

Download the above Sales_Source.txt File

Step 2)Create a mapping having source “sales_source” and target table “sales_target”

Normalizer Transformation in Informatica

Step 3) From the transformation menu create a new transformation

  1. Select normalizer as transformation
  2. Enter name, “nrm_sales”
  3. Select create option

Normalizer Transformation in Informatica

Step 4) The transformation will be created, select done option

Normalizer Transformation in Informatica

Step 5) Double click on the normalizer transformation, then

  1. Select normalizer tab
  2. Click on icon to create two columns
  3. Enter column names
  4. Set number of occurrence to 4 for sales and 0 for store name
  5. Select OK button

Normalizer Transformation in Informatica

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

Normalizer Transformation in Informatica

Step 6) Then in the mapping

  1. Link the four column of source qualifier of the four quarter to the normalizer columns respectively.
  2. Link store name column to the normalizer column
  3. Link store_name & sales columns from normalizer to target table
  4. Link GK_sales column from normalizer to target table

Normalizer Transformation in Informatica

Save the mapping and execute it after creating session and workflow. For each quarter sales of a store, a separate row will be created by the normalizer transformation.

The output of our mapping will be like –

Store Name Quarter Sales
DELHI 1 150
DELHI 2 240
DELHI 3 455
DELHI 4 100
MUMBAI 1 100
MUMBAI 2 500
MUMBAI 3 350
MUMBAI 4 340

The source data had repeating columns namely QUARTER1, QUARTER2, QUARTER3, and QUARTER4. With the help of normalizer, we have rearranged the data to fit into a single column of QUARTER and for one source record four records are created in the target.

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