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.

Desktop Intelligence 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.

Desktop Intelligence Free Hand SQL Query Panel

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.

Desktop Intelligence Free Hand SQL statement is correct

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?

Dallas Marks

Dallas Marks

I am an analytics and cloud architect, author, and trainer. An AWS certified blogger, SAP Mentor Alumni and co-author of the SAP Press book SAP BusinessObjects Web Intelligence: The Comprehensive Guide, I prefer piano keyboards over computer keyboards when not blogging or tweeting.

7 thoughts on “Free-Hand SQL Isn’t Free

  1. Hi Dallas

    I fully agree with your blog post! What helps me a lot to talk to various stakeholders about a topic like the usage of Free-Hand-SQL is the term “technical debt”. Free-Hand-SQL (or Custom SQL too) definitely makes you pay “interests” earlier or later. Therefore, if there are reasons to make such debts for a quick win you should also plan to pay back your debts, e.g. get rid of Free-Hand-SQL as soon as you can do it. You can find an interesting blog about it here: http://martinfowler.com/bliki/TechnicalDebt.html

    Cheers
    Raphael

Comments are closed.