Creating a Scorecard in PerformancePoint Server 2007 (PPS)

By: Ray Barley   |   Comments   |   Related: > Performance Point

Problem

We are just getting started with PerformancePoint Server 2007 and trying to help our business users to develop their first scorecard.  For those of you who are new to Office PerformancePoint Server 2007, PPS is a recent addition to the Microsoft Business Intelligence (BI) offering.  PPS enables users to develop a variety of reports and scorecards, then compose them into a dashboard which is deployed to SharePoint.  Business users can then simply navigate to a URL in a SharePoint site to access the dashboard..  Can you help with some background on scorecards and also provide the detailed steps involved to develop a scorecard?

Solution

Scorecards are becoming an integral part of many BI applications.  They offer an easy way for a business user to see how the business is doing.  Scorecards can be developed to measure the performance of many different parts of the business such as the total company, divisions, departments, etc.  In each case the scorecard clearly enumerates the objectives, targets, and actual results of the particular area of the business.  The power of the scorecard is its ability to present this information in a way that allows us to focus on whether the actual business results are meeting our stated goals and objectives.

Let's take a look at the sample scorecard that we will build in this tip:

The scorecard shows actual results, targets and a visual indicator which clearly shows whether we are meeting our objectives.  The indicator above is the default stoplight that comes with PPS.  Green means we are on target, yellow means we are slightly off target, and red means we are way below the target.  The indicators allow us to quickly spot the problem areas.

In order to develop a scorecard, the business users need to identify the objectives, targets and results to be measured.  The results to be measured are commonly called Key Performance Indicators or KPIs.  A KPI is typically made up of an actual and a target.

The focal point for developing BI applications using PPS is the Dashboard Designer, a rich client application with the now familiar Microsoft Office 2007 user interface.  In order to create our first scorecard, we will perform the following steps:

• Deploy the Dashboard Designer to our local machine
• Create a Data Source to specify where to pull data for our report
• Create a Scorecard
• Edit KPIs
• Add the scorecard to a dashboard
• Deploy the dashboard to SharePoint

For this tip we will begin with the Create a Scorecard step.  To deploy / launch the Dashboard Designer and create a Data Source, please refer to the Deploying the Dashboard Designer and Creating a Data Source topics in our earlier tip, Creating an Analytic Grid Report in Performance Point Server 2007 (PPS).

Creating a Scorecard

To create a Scorecard in the Dashboard Designer click the Create tab then click Scorecard.

For our example we are going to build a scorecard that uses data from the sample AdventureWorks cube that comes with SQL Server Analysis Services 2005.  Select Analysis Services as the Template and make sure that "Use wizards to create scorecards" is checked.  When you are getting started with developing scorecards it's a good idea to use the wizard provided by the Dashboard Designer.

After checking the Wizard option, you will be guided through a sequence of dialogs to define the scorecard.  First you will name the scorecard.  Check Grant read permission to all authenticated users if you want everyone to be able to view the scorecard; you can also fine tune security later if necessary.

Select AdventureWorks for the Data Source.  A scorecard can contain KPIs from multiple data sources; however, the wizard only allows you to select a single data source.  After you complete the wizard you can add KPIs from other data sources if necessary.

The Analysis Services template allows you to create KPIs from measures in the cube or import KPIs already defined in the cube.  For our example we will create KPIs from measures in the cube.  Defining KPIs in the cube is a best practice that you should consider; however, it does require a good understanding of MDX. 

After deciding to create KPIs you will have the opportunity to choose measures from the cube.  Each time you click the Add KPI button, another measure will be added to the list below.  Keep clicking the Add KPI button until there are no more measures to add.  You can delete measures that you don't want by highlighting the row(s) then clicking the Delete Selected KPIs button.

For the dialogs that appear after Select KPIs to import, just accept the defaults.  After the wizard steps are completed, you will see your initial scorecard:

Up to this point we have created many new elements in the Dashboard Designer; a scorecard and several KPIs.  We now need to publish these elements to the PPS Monitoring server.  Click the Home tab then Publish All to publish all of our new elements.

Edit KPIs

The scorecard created by the wizard is a good starting point but some fine tuning is required.  For example you may have noticed that the default behavior is to set the Actual and Target amounts to the same measure.  In the next couple of steps we are going to create some objective KPIs and perform some edits to the scorecard and KPIs.  Remember to Publish after you make changes to the various elements.  In addition you will have to click the Update icon on the Edit tab to refresh the scorecard with your changes to the various elements.

An objective KPI is one that rolls up other KPIs.  Objective KPIs are where we define our high level objectives like Increase Sales, Increase Customer Satisfaction, etc.  We are going to create two objective KPIs - one called Internet Sales and another called Reseller Sales.  The steps to create each objective KPI are exactly the same; we will walk through one of them.

To create an objective KPI click the Create tab then click KPI. 

Click Objective on the Select a KPI Template dialog.

Enter a name for the objective KPI.  Click the Grant read permission to all authenticated users checkbox to allow others to view the KPI.  You can fine tune the security later if necessary.

After naming the objective KPI it will be displayed in the Editor pane (center) of the Dashboard Designer.

The AdventureWorks cube does not have measures that we can use for the Target values in the KPIs that we created.  In order to make the scorecard look somewhat realistic, we will edit each KPI and change the Data Mapping for the Target to Fixed Values and specify a value.  Click the Data Mappings column Target row, click the Change Source button, then select Fixed Values from the list and enter a value.  Note that if we had other Data Sources defined we could specify them as the source for our target values.

You can see our sample values for the targets in the first screen shot in this article which shows the completed sample scorecard.

To complete our initial scorecard, perform the following steps:

• Drag the Internet Sales and Reseller Sales objective KPIs from the Available Items in the Details Pane and drop them above the Internet Sales Amount and Reseller Sales Amount KPIs in the scorecard
• To move the position of a KPI up or down in the scorecard, click the KPI name (i.e. select it) in the scorecard, click the Edit tab, then click the up or down arrow in the Format area:

• To indent a KPI under one of the objective KPIs select the KPI name in the scorecard then click Ctrl + Right Arrow.  You can also click the icon right above the down arrow in the Format area of the Edit tab.
• To edit the format of a number, click the Number Format column and the Actual or Target row for the KPI, then fill in the Format Numbers dialog.  Type % in the Symbols on right textbox and enter 100 in the Multiplier textbox for any number that is a percent.

After completing these steps, your scorecard should look like this: 

Hopefully now you can appreciate the power of the scorecard.  It allows us to focus on objectives and whether or not we are meeting those objectives based on specific KPIs.  In the example we accepted the defaults in a number of places for convenience.  You can customize the indicators and many aspects of the KPIs themselves. 

Before we wrap up there is one aspect of the KPI that we should mention.  Click the Thresholds column in the Target row for a KPI and you are able to specify the thresholds that determine which indicator will be shown in the scorecard.

Click on the Set scoring pattern and Indicator button in the Thresholds area of the above dialog to fine tune some options on the KPI.  The scoring pattern options are Increasing is Better, Decreasing is Better, or Closer to Target is Better.  The Banding method default is shown below and should be used most of the time.  We'll explore the other banding options in a future tip.

Now that we have created a sample scorecard, our final step is to create a dashboard and add our scorecard to it.  Make sure that you have published the scorecard and all of the KPIs.

Creating and Deploying a Dashboard

Dashboards are the main element that we create using the Dashboard Designer.  The dashboard is a container for reports and scorecards.  We layout the dashboard in the Dashboard Designer then publish it to SharePoint where users can access it.  PPS comes with a set of SharePoint web parts that are used to render a dashboard.

To create a dashboard and deploy it please refer to the Creating and Deploying a Dashboard topic in our earlier tip,  Creating an Analytic Grid Report in Performance Point Server 2007 (PPS).  After creating the dashboard simply drag the scorecard from the Available Items in the Details Pane of the Dashboard Designer and drop it onto the dashboard designer.  The screen shot below shows the scorecard in the dashboard that we created in our earlier tip.

As you can see the design experience for the dashboard is not WYSIWYG as you might have expected.  However, you can preview the dashboard before deploying it to SharePoint.  Remember to publish the dashboard then click the Edit tab and the Preview icon.  The Preview function creates a web page on the fly and renders the dashboard using the PPS web parts.  When you are satisfied with the dashboard you can publish it to SharePoint.  Click the Edit tab in the Dashboard Designer then click the SharePoint Site icon.  You will step through a couple of dialogs then the dashboard will be available on a SharePoint web page.

Next Steps

• Take a look at Performance Point Server.  It is a new tool in the Microsoft BI stack that will help you to provide the kind of Business Intelligence applications that users want.  For a great starting point and to download an evaluation copy visit this site.
• Download a copy of the sample Dashboard Designer workspace here and experiment with the Dashboard Designer.  You can get the Adventure Works BI database used in the sample here (click on AdventureWorksBICI.msi).

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

View all my tips