Microsoft Office

Create Scorecards For Your Business With Office

Mauro Cardarelli

At a Glance:

• Installing Business Scorecard Manager
• Common scenarios
• Building a scorecard
• Deploying the scorecard

Download the code for this article: BusinessScorecard2006_07.exe (155KB)

Let's assume, as I'm going to do here, that you manage a sales team tasked with meeting regular sales quotas. Team member sales are logged into your corporate database, but you have no easy way of telling your team members how well they are doing.

Getting BSM Installed

The first step in building your scorecard is to install the software. Business Scorecard Manager should be installed on a machine running Windows Server 2003 and requires a SQL Server database. While it is possible to install BSM with SQL Server 2000, the steps here detail the installation with SQL Server 2005. Since this is a sample scorecard, it assumes your development will be on a virtual machine environment. Installation in a production environment requires the same steps, but using more servers.

1. Create a new Virtual PC image.
2. Install Windows Server 2003.
3. Apply Windows Server 2003 Server Pack (SP)1.
4. Install SharePoint Portal Server (SPS) 2003.
5. Apply Windows SharePoint Services (WSS) SP2 and SPS SP2.
6. Install SQL Server 2005.
7. Configure SPS to use the corresponding SQL Server instance.
8. Create a sample Portal.
9. Run SQL Server 2000 Analysis Services SP4. Find and run ptsfull.exe (this installs the PivotTable® service). (Note: if you don’t do this, the BSM server install will fail with a "Microsoft OLE DB Provider for OLAP Services (MSOLAP) 2000 SP4" error.)
10. Install Microsoft XML Core Services (MSXML) 4.0 SP2.
11. Install ADOMD.NET 8.0.
12. Install BSM (Builder and Server).

For more installation guidance, see the Business Scorecard Manager Team Blog.

You would like a quick and secure way to give the sales team insight into their progress against defined targets. You would like the interface to be simple but the messaging very powerful. When any salesperson is in danger of missing his target, you want it to be very clear. You have already invested in Microsoft® SharePoint® Portal Server (SPS) and would like to use it for the presentation of sales team data.

Well, Microsoft Office Business Scorecard Manager (BSM) 2005 has just the solution for you. It is a product designed to help information workers design, build, secure, and deploy organizational scorecards. You may be surprised at how often such a scorecard can come in handy. For example, scorecards can chart the progress of tactical and strategic goals, and they offer a visual presentation of the data at hand. Scorecard data is often point-in-time and is meant to show how the information you are tracking compares to your expected target values. A scorecard is a visual summary meant to give the viewer a pulse on the data being measured. This can be useful for anyone in an organization, from senior executives and business managers to teammates and staff members.

In this article I will walk you through the construction of a simple business scorecard based on the scenario I just presented. I'll include details on the installation, development, and deployment of your first BSM scorecard. But before you get started, you'll need to install the necessary software. You'll find detailed instructions for this in the "Getting BSM Installed" sidebar.

A Simple Scenario

First you'll need to choose the data source. To keep this demonstration simple, I'll use Microsoft Excel® as the data source here. Note, however, that BSM supports a full range of data sources (SQL Server™, Microsoft Access, and Oracle to name a few). As you'll see, connectivity is established via an ODBC connection string. A good place to find the right connection string format for your data source is www.connectionstrings.com. It has dozens of connection string examples for various sources.

Figure 1** Sample Data from Excel **

Figure 1 shows the sample data I'll use. I have four salespeople. I am interested in measuring how each performed during the last sales cycle (units sold) how they are doing for this sales cycle (again, units sold), what the expected sales target is, and how well they are doing against the target number. The idea here is that the third column will be updated regularly as new sales data is obtained. The first and third columns will stay fixed until the next sales cycle. Figure 2 shows what the scorecard will look like eventually.

Figure 2** Scorecard Under Development **

Now let's open Business Scorecard Builder and see how to get there. As you can see in Figure 3, the interface is pretty simple. Since this is your first scorecard, you'll need to start from scratch. As you make some progress, you'll want to save your work (or workspace). The workspace file (*.bsw) is an XML-based file containing all the data used in configuring the scorecard.

Figure 3** Business Scorecard Builder UI **

Defining the Data Source and Indicators

First you'll need to choose a data source, so click the Data Source link in the left-hand navigation area. Next you'll see that you need to provide a connection string to the Excel file you created. It is always a good idea to test the connection. Scorecard Builder will tell you if there are problems accessing the data source.

The next step is to select the type of graphical indicator you'll use to show status. There are two main types (although you can create your own). Three-Level is the stoplight presentation—Green, Yellow, and Red. Five-Trend has arrows indicating what direction the current data is trending toward (against previous data)—Up, Slightly Up, Level, Slightly Down, and Down). Indicator selection is performed in the UI.

Defining the KPIs

Before proceeding, let's label the scorecard. Figure 4 shows the selection of New Definition for the scorecard and the labeling of MyFirstScorecard. This is the name of the scorecard as it will appear on your SharePoint portal.

Figure 4** Defining a New Scorecard **

Now it's time for the meat of the scorecard—the Key Performance Indicators (KPIs). These are the data elements that I'm tracking. In this simple example, the four salespeople are the data elements. In more complex scorecards, you might have dozens of KPIs with groups associated with specific objectives (appropriately named Objectives). Figure 5 shows the creation of my first KPI, which is mapped to the first salesperson.

Figure 5** The First KPI (Mapped to the First Salesperson) **

I'll label the KPI with the salesperson's name. Figure 6 is an example of how I map the data from the spreadsheet. To get the data values, I actually have to write conditional queries against the Excel worksheet.

Figure 6** Data Mapping from Spreadsheet **

It is interesting to note that Scorecard Builder tracks what has been done locally versus what has been published to the server. If you get a red exclamation mark next to one of the KPIs, this indicates that any changes you've made have not yet been committed. Therefore, the changes will not show up on your scorecard until you publish them. To be ready for deployment, I'll need to define all the KPIs and the respective mapped data. Figure 7 shows what a finished KPI looks like.

Figure 7** Finished KPI **

When that is finished, I still need to determine what values should be shown on my scorecard and how they should be displayed (as raw values or graphical images). Figure 8 indicates that I would like to present Actuals (previous and current) as well as Target (with a graphical indicator to show progress).

Figure 8** Actual and Target Goals **

Once I create KPIs for each of the four salespeople and map data values for each, I am ready to publish the data to the server using the Publish All button. Once complete, I'm ready to display the data in SharePoint. Note: you can see all of the mappings I made by downloading the MyFirstScorecard.bsw file from the Code Downloads section of the TechNet Magazine Web site, and opening it in Scorecard Builder.

Deploying to SharePoint and Seeing the Scorecard

You will see two new Web Parts in the SharePoint Virtual Gallery—the Office Scorecard View and the Office Report View. In this case, I'll use the Office Scorecard View. Once I drag the Web Part onto my page I can select the MyFirstScorecard scorecard. That's it! Look back at Figure 2 for the completed scorecard.

Doug and Paul are doing a fine job this sales cycle. Mary is in danger of missing her target number. Susan is in big trouble! I now have a clear sense of how the team members are doing. The information presented is simple, yet powerful. The scorecard is truly displaying a "pulse" on the measured sales data.

Next Steps

This example was very simple. BSM offers a great deal more in terms of the complexity of the scorecard as well as how to get even more information from it. Perhaps the next step is to connect a SQL Server 2005 Reporting Services report with each salesperson in order to gain more insight into his pipeline. You can do this with the other Web Part installed with BSM, the Office Report View. You can connect the KPIs with reports shown in that Web Part so that the scorecard becomes more dynamic.

Business Scorecard Manager offers tremendous value in allowing users to process organizational data quickly and easily. It lets them monitor and analyze business activities. Not only does Business Scorecard Manager further the goal of delivering business intelligence for the masses, but it actually extends it by giving users a tool for controlling the presentation.

Mauro Cardarelli is a Managing Partner at Jornata, a Boston-based information technology consulting services company. His responsibilities include technology evangelism, architecture design, and software development. He can be reached at mauro.cardarelli@jornata.com.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.