Best way to prevent fetching large data set by a report
5 August, 2014
Hi
In our reporting model, we have reports in default organisation level and custom reports in client organisations which are created by report writers. But there is a performance impact by custom reports when these reports fetch large data set. Therefore, we need to set pre-defined active row limit, turn off auto refresh and set pre-defined preview row limit automatically for custom reports in order to prevent performance issues.
We cannot set maximum row count in data source since default level reports should run with unlimited row limit. Therefore, we have proposed and tested following approach to prevent creating reports with large data result.
Insert and update following config values using MYSQL database triggers (Please refer attached reportingTriggers.sql),
1. ReportHeader table update with Active Row Limit by setting a value to MaxRowReturnedCount column, for instance MaxRowReturnedCount = 15000
2. At the same time, two records insert to ReportFormat table and turn off "Auto Refresh" option and "Preview Row Limit"
INSERT INTO ReportFormat (ReportId, EntityCode, EntityId, FormatTypeCode, FormatCode, FormatValue, Description, FieldId, ChartId, SeriesId, AxisCode) VALUES (NEW.ReportId, 'REPORT', 0, 'EDITDATAREFRESH', 'MANUAL', 0, NULL, 0, 0, 0, NULL);
INSERT INTO ReportFormat (ReportId, EntityCode, EntityId, FormatTypeCode, FormatCode, FormatValue, Description, FieldId, ChartId, SeriesId, AxisCode) VALUES (NEW.ReportId, 'REPORT', 0, 'EDITROWLIMIT', NULL, 50, NULL, 0, 0, 0, NULL);
We would like to know your suggestions for this approach or there is a recommended alternative approach to achieve our goal ?
Thanks
Supun
In our reporting model, we have reports in default organisation level and custom reports in client organisations which are created by report writers. But there is a performance impact by custom reports when these reports fetch large data set. Therefore, we need to set pre-defined active row limit, turn off auto refresh and set pre-defined preview row limit automatically for custom reports in order to prevent performance issues.
We cannot set maximum row count in data source since default level reports should run with unlimited row limit. Therefore, we have proposed and tested following approach to prevent creating reports with large data result.
Insert and update following config values using MYSQL database triggers (Please refer attached reportingTriggers.sql),
1. ReportHeader table update with Active Row Limit by setting a value to MaxRowReturnedCount column, for instance MaxRowReturnedCount = 15000
2. At the same time, two records insert to ReportFormat table and turn off "Auto Refresh" option and "Preview Row Limit"
INSERT INTO ReportFormat (ReportId, EntityCode, EntityId, FormatTypeCode, FormatCode, FormatValue, Description, FieldId, ChartId, SeriesId, AxisCode) VALUES (NEW.ReportId, 'REPORT', 0, 'EDITDATAREFRESH', 'MANUAL', 0, NULL, 0, 0, 0, NULL);
INSERT INTO ReportFormat (ReportId, EntityCode, EntityId, FormatTypeCode, FormatCode, FormatValue, Description, FieldId, ChartId, SeriesId, AxisCode) VALUES (NEW.ReportId, 'REPORT', 0, 'EDITROWLIMIT', NULL, 50, NULL, 0, 0, 0, NULL);
We would like to know your suggestions for this approach or there is a recommended alternative approach to achieve our goal ?
Thanks
Supun
Hi Supun,
Adding row limits is one way to handle this, but the other way is to force mandatory filters for reports.
This means at the view, you set the mandatory filters.
Then when any user creates a report, the filter needs to be selected, otherwise they cannot complete the report creation.
E.g.
However, if a client is creating their own view, they will need to set this up themselves. So in this instance, view creators need to be told about best practices with your data set.
Please let me know what you think about this method.
Regards,
David
Adding row limits is one way to handle this, but the other way is to force mandatory filters for reports.
This means at the view, you set the mandatory filters.
Then when any user creates a report, the filter needs to be selected, otherwise they cannot complete the report creation.
E.g.
However, if a client is creating their own view, they will need to set this up themselves. So in this instance, view creators need to be told about best practices with your data set.
Please let me know what you think about this method.
Regards,
David
Hi David,
Above suggestion would solve our problem partially. There can be cases where our client report writers set filter values in a mandatory filters to fetch large data set. This is the reason we need a solution to limiting number of rows return by a report.
Our clients will not create views, we provide them set of views. Do you suggest any other solution other than triggers option ?.
Thanks
Supun
Above suggestion would solve our problem partially. There can be cases where our client report writers set filter values in a mandatory filters to fetch large data set. This is the reason we need a solution to limiting number of rows return by a report.
Our clients will not create views, we provide them set of views. Do you suggest any other solution other than triggers option ?.
Thanks
Supun
Hi Supun,
The only other thing I could suggest is source filters, but this would only work if you want to limit specific data from users.
I think maybe you could use mandatory dependent filters ? This way you force them to use multiple filters?
It's a hard one, as you want to stop people from doing bad things, but also don't want to limit what they do.
Bit of a catch-22.
:(
Regards,
David
The only other thing I could suggest is source filters, but this would only work if you want to limit specific data from users.
I think maybe you could use mandatory dependent filters ? This way you force them to use multiple filters?
It's a hard one, as you want to stop people from doing bad things, but also don't want to limit what they do.
Bit of a catch-22.
:(
Regards,
David
Hi David,
Thank you for the reply. I would like to know one more fact relates to "Active Row Limit" setting in report builder.
I cannot see LIMIT keyword in generated SQL in either "SQL Statement" in UI or Yellowfin log file.
Could you please describe how Yellowfin has implemented "Active Row Limit" in reports ? Is this implemented in SQL level or rendering level or any other way ?
Can I expect performance improvement (execution time of report) if report configured with less value for "Active Row Limit" ?
Thanks
Supun
Thank you for the reply. I would like to know one more fact relates to "Active Row Limit" setting in report builder.
I cannot see LIMIT keyword in generated SQL in either "SQL Statement" in UI or Yellowfin log file.
Could you please describe how Yellowfin has implemented "Active Row Limit" in reports ? Is this implemented in SQL level or rendering level or any other way ?
Can I expect performance improvement (execution time of report) if report configured with less value for "Active Row Limit" ?
Thanks
Supun
Hi Supun,
The active row limit is actually passed directly to the driver and not included in the SQL.
It can improve performance if your query is slow due to the number of records returned.
Hope this covers what you're after.
Regards,
David
The active row limit is actually passed directly to the driver and not included in the SQL.
It can improve performance if your query is slow due to the number of records returned.
Hope this covers what you're after.
Regards,
David