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