2

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
Improve this question
5
  • Does your hardware and sql license match your needs? – Mr Zach Jan 12 '20 at 21:11
  • 2
    Yes, there is just too much data to perform analysis on customers for an entire year in a timely manner. I figured many people would have hit this problem and there would be some common solutions, I just cant seem to find any... – Chris Jan 12 '20 at 21:35
  • This is an interesting question, however, way too broad for a sufficient answer using the format of this site. I recommend to read one or more books about data warehouse design (there are several good books, ask Google). – Doc Brown Jan 12 '20 at 22:32
  • Thanks Doc... I suppose I am in that awkward place where I don't know enough to start and not sure what questions to ask. Where do you turn to if you are in that place? Was hoping some kind people on here would give me a nudge in the right direction. – Chris Jan 12 '20 at 22:43
  • 1
    The search term you are looking for is "data warehouse". Usually, the "live" data is periodically dumped into separate system, which specifically optimizes it's schema for this kind of aggregate analysis. Attempting this analysis on database schema that is used of "live" data is usually not worth the effort. – Euphoric Jan 13 '20 at 10:01
3

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'.

Improve this answer
3
  • Hi Steve, thanks for answering. Our customers can be very exacting in the accuracy of the data so an approximation would not hold. However I like your idea of having a lookup to cover the detail near the end of an aggregation period. Thank you! – Chris Jan 12 '20 at 22:11
  • If the timezone is crucial but fixed per customer, you might just take it into account in the initial aggregation step so that aggregated data per customer is always customer timezone aligned. (cont) – Hans-Martin Mosner Jan 13 '20 at 7:04
  • 1
    However, my experience is that such demands often stem from an incomplete understanding of what event timestamps actually mean, and that customers will come up with all kinds of ridiculous fudge rules to make two separate reporting systems match. It might be worthwhile to tag events with a date (not timestamp) based on a rule that is accepted by the customer, and use that to aggregate report data. – Hans-Martin Mosner Jan 13 '20 at 7:09

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.