Executing a list of SQL scripts using SSIS

The redoubtable Jamie Thomson has made a simple and clear video demo of how SSIS can be used to execute a number of SQL scripts automatically. You can find the video here.

New application version

We went live today with a new version of Rijk Zwaan's custom-built BAS (Basiszaad Administratie Systeem) application. 360Data developed major new functionality for analyzing large data sets of gene test results automatically, thus saving many hundreds of man-hours of painstaking manual comparisons annually and making scenario and what-if analyses possible.

The implementation involved not only updates to the desktop application, but also setting up and configuring a new SQL server and interfaces to other systems.

360Data wishes Rijk Zwaan every success with the new functionality.

Things To Come

This week I attended a Microsoft TechNet session on the new and improved BI features that will arrive this summer in SQL Server 2008 R2 and Office 2010. The presentation was interesting and informative, but the products being presented were something of a mixed bag.

We first got a good look at PowerPivot, the Excel pivot table-local Analysis Services hybrid formerly known as Project Gemini. The performance, functionality and ease-of-use were certainly impressive given multi-million record datasets, but there seems to be no good reason to confine the functionality to Excel 2010. It's an add-in, and Excel 2007 supports large datasets, so there would seem to be no insurmountable technical reason to confine it to the new version. The MS take on the issue is apparently, "give Excel 2010 to a few power users, and the other users can access the workbooks thus created from SharePoint/Excel Services". Which may work fine for organisations that have implemented SharePoint, but leaves a lot of other companies in the cold. I'm not at all convinced that many firms are interested in upgrading to Office 2010 any time soon, and this smells like a marketing-driven decision to force upgrades in the absence of other compelling new features.

We saw the newest version of Report Builder in action, and to be honest it was a let-down. I was discussing it with another attendee afterwards, and we both felt that Report Builder is neither fish nor flesh. It's not full-featured enough to compete with professional reporting applications, and not simple enough to provide to users for basic or personal reporting needs. It's certainly better than the reporting tools included with the SQL 2005 BI Studio, but that's hardly the most ringing endorsement.

The biggest disappointment, though, was the first look at the long-awaited Master Data Management solution. This turns out to be little more than a data modelling tool with a very poor interface and a data store on the back-end. Populating the data store was out-of-scope (the sensible suggestion was that SSIS be used, but I'd expected at the very least some rudimentary wizards for building the pipes) and there was no mechanism whatsoever for enforcing the business rules defined for and in the central data model in the host applications and data stores. The idea is good and a market exists, but the execution, in this iteration at least, seems to leave a lot to be desired.

The last new product we got a look at was StreamInsight, an event response platform that will ship with SQL Server 2008 R2. The idea is, instead of checking for the existence of data meeting particular criteria in large volumes of fast-changing data, you define sets of criteria and filter your data through them. It sounds at first like the answer to many prayers, until one considers that SQL Notification Services did exactly the same thing in SQL Server 2005, only to be unceremoniously dropped with little explanation or apology less than three years later in SQL Server 2008. It's very difficult to expect customers and partners to invest time or money in technologies if they can't be confident that support and upgrade paths will exist in the future. Most businesses simply don't work with the three-year version cycles of Microsoft, so StreamInsight will be very much a wait-and-see proposition. Which is something of a pity.

It brings me no pleasure to complain about Microsoft; I've earned a living for many years on the back of their efforts and am always interested in seeing what they come out with next. The new crop of BI technologies don't seem to cut the mustard, though, in many essential aspects.

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.