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.

New version

PER Plus Logistics of Zevenbergen have implemented the newest version of their workflow system, including changes to invoicing to reflect the new EU intracommunitaire VAT regulations, additional interfaces to their bookkeeping software and layout changes to documentation.

Invoicing from the system is now fail-safe: an invoice can't be completed, printed or posted if the customer's full address details aren't known. If the customer is an EU customer, the VAT number is mandatory and warnings are displayed if it's not present. Again, the invoice can't be completed, printed or posted if this is the case. No VAT is charged if the customer is based in an EU country (other than the Netherlands).

All this decision-making takes place behind the scenes, meaning that staff don't need to decide in each case how VAT should operate and preventing mistakes from being made.

360Data wishes PER Plus continued success with the application and looks forward to the next challenge.

Why bad data management is dangerous

There's a fascinating article in The Economist this week highlighting the role played by poor data management practices in exacerbating the global financial crisis last year. Data integrity issues, the lack of a "single version of the truth" and hand-built Excel calculations are phenomena common to many companies in many industries, but the effects described in this article can truly be described as earth-shattering.

Faster

A brand new laptop arrived here yesterday equipped with all the latest bits and pieces. Perhaps the most interesting component is the hard drive: instead of the traditional magnetic platter design, this one has a solid-state disk (SSD). This, for those who may not know, is basically an array of flash RAM chips much the same as the ones you use every day in your USB drives, but configured to work as a hard disk and to identify itself to the OS as such.

The disk is startlingly fast. I haven't run any proper tests on it yet, and don't have figures to prove my assertions, but what I've seen so far is that all disk-intensive operations (WinDirStat analysis, large installations) are completed in a fraction of the time of a "normal" disk drive.

This prompts me to wonder what such drives might accomplish in database servers. Disk reads and writes are often one of the major bottlenecks in poor-performing systems, and adding SSDs sounds like a cheap and easy way to boost performance. Or a mix of SSDs and magnetic disks; put tempdb on an SSD, logs (sequential writes, relatively few reads) on a magnetic disk, backups on magnetic, index filegroups on SSD... I'm really curious about what the real-world difference might be on a production system.