Building and Delivering BI Reports

Here's how to build, test and deploy standard reports for key business processes.

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]

Quick Study

BI application standard reports are the primary vehicles for delivering business intelligence to the vast majority of users (see February's "Standard Reports: Basics for Business Users," for a detailed design process). Once you have real data available, build your starter set of standard reports for each business process dimensional model using the following approach:

  • Install your front-end tools and set up the development environment.
  • Create a standard template with description fields, datasets and styles.
  • Build the reports working with the production database.
  • Test each report to validate its contents, interactions and performance.
  • Deploy the report set out to the production system.
  • Maintain and enhance the reports to keep them fresh and relevant.
  • Extend the BI application set to include data mining and closed-loop operational BI.

Report Replication: A Risky Idea

It is common for a DW/BI team to replicate a set of reports from an old system into the new reporting environment. Although this may be necessary and justified because it will let you turn off the old environment, the replication of existing reports rarely brings much perceived value. All you are giving users is what they already had. It is also risky, since the old reports often have embedded business rules that are complex and undocumented. Accurately replicating reports will be much harder than you think.

If you must replicate a set of existing reports, work with your business users to identify the most important legacy reports, but incorporate new reports that stimulate interest and offer more business value.

Get Users Involved in Building Reports

If you have users who know the front-end tool or are capable of learning it quickly, the BI application development process is a great opportunity to get them directly involved in the DW/BI system. There are several good reasons to get key users to participate. First, it gives these users an opportunity to learn the tools, techniques and data as early as possible. Second, working together helps build stronger relationships, especially if you physically bring the group together. If it's at all possible, set up a development lab with as many workstations as you need. Schedule a week or two for the group to meet daily for a half-day (or all day if your organization can handle it). Bring food and maybe even some kind of gift to show your appreciation. The early involvement of these key users emphasizes their special status and it builds their ownership of the reports and the overall DW/BI system.

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