Planning a report
To paraphrase Isaac Asimov’s laws for robots:
- 1. A report may not injure a timetable.
- 2. A report must accurately inform a human being except where such orders would conflict with the first law.
- 3. A report must be available to use as long as such availability does not conflict with the first or second laws.
To process the data in a complex report can take an immense amount of processor power and memory. Attendance data can number over two million attendance marks. Many queries will relate marks against each other requiring four billion or eight trillion points of data. Often, many people may be accessing reports at the same time, each person’s report will require its own data processing.
My re-writing of the Asimov’s Laws for Robots does have a serious angle.
Law 1 - We should assess how much data is required, how complex the processing is for the report and how many people will be accessing the reports, especially if we are reporting with the live database.
Law 2 - We should assess whether live data is needed, or you can use an overnight data set that is processed just once.
Law 3 - We should decide if the report needs to be processed at viewing time, or can it be cached or saved as an Excel/PDF document.
We must be realistic in our requirements and why we need a report. A report is generally a management tool for assessing specific Key Performance Indicators which can be processed over night when the computer’s memory and processor is not used for the front-end system. Reports such as course-based attendance rates, consecutive absence alerts and room utilisation fall into this category. Enhancements to the front-end software such as a single student’s attendance pattern or listing the inventories for a room, can be performed on the live system. However, it is better to have filters (correctly used) to view one record at a time rather than the whole data set where someone has to look through 100 pages. If the data is for an CSV of XML extract for another system to process or for someone to examine with Excel Pivot Tables, don’t use a reporting tool such as SSRS. Extracts can be produced by an SQL Server or the SIM tool; whilst SQL views on data may be used for Excel to access through the “External Data” processes.
My view has always been to protect the primary data source, and let its software do what it’s designed to do. Take a copy of the live data you need and work with it in its own safe area.