Informatica Workflow shows different number of records in transformation and target

0
votes

Hi I am a informatica newbie trying to train myself in power center workflows.

when I look at many of the workflow last sessions runs in the workflow monitor I see the number of records that are picked by the transformation is different than the number of records that get updated or inserted in the target table.

in the below image for example my sql transformation picks 80,742 rows from the source table. but only 29,813 rows get loaded into the target table.

image of informatica workflow monitor

on further analyzing the workflow log file I can see it loaded both insertable records and updatable records:

WRT_8036 Target: W_SALES_ORDER_LINE_F (Instance Name: [W_SALES_ORDER_LINE_F]) WRT_8038 Inserted rows - Requested: 15284
Applied: 15284 Rejected: 0 Affected: 15284 WRT_8041 Updated rows - Requested: 14529 Applied: 14529 Rejected: 0 Affected: 14529

WRITER_1_*_1> WRT_8035 Load complete time: Wed Mar 19 04:41:24 2014

I am not able to figure out why would the workflows load lesser records than what source sql gives. and I would really appreciate some help in this matter.

Thanks, Matt

1
informatica-powercenter
Impossible to answer if we don't see your mapping. Every active transformation (e.g. filter, aggregator) may change the number of rows. – Marek Grzenkowicz
That's what ETL is all about: Extract-TRANSFORM-Load. Look like in this example there is some aggregation or filtering done. Consider an example where the result of the mapping would be top 10 biggest daily sales - there would be always 10 output rows regardless of how many source rows there were. It's also possible to have more rows on the output than in source. – Maciejg
It may be happening when you have chosen Upsert(update else insert) in the target property.Can you please paste the excerpts of that property here so that I can tell where it is going wrong. For Example : update as update,update as insert – Alok Vajpayee

1 Answers

0
votes

This happens when there is a join happening in the ETL and the column over which we join has got duplicate values.