Solution Overview

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.

Business Requirements

The high-level business requirements to support the objectives are as follows:

• Managing projects Each project that this company undertakes has a budget that is agreed up front with the customer. Project managers need up-to-date weekly reports so that they can ensure that the project is delivered within budget, which will increase customer satisfaction and lead to the most profitable type of work, namely contract extensions and repeat business. Also, project managers must ensure that they are on target to hit the planned budget by the end of the project, to ensure that the services business meets its forecasted revenue.

• Managing utilization All consultants have target utilization levels that they must meet for the business to succeed. Consultants and their managers need to see up-to-date information on their utilization levels so that they can take action early enough to hit their goals.

• Managing the business The business operations team needs access to strategic business measures so that they can report monthly information to management. They urgently require information on billed revenue versus planned revenue, utilization, average billing rates (ABR), sales pipeline, and backlog, with a need for more measures in following phases of the project.

High-Level Architecture

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.

Alternative Solution: Reporting against the OLTP Timesheet System's Database

One option that is often popular with clients is to build reports directly against the existing application database, under the mistaken assumption that by skipping the step of building the data warehouse they could drastically reduce the cost for the project. Unfortunately, it's never that simpleone issue is that the time to develop even the initial set of reports would be increased quite considerably because the report developer would need to deal with the complexities of the normalized OLTP schema, which would increase the cost of the report development section of the project. This cost increase would also be ongoing, because every report developed in the future would face the same issue. Any changes to the underlying source system may affect the reports rather than just affecting a clearly defined interface between the source system and the data warehouse.

Also, the information required to make sensible decisions about a business is rarely contained within a single application; in this case, forecasting numbers and sales targets are located in various spreadsheets and can't be included. And most important of all, report queries typically reference large amounts of data, and because OLTP databases are typically optimized for single-row writes and not multi-row reads (as discussed in early chapters), the performance impact on the timesheet system is likely to be too great. (Although it's difficult to give good impact estimates for this kind of question when pressed, the best answer is usually the standard BI consultant's response: "It depends.")

Business Benefits

The solution will deliver the following benefits to the client:

• Increase customer satisfaction, because of better project management leading to more projects that are delivered within budget

• Decrease administrative costs by automating time-consuming manual business operations reporting

• Increase revenue by enabling consultants and their managers to plan to meet their utilization targets