How to denormalize data in Informatica

Most of you know that Informatica does not provide a function for denormalization. However, denormalizing a table is easy and could be done in Informatica by using two functions: an Expression and an Aggregator.

Step 1:
Let us look at the data:

CUSTOMER_BRAND

CUSTOMER_IDBRAND
1BMW
1CHEVY
1FORD
2BMW
2FORD
3CHEVY

 

Step 2:
Let us look at the desired output:

CUSTOMER_IDBMWFORDCHEVY
1YYY
3NNY
2YYN

 

Step 3:
Let us look at the general architecture of the mapping:

The mapping consists of the following a source table, a SQ, an Expression, an Aggregator, an Expression and a Target.

 

Step 4:
Let us look at the SQ:

Make sure that we sort the on CUSTOMER_ID asc. This is needed so we can send sorted input to the aggregator.

 

Step 5:
Let us look at the Expression function

The expression function will set the Brand to “Y” if it exists else it will set it to “N”.


Step 6:

Let us look the Aggregator function:

Since “Y” > “N” then if a customer has the brand it will output “Y” else it will output “N”.


Step 7:
The final step is easy just connect the Aggregator to the Target:

If you are interested in a more complicated example please check: Denormalize Transactional Data using Informatica