Summary

In this chapter, we built a new data warehouse focused initially on the key areas of the professional services business, with data loaded from the timesheets system and forecasting spreadsheets, and provided the information to users through a set of Web-based reports. The business operations team now gets reports delivered to them weekly rather than spending time using spreadsheets to manually produce information from various sources. Project managers and consultants use the Report Manager Web-based application so that they have easy access to reports that show the current situation; they can also print the reports or export them in formats such as Excel or PDF.

We decided to build a data warehouse instead of reporting directly against the OLTP timesheets system because the reports need additional information from other sources, and to reduce the complexity of report designs and avoid any performance impact on the timesheets system. We decided to use the standard Report Manager Web-based application to publish reports because it provides all the features required for this solution without doing any additional Web development.

We used a shared data source definition in the report project so that it can be changed in one place for all reports if necessary. The data source was configured with a specific Windows account with the minimum permissions required to access the database, because this approach works well for accessing data on different servers and also for automated processes like report snapshots and subscriptions.

We secured the reports by restricting access to specific groups of users, including using linked reports to provide more limited views of data for some groups. Report caching was used to improve the performance by avoiding the need to hit the data warehouse every time, and we also used report snapshots to run some key reports overnight and provide a historical view. We used the subscriptions feature to send Excel versions of the reports to a file share, and a data-driven subscription to e-mail tailored reports to all project managers and consultants.

We deployed the reports to a separate reporting server rather than using a single combined database and reporting server, using the rs utility and a custom script. Key operations tasks now include managing long-running reports and backing up the report server databases in addition to the data warehouse.