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.

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.

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.

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.

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 5 shows the results. As you can see, the amount is apportioned according to the weights previously configured.

 

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.

 

Creating the Excel Services Report

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

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.

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.

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.”

 

 Creating the Strategic Map Report

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.

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.

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.

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).

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.

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.

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.

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.

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.

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.