Scorecards - SharePoint, SQL Server, or PerformancePoint?

  • Article

A few years ago, if you asked Microsoft how to build a scorecard, they may have shrugged. Then we released the "Business Scorecard Accelerator" - a free technology mainly designed to showcase SQL Server Analysis Services.

It was so popular that Microsoft made a product out of it - Business Scorecard Manager 2005. While BSM could pull data from ODBC data sources, its best buddy was still Analysis Services. With the right cube, you could throw together a scorecard in under a day. Of course, BSM still had its quirks...

After Microsoft bought ProClarity in 2006, the BI vision coalesced - BSM v2, ProClarity, and Biz# (a multidimensional planning application) were unified into PerformancePoint. PerformancePoint, which just launched a few weeks ago, is a huge step forward in business intelligence, but more on that another time.

At the same time, SharePoint 2007 has "KPI lists" - a special type of document list which can display collections of key performance indicators. This can be distracting as a type of "scorecard." SharePoint also adds Excel Services, which can allow users to build scorecards in Excel and display them inside SharePoint.

 So what to use when?

A quick overview...

MOSS KPI Lists:

Pros:

  • Easy to use
  • Integrated with MOSS (web parts & lists)
  • Can show multiple data sources
  • Can show KPI's from SQL Server Analysis Services

Cons:

  • Does not scale across the organization
  • Limited functionality

Basically, MOSS KPI lists are a good way to pull KPI functionality on to a SharePoint page - surface some business intelligence on a portal page or shared site. They make a good companion to a BI effort, but this is not the way to start a BI or scorecard initiative - you will quickly be frustrated by some of the limited functionality (formatting, lack of drill down, etc).

Business Scorecard Manager 2005:

Pros:

  • Strategic
  • Scalable
  • Linked Analytic Charts
  • Annotations
  • Alerts

Cons:

  • Need SQL Server 2000 Notification Services for alerts
  • Builder is quirky
  • Working with ODBC data sources is labor intensive

The greatest thing about BSM is how very easy it is to set up a manual scorecard and publish it into SharePoint. Once you learn your way around the Builder, you can whip up a manual scorecard in a few hours. This is powerful because in a scorecard initiative putting an ad-hoc scorecard on the web can "wake up" stakeholders and get their attention while you start wiring it to back end sources.

Having said that, wiring BSM to those back end sources can be labor intensive - each current value, target, and trend has to be wired up as an independent query. Again, using SQL Server Analysis Services can make this far easier - dimensions automatically display across the scorecard.

Excel Services Scorecard

Pros:

  • Easy to use
  • Ad-hoc

Cons:

  • No drill down
  • May not scale
  • No linked charts

Creating a scorecard in Excel and publishing it to Excel Services is a nice way to publish an ad-hoc scorecard. However, it lacks the linked ad-hoc charting and drill down capabilities. It also may be difficult to maintain due to its nature as an Excel spreadsheet. (This is not meant as a limitation of Excel, but rather how Excel xls files often invite sloppy processes...)

ProClarity Dashboard Server:

(ProClarity had a Dashboard Server product, which is being discontinued)

PerformancePoint Monitoring:

Pros:

  • Designed for the enterprise
  • Annotations
  • Linked analytic reports
  • Drill-down
  • Dashboard builder
  • Extensible

Cons:

  • A more expensive option
  • No alerts

Honestly, PerformancePoint Dashboard & Scorecard builder is awesome to work with. The designer is drag and drop and pretty intuitive. It still works best with multidimensional data, but can also show data from other data sources (SharePoint lists, Excel spreadsheets, ODBC data sources, etc). In addition PerformancePoint includes powerful analytics from ProClarity, and the new planning engine for what-if and forecast modeling.

So - SharePoint KPI lists and Excel Services are good introductions into scorecarding, but for a real scorecard/dashboard/BI initiative, PerformancePoint is the way to go.

For more information: https://www.microsoft.com/bi