We will consolidate the data from the timesheet system and other sources into a new SQL Server data warehouse, and use Reporting Services to deliver predesigned reports. Users can access the reports from the intranet either on-demand using a Web browser or subscribe to the reports so that relevant reports are e-mailed to them automatically when the data is ready.
The high-level business requirements to support the objectives are as follows:
The solution has two main components: the data warehouse database and the reporting server to publish reports. We will be loading the data from the timesheet system on a periodic basis using Integration Services, and the data warehouse will also include information from other sources such as forecasting spreadsheets. The reporting server will run Reporting Services, which is essentially an ASP.NET application and web service.
Unlike older client/server reporting environments that require a client application to be installed on every machine, Reporting Services is a server-based reporting solution, as shown in Figure 6-1. One way to understand this approach is by comparison with ordinary Web development. For regular Web applications, HTML and ASP files are created using a designer and then published to a Web server, which then executes any server instructions in the files and serves up the resulting information to client machines' Web browsers. Similarly for Reporting Services, reports are designed using a graphical designer such as the report designer in BI Development Studio, and then published to the reporting server. When a request for a report is received by Reporting Services, the reporting server then retrieves any data required and renders the results for display on the client.
Figure 6-1. High-level architecture
Users can access reports using a Web browser and can also export reports into other Office applications such as Excel to work with the numbers, or export to printable formats such as PDF. Although Reporting Services is designed so that reports can be integrated into other applications such as intranet portals (see the sidebar "Integrating Reports into Other Web Sites or Applications"), we will be using the Report Manager Web-based application that ships with SQL Server. It provides all the features we need, such as browsing a list of reports and securing reports for specific audiences.
Building standardized reports against a properly architected data warehouse has many advantages and is usually the best solution for the long-term information requirements of a client. The solution can be extended in future iterations to include information from more data sources in the data warehouse or to provide information in more flexible ways for users or applications that require it.
Typically for this kind of solution, we would suggest building an Analysis Services database and then writing reports against the cubes as described in Chapter 9, "Scorecards." However, because this customer doesn't have any experience with building or managing Analysis Services databases, we focus on providing some high business-value reports against the relational data warehouse and leave the more flexible Analysis Services approach for a follow-up phase.
The solution will deliver the following benefits to the client: