Building and Delivering BI Reports

In our last column, we laid out a process for identifying, prioritizing and specifying the core set of business intelligence (BI) applications (see "Standard Reports: Basics for Business Users"). In this column, we describe the BI application development tasks that begin much later in the lifecycle, once real data is available in its final dimensional structure and the BI tool has been selected.

As always, our process starts with a bit of planning and preparation. We then create the initial set of standard reports, based on the prioritized list from the design phase. The next step is testing and deployment, which leads to a discussion about ongoing maintenance and extending the standard reporting system.

Set Up Development

It's hard to resist the temptation to dive in and start building reports once real data is available. Be strong! Take a few days to set up your reporting environment and figure out the overall reporting process before you start creating reports. Begin by setting up the development environment and creating a standard report template.

If this is the first time you're using your front-end tool, be warned that installation and configuration can take more work than you might expect. Many reporting environments have several components, including developer tools, report viewers, an administrator tool and a report server. Adding to the complexity, the report server often works best when installed on its own machine and usually must work closely with a Web server. In some cases the report server needs a database or file directory to hold metadata about reports, schedules, events and distribution lists; make sure you include this database in a regular backup routine. Depending on the size and complexity of your BI environment, you may want to set up a separate test report server to support the testing process.

The best practice for new extract-transformation-load (ETL) projects is to develop them against a test system to protect the production environment from problems such as table locks and disappearing data. This approach also can make the development process faster and easier. On the other hand, in the BI report development process, it usually makes sense to develop reports directly against the production DW/BI database. The risk of a negative impact on the production system is relatively small: Reports are read-only and are generally similar to any other ad-hoc usage of the database. If the DW/BI database is designed to support ad hoc queries, it should support report development. Building reports against the production database gives you an early opportunity to assess performance and validate the reports. It also makes it easier to move the reports into production, since they already point at the production database.

In addition to installing the tool components, you may need to take other steps before you can get started. Some front-end tools may require you to define the metadata layer that insulates users from the database, set up delivery and notification metadata and processes and a usage-tracking system.

Create the Reports

Figuring out which report to start with is easy — if you've done your homework. As we described in our last column, the specifications from the design phase include a list of the standard reports in order of priority along with mock-ups and documentation on the definitions and contents of the reports. Start with the first report on the list and work your way down.

The first step in creating the report is to define the query (or queries) that populate the contents of the report. Report specs often require user-provided query constraints, most of which should leverage the pick lists and parameters already included in your standard template. In some cases, the report may require multiple datasets. For example, you may have revenue data in one fact table and costs in another. To show product contribution, you may need two separate queries to combine these two sources. Your front-end tool will need to join the two results sets so you can calculate revenue minus costs.

Once the dataset is defined, the next step is to lay out the report contents according to the specifications. That means deciding what goes into the rows and columns, what calculations will take place in the report and how the report should be formatted. Getting the report exactly right takes a lot longer than you might expect. Make sure you preview the report in all its possible delivery forms such as spreadsheet, PDF, Web, e-mail and print.

The guiding philosophy in formatting reports is that they should be as clear and self-explanatory as possible. Users will not take the time to look elsewhere for report documentation, nor should they be expected to. Clarity is one of the major challenges the DW/BI team takes on when they include standard reports as part of their responsibilities. It's helpful to involve someone with solid graphic design expertise while designing the template and the initial report set. Experiment with alternatives and get feedback from users as to which one works best. A bit of extra work at this point will pay off massively in the long run.

Test Accuracy and Performance

The development process includes testing various combinations of parameters and ensuring the report returns appropriate results. Test the contents of the report to make sure the calculations and constraints are correct. Check the numbers as carefully as possible, comparing them to any known alternative sources for the same information. If the numbers should be the same and aren't, figure out why. If the numbers are supposed to be different because they have been improved or corrected in the ETL process, carefully document the reasons for the differences. If possible, show how a user or auditor can get from the data warehouse numbers back to the alternative source numbers. This documentation should be available in the BI portal, and the report description should refer to it.

In large organizations, with hundreds or thousands of users pounding on the standard report set, it makes sense to deploy the reports to a test server environment that is as similar to the production environment as possible. A test server lets the reporting team stress-test the new reports to ensure they don't reduce the performance of other reports before moving them into production. In midsize and smaller organizations, you may not need a full test server environment. The reporting team could deploy the reports to the production report server and test them there. You can minimize the risk by limiting access to the test report directories and by not publishing the new reports in the BI portal until testing is completed.

There are several testing steps, starting with deploying the project to the test or production report server. Next, the reports need to be reviewed to ensure proper display and printing. If they are not working as expected, try performance enhancement techniques such as tuning the query, creating report snapshots or changing the server configurations. Carefully retest, as reports are the only experience most users have with the DW/BI system; they'd better work and they better be right.

Deploy to Production

The next step is to integrate the new reports into the production process. The report specifications should indicate whether the report is executed on demand or cached on a time-based or event-based schedule. Exactly how you set up these procedures depends on your report operations environment. As part of the deployment process, you should develop instructions on how the system should distribute the report: caching the results to quickly serve future requests, e-mailing the report to a distribution list or saving the report to the file system or database. You may need to set up a subscription process to let users select reports they would like to receive on a regular basis. If you are providing reports through a BI portal, you'll need to integrate this new set of reports into the portal as part of the production deployment.

Whenever there's a deployment to the production server, you'll need to repeat many of the steps you went through to move the reports into test, including schedules, snapshots, subscriptions and e-mail distribution lists. However, in most cases, the deployment to production took place in the test step, so this step is more of an unveiling, especially when the primary report interface is through a Web site or portal. For this situation, deployment is a matter of changing the security settings to make the reports accessible through the portal.

Manage and Maintain

Once BI applications are in use, the DW/BI team must keep them current and in good working order. Individual reports often go stale as the business evolves. The reports created to track a new product are no longer interesting once the product has been discontinued. Reports may fail for technical reasons; for example, you may make enhancements to a database that cause a report to fail but not realize it unless you are monitoring the report server logs and regularly inspecting the results.

The DW/BI team must add and delete data-driven subscriptions that involve individual users and e-mail lists as people come and go. The same goes for other distribution mechanisms, such as file shares. Computers and networks change. The accounting department may have requested a set of reports distributed to its file server. Then it gets a new file server and turns off the old one without telling you, so you have a group of users not receiving their requested reports.

Extend the Applications

The DW/BI team also must provide ongoing report development resources expecting that the initial reports and BI applications for a new business process dimensional model will soon be modified and augmented. Users don't always know what reports and analyses they want until you show them something close. Then they'll tell you what they don't want — the report you just created — and hopefully give you clearer guidance about what they now think they need.

Data mining applications and other closed-loop systems are seldom implemented in the first phase of a DW/BI system (unless they generate the return in the return-on-investment analysis). The process of developing a closed-loop BI system requires close partnership between the business people, who can effectively develop the business rules and analytic models, and the DW/BI team, who will write the system specifications and formalize the models. The majority of the application development effort requires a standard skill set, which is often met by the same developers who work on the operational systems. The developer needs a relatively small amount of specialized knowledge — the object model for the data mining system — to implement the calls into the databases or data mining model.

Every 12 to 18 months, review the entire business intelligence system. Evaluate what's working well for users and what should change. Remember, change is inevitable and is a sign of a healthy system. As part of this periodic evaluation, consider refreshing the look, layout and content of the BI portal.

Warren Thornthwaite is a member of the Kimball group and coauthor of The Microsoft Data Warehouse Toolkit (Wiley, 2006). Write to him at [email protected].

Required Reading

"Standard Reports: Basics for Business Users" (February 2006)

"Kimball Design Tip #68: Simple Drill Across in SQL"

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