Let’s de-normalize transactional data.
Here is the input:
|1||COOKING BOOKS||12.5||06/07/2013 10:57:10
|1||MEDICAL BOOKS||120.5||06/07/2013 10:57:10
|1||TRAVEL BOOKS||10.5||06/07/2013 10:57:10
|2||FOOD PROCESSOR||12.5||06/07/2013 10:57:10
|2||INTELLIGENT OVEN||120.5||06/07/2013 10:57:10
|3||CAR VACUUM||15520.45||06/07/2013 10:57:10
|3||HOTEL PASS||1550.55||06/07/2013 10:57:10
|4||ZOO PASS||110.4||06/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
In SQ_ORDERS sort by CUSTOMER_ID
In EXP_SetSequenceNumbers create three variables as follow:
- currCustomer_v ==> to_char(CUSTOMER_ID)
- 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.
- prevtCustomer_v ==>to_char(CUSTOMER_ID)
Make sure that prevtCustomer_v is below currCustomer_v and Seq_v as in the following pic:
In AGG_DenormalizeOrders make sure that the following is set:
- Sorted input is checked.
- Group by is checked for Customer_ID
- Create outputs to house ProductName, ProductPrice and TransactionDate
- 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:
Connect the aggregator to the target.
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.
Let us look at the data:
Let us look at the desired output:
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.
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.
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”.
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”.
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
In order to successfully implement upsert using Informatica we need to follow these steps:
- In the mapping we need an update strategy with DD_UPDATE before the Target.
- In the session the we need to have the following settings:
- Under Properties:Treat Source rows as Data driven
- 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.