Forty-four thousand satisfied customers

A couple of years ago, I wrote an article for SQLServerCentral.com that described how SQL Server Integration Services could be (mis)used to query a SQL database, format the query results as HTML, and automatically e-mail them on to interested parties. It was more of a demonstration of what were then relatively poorly documented features of SSIS than a serious attempt to recreate a reporting solution, but it touched an unexpected chord and got a huge response on the site. It's been a featured article three times, been included in the publication "Best of SQLServerCentral Volume 6", and has a maximum five-star rating from no fewer than 44,550 readers as I write this.

All this blowing my own trumpet is well and good, but up to now this piece was only visible to registered visitors of SQLServerCentral.com. Today, however, in a shameless attempt to drive some traffic to my refurbished and improved website, I've posted it as a PDF on my own site for the enjoyment and enlightenment of one and all. It's a bit dated by now, and I can certainly think of easier and more efficient ways of achieving the same ends than by using SSIS, SQL XML queries and XSL transformations, but if that's your idea of fun then please take a look.

Sparklines (2)

Last September I wrote here about sparklines, their uses and the upcoming implementation in Excel 2010. By now I've had the chance to try out the sparkline functionality in the Office 2010 preview release and I'm pretty positive about the experience.

I downloaded a set of daily averaged exchange rate data for the last three months. This gives me a table with 279 data points (most are hidden in the example below for readability's sake), all of which are decimals with tiny variations. Not very readable, then, and certainly difficult to interpret without careful study. If I add three sparklines though, I have an instant, in-context overview of the general trends: it's clear that the Euro has taken a significant fall in value in the past three months.

The value of these small but intensely data-rich graphs should be clear (though if you're still not convinced I'd urge you to read Prof. Tufte's writings on the subject), but how does the Microsoft implementation stack up?

Firstly, it's extremely simple to use, more so than the regular Excel charting tools. The presentation options are necessarily much more limited, but there's a choice of three formats (line, as shown here, column, and win-loss) and lowest and highest points can be highlighted. I've done so in this example, with a green dot showing the minimum data point and a red dot indicating the maximum value. Resizing the sparkline cell resizes the graph automatically, and it's easy to change colours and the likes.

There are some minus points too, though. It appears to be impossible to combine sparklines in a cell, so you can't have lines from two different data sets overlaid on each other, or show (for instance) expected or normal values/value ranges together with the sparkline. I had expected that saving a sparklined workbook in Excel 97-2003 (.xls) format wouldn't preserve the sparkline, but I was surprised when I opened a sparklined workbook in Excel 2007. The .xlsx file format hasn't changed, so I figured that my sparklines might be saved as images, or displayed as miniature line charts, but instead the cells just showed up blank.

I'm not sure if this behaviour will also show up in the final release, but it would be a real shame if one couldn't share sparklined workbooks with Office 2007 users. That apart, it's a very creditable effort from Microsoft and a valuable addition to the Excel toolbox.

Sparklines in Excel

You could be forgiven for thinking that a book entitled "The Visual Display of Quantitative Information" promises little in the way of entertainment if you're not familiar with Professor Edward Tufte's groundbreaking work in information design. Nothing could be further from the truth, though: this and all of Professor Tufte's books are classic studies in the history and principles of data presentation, the sort of books that reward the patience and attention of the layman just as much as the professional.

One of the most interesting concepts presented by Professor Tufte is that of sparklines; small, data-rich graphs displayed inline and in the context of documents and information graphics, providing a quick but detailed overview of trends and comparisons in data. A more complete explanation and some examples are presented on his website.

The concept has been around for some years by now, and you see sparklines popping up in all sorts of information displays recently, but support in software applications has been patchy, at best, meaning that it's relatively troublesome to create sparklines for your own data.

The best solution up to now, for my money, has been SparkMaker from the German firm Bissantz. This is an Excel add-in and it works beautifully, but is relatively expensive and suffers from the important limitation that, as a third-party add-in, it's not standard, so sharing sparklined files or working on multiple computers causes problems.

Microsoft have seen the light, however: Excel 2010 is going to offer native support for sparklines, which has to be the best improvement in some years to the unfairly neglected charting functionality. As far as I'm concerned, if the Microsoft implementation works well then it's reason enough on its own to upgrade. I hope that it's supported by the SQL 2008 R2 Reporting Services charting controls, too - imagine the level of detail you can add to performance reports if that's the case...