Standard Reports: Basics for Business Users

Here's how to plan, prioritize and design standard BI application reports.

Create the standard template. Think of the reporting system as a publication and yourself as the editor. To communicate effectively, you need consistent format and content standards. Create a template identifying the standard elements that will appear on every report (see mock-up, below). The basics include:

  • Report name and title
  • Report body
    • Data justification, data precision and data format
    • Column and row heading format
    • Background fills and colors
    • Formatting of totals and subtotals
  • Header and footer
    • Report name and navigation category
    • Report run date and time
    • Data source(s) and parameters used
    • Report notes, including important exceptions, such as "Excludes intracompany sales."
    • Page numbering
    • Confidentiality statement

  • DW/BI reference (name and logo of the DW/BI system)
  • Report file name

    Not all report information is displayed on the report itself. Use a specification document or repository to collect the following report metadata:
    • User variables and other user interactions such as drill downs
    • Report calculations, derivations, author and date created
    • Security requirements
    • Execution cycle or trigger event, if the report runs automatically
    • Delivery mechanisms, such as e-mail, Web site, file directory or printer
    • Standard output format, such as HTML, PDF or Excel
    • Page orientation, size and margin settings

    Create report specifications and documentation. For each report on the target list, create a specification that includes the following components:

    • Report template information as outlined above
    • Report mock-up
    • User interaction list
    • Detailed documentation

    Report mock-ups are a great way to communicate the content and purpose of the reports. Use symbols to denote functions, such as:

      < > = User entered variable

      < < > > = Drillable field

      {} = Application entered variable

      \\ \\ = Link to another report or documentation source

      ( ) = Page or section break field

      [ ] = Report template comments

    The function symbols tell you what kind of interaction is possible, but they don't specify how that interaction works. Create a user interaction list to identify the nature and degree of interaction a user may have with each report, including variable specification, pick list descriptions, drill down and field addition or replacement.

    Document required information not directly associated with the report display, such as the report category, the sources of the data, the calculations for each column and row, and any exceptions or exclusions to build into the query. You can append this document to the user interaction list.

    The mock-up, user interaction list and additional documentation must provide enough information so that a developer can build the report.

    Design the navigation framework. Once you know which reports to build, categorize them. This structure should enable anyone who knows something about your business to find what they want quickly. The best approach is to organize the reports by business process — just like the data warehouse bus matrix. This navigation framework is the primary entry point into the BI system. We call it the BI portal.

    Conduct a user review. Review the report specifications with the user community to:

    • Validate your choice of high-priority reports and test the clarity of the specifications.
    • Validate the navigation hierarchies in the BI portal.
    • Involve users in the process, emphasizing their roles and developing their commitment.
    • Give users a sense of what will be possible in just a few months' time.

    Once the specs have been reviewed, you can put them on the shelf until it's time to develop the reports. They'll be useful if you evaluate front-end tools, as the candidates should be able to easily handle the range of reports in the initial report set.

    In a Nutshell

    BI applications — whether standard reports or advanced analytic applications — are typically the only access to your DW/BI system for 90 percent of business users. Standard reports are the backbone of the system, so you need to do a great job designing them and creating a navigation framework. Do this work early in the project, when business users' requirements are fresh in your mind.

    When the hard technical work of building and populating the data warehouse nears completion, it's time to think about the BI applications again. That's when you'll pull your target list specs off the shelf and build the standard reports and BI portal, including plans for maintaining, extending, securing and tuning the reports — issues we'll discuss in an upcoming column.

    Warren Thornthwaite and Joy Mundy are members of the Kimball group and authors of The Microsoft Data Warehouse Toolkit (Wiley, 2006), from which this article was excerpted. Write to them at [email protected] and [email protected].

    Quick Study

    BI application standard reports are the primary vehicles for delivering business intelligence to the vast majority of users. Soon after gathering user requirements, take the following approach to design a starter set of standard reports for each business process dimensional model in your data warehouse:

    • Create the target list of high-priority reports.
    • Design a template that identifies the report layout and content.
    • Create specifications and documentation for each target report.
    • Design a navigation framework to organize reports and make it easy for users to find the information they need.
    • Review the report specs and navigation framework with key business users.

    Required Reading

    "Kimball Design Tip #58: The BI Portal,"

    "The Microsoft Data Warehouse Toolkit: With SQL Server 2005 and the Microsoft Business Intelligence Toolset," by J. Mundy and W. Thorthwaite (Wiley, 2006), Chapter 8

  • Editor's Choice
    Salvatore Salamone, Managing Editor, Network Computing
    Mary E. Shacklett, President of Transworld Data
    Joao-Pierre S. Ruth, Senior Writer
    Richard Pallardy, Freelance Writer