What reason is there to create a physical table for a report output?

Question

I've noticed that for Crystal Reports made by our organization and by some of our ERA software providers have a tendency to use physical tables for their reports' data sets, rather than using a view or a stored procedure to collect the data. Occasionally I've seen reports use stored proceedures which then use physical tables rather than temporary tables to store and manipulate data sets. In these cases the report output often exists as a table like rpt_ap_vendors or similar, and it may or may not be devoid of data when not in use.

These are always cases where the report is generated on-demand, so this is not a case where a report could be generated once and served multiple times, and there are not multiple reports/stored procedures accessing this data at the same time.

What reason would there be for using physical tables for reports like this? Is there a logical, technical or performance related reason to do so? In generating reports I personally have always used views and stored procedures with temporary tables or better yet derived tables to avoid extra disc reads involving clearing out/deleting a temporary table.

Answer 1

(+) Reasons to create a physical table to store report data:

• The report data is reusable. I point Crystal Reports or SharePoint to the table and then don't worry about how often or when those tools or my end users access the data. (Well, to an extent, since repeatedly reading a large report table will trash my buffer cache.) I can also maintain a sliding window of old reports for the inevitable requests along the lines of: "Can you generate last year's report again? I can't find the CSV I extracted at the time."

  This is probably the main reason it's set up that way at your site. Crystal Reports may not be smart enough to cache the report data as users paginate through it or change the report's settings. So in the worst case CR is regenerating your report with each of those actions--a costly and time-consuming operation. With the physical table it just re-queries the table as many times as it needs.

• Setting permissions on the report is easy. You want to see this report? Well, all you need is permission to read the results, NOT generate them. So here, have some read rights on this table in a locked down schema and filegroup/tablespace.

By manually caching the report, you control and isolate the process of generating it. You give your report readers more freedom to act and yourself less to worry about as a DBA.

(-) What you lose with a physical report table:

• Flexibility. Wanna change the report? Argh, need some DDL changes.
• Storage space. You're persisting the data on disk, so duh.

Answer 2

At a previous employer, some of the reports would take hours to run. The month-end and quarter-end reports were the worst (8 and 20 hours respectively). By computing the results, and storing them into a permanent table, the user could look at the results of the report at whim without recalculating the numbers on the fly. The process that calculated the reports was able to restart if it was interrupted, so that also helped: in that part of South Florida, there were multiple second-long power outages each day. While the company had a generator on-site for the bad weather days, not all of the staff had UPSes.

Some of the "power users" wanted to be able to access the data and crunch the numbers in Excel, so they had read-only access to the reporting tables.