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

6 thoughts on “How to denormalize data in Informatica

  1. Hey thats really nice explaination…i just want to asked whether we need Expression Transformation after aggregator…i think we can directly connect the aggregator port to target…..

    • You are correct. The expression is optional. But it is a good practice to have an expression before the Target, just in case if you need to add an audit column. For instance when a row was either inserted or updated.

    • In order to denormalize you need to use either max/first/last/sum function. In my case I used MAX because “Y” is greater than “N”. Another example if you need to compute the sum then in your expression you set 0 or 1 and in the aggregator you will use the sum function.
      I may have to publish another denormalization example with the sum function in the next few weeks.

      Thanks!

  2. Hi Could you please show me the ports of Customer_brand(SQ)? I am getting an error msg “WRT_8229 Database errors occurred: ORA-00972: identifier is too long.

Leave a Reply

Your email address will not be published. Required fields are marked *