Poor Quality in action: Microsoft BIDS

Microsoft SQL Server Business Intelligence Development Studio is an interesting product. The basic idea of it is simple: most companies have lots of databases floating around with information that's potentially useful to those in power. Hence, why not make it easy to produce lots of reports capable of digesting that information into pretty charts?

The idea is good. The implementation is atrocious. I'm currently stuck in the midst of one particular conundrum, which I wish to share.

BIDS uses the same interface as MS Visual Basic - it's a grid that you can position block elements such as charts and textboxes on. Whilst this is extremely effective for small reports, it's bloody awful for large ones - if you want to make a change to the size of the top element you have to select every other element of the report and shift them all a bit. Which you can't do. For reasons known only to themselves, Microsoft have made it very difficult to select more than a screen's worth of elements at any given time.

Of course, there are workarounds. The best one by far is to create a subreport - a separate report that can be embedded into the main report. This is represented in the main report by a fairly small block element, so can be easily moved around. A nested set of subreports can be used to create a fairly elegant layout. So far so good - why am I complaining?

The problem with this approach relates to the means by which data is imported into BIDS. Each report actually has two parts - a backend consisting of one or more SQL queries ("datasets"), and a frontend consisting of pretty charts etc. The upshot of this should be obvious: if you want to use the same dataset in more than one report, the only way to do it is to include a copy of the same SQL code in each report*. This is unmaintainable - with enough reports, you'll end up with multiple versions of the same SQL, all of which produce subtly different results. So much for the elegance of subreports.

I'm particularly peeved about this problem because it is so completely unnecessary. Simply by introducing the backend and frontend as separate objects that could be linked as appropriate, this quite major problem could have been avoided. But it's fairly clear that the developers of the system never thought of this - they just took the existing concept (individual reports a la Crystal Reports) and built a system that could create a bunch of them in parallel. This product is fundamentally not designed to produce suites of reports.

There is one positive upshot, though. Next time someone complains to me that Microsoft's offerings are so much more ready for primetime than Open Source stuff, I'll have a really good counterexample handy...

* There is another way, which is to use the SQL to create a View (basically a dataset) embedded in the database itself. However, most non-programmers can expect to receive write access to the database schema shortly after the mercury freezes in Satan's thermostat.