Free-Hand SQL Isn’t Free

Editor’s note: I’m a bit embarrassed that this article has languished in draft mode for almost two years as I try to clean out my backlog of unfinished blog articles. But it’s still a relevant topic to discuss.

With custom dimension grouping finally added to SAP BusinessObjects Business Intelligence BI 4.1 SP2, the functionality gap between legacy Desktop Intelligence and its successor Web Intelligence is largely closed. One large gap remains, although it’s a controversial gap in my view.

Three words- Free-Hand SQL.

Free-Hand SQL is a feature of legacy Desktop Intelligence that allows a report to be created from a hand-crafted SQL statement. It’s typically used when there’s not enough time to create a universe. A highly normalized data model is sometimes too complex to model generically in a universe and nobody wants to bother transforming it into a star schema. Web Intelligence presently doesn’t support Free-Hand SQL, but the Report Conversion Tool does an adequate job of converting existing Desktop Intelligence documents that use it to Web Intelligence (see related article, Retiring Desktop Intelligence Free-Hand SQL).

The user interface for Free-Hand SQL is spartan, so the query is usually crafted elsewhere in a tool with better SQL editing features such as Microsoft Access, TOAD, or the SQL editor provided by the database vendor then simply pasted into a humble box in Desktop Intelligence.

It’s a pretty basic box, without any tools to help the Desktop Intelligence user fashion a well-written query. You can validate the final result, though.

My friend and SAP Mentor Greg Myers had this exchange a few years ago when Free-Hand SQL was first mentioned on the Web Intelligence product roadmap.

SAP’s Matthew Shaw writes eloquently on the SAP Community Network about the benefits of the semantic layer and drawbacks of Free-Hand SQL (see related article, Use of Semantic Layer over ‘free hand SQL’).

Matthew elaborates on several valid technical considerations, but this one stands out.

There is no central control over the SQL with free hand SQL. Should the database change, corrections needs to be made, or improvements made, then each and every document containing that free hand SQL needs to be inspected and manually updated. Compare this to the semantic layer where one change is made and that change is automatically propagated to all related documents.

Matthew Shaw, Use of Semantic Layer over ‘free hand SQL’ on SAP Community Network

In other words, maintenance nightmare! Free-Hand SQL is difficult to manage. If the data model or reporting requirements change, the task of tracking down and updating the offending SQL is tedious and time consuming. Of course, the manager that told you to “just get it done” with Free-Hand SQL won’t be around when the maintenance requests come in- she put “agile BI project management” on her resume and now has a much better paying job than yours.

SAP no longer supports SAP BusinessObjects Enterprise XI R2. But unfortunately, it still supports the decade-old philosophy that “all features of Desktop Intelligence must be crammed (eventually) into Web Intelligence”. In my view, a better approach would be to ask “where is the best place in the platform to support users that need to write free-hand SQL” and “what features can we add to the platform to support the lifecycle of free-hand SQL”. There’s an opportunity here to share SQL query design-time features across multiple tools in the BI suite, possibly leveraging some of the existing query builder functionality in Crystal Reports.

Although it first appeared on the official roadmap nearly two years ago, Free-Hand SQL in Web Intelligence does not have a public timetable. It is not mentioned in the “What’s New” document for SAP BusinessObjects BI 4.1 Support Pack 3 expected to ship at the end of this month. Unless SAP changes its mind, Free-Hand SQL is coming to a future version of Web Intelligence (UPDATE: Free-Hand SQL was delivered in BusinessObjects BI 4.1 Support Pack 6 – see related article Web Intelligence and Free-Hand SQL). But if we must have Free-Hand SQL in Web Intelligence, can we at least have something more elegant than a big box?

How do you feel about the SAP product roadmap for Free-Hand SQL?