Using Informatica Normalizer Transformation

Normalizer, a native transformation in Informatica, can ease many complex data transformation requirement. Learn how to effectively use normalizer here.

Using Noramalizer Transformation

A Normalizer is an Active transformation that returns multiple rows from a source row, it returns duplicate data for single-occurring source columns. The Normalizer transformation parses multiple-occurring columns from COBOL sources, relational tables, or other sources. Normalizer can be used to transpose the data in columns to rows.

Normalizer effectively does the opposite of Aggregator!

Example of Data Transpose using Normalizer

Think of a relational table that stores four quarters of sales by store and we need to create a row for each sales occurrence. We can configure a Normalizer transformation to return a separate row for each quarter like below..

The following source rows contain four quarters of sales by store:
Source Table

Store	Quarter1	Quarter2	Quarter3	Quarter4
Store1	100	300	500	700
Store2	250	450	650	850

The Normalizer returns a row for each store and sales combination. It also returns an index(GCID) that identifies the quarter number:

Target Table

Store	Sales	Quarter
Store 1	100	1
Store 1	300	2
Store 1	500	3
Store 1	700	4
Store 2	250	1
Store 2	450	2
Store 2	650	3
Store 2	850	4

How Informatica Normalizer Works

Suppose we have the following data in source:

Name	Month	Transportation	House Rent	Food
Sam	Jan	200	1500	500
John	Jan	300	1200	300
Tom	Jan	300	1350	350
Sam	Feb	300	1550	450
John	Feb	350	1200	290
Tom	Feb	350	1400	350

and we need to transform the source data and populate this as below in the target table:

Name	Month	Expense Type	Expense
Sam	Jan	Transport	200
Sam	Jan	House rent	1500
Sam	Jan	Food	500
John	Jan	Transport	300
John	Jan	House rent	1200
John	Jan	Food	300
Tom	Jan	Transport	300
Tom	Jan	House rent	1350
Tom	Jan	Food	350

.. like this.

Now below is the screen-shot of a complete mapping which shows how to achieve this result using Informatica PowerCenter Designer

Setting Up Normalizer Transformation Property

First we need to set the number of occurences property of the Expense head as 3 in the Normalizer tab of the Normalizer transformation, since we have Food,Houserent and Transportation.
Which in turn will create the corresponding 3 input ports in the ports tab along with the fields Individual and Month

In the Ports tab of the Normalizer the ports will be created automatically as configured in the Normalizer tab. Interestingly we will observe two new columns namely GK_EXPENSEHEAD andGCID_EXPENSEHEAD.
GK field generates sequence number starting from the value as defined in Sequence field while GCID holds the value of the occurence field i.e. the column no of the input Expense head.
Here 1 is for FOOD, 2 is for HOUSERENT and 3 is for TRANSPORTATION.

Now the GCID will give which expense corresponds to which field while converting columns to rows.
Below is the screen-shot of the expression to handle this GCID efficiently:

image:Expression to Handle GCID