Hi, We are working on a Data Wa

[astra1]

Hi,

We are working on a Data Warehouse. We have a situation when i have to summarize a group of rows in one row. presently we are doing this by using aggregate functions such as sum and max...

My query is that is there a way to summarize in a more effective manner, ie without using the aggregate function? We need to avoid the same as it is taking longer time at the ETL level

Regards,

Vish

 

[SQLSister]

I can't think of a replacement for sum or max. But you could consider keeping a running total and then sending the total to the data warehouse. It works like this. You set up a table for the running totals.

You put triggers on the table where the total comes from. So if a record is inserted the a value is added to the total, if it is updated and the value changes, the old value is subtracted from the total , the new value is added and if a record is deleted, the value is deleted from the total. This takes a little more time at the transaction level, but would only take significantly more time if you are doing bulk inserts/deletes.

Then when you warehouse the data you send the summary table to the warehouse.

You can set this up to do by the month is you are going to do monthly totals, or by the week, day, quarter or year depending on what your needs are. This way you avoid trying to sum a million records all at one time. It does affect the insert/update/delete time though so test to make sure you haven't made this unacceptably slow. Of course when you set it up you will have to calculate the current totals to start off the running totals and this will take a while. Suggest you do this during off peak hours with the data base set to single user mode, so that no records can be changed while you are doing this. Otherwise your runnning total might always be off by a couple of records that came in during the summation process.