Chapter 9: Building the BI Application in Reporting Services

Building a bridge for those who dont want to swim

Overview

Every user of your DW/BI system will access BI applications and standard reports. The vast majority of themtypically between 70 and 90 percentwill use only standard reports. To those users, the standard reports are the DW/BI system. After working through the database design, ETL system requirements, and OLAP database design, creating reports seems easy. If your designs are driven by business imperatives, creating reports is easy. But you wont know youve succeeded until you actually create some.

Reporting Services is Microsofts offering for enterprise reporting. It was first released in January 2004 and has moved through a couple of development iterations since then. Reporting Services has been well received by the Microsoft customer base and has been successfully implemented in many large organizations. Given its reasonable level of functionality and its more than reasonable incremental cost, we expect that a large percentage of folks reading this book will choose Reporting Services as the delivery vehicle for their standard reports and analytic applications.

This chapter provides the basic information you need to build your initial set of standard reports. We start with an exploration of general business requirements for standard reporting and the architectural implications of those business requirements. We then examine the Reporting Services architecture to see how it matches up to the business requirements and to learn its terms and terminology.

Note 

As we described in Chapter 8 , analytic applications are a more sophisticated form of standard reports. Your initial set of standard reports may include some analytic applications, but that doesnt matter at this point. For the sake of simplicity, we refer to the general class of official, predefined reports as standard reports.

As Figure 9.1 illustrates, this chapter addresses the BI application development step of the Lifecycle methodology based on using Reporting Services as the application platform.

Figure 9.1: The BI application development step

The bulk of the chapter describes how to work with Reporting Services to develop standard reports. We start with the usual planning and preparation stepsin this case, setting up the environment and the report templates. Then, we create one of the standard reports for Adventure Works Cycles based on the prioritized target report list from Chapter 8. Finally, we discuss the creation of a navigation portal to provide the framework for presenting these reports in a simple BI portal.

By the end of this chapter, you should be able to answer the following questions:

• What is Reporting Services? How does it work, and where does it fit in the overall DW/BI system?

• What tools does Reporting Services provide for report development?

• What does it take to create a standard report in the Reporting Services environment?

• Why is the BI portal important, and what information should be included in the portal? How can you design the portal so its easy for users to understand, and easy for you to maintain?

LEARNING REPORTING SERVICES

This chapter is not a tutorial on Reporting Services. If youve been charged with the task of building the initial set of reports, you should first install Reporting Services and the examples on your development machine. Then review the documentation in Microsofts Books Online and work through the tutorials. You may also want to get one of the many Reporting Services books available, or even take one of the many classes offered in Reporting Services.