Best Design for creating Historic Reports on GAE

Question

My App requires Daily reports based on various user activities. My current design does not sum the daily totals in database, which means I must compute them everytime.

For example A report that shows Top 100 users based on the number of submissions they have made on a given day.

1. For such a report If I have 50,000 users, what is the best way to create daily report?
2. How to create monthly and yearly report with such data?

If this is not a good design, then how to deal with such design decision when the metrics of the report are not clear during db design and by the time it is clear we already have huge data with limited parameters (fields).

Please advice.

Answer 1

Ideally, I would advise you to create your data model in such a way that all of the items that needed to be reported could be precomputed in order to minimize the amount of querying that had to be done on the database. It sounds like you might not be able to do that, and in any case, it is an approach that can be brittle and resistant-to-change.

With the release of the 1.3.1 version of the SDK, you now have access to query cursors, and that makes it a good deal easier to deal with generating reports based on a large number of user. You could use appengine cron jobs to put a job on a task queue to compute the numbers for the report.

Since any given invocation of your task is unlikely to complete in the time that AppEngine allows it to run, you'll have to pass the query cursor from one instance to the next until it finishes.

This approach allows you to adapt to changes to your database and reporting needs, as you can record that Task that computes the report values fairly easily.