Jeremy Kashel

Jeremy Kashel's Blog

Reporting Services KPIs Or PerformancePoint Services KPIs?

Although you've always been able to deliver KPIs in Reporting Services by using images and expressions, Reporting Services in SQL Server 2008 R2 now includes a feature called Indicators, which assists with producing KPIs out of the box.

This got me thinking how producing KPIs and/or scorecards in Reporting Services compares to doing the same in PerformancePoint Services. This blog post gives a short overview of creating KPIs in each technology, before comparing the pros and cons of each.

Reporting Services 2008 R2 Indicators

The indicators in Reporting Services come as an extra item on your toolbox, and can be dragged onto a Tablix for example. Once you do add the indicator to your report, you'll be prompted to select the shape that you want for your indicator:

image

So no more messing about with images! Once you click OK your indicator will be added to your report. If you then right click on the properties of the indicator, you can choose what to base the indicator's value on on, and also set its state, both of which essentially control the points at which your indicator changes its colour and shape. What I quite like here is that it's easy to change the colour for each state, and it's also easy to add new states, if you need four colours rather than three for example:

image

You seem to be able to add a huge number of indicator states here, certainly more than enough to deal with KPIs. Taking some data from AdventureWorks, based around employee sales targets, I've created the following very basic report using the Indicators:

image

PerformancePoint Services KPIs

The KPI and Scorecard have had a few decent changes in SharePoint 2010 (e.g. drill up/down), but Dashboard Designer is still the tool that's used to create KPIs in PerformancePoint Services, and its a different experience to developing in Visual Studio.

Assuming that you get to the point where you have a data source set up, the first thing to do in order to create a KPI is to pick an indicator. The choice here is very similar to the choice in SSRS:

clip_image002

The alternative here is to pick a blank indicator, where you'll have to decide yourself how many levels you want for your indicator, and what the colours and shapes should be. You can pick an image for an indicator state (as you can with SSRS), although you are limited to a maximum of 10 states. Again, that should be more than enough for KPIs.

The objects within PerformancePoint Services are very modularised, meaning that you create indicators, KPIs and scorecards separately. If you need to re-use an indicator in a new KPI, then you can just point to an existing indicator, or alternatively you can create a new one.

Again, taking some sample data from AdventureWorks, I've produced the following basic scorecard, based on similar data:

image

Comparison

The following table lists some KPI/Scorecard features and compares how SSRS and PPS are able to deal with them:

Feature

PerformancePoint Services

Reporting Services 2008 R2

Parent KPIs

Out of the box Parent KPIs

No built in parent KPIs

Scoring

  • Two built in scoring methods;
  • Weighting concept to assist with scoring.

No built in scoring capability

Data Sources

  • Analysis Services;
  • SQL Server;
  • SharePoint List;
  • Excel.

  • SQL Server;
  • Analysis Services;
  • Oracle;
  • OLE DB, ODBC;
  • XML;
  • SharePoint List;
  • SAP Netweaver BI;
  • Teradata;
  • Essbase.

Positioning

  • KPIs can only exist within Scorecards.

  • A scorecard can only contain KPIs and their associated Indicator and data items.

  • Indicators can be embedded in other objects or exist by themselves.
  • Other objects (e.g. spark lines) can co-exist alongside Indicators in the same Tablix.

Formatting

  • Colours & number formats of the Scorecard grid can be changed;
  • Colours/fonts in data cells cannot be changed.

If the Indicator is embedded in a Tablix, then each cell can be altered in terms of Colours and number formats.

Ease of Use

As long as your data is in good shape, its possible for power users to build KPIs and Scorecards with Dashboard Designer relatively easily.

The indicator windows are very intuitive in SSRS, but I'd say that building an SSRS report is harder for a power user than building a PPS dashboard.

Other functionality

Scorecards allow users enter comments against KPI values, which is a great feature.

The standard SSRS expressions allow you to fine tune indicators, states, values and formatting of your KPIs.

In summary, PerformancePoint Services in SharePoint 2010 will allow you to get some great looking KPIs and Scorecards up and running much quicker, as that's what it's designed to do. It is, however, more restrictive than Reporting Services, so SSRS is the better choice if your KPI requirements are complex in terms of layout or formatting.

Comments (2) -

  • PeterEb

    5/1/2010 1:49:02 AM | Reply

    Good summary. The indicator functionality in RS reminds me a lot of the icon set functionality in Excel conditional formatting. Which has seen some minor tweaks in O2010. I know ordinary users have quite a high degree of success with the Excel UI, but I'm not sure as many people are successful with the PivotTable portion of the exercise... Overall I think Excel + Excel Services get you to a very similar column as RS2008R2.

    Another thing I'd think about adding to the comparison is where the KPI is defined. Is it in the data source (AS KPI) or in the report? If you add a KPI from an OLAP data source does the reporting tool automatically know how to render the indicator? PowerPivot doesn't support KPIs yet, but if it did I think this data point becomes more relevant...

    Not that I want to make this a more complicated story, I've been advocating for simplicity as much as I can. Smile

  • Jeremy Kashel

    5/25/2010 5:42:46 PM | Reply

    Hi Peter,

    Thanks for the comment - all good points.

    You can certainly use SSAS KPIs in SSRS, but it doesn't pick them up automatically. Worth another blog post I think. PerformancePoint Services, incidentally, does pick them up ok.

Loading