Report Snapshots for time series analysis

Dear all, We are building a feature (which can go in frappe core) to provide an ability to save report snapshots for time series analysis. There could be some complex reports which would do aggregation of transaction data. This data can be then saved to summary doctype on daily basis. New snapshots could replace the old snapshot or append with date column populated.

The idea is to keep creating summary doctypes which would have fields same as the output columns of any report and then we can configure a time when the report should be executed and the data would be saved in that Summary table.

We will introduce a new DocType called - ‘Snapshot Config’. This will have following fields -

  1. report_to_be_executed (Report)
  2. doc_type_to_be_populated (DocType)
  3. at_hour (int)
  4. replace_old_snapshot (boolean)

There would ofcourse some other columns which will allow us to provide filter inputs to report_to_be_executed. This can be a plain ‘Key-Value’ child table with JINJA support in the ‘Value’.

A scheduled function will invoke every hour and retrieve all the Time Series Snapshot rows where hour = current_hour and -

  1. execute the report
  2. populate the data in corresponding doctype either by replacing or appending based on replace_old_snapshot flag

Initially we will follow a convention of mapping report column with doc_type field. If required an explicit configuration based mapping can be provided in future.

This would allow to record aggregated data everyday/configured hour of the day

Any comments/improvements/suggestions are welcome.