Speaking the language of business intelligence with an Australian accent

Saturday, February 3, 2007

PPS / BSM - Sparklines in Scorecards

A while back I found out about MicroCharts, an innovative product that enables the creation of efficient, simple data visualisations using fonts. When I first heard about this I wondered if it would be possible to add a new spin on BSM KPIs by incorporating sparklines, a data visualisation concept created by Edward R Tufte, into their definition. I immediately tried to get a sparkline into a BSM scorecard with no luck. Obviously I didn't try hard enough because I tried it again today and, as you can see from the screenshot below, I was successful.

BSM only gives us the ability to stipulate one global font setting for the entire data grid of the scorecard via the cell options pane of a scorecard view. As a result the numbers displayed in the Actual and Budget columns don't look the best as a result of having the Micro Line Charts font applied to them. There's also a little extra space to the right of the sparkline (maybe another tweak or two would get rid of this). Nonetheless, you can see a great little sparkline which gives scorecard users a much better insight into their sales revenue KPI numbers over time, in this case a 24-month rolling window. At a glance you can see that Germany's revenue was flat for quite a time and then jumped considerably, whereas the USA's revenue, while high in comparison, has been quite erratic over time. France, just did business as usual for quite a while, had one monster month and then dropped back to where they were.

UPDATE: the Microcharts guys got in contact to tell me that my original attempt at sparklines alongside numbers in BSM would not have worked no matter how hard I had tried. Apparently the 1.0.1 version of the Microcharts fonts did not handle numbers alongside the graphical elements of the font that make up the charts. The 1.1 (current) version does, hence the success I experienced this time over the last. While the numbers don't look the best at present the MC developers are going to ensure the next version of the fonts will display numbers properly. So in v-next when using the BSM scorecard cell global font setting you will probably see Arial-style numbers (or some other standard font) alongside the sparklines.

I have also been playing with the CTP1 release of PerformancePoint (we also did a bit of research on PPS for the upcoming BSM book in which we have a small chapter). Below is a screenshot of what a similar sparklined scorecard (slightly different numbers) looks like in PPS builder. One of the notable features here is that we can stipulate specific font settings for each column in the scorecard, no need for a global data grid font like BSM. This way I can use the appropriate MicroCharts font only in the sparkline column and a regular font for the others. The extra space that BSM displays is also gone :)

Outside of the sparkline stuff I love the fact that I was able to create and preview the scorecard in PP Builder alone, no deployment needed. Aside from many other things (about which I will blog more in the coming months), the ability to preview whatever you build without having to deploy is a great (and long overdue) feature. The screenshot below shows the PP Builder environment in its entirety with the sparkline scorecard preview.

If you're interested in more of data visualisation side of things check out Tufte's book Beautiful Evidence (which I hear is good and plan on reading soon) and Stephen Few's excellent Information Dashboard Design. Tufte came up with the sparkline and Few invented the bullet graph as a more effiective means of communicating the status of a KPI. Few also maintains an interesting blog.

As for MicroCharts, I think this product dovetails beautifully with one of Microsoft's key BI strategies of data delivery using Excel while also embracing Few & Tufte's ideas on simple, efficient communication of data without the excess visual "fluff". Because their visualisations are font-based the ability to surface many of them in other places like SSRS and other reporting tools is possible, just like I've done with BSM & PPS. As far as I understand, the MC team are currently working hard on getting their product to integrate with Excel Services. When they succeed, I'll be the first in line.


Anonymous said...

also worth looking at is www.bissantz.com for sparkline software

shreyas said...

how to adjust the width of Actual/Target columns in scorecard, and can we freeze the scorecard rows and column headers

Max Anfilofyev said...

Using advanced visualization technics in PerformancePoint is a very interesting topic. Kudos to Nick for providing a proof of concept for using sparklines in PerformancePoint dashboards.
Nick, could you talk more on how you accomplished the task or share the solution? Are you calculating members in SSAS or PerformancePoint?

Anonymous said...

Could you give a some detail on how exactly you implemented sparklines in your scorecard? The MicroCharts guys are telling me that I need to start with their (free) MicroCharts Fonts, thats fine and I see where they are available as a font in a scorecard cell, but what exactly am I using as the source of data for the sparkline? I assume it has to be something with multiple values, but can you elaborate on your solution. thanks

Anonymous said...

Mate how do you create a sparkline using dashboard designer? i cant find where to get it from, i created a KPI with a gauge but want to add a sparkline to it like you have done. thanks in advance


Nick Barclay said...

Roger, the sparklines in this post, were kind of a POC. Theywere done using functionality in a product called Microcharts (as referenced in the post). I am not sure if the font-based implementation of sparklines using this software is supported any more.