In this part
A scorecard can be thought of as the business version of a school report card: it’s a summary of how the business has performed over the last period, shown as numbers for key performance areas (KPAs), which are aggregated from key performance indicators (KPIs). A KPI is loosely similar to the subjects on a school report card, but generally a scorecard’s KPIs have graphical indicators and interactive capabilities (unlike a school report card).
Scorecards have been popularized in recent years through the widespread adoption of Performance Management. Starting in the management consultancy McKinsey & Company in the 1930s, Performance Management as a discipline advocates building a measurable strategy to grow a business and identifying key performance indicators to measure various aspects of the business. Key performance indicators, roll up into key performance areas, and these are put together in a visual way in a scorecard, typically using “robot” indicators of red, yellow, and green to indicate whether targets are being met. On the Microsoft platform, Business Scorecard Manager evolved into PerformancePoint as the tool of choice for displaying these scorecards and other visualizations together in dashboards.
This chapter discusses how these tools can be used both for Performance Management and for business intelligence (BI) in general.
The main reason for implementing a scorecard system is to enable quick analysis of key metrics—the ability to view a metric (be it a KPI, an operational metric, or indeed any metric in your business) and either move on when values are within predefined bands, or start an investigation when the values are out of band. This definition often sits outside the traditional scorecard and dashboards arena, but is a very important and growing area for these tools.
Some key design features to enable this are vital—the details vary from tool to tool, but the basics are the same:
KPIs are the heart and soul of scorecards. In its simplest form, a KPI is an actual value, measured against a target, and assigned to a band by the distance from the target. The reality of choosing KPIs for an organization’s strategy is that the process is long, complicated and fraught with difficulties—for instance, measuring customer service representatives by how long they spend on the phone with customers (what appears to be an easy way to drive down costs) can have grave effects.
In this book, it’s assumed that you’ve gotten all of that perfect, and you need to master using the technology.
The other piece of this puzzle is that technologies allow you to do much more than simply assign an actual, a target, and bands to a KPI. For instance, in PerformancePoint you can add both dimension and time filters, and have multiple actuals and targets—for example, actuals of Today, Month-to-Date, Year-to-Date, with targets of This-Day-Last-Year, Last-Month-to-Date, and Last-Year-to-Date. The bands are the range of values that are considered good, acceptable and poor. For instance, 120 percent and above against the previous year would be good, anything above 100 percent of the previous year would be acceptable, and anything lower than the previous year would be poor. In addition, the drilling functionality that comes out of the box adds a lot of depth to the scorecards.
Reporting Tool | Predefined Chart Type | Ease of Development |
Excel | Yes | |
PerformancePoint | Yes | |
Power View | Yes | |
Reporting Services | Yes | |
Silverlight/HTML5 | N/A |
Drill down is the process of starting analysis at a high level, with aggregated data, and narrowing the focus to show detail data. This is typically done using a list of higher-level dimension items with a drill-down plus sign next to each and inserting the more detailed items between the drilled member and the next member. Lower levels are indented to distinguish them. A minus sign allowing you to drill up is usually included as well. For instance, in a retail environment, the aggregated levels may be states or regions, showing sales figures, and drilling down allows you to view individual stores.
Reporting Tool | Predefined Chart Type | Ease of evelopment |
Excel | Yes | |
PerformancePoint | Yes | |
Power View | Yes | |
Reporting Services | Yes | |
Silverlight/HTML5 | N/A |
Drill through, although it has a similar function to drill down, is usually implemented as a double-click, or a click on a link, that takes you to a different report or worksheet to get more details about the KPI. Drill through is very often used to go to view the individual transactions that make up the values on a scorecard.
Reporting Tool | Predefined Chart Type | Ease of Development |
Excel | Yes | |
PerformancePoint | Yes | |
Power View | No | |
Reporting Services | Yes | |
Silverlight/HTML5 | N/A |
Drill across is aimed at obtaining additional information about the dimension member. For example, when viewing the sales figures in a scorecard that has a store drill-down hierarchy, you may want to see the breakdown by product category and include a profit margin.
You accomplish this by linking the scorecard to another item on the same page—a chart or a table of values—and filtering this item by the dimension value that’s been clicked in the scorecard.
Reporting Tool | Predefined Chart Type | Ease of Development |
Excel | No | |
PerformancePoint | Yes | |
Power View | Yes | |
Reporting Services | No | |
Silverlight/HTML5 | N/A |
Scorecards can be built with all of the Microsoft tools. PerformancePoint is designed around scorecards, but with SQL Server 2008 R2, scorecard functionality was added to Reporting Services. Excel has always been able to build scorecards and indicators, but has had this functionality natively since Excel 2010. In the following sections you learn in detail about the capabilities of each tool.
PerformancePoint excels at scorecards, as it is aimed squarely at this segment of BI. A key point, however, is that PerformancePoint works immensely better when combined with Analysis Services or PowerPivot Services—the functionality around analytic charts, hierarchies for drill down in scorecards—which are all based upon having the semantic layer already built.
PerformancePoint dashboards can scale from the simplest—a single traffic light visualization (red/yellow/green) used on an operational page, as shown in Figure 10-1—to a completely interactive dashboard that has multiple pages, several filters, a scorecard driving an analytic chart and another scorecard, as shown in Figures 10-2, 10-3, and 10-4.
The true power of PerformancePoint is that the connections between these components are completely developer driven—having some filters only affect a selection of components on the page is a great tool to have in your arsenal. Another useful advantage is that the filters remember the selection of each user—allowing them to seamlessly move from page to page and continue analyzing the same selections.
The following is a list of some key design guidelines to work with in PerformancePoint:
Figure 10-5 shows an example of these.
Excel in Office 2013 has become a very powerful dashboard tool. Although it has some limitations compared to the power of PerformancePoint in terms of the control over the connections, Excel still has a very simple and intuitive interface.
Excel includes KPIs, with the sole limitation that you must use the built-in icons. PerformancePoint enables you to import your own indicators.
Excel is much more customizable than PerformancePoint when it comes to appearance. You can fairly extensively customize a basic pivot table, as you can see in Figure 10-6. When further customization is required, you can use cube functions, which allow for total control, as you can see in Figure 10-7.
The design principles for Excel are a little different, due to the limitation on filters that a standard filter can only be along the top of the screen, and the fact that slicers are inherently larger than the filters. The guidelines are the following:
In this section you learn how to implement a scorecard in Excel (using PowerPivot) and in PerformancePoint. You also learn how to implement indicators in PerformancePoint using your own images in place of the standard ones.
To follow along with the samples, you will need to install the samples, which are available on this book’s web site on Wrox.com. (See Chapter 4 for guidance with installing the database samples.)
After you have set up a data model for Excel, it is remarkably easy to create something quite good looking in a fairly short amount of time. Luckily, setting up this model is also fairly easy in the latest version of Excel. To do so, open a new Excel workbook, and, on the PowerPivot tab, click the Manage button. (See Chapter 5 for enabling PowerPivot if the tab is not visible). Click the From Database button, choose SQL Server, and enter the location of your SQL Server. Choose the VI_UNData database from the drop-down and click Next. Choose Select from a List of Tables and then select the DimDate, DimCountry, DimOECDStatistic, FactOECDPopulation, and FactUNData tables, and click Finish. Your screen should look like Figure 10-8.
In order to use indicators, you first need to have a target, and you also need to have a calculated measure on both the actual and the target.
For the target, you are going to compare each country against the best of its peers. To do that, you are going to create a Data Analysis Expression (DAX) calculation as a calculated column on FactOECDPopulation. The DAX code to put into the column is:
=CALCULATE(
MAX(FactOECDPopulation[Value])
, FILTER (ALL(FactOECDPopulation), FactOECDPopulation
[DimOECDStatisticID] = EARLIER(FactOECDPopulation
[DimOECDStatisticID])
)
)
After this, create a SUM rollup on both the new column and on the actual, which you will do by clicking the Sigma (sideways M) symbol on the Ribbon while you have those columns highlighted.
Then click the measure you just created on the actual column, and click Create KPI on the Home tab of the Ribbon. The pop-up box in Figure 10-9 appears. Configure it to use your new measure as a target by selecting your new measure from the drop-down and set the red band to 40%, set yellow from 40% to 80%, and set green above 120%.
When you’re done, create a new pivot table. Add the Sum of Value and Status measures to the pivot table, and drag the country column of the DimCountry table from the list of fields to the rows. You need to add a filter and choose a single metric to get a meaningful number. See Figure 10-10 for an example.
Of course, the ability to accidentally leave off a filter and get meaningless values is quite dangerous. With a slight tweak to your calculated measures, you can fix that up. Change your two measures (Sum of Value and Status) to the following DAX to show a value only when the DimOECDStatistics table has only one value:
Sum of Value:=IF(
HASONEVALUE(DimOECDStatistic[Metric]),
SUM([Value]),
Blank()
)
BestOfPeers:=IF (HASONEFILTER(DimOECDStatistic[Metric]),
SUM([MaxOfPeers]),
Blank()
)
In this section, you are going to create a scorecard using the standard PerformancePoint indicator images for the KPIs. The data source you will be using is from the Organization for Economic Development.
Start by setting up the data connection and mapping the date dimension as described in Chapter 7. You are connecting to the OECD_Data tabular mode cube.
To make the connection, right-click the PerformancePoint content list and choose New > KPI. Select Blank KPI in the screen that follows. You will see a screen like the one shown in Figure 10-11.
You need to map the KPI metrics to data points because they default to a fixed value. Click 1 (Fixed Values) under Data Mappings on the same line with the title Actual to start editing. In the screen that displays, click Change Source and then double-click the data connection you created. In Figure 10-12, the data connection is called dsOECD.
Choose MetricValueAvg in the Select a Measure drop-down field, as shown in Figure 10-13, then click OK to close the window. Click the (Default) link underneath Number Format and then choose Number. Click OK.
To finish setting up the Actual, click the Default link under Calculation, choose Average of Children, and then click OK.
Now, map the Target metric in exactly the same manner, but use MetricValueMax for the measure and Max of Children for the calculation. Your screen should look like the one shown in Figure 10-14.
Finish by naming your KPI Metric Value and then saving it by right-clicking on the KPI and clicking save.
It’s now time to create a scorecard: Right-click the PerformancePoint content list and choose New > Scorecard. Choose a Blank Scorecard from the Standard Scorecards category. Drag the KPI from the Details tab onto the left-hand column labeled Drag KPI Items Here. Click the Update button on the Edit tab of the Ribbon, and the scorecard displays as shown in Figure 10-15.
In the example in Figure 10-15, no values show because of the rollup up we chose; as there are no children, and neither average nor max will work. Drag the Regions hierarchy onto the Metric Value KPI so that the tooltip shows Last Child, as shown in Figure 10-16. Release the mouse button.
Right-click the All member select Autoselect Members > Select Children, and then click OK. Click the Update button in the Edit tab to see the result.
The current result showing on the scorecard is meaningless because it is a rollup of all the indicators for all time. You are going to fix this by adding two filters for year and statistic. Begin by renaming the scorecard as Metric Scorecard and then saving it.
Right-click the PerformancePoint content list and choose New > Filter > Member Selection. Select the dsOECD data connection you created earlier by double-clicking it, and then click Select Dimension on screen that displays. Select the DimOECDStatistic.Statistics dimension hierarchy. Click Select Members right-click All, and then choose Autoselect Members > Select All Descendants, as shown in Figure 10-17.
Leave the Default member selection blank and click Next. Choose Tree on the next screen. Name the filter Statistic and save it.
Repeat this process for DimDate.Year, using the member selection filter again, but only select the years 2000, 2005, 2008, 2009, 2010, and 2011 (which are the years we have data for). Name the filter Year and save it.
Finish by creating a new dashboard (right-click the PerformancePoint content list and select New > Dashboard). Select 2 Rows and click OK.
Drag the filters to the top row and then drag the scorecard to the bottom row. Connect them as described in Chapter 7 in the “Filters” section.
Rename your dashboard as OECD Statistics, right-click it, and deploy it to SharePoint. You should have a dashboard similar to the one shown in Figure 10-18. Not all data points have data for all years in this data set, so you may need to work through a few.
MAX( [DimCountry].[Regions].CurrentMember.Parent.Children ,
[Measures].[MetricValue] )
IIF( [Measures].[MetricValue] = NULL, NULL,
MAX( [DimCountry].[Regions].CurrentMember.Parent.Children ,
[Measures].[MetricValue] )
)
IIF( [Measures].[MetricValue] = NULL, NULL,
RANK([DimCountry].[Regions].CurrentMember,
ORDER( [DimCountry].[Regions].CurrentMember.Parent.Children ,
[Measures]. [MetricValue] ) )
)
Custom indicators are relatively easy in PerformancePoint. For this example, you are going to use them to create conditional formatting, but you could as easily add your own images.
Working on the workspace created in the previous example, right-click the PerformancePoint content list and choose New > Indicator. Choose Blank Indicator and then click OK. Leave the defaults of Standard and 3 levels, and click Finish.
You change the image by double-clicking the red-lined box, or change the background color by double clicking. For Level 1 (Worst) click background color, choose Red from the standard colors, and then click OK. Set Level 2 as Yellow, and set Level 3 (Best) as Green. Your indicator setup screen should look like Figure 10-19.
Name your indicator ConditionalFormatting and save it. Now, go back to your KPI, click the Target, and click Set Scoring Pattern and Indicator. Leave the defaults as they are and click Next.
Click the Workspace tab and choose the indicator you just created, as shown in Figure 10-20. Click Next and then click Finish.
Save your KPI and go to your scorecard. By default the scorecard doesn’t use KPIs’ conditional formatting settings, so right-click any cell below the Target and choose Metric Settings. Click the Indicator tab and select the background color check box, as shown in Figure 10-21.
Click OK to see your scorecard. Notice that most values are red. The empty values are black because you didn’t choose a color.
You are going to hide those values, so right-click again and choose View Settings. Select to the Filter tab select the Filter Empty Rows check box, and then click OK. The rows that are hidden still show on the scorecard in design mode, but they are highlighted in red to show that they will be hidden when the scorecard is deployed on a dashboard. Save your scorecard, go back to your deployed dashboard, and refresh it to see a similar dashboard to the one in Figure 10-22.
In this chapter you learned the basic principles of using scorecards, and you worked through the practical implementations in both PerformancePoint and Excel. Scorecards are one of the most prevalent forms of business intelligence deployed today, and knowing how to build them is an essential part of your toolbox.
3.136.234.229