Summarization + Incremental Load + Joins

[mike_garcia]

Hello,

I need to summarize data in a table based on fields that are stored in another table. It would be straight forward if there was no need for incremental load, but there is.

One approach I'm considering is using ApplyMap to bring the new data from Table B (Which contains the filed I want to base my summariziation on) to table A (Fact Table), or even Joining the tables (taking only the Updated Data in Table A) and then summarizing. The problem with this is that Table B might change for example one product from category A to category B and that should also affect all previously loaded and saved data. So Incremental load is difficult to accomplish.

In another application in which I don't need to summarize, I just join the tables at the end and this Join affects all records, new or old.

I'm thinking there should be something like "Sum Join (B) Load * From A" or something like that (I know there isn't, but would be nice) in which tables are instantly summarized by a field in Table B.. Anyway.. How would you do it?

Thanks in advance.

Mike.