Denormalize Transactional Data using Informatica

Let’s de-normalize transactional data.
Here is the input:

1COOKING BOOKS12.506/07/2013 10:57:10
1MEDICAL BOOKS120.506/07/2013 10:57:10
1TRAVEL BOOKS10.506/07/2013 10:57:10
2FOOD PROCESSOR12.506/07/2013 10:57:10
2INTELLIGENT OVEN120.506/07/2013 10:57:10
2SAFE10.506/07/2013 10:57:10
3CLOTHING12555.2506/07/2013 10:57:10
3CAR VACUUM15520.4506/07/2013 10:57:10
3HOTEL PASS1550.5506/07/2013 10:57:10
3MAGAZINE1110.5606/07/2013 10:57:10
4ZOO PASS110.406/07/2013 10:57:10

Here is the output:
The table is quite wide so you can download it as csv.

Here is the mapping overview:
SQ ==> EXP ==> AGGR ==> TGT


Step 1:

Step 2:
In EXP_SetSequenceNumbers create three variables as follow:

  1. currCustomer_v ==> to_char(CUSTOMER_ID)
  2. Seq_v ==> IIF(currCustomer_v != prevtCustomer_v , 1 ,  Seq_v + 1  ). Each time we get a new customer_id the Seq get reset to 1 otherwise it will increment by 1.
  3. prevtCustomer_v ==>to_char(CUSTOMER_ID)

Make sure that prevtCustomer_v is below currCustomer_v and Seq_v as in the following pic:


Step 3:
In AGG_DenormalizeOrders make sure that the following is set:

  1. Sorted input is checked.
  2. Group by is checked for Customer_ID
  3. Create outputs to house ProductName, ProductPrice and TransactionDate
  4. For each sequence select the value of seq. For instance, ProductName3 select FIRST( PRODUCT_NAME, seq = 3) etc…

For better understanding please see pic below:



Step 5:
Connect the aggregator to the target.

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:




Step 2:
Let us look at the desired output:



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

Update else Insert in Informatica

In order to successfully implement upsert using Informatica we need to follow these steps:

  1. In the mapping we need an update strategy with DD_UPDATE before the Target.
  2. In the session the we need to have the following settings:
    1. Under Properties:Treat Source rows as Data driven
    2. Under Mapping, the Target should have both Insert and Update else insert checked.

I do not recommend update else insert for large sets of data if you need a process that is efficient and fast. It is better to stick with having two separates paths in the mapping. One for updates and the other one for Inserts.