Dashboard & visualisation software: Excel Services review

It’s been a short wait for this first dashboard software review because… well because I’ve been busy building dashboards. Now with a long train journey from London to Leeds, it’s time to take a quick look at Excel Services. I wanted to look at this one first, because it doesn't seem to have been widely adopted and I've come across a few people who build very good Excel dashboards but who don't even know it exists. Excel is still the go-to solution for just about everything, so what about its Services sibling?

When I first came across Excel Services, I have to say my immediate reaction was “what took them so long?” Lots of people, including me, build dashboards in Excel - from simple report tables to large macro driven data explorers – and it’s a logical step from mailing them around a business to publishing them for viewing on a website: One version of the truth, no versioning problems and easier to read for people who aren’t comfortable in Excel.

Excel services sits on top of SharePoint and lets you publish spreadsheets from Excel 2007 onwards (*.xlsx files) to the web. You can then view elements of the spreadsheet in your web browser.

(microsoft don't really do pretty)

First the good, and publishing is fairly easy. You specify the sheets and named ranges that you want to make available and then from Excel, you save the workbook to the server. There’s not much more to it than that. Hopefully you already know how to make a reasonably good looking spreadsheet, so now you can make a reasonably good looking online dashboard.
Live data comes from data connections to pivot tables that refresh automatically when a user visits the dashboard. Not the fastest, but it works.

There are a few things you can’t publish and we’ll come back to the limitations of Excel Services later, as you’ll encounter some of the issues pretty early on. Big things that you can’t use are:

• Macros. Your workbook has to work with worksheet formulas only.
• Any kind of drawing object (including buttons and pictures) so you’ll have to make it look good by colouring in cells and giving them borders.
  

Once you’ve published the workbook, it’s immediately viewable online, but you’re going to want to build it into a proper dashboard with menus for the different screens and some element of user control. For that you use the dashboard builder and here’s where things get a little less smooth. The builder is fine and it works, but it’s not friendly. Creating filters and dashboard screens is very manual, pretty clunky and to be honest a bit of a pain. There’s a weird distinction between different ways of publishing and refreshing items on the server and you’re never sure which one your latest change needs, so you end up always doing all of them.

There’s also the potential to get into a hell of a mess with the builder if you don’t keep your files and working practices organised. One (local) dashboard builder file can contain the elements of several different online dashboards. Get two or three people building and uploading without remembering to refresh from the server and you can find your painstakingly created view has disappeared when somebody else ran an upload. It’s not that hard to avoid but it shouldn’t be possible at all.

For an overall idea of feel, have a crack at the skydrive. They’re not too similar in function, but they both have that hint of “we’re Microsoft, we know what’s best and if you do it wrong or can’t find a setting, then it’s user error”. They're also both slow. You know what I mean - Hotmail’s the same.

User interaction comes from setting up drop-downs on the dashboard and linking them to named cells in your workbook. You populate what’s in the drop-down from a workbook or database connection.

One major - and I mean really major – limitation is that you can’t make the drop-downs drill. So if you’ve got a list of countries in one and a list of cities in another, you can’t only show the UK cities if the viewer selects UK. This one issue alone kills Excel Services for serious dashboard work for me. As soon as you’ve got any amount of data, you have to be able to drill the options or there will be hundreds of items in a list. There are workarounds involving tree based lists but they’re not very satisfactory.

While we’re doing the negatives, here are a few more:

• If you’ve got something that’s not allowed for publishing in your workbook, like a cell with a comment in it, you get no help at all about what’s wrong and have to go over everything with a fine toothed comb until you work it out. One of mine had broken links in a hidden named range (it had disappeared out of the names list – a ‘known issue’ apparently…) and that took ages to solve. There’s a download tool from MS to help, but frankly, it’s rubbish.
  
  
• Viewers can’t download data from the dashboard unless they’ve got Excel 2007 or better. If you’ve got users on Excel 2003, my recommendation is that you don’t touch Excel Services with a bargepole. Getting the data out for viewers who are using 2003 becomes a complete pain.
  

Would I buy it? Well we did at EMI and it was useful. Talk to your IT people and if they’ve invested in the top-end version of SharePoint then you may find you’ve already got it.
For small dashboard tasks, it’s not bad and for my current needs, if we already had the licences then I’d use it to help with a lot of them.

Unfortunately though, we don’t already have the licences and it will cost somewhere around £8k to get them. With the other tools on the market (some of which we’ll come to) that’s too much when it only looks ok and is only ok to use.

Microsoft are being left behind in dashboarding and visualisation. In Excel, they’ve got what is still everybody’s first-stop BI tool, but they just haven’t made creation of an online Excel dashbard easy enough and they haven’t implemented the macros that make Excel dashboards so flexible.

In short, Excel Services is ok, but only ok. If you’ve got it already, give it a try and if you only want to publish small dashboards, have a look at it. For everyone else though, you can do better.