HOW TO De-normalize data/pivot rows into columns
 

Informatica Tips

HOW TO Use a Joiner transformation instead of Lookup Transformation

HOW TO De-normalize data/pivot rows into columns

HOW TO De-normalize data/pivot rows into columns


The Normalizer transformation is used to "normalize" data, or to pivot columns into rows.  How can you do the opposite (de-normalize, denormalize), or pivot multiple rows into multiple columns in a single row?   Suppose you have a source table with this data that is a record of monthly expenses for each of your Sales Reps: 

Source Data 

SALES_ID 

MONTH 

AMOUNT 

JAN 

100 

FEB 

50 

MAR 

75 

... 



DEC

200 

JAN 

120 

FEB 

250 

MAR 

125 

You want to de-normalize this data into this structure: 

Target Data 

SALES_ID 

JAN_AMT 

FEB_AMT 

MAR_AMT 

100 

150 

75 

120 

250 

125 


Solution


accomplish this do the following

1. Create an Aggregator transformation with the following ports and expressions: 

NAME 

IN/OUT 

EXPRESSION 

GROUP BY 

SALES_ID 

IN 


YES 

MONTH 

IN 


NO 

AMOUNT 

IN 


NO 

JAN_AMT 

OUT 

FIRST(AMOUNT, MONTH='JAN') 


FEB_AMT 

OUT 

FIRST(AMOUNT, MONTH='FEB') 


MAR_AMT 

OUT 

FIRST(AMOUNT, MONTH='MAR') 


APR_AMT 

OUT 

FIRST(AMOUNT, MONTH='APR') 


2. Connect the SALES_ID, MONTH and the AMOUNT ports from the Source Qualifier to the Aggregator. 

3. Connect the JAN_AMT, FEB_AMT, MAR_AMT, etc. ports to the target. 



MS SQL SERVER; ORACLE: Sessions will now fail in bulk mode if they are data driven

ke sure that if you have sessions running in Bulk mode that they are not set as data driven.