Creating the Scorecard
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.
- PerformancePoint Services: a different Point of View - February 21, 2012
- Designing a Dashboard with PerformancePoint Services - September 22, 2011