I have a very large, slow data set to query, and I'd like to provide a report covering a sliding window from -21 days ago until 'now' for use on a dashboard. It's far too slow to run a query like this on demand. To work around that, I've added multiple reports: A) a daily scheduled report to query 21 days of data ending on the start of the current day. Takes 30 minutes to run each night. B) an hourly scheduled report to query data from the start of the current day until the top of the hour. takes ~5 minutes. C) a final real-time report that uses `append' and 'loadjob` to combine A and B, plus gathers any new data from the last time B was run. The dashboard then references report C and sets it as the base search for the dashboard. The end result is 21 days of recent data with minimal wait time for the dashboard user. It's working, but clunky. The dashboard can assemble the data in about a minute, but report C ends up duplicating all the data from A and B, which is causing storage issues. I'm hoping there is a better way to maintain a sliding window of data that uses a more efficient means to add new events and purge the old ones. Suggestions?
... View more
