Speaking the language of business intelligence with an Australian accent

Friday, May 26, 2006

BSM - To Scorecard or not to Scorecard?

I've been doing quite a bit of BSM work recently. The product has really come a long way from the Scorecard Accelerator a few years ago. By the sounds of things we have more to look forward to with the version that is being worked on currently for release sometime in 2007. I'd be lying if I said I haven't cursed a few BSM shortcomings under my breath in recent times, but that got me thinking, how many times are we trying to create a scorecard where we really should be using other tools/methods to get data to the user. Is what I considered a BSM shortcoming just me trying to make it fit a role other than the one it is designed to best fulfill?

I can't think of many business people who don't like the idea of scorecarding. Everyone loves the concept of seeing their business' health depicted in one screen represented (hopefully) by lots of green circles, big check-marks and brightly coloured arrows pointing in the right direction. Some people want that view of the world so much that they try to define scorecards with only a few poorly-defined KPIs and then wonder why BSM isn't capable of generating the output they expect to see. Bad product right? Wrong. Bad use of product.

An example: The ABC company sells widgets through its distributed sales force. All sales people are goaled on how many widgets they can sell. So in our SSAS cube (UDM) we'll slice our WidgetSales fact table by Time, Geography and SalesTeam dimensions. The primary (and at this point, only) KPI for sales people is # of widget sales per week compared to budget. The CEO has seen BSM and thinks a scorecard would be perfect to show just how well things are going across all geographies down to individual sales person. Will a scorecard give her what she wants? Possibly, but would it be the best angle of attack for this kind of data? Would we be better displaying this data either a) in an SSRS report or b) through an OLAP browser. Data like this (i.e. a single KPI) is not conducive to what I consider to be a scorecard. Sure you can use BSM to create a scorecard with this data but how useful/flexible will it actually be compared to another approach?

BSM is built to roll up different types of data, not aggregate figures. KPIs are almost always completely unrelated numbers, this is why we have to have a linear score calculated for each KPI's actual vs target. KPIs should come in all shapes and sizes. We may have a percentage, a value out of 10 and then a raw dollar figure. In order to get an overall goodness score for this particular objective BSM must 'linearise' the score of each KPI. Try adding 196.4657, 10.2%, $104,587.53 and 7.5/10 together. You can't, it doesn't make sense. This is the kind of data BSM is built for, not displaying total widget sales by region for Week 10, 2006. The product can do all sorts of things but I prefer to use it for the jobs that it was specifically designed to do, and does best.

BSM is not meant to AGGREGATE data and display totals. Many customers I have begun to build scorecards for immediately ask where their rollup numbers are at the objective and perspective level. "Why is there only an indicator? Where are the rollup numbers? It must be a bug!" If you're looking for group totals, such as "widget sales by sales person" summing up to their respective regions then a scorecard is not what you are looking for. I can see an SSRS report, or an OLAP browser in your future.

In its present form BSM does not really allow you to dynamically define the dimension hierarchy members to make up the rows and columns axis for stepped drill-down (expand & collapse). We can use MDX and named sets to define what members of a dimension we see on rows of columns. Unfortunately this is still not completely dynamic, particularly when you want to define those axis members based on a user-selected page filter, this cannot be done (I'm sure there are some creative solutions involving the API but I'm just focused on the GUI here). Another row and column option gives us a stepped drill path of members if we manually (groan) check all the appropriate boxes (sore mouse finger) for the members we want. So what happens when our SalesTeam dimension changes and we employ more sales people? Product shortcoming? Not really. Sure I'd like a little extra flexibility in this area but the more I think about it the more I feel I should consider displaying this kind of data in an SSRS report or my favorite OLAP browser again. BSM is a scorecarding tool, not a report generation tool, SQL Server already has a great reporting tool in SSRS, do not cast it asunder.

The more KPIs that can be defined the better. Soon there may be enough KPI data to make a useful scorecard. In the meantime, define the ones already available as SSAS-based KPIs. You can now create some great-looking SSRS reports or expose the data through various OLAP client tools. The data can also be exposed through pivot tables in Excel 2007 (including the icons!).

A scorecards' foundation is KPIs - the more of them you have, and the more varied they are, the better your scorecarding experience and benefits will be. You can assign different weights to your more important KPIs to 'balance' out your scorecard. Balanced scorecards are there to bring all sorts of unrelated numeric data together and assist business people to see whether their business is going to be around tomorrow or not. IMHO scorecards are not meant to be used as a way to distribute monthly sales report figures. SQL 2005 has all kinds of great products you can use to get this kind of information to users.

We have become spoiled in recent times because we getting used to MS packaging all this great BI stuff into the one license. BSM does not come included with the SQL license, it costs extra $. However, just because the business has paid for it doesn't mean it should be used for everything. Use the right tools for the right jobs, the ROI will take care of itself.

1 comment:

Anonymous said...

Another blogger describes how row-members can be made dynamic in a scorecard with the right tweak to a custom MDX statement-- personally I agree with your perspective on when/when not to scorecard, but, the promise of a true "multi-dimensional scorecard" (from the Tim Kashani BSM 2005 web-cast series) has me edgy for both dynamic rows and dynamic columns!

Hopefully PerformancePoint 2007 will address this, bringing the ability to page-filter on both column AND row axes

Coverage of dynamic row members can be found here:
http://bimvp.com/blogs/bsm/archive/2006/08/08/219.aspx

and a more recent entry here:
http://bimvp.com/blogs/bsm/archive/2006/08/25/226.aspx

Finally, Update Rollup 3 can be found here:
http://www.microsoft.com/downloads/details.aspx?familyid=6A166667-29E4-4329-A5AF-72BF0F23C621&displaylang=en