Building and Delivering BI Reports - InformationWeek

InformationWeek is part of the Informa Tech Division of Informa PLC

This site is operated by a business or businesses owned by Informa PLC and all copyright resides with them.Informa PLC's registered office is 5 Howick Place, London SW1P 1WG. Registered in England and Wales. Number 8860726.

Software // Information Management

Building and Delivering BI Reports

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

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.

We welcome your comments on this topic on our social media channels, or [contact us directly] with questions about the site.
1 of 2
Comment  | 
Print  | 
More Insights
InformationWeek Is Getting an Upgrade!

Find out more about our plans to improve the look, functionality, and performance of the InformationWeek site in the coming months.

Blockchain Gets Real Across Industries
Lisa Morgan, Freelance Writer,  7/22/2021
Seeking a Competitive Edge vs. Chasing Savings in the Cloud
Joao-Pierre S. Ruth, Senior Writer,  7/19/2021
How CIO Roles Will Change: The Future of Work
Jessica Davis, Senior Editor, Enterprise Apps,  7/1/2021
White Papers
Register for InformationWeek Newsletters
Current Issue
Monitoring Critical Cloud Workloads Report
In this report, our experts will discuss how to advance your ability to monitor critical workloads as they move about the various cloud platforms in your company.
Flash Poll