Solution Overview

To address the police service's need to easily communicate their strategy across the organization, we will build an intranet-based portal to publish an up-to-date scorecard that spans all the business areas. The scorecard will communicate progress on all the key areas and allow management to publish related information.

Business Requirements

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

• Communicate the strategy to all parts of the organization. Building additional reports that simply include even more information is not sufficient to meet the police service's needs: To really be effective, the proposed solution must include a way to communicate the strategy across the whole organization and must enable every member of the police service to see the performance in the areas where they have an impact.

• Include all aspects of the strategy. The proposed solution must include all four main aspects of the police service's strategy:

  • Crime reduction. The primary goal is to reduce the levels of specifically targeted types of crime in certain regions of the city.

  • Community relationships. One clear measure of the condition of the police service's relationships with the various communities is the number and type of complaints that they receive.

  • Internal processes. A key focus area is how well they handle calls to the emergency 911 service, because this has a major effect on the way that the public views the police service. This includes everything from how long the operator takes to answer the call through to the arrival on scene of the dispatched employees.

  • Professional standards. To achieve their goals in the other three strategic areas, the police service needs to improve the training that they are providing to their officers.

High-Level Architecture

The existing data warehouse and Analysis Services database will be extended to cover the new subject areas outlined in the business requirements, and new Integration Services feeds will be built to load the information. Any common dimensions across different business areas will be identified and conformed to allow cross-functional reporting. Reporting Services will be used to create the components of the scorecard such as charts and other graphical reports directly from the Analysis Services cubes (see Figure 9-1).

Figure 9-1. High-level architecture

Instead of using standard Web development tools such as HTML or ASP.NET pages to build the portal, we will use Windows SharePoint Services (WSS) both to reduce the complexity and cost of development and to provide a number of additional features. WSS is a server-based component of Windows Server 2003 and is used for building information sharing and collaboration applications.

WSS is based on the idea of web parts, which are reusable components that can be flexibly assembled on a page to build dashboards or scorecards without any programming. Reporting Services reports can be hosted in these web parts, along with other types of content such as announcements or other Web pages. Users can also publish documents to the portal from within Office applications, making it much easier to disseminate information.

Alternative Solution: Build Reports Directly on the Relational Tables

Instead of using an Analysis Services database as the source for the reports, we could have chosen to build the reports on top of the relational tables using standard SQL. In this solution, as in most Business Intelligence (BI) solutions, there are a number of compelling reasons to include an Analysis Services database. In addition to the performance increase from using on-line analytical processing (OLAP) rather than relational database technology, Analysis Services databases can include some flexible and advanced features, such as parent-child dimensions that can easily be queried, and key performance indicators (KPIs), as you will see in this chapter.

Business Benefits

The solution will deliver the following benefits to the client:

• Increase the ability to communicate their strategy across the organization, with enough flexibility that the information can easily be updated as progress is made

• Create a common set of measures that is understood across the whole police service, so that everyone has a clear and up-to-date understanding of his or her performance