Designing a Dashboard with PerformancePoint Services

In “PerformancePoint Services: A Different Point of View,” I introduced you to PerformancePoint Services, which is now part of SharePoint 2010. In this article, I’ll show you how to use PerformancePoint Services’ Dashboard Designer to design a dashboard that has a scorecard, an Excel Services report, a strategy map report, and two filters. The Excel Services report will give users access to a PowerPivot workbook. The strategic map report will feature a geographic map that dynamically displays data.

To build this dashboard, you first need to create the dashboard elements (e.g., the scorecard, the Excel Services report). Then you need to create the dashboard that will host those elements.

Creating the Scorecard

The dashboard’s monitoring system is based on the scorecard and its indicators. The scorecard is made of Key Performance Indicators (KPIs). They are represented by color-coded graphics that quickly convey that things are going well (green), average (yellow), or bad (red). Both the KPI graphics and the color codes are configurable.

In the scorecard, there is a feature that provides the current KPI value, which reflects the actual business activity (e.g., actual sales). This figure is read from the storage system since it is gathered from the transactional systems and shows a reality that already occurred. There is also a feature that provides the target KPI value, which reflects the value that must be reached in the business process (e.g., sales quota). The target is usually set by the CIO or the business manager and is linked to a business strategy.

There are some additional features you can use in a scorecard. For example, one feature provides a score that reflects an assessment of the current KPI value versus the target KPI value. Another feature shows trends based on several KPI values over time.

You can also configure the scorecard so that users can input values to determine possible outcomes. One way to achieve this is by using Excel’s What-If Analysis feature, which enables you to write back directly to cube, and the writeback functionality found in the Enterprise Editions of SQL Server. The data insertion can be done in multiple ways, although not many tools support that functionality. Among the tools that support it are Excel 2010’s What If Analysis feature and ProClarity-tailored developments using Multidimensional Expression (MDX) and the UPDATE CUBE statement.

For example, suppose that you need to create a dashboard for the managers in your company’s Sales department. Figure 1 shows the managers and other members of that department. In the dashboard, you need to create a scorecard that has two KPIs: sales and sales quota. The sales managers need to be able to modify quotas in the scorecard to assess different scenarios when planning their budgets. This needs to occur without the involvement of the IT team once the proper settings have been configured.

Figure 1 The Sales department staff

The first thing that you must take into account is the allocation method. There are two options: using an equal allocation or using an allocation defined by weighting coefficients.

Suppose that the Sales department has a total sales quota of $10 million and you want an equal allocation among the leaf-level members of the Sales department. When you divide the total sales quota by the 18 leaf-level members, the sales quota for each member is $555,555.56, as Figure 2 shows.

Figure 2 Equal allocation of the quota

The other option is to assign each Sales member’s quota based on a weighting coefficient. Suppose that the individual quotas are weighted based on both job position and region. First, you need to post the weighting coefficients (in this case, .12, .12, .25, .50, .01) in the same cells used for the quota assignment, as Figure 3 shows.

Figure 3 Assignment of the weighting coefficients

Next, you need to assign the total sales quota. In this particular example, the Sales department has a total sales quota of $200 million.

The last step is to modify the What-If Analysis settings so that the weighting is taken into account. In the Allocation Method section, you must select the Weighted Allocation option, as shown in Figure 4. If you have an MDX returning the weighting coefficients, you can enter it in the Weight Expression text box. Otherwise, the current weighting coefficients in the cells will be used.

Figure 4 Configuring the What-If Analysis

Figure 5 shows the results. As you can see, the amount is apportioned according to the weights previously configured.

Figure 5 Quota allocation defined by weighting coefficients

This method will let the sales managers modify the quota values, without having to ask the IT team for assistance. They can change the quotas and weighting coefficients at their convenience and immediately can see the outcome. It is important to note that the changes can be published to the cube. However, this operation is resource-consuming, so the IT team will need to carefully consider the impact on the other operations that will be running.

Figure 6 shows an example of a completed scorecard. In it, you can define several quotas and specify which particular quota you want to compare with the current one. You use the Data Mappings column to specify the data sources for the quotas. In this example, I chose Adventure Works DW’s Reseller Sales Amount measure for the current quota and Adventure Works DW’s Sales Amount Quota measure for the target quota. You can use the Number Format column to format the KPI values and the Calculation column to select a calculation type when a scorecard includes a hierarchy of KPI calculations.

Figure 6 Example of a Completed Scorecard

If you click any of the quotas in the scorecard, the Thresholds options pop up. Just like in the previous versions of PerformancePoint Services, this is where you can configure the KPI graphics and color codes, the band thresholds, and the trend indicator.

Creating the Excel Services Report

Reports supplement the information displayed by the scorecard. The first report I’ll show you how to create is an Excel Services report that lets you gain access to a PowerPivot workbook stored in the PowerPivot gallery for SharePoint.

To begin, select the Excel Services report template in the Dashboard Designer, as Figure 7 shows.

Figure 7 Selecting the Excel Services report template

After you name and publish the report, you just have to browse the Document Library and select the PowerPivot workbook that you want to use in the report. At this point, you have the option of choosing the items you want displayed, such as defined tabs or reports independent from the PowerPivot sheet. However, I recommend not specifying any items now because what you want displayed will depend on the settings you select when you publish the PowerPivot workbook in the SharePoint gallery. So, simply save the PowerPivot workbook, and then use the Dashboard Designer’s editing window to open the workbook, as Figure 8 shows.

Figure 8 Using the Dashboard Designers editing window to open the PowerPivot workbook

The report created and stored in the PowerPivot library consists of two slicers with four filters:

A vertical slicer with the title (representing the area manager in the department hierarchy) and the employee’s complete name
A horizontal slicer with the year and quarters

In the graph, the vertical axis shows the sales in blue and the budgets in red, as Figure 9 shows. This graph is based on a dynamic table that has the sales and sales quotas ordered in columns and the time dimension in rows.

Figure 9 Graph showing sales and sales quotas by quarters

Changing a slicer affects both the graph and the dynamic table. For example, Figure 10 shows how the graph and table are affected when you select the year “2005” and the area manager “North American Sales Manager.”

Figure 10 The effects of changing a slicer

All the filtering functionality in the report remains after it is integrated into the dashboard as well as after it is integrated into the SharePoint presentation.

Creating the Strategic Map Report

The strategic map report has a rather special purpose: It dynamically displays the sales and sales quota data in given regions or countries in a strategy map. Like the scorecard, the strategic map lets the sales managers modify quotas.

The strategy map needs to be built in Visio 2007 or later. Once built, it is accessible in the strategy map report by means of the Import Visio File button. The Visio map drawing I used is downloadable for free at http://www.visguy.com/visio-links/shapes. To download it, you need to register using a valid email address.

As I mentioned previously, the dashboard’s monitoring system is based on the scorecard and its indicators. In fact, to be able to include a Visio strategy map, you need a scorecard. Thanks to the scorecard, you can link KPI values with zones (e.g., country or region) in the Visio map, as Figure 11 shows.

Figure 11 Linking a KPI value with a zone in a strategy map

After each relevant zone has a link and the report is published, you’ll have a map like that in Figure 12. When the KPI data is modified, the map will be dynamically updated and display the appropriate color, depending on the KPI scoring.

Figure 12 Completed strategy map

You need to include two filters in the dashboard. The first one filters the data by time period. The second one filters the data by the geographic area in which the sales managers work. I’ll refer to them as the time and geography filters, respectively. The Member Selection filter template is used to create both filters, as Figure 13 shows.

Figure 13 Using the Member Selection filter template

Moreover, as Figure 14 shows, each filter will be based on (1) the data source, (2) the dimension and members, and (3) the display method (List, Tree, or Multi-Select Tree).

Figure 14 Configuring the filters

Creating the Dashboard

Now you need to create a dashboard that will host the scorecard, reports, and filters you just created. The dashboard will have two pages. The first page will contain the scorecard and the strategic map. The second page will contain the PowerPivot report. The first page needs to consist of four sections that have the distribution shown in Figure 15. The second page needs just one section, as Figure 16 shows.

Figure 15 The first page of the dashboard

Figure 16 The second page of the dashboard

After the sections are in place, you just have to drag the scorecard and reports from the Details pane to the desired sections. Figure 17 shows the Details pane.

Figure 17 The Details pane

The next step is to drag the time and geography filters to the scorecard section so that they interact with the scorecard. You need to configure the time filter’s connection so that the Member Unique Name value affects the columns in the Calendar hierarchy, as Figure 18 shows. When the connection is set up this way, the values in the scorecard will be conditionated by the filter selection. Similarly, you need to configure the geography filter’s connection so that the Member Unique Name value affects the rows in the Employee Department hierarchy, as Figure 19 shows.

Figure 18 Configuring the connection for the time filter

Figure 19 Configuring the connection for the geography filter

Once the dashboard is ready, you can deploy it, assuming you have the necessary permissions. Right-click the bottom of the dashboard and select Deploy to SharePoint. By default, it will be saved in the PerformancePoint content library of the specified site. You can change the library in the dashboard’s Properties page, as Figure 20 shows. You can also change the master page template, add the dashboard to the page navigation list, and add other properties.

Figure 20 The dashboard’s Properties page

After deploying the dashboard to SharePoint, you will have the entire solution stored in the same site. However, SharePoint allows you to customize a PerformancePoint content site by adding Web Parts, which makes the final layout flexible and lets SharePoint elements interact with PerformancePoint elements. Figure 21 shows the completed dashboard.

Figure 21 The completed dashboard

Summary

In this article, I’ve shown you how to create a dashboard that uses a strategy map report in a not-so-common way. The map colors change according to the linked KPI scoring. This is accomplished by including a geographic map prebuilt in Visio. I also showed you how to include a PowerPivot workbook in the dashboard by creating an Excel Services report. Perhaps more important, you now know how to configure a scorecard in which users can input values to determine possible outcomes. As you saw, this is achieved by using Excel’s What-If Analysis feature and the writeback functionality in SQL Server.

The individual elements are impressive, but not as impressive as the completed dashboard. With this user-friendly tool, sales managers can quickly assess how the Sales department is doing as a whole, then explore how individual staff members are doing—all without having to ask the IT team for assistance.