Techniques for handling large reporting periods in MSSQL

Question

We have a fairly large database with some fairly large tables (100s of millions of rows). Some of those tables are reported on.

We have indexes in place to make this as fast as possible, but still we hit limits on what we can achieve. As such we limit any reporting to 1 months worth of data per user.

I am trying to find out how one can achieve reporting on a larger scale, such as over a year, or comparing one year to the previous.

I figured the only way to achieve this is to roll up the data somehow into aggregations at a different scale. So instead of 5 data points a day, you could store 1 aggregated data point per day, week, month etc.

This option seems great until you consider timezones; all our data is stored in UTC but a user may be in a different timezone to their colleague. I had thought we could aggregate once per timezone that is in use, but then the data creeps up again.

I'm sure there are some best practices for this kind of work but have been unable to unearth them, if someone could put me in the right direction I'd appreciate it.

Some additional info:

• Server: MSSQL
• Coding stack .NET
• We do not use SSIS

Answer 1

The standard technique for this sort of reporting work is to define the aggregates you need, and then compute them incrementally on a regular basis and store the results. Then, when the time comes to prepare the report, you assemble it (at least, as much as possible) from these stored results.

The computation of longer-range aggregates may itself use stored aggregates covering a smaller range - for example, computing a yearly aggregate will not use the raw data, it will use the monthly aggregates which were previously prepared from the raw data.

You appear to have convinced yourself that the timezone issue makes this unfeasible, but it's not clear from the information you've given whether this is true.

A person who wants a yearly or three-yearly report, may be willing to tolerate small inaccuracies based on the difference in when the threshold of a year is treated as occuring in different timezones - especially if the alternative is getting no report at all.

If the results must be precise, then it may still be possible to use the UTC yearly aggregate as an approximation to the correct value, and then lookup the detail data at either end of the year, adding and subtracting the small number of values necessary to establish the correct aggregate value for the 'local year'.