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”

Step 3) From the transformation menu create a new transformation

Select normalizer as transformation
Enter name, “nrm_sales”
Select create option

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

Step 5) Double click on the normalizer transformation, then

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

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

Step 6) Then in the mapping

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

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.

You Might Like: