The number of data warehouse reports that users need can be overwhelming. This tip from Ralph Kimball's book The Data Warehouse Lifecycle Toolkit (Wiley Computer Publishing) provides a checklist of features
Standard reporting provides the ability to create production style fixed-format reports that have limited user interaction, a broad audience, and regular execution schedules. At the formal end of the spectrum, large standard reporting systems tend to surface when the ERP system cannot handle the workload of operational transactions and reporting. Be careful not to take this on as a side effort of the data warehouse. Full-scale standard reporting is a big job that involves its own set of requirements and services. In this case, there should be a standard reporting project solely responsible for managing this effort.
Of course, the data warehouse needs to support standard reports regardless of whether there is a large-scale standard reporting environment. In fact, most of the query activity on many warehouses today comes from what could be considered standard reporting. In some ways, this idea of running production reports in an end user environment seems inappropriate, but it is actually a natural evolution. Often, analyses that are developed in an ad hoc fashion become standard reports. The ability to put these into a managed reporting environment is an obvious requirement. They will need to be run on a regular basis and made available to a broad base of customers either on a push or pull basis (e.g., email or Web posting). Most of the front-end tool developers include some form of this reporting capability in their products. Requirements for standard tools include:
- Report development environment. This should include most of the ad hoc tool functionality and usability.
- Report execution server. The report execution server offloads running the reports and stages them for delivery, either as finished reports in a file system or in a custom report cache.
- Parameter- or variable-driven capabilities. For example, you can change the Region name in one parameter and have an entire set of reports run based on that new parameter value.
- Time- and event-based scheduling of report execution. A report can be scheduled to run at a particular time of day of after a value in some database table has been updated.
- Iterative execution. For example, provide a list of regions and create the same report of each region. Each report could then be a separate file e-mailed to each regional manager. This is similar to the concept of a report section or page break, where every time a new value of a given column is encountered, the report starts over on a new page with new subtotals, except it generates separate files.
- Flexible report definitions. These should include compound document layout (graphs and tables on the same page) and full pivot capabilities for tables.
- Flexible report delivery. Via multiple delivery methods (e-mail, Web, network directory, desktop directory and automatic fax). In the form of multiple results types (data access tool file, database table, spreadsheet).
- User accessible publish and subscribe. Users should be able to make reports they've created available to their departments or to the whole company. Likewise, they should be able to subscribe to reports others have made and receive copies or notification whenever the report is refreshed or improved.
- Report linking. This is a simple method for providing drill-down. If you have pre-run reports for all the departments in a division, you should be able to click on a department name in the division summary report and have the department detail report show up.
- Report library with browsing capability. This is a kind of metadata reference that describes each report in the library, when it was run, and what its content is. A user interface is provided that allows the user to search the library using different criteria.
- Mass distribution. Simple, cheap access tools for mass distribution (Web-based).
- Report environment administration tools. The administrator should be able to schedule, monitor, and troubleshoot report problems from the administrator's module. This also includes the ability to monitor usage and weed out unused reports.
This was first published in June 2001