Is Incremental Aggregation possible

[vcannadevula]

Be aware that if you do it all within the transformer, you run the risk of "losing" your last output row (in the case where your number of input rows is not an even multiple of 5). When doing an aggregation entirely within a transformer, you usually need to detect an "End Of File" condition so that you can output that last row.

Mike

Ya thats true ,
so to avoid this we can declare another stage variable
If @INROWNUM/5=0 then 1 Else stagevariable+1 = count
now stagevar4=If @INROWNUM/5=0 then stagevar3/5 Else stagevar3/count-1

This will take care of last ouput row.

[Mike]

That will give you a correct average for the last group, but it still doesn't get it written to the output (see your output constraint logic). Take Ken's advice and use the aggregator stage. Why waste all the effort duplicating its functionality in stage variables? It can't get any simpler than using the aggregator for this situation.

Mike

[kcbland]

This will take care of last ouput row.

No it won't. Build the job, you will see that it does not work.

[peternolan9]

I like to use stage variables and I will implement it and let you guys know... All are of great help. Thank's everyone in person...

I heard that informatica has a built in module for handelling incremental aggregations.

So any built-in stage in DS?

Hi kommven,
yes, INFA and DS both have 'built in module' for handling incremental aggregations...they are both called 'write it yourself' and it works really well in both cases.....

To incrementally update an RDBMS (other than RedBrick) you must understand the aggregation rules and then you must merge input rows that will contribute to existing rows in the target table and insert rows that do not have an existing target row.

In 1994 I invented a mechanism to incrementally update rows in common databases and it even allowed the introduction of new aggregates with no code changes. Both in direct response to a customer need. All the code you need to read to find out how to do this in C (or even COBOL) is on my downloads page.

I have not seen how either of these features can possibly be implemented in any of the ETL tools and I've had some of the best people take a look at it....