Chapter 9. Reporting Services
|
The first phase in implementing a solid IT infrastructure is to develop a data management strategy. The organization needs to know what data is stored, how it is stored, where it is stored, for how long it is stored, and a description of its security. The second phase of the infrastructure implementation is the reporting layer. Using a solid data foundation allows the organization to form a reliable, consistent way to report data from all its sources. That is the focus of this chapter. The third layer, which I cover in the next chapter, is analytics. Among all the disciplines in database technology, the one that crosses the most skill lines is reporting. To implement a complete reporting system, you need people who know the data and the best ways to access it, infrastructure talent, and people gifted with design. Reporting touches every part of the data cycle. From online transactional processing (OLTP) to online analytical processing (OLAP) systems, reporting interfaces with the organization's data at every stage. One of the most common problems in reporting infrastructures is showing different results on reports that are developed from the same base data. This occurs because so many stages are involved, and sometimes coordination between them becomes an issue. As the database administrator (DBA), your part of this process is to manage, secure, and maintain the reporting system. In days past, the reporting system was separate from the database layer. Microsoft SQL Server 2005 now includes a full HTTP-based reporting system that you can use to fulfill both the OLTP and OLAP requirements for data output to screen, printer, or other binary formats such as Excel and PDF files. Users can access reports over using their browser, an application, or even have reports delivered to them. In this chapter, I show you what you need to know to install, manage, and monitor a reporting system. In the "Take Away" section at the end of the chapter, I show you how to connect a report to an OLAP system, something you will use in the next chapter. Reporting Services, much like SQL Server Integration Services (SSIS), is less a single product than a full suite of features based on the SQL Server 2005 platform. It is a complete reporting system, enabling you to design, create, manage, and distribute reports. There are a lot of components in Reporting Services, each capable of multiple configurations. I have found that the best approach for understanding Reporting Services is to explore the architecture and then examine the aspects of what the product can do. I spend a little time explaining how to design and create reports and then more on how you can distribute them and manage the entire application. In this chapter, I cover some, but not all of the things you will be called on to do. Just like in SSIS, as the DBA you will not normally be called on to design and develop full reporting applications. In fact, because reports have so many capabilities, your developers will (read that should) have dedicated at least one resource to creating them. Those development professionals can use the Business Intelligence Development Studio, which is a full design environment, power users can arrange reports based on a model created for them using a more simplified interface in the Web-based Report Builder, developers can use a full report development suite in Visual Studio, and everyone can create reports using a special application of XML called the Report Definition Language (RDL). We take a quick tour of all of these methods in this chapter. That is not to say the DBA does not ever create reports, but in complex environments, it is often a full-time job just managing and keeping up with them. Where your talents will come into play is in assisting the users, report professionals, and developers create reports that access the data in the most efficient way. You will need to explain to them how the architecture works, what the data flow looks like, and how security fits into the whole picture. You can run Reporting Services on every SQL Server 2005 edition starting with the Express; to get higher security and performance features, however, you want to install the software on the Standard Edition or higher. You can use all of the editions as a source of data for your reports, and other sources such as XML files and other database systems can be accessed as data sources. Before you start working through the examples in this chapter, make sure that Reporting Services is installed on your test server. If you chose the same options shown during the installation exercise in Chapter 1, "Installation and Configuration," you will already have an instance of Reporting Services running. If not, you can put the installation media in the drive and walk through the installation screens to add it. You can also use the Add or Remove Programs applet in the Windows Control Panel to add features to SQL Server 2005, but you still need to have the CD or other SQL Server 2005 source ready. Just check the boxes for Reporting Services; the installer will do the rest. In either case, when you install the feature, you are asked whether you want to create the standard configuration for Reporting Services. If you select that option, the installer creates all the services, directories, Web sites, and databases you need to run the product. If you do not choose that option, after you have installed the feature, you can tailor the settings for these objects. You will need to create a Web site, secure the file locations, and choose a service account and other options before you can process reports. I walk you through the screens where you bring all those settings together in Reporting Services, but read through the entire chapter first so that you can decide on the layout that works for your situation.
Although you can install everything on one system, in production this is not a good idea, for performance reasons. I explain the various layouts a system can have a little further on in the chapter. For now, you can set up your test system to have all components. On my test system, I have taken the default configuration of Reporting Services, and I am running Windows 2003, Enterprise Edition. Because Reporting Services depends on an HTTP server, you will run into differences based on the version of Internet Information Services (IIS) you are running. The Windows 2000 version of IIS has fewer security features that interact with Reporting Services, and so some settings differ. I point those out in the "Management" section a little later. To allow access to the system and secure Reporting Services, you need to open the SQL Server Surface Area Configuration (SAC) tool. In the following screen, I have navigated to the Surface Area Configuration for Features area of the tool and set my system to allow HTTP connections. In fact, on this system I have opened up all the options for Reporting Services. I have allowed HHTP access, and I also selected Windows Integrated Security for authentication. Make your choices here based on the level of security you require. You can see my settings in Figure 9-1. Figure 9-1.[View full size image] 
Next, navigate to the Microsoft SQL Server 2005 item in the Windows Start menu, open Configuration Tools and look for the Reporting Services Configuration item. Click that to start configuring your server, as shown in Figure 9-2. Figure 9-2.[View full size image] 
You are asked to connect to the Reporting Services instance name. When you enter that information and connect, you are presented with a step-by-step set of tasks to configure your Reporting Services instance, as shown on the left side of Figure 9-3. If you took the defaults on installation, your system will already be configuredat least for the most part. Figure 9-3.[View full size image] 
On this system, I have two more tasks to complete, indicated by yellow icons. For now, I close the tool, but we will revisit it in the "Management" section a little later. To verify that you can access your Reporting Services instance, open a Web browser and enter the following string: http://SERVERNAME/Reportsor http://SERVERNAME/Reports$INSTANCENAMEReplace SERVERNAME with the name of your server, and INSTANCENAME with the name of the SQL Server 2005 instance where you installed Reporting Services if you installed a named instance of SQL Server. If all goes well, you will see a screen similar to Figure 9-4. Figure 9-4.[View full size image] 
If you do not see this screen or something like it, first check your spelling. You may have the instance name wrong. You can open the IIS Manager in the Administration Tools from the Windows Start menu to see whether the Web site name shows up there. You may also have to preface the Web site with https:// rather than http:// if your IIS system has been secured to use Secure Sockets Layer (SSL). If you still cannot seem to make it work, make sure your Web server is installed and running. Try entering just the name of the server in a Web browser. If that does not return anything, check the IIS configuration. Correct that issue, and then run the Reporting Services Configuration tool again, and verify the operation there. With all of that corrected, you should be able to see the Web site open up. When you are able to see the Reporting Services Web site, we are ready to start. |
