The Questions you need to answer before developing a reporting solution

1. Is the report a Detail or Summary report?
   Every valuable report sits at a specific section on what we call the "Summary-Detail Spectrum". For example: imagine you are responsible for reporting to management on monthly "inside sales" calls trends. This would be a Summary report. On the other hand, a different report, a hot-prospect status report, would be a Detail report, showing low-level transaction details of prospects that looked likely to follow with a purchase. When a report tries to be both a Summary and a Detail report, it increases the chances that it will solve neither problem. It is always good to know which type of report you are trying to create before development begins.
   Reports that try to be both, espeically when the data volumes are large, usually offer less value.
    
2. What question does the report answer?
   To the inexperienced, it is tempting to deliver as much information as possible from a single report. From our experience, we know that a useful and valuable report will rarely try to answer more than one question. Often, a drill-down from inside one report will, in practice, generate a different report which will answer a different question. It is important to allow each report to answer its own question, and for no two reports to try to answer essentially the same question. For example: a regional expense report might show a summary stacked chart of all five sale persons in the Puget Sound region. The question the report answers is "how much did my sales people in the Puget Sound spend over the past twelve months?". The report user could then drill into one sales person and another report would appear. This second report answers a different question, "How much did Willie O'Toole spend over the past twelve months?"
   Narrowing any given report to answering a single question intensifies the value of the report and is more likely to be understood and re-used again and again.
    
3. How fresh must the report data be?
   A common mistake in developing reporting solutions is to overestimate the 'freshness' required in a report's underlying data. For higher level Summary reports, it is often enough for the data in the report to be weeks old, because information provided by the report is not time-sensitive. For operational-level reports, on the other hand, the information might need to reflect occurrences that are minutes or even seconds old. Often, a variety of freshness requirements is found in a single database. There might be an ETL (Extract, Transform, Load) process that occurs every fifteen minutes to bring fresh data into the reporting database, but a monthly summary report that only gets updated weeks later.
    
4. How often does the data need to be refreshed?
   As a rule-of-thumb, the more "Summary level" a report is, the less often its underlying data needs to be refreshed. A sales-report-by-month might only have to be updated once a week or even once a month, whereas a daily-web-sales-report might need to be updated every 24 hours. The closer the report content is to the transactional records in terms of granularity, the more often the data is likely needing to be refreshed.
    
5. Why is the difference between Reporting and Monitoring important?
   Monitoring, generally speaking, assists operational staff in knowing when a problem occurs in the operation of the business. It might also be classified as Exception Reporting, showing only the errors or problems that have occurred, while omitting those business operations that worked as expected. Monitoring solutions are thus used by operational staff.
   Reporting (as opposed to Monitoring) is usually related more to the business function itself. Views of how sales, finance, costs, etc., are working usually falls into the Reporting category.
   Having a different audience and different update/refresh requirements means a different type of scalability challenge is presented to the developer of Reporting vs. Monitoring solutions.
    
6. Why is it often better for the reporting database to be separate from the production database?
   Operational databases are architected for the optimization of transaction processing. That is, the daily business of entering and processing individual transactions. The performance requirements for such interaction are much different to those required of a reporting solution that may query huge numbers of records far less frequently. Thus, the database architecture of tables, queries and views in a reporting database (or the reporting part of the database) is structured differently to that of a production database (e.g. a database that supports an online order processing system).
    
7. What does it mean to have a "separate database for reporting"?
   A separate reporting database is either an identical copy of the business data in another database (often) on a different server, where the source data (or part of it) is periodically copied from the production database to the reporting database. A separate reporting database allows CPU intensive queries to be executed against underlying report data without impinging on the operational performance of the production system which often requires split-second response times because customers are entering orders. Often, the very structure of the database is significantly different in the reporting database to that of the production database. Most of the production database is (usually) not duplicated in the reporting database (because it is not needed for reporting) and conversely, there are additional tables created in the reporting database that do not exist in the production database. This is because pre-built, progressively higher summary level tables allow for high level reports to run quickly even though they represent potentially vast numbers of transaction records.
   The greater the number of underlying transaction records, the greater the need for a well architected, multi-summary level reporting database.