Where do I create my KPIs?

In recent times there have been requests by many (including me a few months ago) to get more clarification on where KPIs are best defined – MOSS Ent 2007, SSAS cubes or BSM/PPS. So which one should we use? The consultant’s answer: It depends. All three alternatives are excellent when used within their boundaries and expectations are set for what each can and cannot do out-of-the-box.

UPDATE: Markus Fisher has rightly pointed out that the SharePoint KPI feature is only available on the MOSS Enterprise license, not the standard out-of-the-box, included-with-Windows Server version. This post in its original form said that this feature was included in the base WSS license, it is not. I have made several changes to bring it into line. Mea culpa.

For more information on the feature comparison of the various editions of WSS / MOSS have a look at this spreadsheet. Thanks Markus.

Microsoft Office SharePoint Server 2007 Ent (MOSS). One of the new features that ships with this portal product enables the creation of basic KPIs that are stored in SharePoint lists and displayed via web parts.

SQL Server Analysis Services 2005 (SSAS) is also one of those very cool and powerful applications that is included in the licence SQL Server 2005. SSAS 2005 brought with it the ability to use MDX to define KPI structures within a cube (or UDM).

Business Scorard Manager / PerformancePoint (BSM / PPS) like MOSS costs money on top of the SQL Server or Windows Server investment. One of their most primary functions is to build, manage and display KPIs and supporting data using a web service backend. KPIs are not an extra add-in feature for these products, it’s a pivotal part of what they do.

Based simply on cost and purpose which would be expected to be the most fully-featured KPI tool? Not much of a choice, is it? However, that is not to say you must purchase BSM / PPS if you even remotely consider creating KPIs. Not at all. KPIs are always about definition and data: what are we measuring and where is the data we need to measure it.

As we go into these next few points please remember that I am not trying to trash one option in favour of the other, which is why I chose to do this as more of a discussion than a “Pros & Cons” list. They are all very good KPI solutions, when used within their bounds and in line with requirements and the point in the BPM life cycle an organisation finds itself in.

Data Sources
MOSS – Each KPI can only have one data source which is used to populate the actual. This data can be sourced from published Excel documents (including Excel services), other WSS lists and statically entered values. They also have the ability to connect to SSAS cubes, however, the data that can be accessed must be contained in an SSAS KPI structure. You can’t access individual cube measures that exist outside KPI structures.

SSAS – Because we are using a cube it goes without saying that KPI data is sourced from its parent object or possibly another related cube. Of course, the data in cubes is often the result of some form of data consolidation, so the data in the KPIs may well have originated from several different areas.

BSM/PPS – BSM can access data from ODBC and ADOMD.NET data sources. PPS will be able to source data from SQL tables, WSS lists (including KPI lists), SAP BW 3.5, OLAP cubes, Excel files and Excel Services files.

Actual / Targets
MOSS – The actual value can be hooked up to a data source and the target thresholds must be manually configured within the definition of the KPI. There is no ability to set any dynamic (data bound) target data. Actuals are compared with the target threshold to see if the actual is above or below the target. This then drives what indicator is displayed.

SSAS – Each KPI definition is made up of Value, Goal, Status and Trend attributes. The data returned for each are driven by MDX scripts defined within the cube. Essentially the Goal and Status elements make up the one “target”. The Goal is the number what we want to achieve and the Status drives the visual KPI indicator.

BSM/PPS – as many targets can be defined per KPI as are required. Each of these targets can be connected to a different (or the same) data source as the actual. There are three different actual-to-target comparison formulas can be used to determine each target status. Targets also suit another purpose here; to provide more contextual information about the KPI. Put simply, target values do not need to be compared to the actual. They can simply hold related data, such as YTD or previous period figures.

Indicators
MOSS – Can be configured with 3-level “Increasing is better” or “decreasing is better” indicator bands. There are 4 sets of icons available including traffic lights & smiley faces. Different icons are not configured when creating the KPI and can be changed in the web part that displays them.

SSAS – Also has 3-level indicators of increasing or decreasing is better. There is a wider choice of icons available but no easy means to make any changes to these at all.

BSM/PPS – Indicators of increasing and decreasing along with “closer to target is better” are available. Indicators can be defined with banding of 2-10 levels. A wide range of indicators ship with the products as well as wizards to create your own. Indicators can communicate status via icons, text, text colour and background colour.

End user interaction
MOSS – Via WSS web parts connecting to the SharePoint list the KPIs are stored in. KPI web parts can also be incorporated in Report Centre dashboards.

SSAS – Cube browsing / querying functionality is needed. KPI figures and icons can be accessed through Excel 2007 pivot tables and hence Excel Services documents. There are MDX KPI...()functions which enable KPI data to be accessed by any (SSAS 2005 compliant) OLAP client.

BSM/PPS – Via custom BSM / PPS web parts communicating with a web service.

Scorecarding
MOSS – Although there is some aggregation capability, KPIs cannot be rolled up to produce what would really be considered a scorecard. Weights cannot be configured against individual KPIs. Using WSS list functionality you could probably group them, though.

SSAS – Weights can be applied as part of each definition and grouping can be configured, However, outside of BIDS these attributes don’t enable any kind of simple out-of-the-box functionality for scorecarding. Calculation of a score and rollup of that score are also not supported.

BSM/PPS – KPIs can be grouped in scorecards for creation of objectives. Scoring is supported, KPI weighting is also supported and is used in rollup score calculation.

Summary
MOSS KPIs – easily created (by just about anyone), easily deployed but simple functionality. Brilliant for prototyping and also for simple, KPIs that may not have a managed data source behind them.

SSAS KPIs – these are not an “instead of” they should be an “as well as”. For some they are a bit more difficult to create because of the MDX requirement, but as a result they can be much more powerful. They greatly enrich the cubes that contain them. The KPI data can be consumed by any OLAP application, in particular BSM / PPS. Chris Webb recently posted some good info on ways to make these KPIs a little more dynamic when it comes to target threshold settings.

BSM / PPS KPIs – by far the most functional & flexible KPIs from just about every aspect, and so they should be, these products are speificially built for this purpose (and you need to pay for it).

The fact that each product can “do” KPIs doesn’t mean that this is a one-or-the-other kind of choice. There is no “answer”, no right or wrong. Each of the alternatives can be used to help a business establish both the definition and data parts of their KPIs. It takes real time and effort to determine what needs to be measured and where the data is. Often the hardest thing is to figure out is the target data (budgets, forecasts etc.). The actuals are normally quite a lot easier.

The creation of a KPI in any of the above three applications is easy, quick and simple if based on a solid definition and good data . The opposite is next to impossible and useless. In comparison to proper KPI definitions and data, the choice of software used to house the KPI is insignificant. In the end users will drive software to be used with their requirements.

Users evolve, they will always want more. Give them one KPI, they want ten. Give them ten KPIs, they want a scorecard. Give them a scorecard, they want drilldown reports. IMHO any business that is serious about BPM will eventually find their way to BSM or PPS, because it is what they are built for.

So use all three types of KPIs while on the road to BPM enlightenment. Understand the strengths and weaknesses of each. But recognise that KPI definition and data are paramount and should take precedence above all else.