Retiring Desktop Intelligence Free-Hand SQL

Historically, it’s easier to create a SQL query and slap it into a Desktop Intelligence report than build or augment a universe. There are two methods to do this. The first method is to use the New Report Wizard to create a free-hand SQL report instead of choosing a universe. The second method uses a universe to build an initial query which is further edited in the Edit SQL panel. The Report Conversion Tool (RCT) can handle both cases – let’s take a closer look at what happens. I’ve used SAP BusinessObjects Enterprise XI 3.1 SP3 to generate my examples. In either case, I must convert these reports to Web Intelligence if I want them to continue to exist in SAP BusinessObjects Business Intelligence 4.0.

Let’s first consider a free-hand SQL query. For a simple illustration, I have used the eFashion universe to build the query and pasted its SQL into a new Desktop Intelligence document.

The Report Conversion Tool will examine the document’s SQL and build a derived table universe (first introduced in BusinessObjects Enterprise XI R2) with a single derived SQL table that embeds the query. Here is what the derived table looks like in the universe.

For the next example, I used Desktop Intelligence to build a standard query with the eFashion universe. But I then modified the SQL using the Edit SQL dialog box in the query panel. Unfortunately, it’s not immediately obvious when you open the query panel that the query has been customized. For example, the query panel below appears to show a simple query with Year, State, and Sales Revenue.

However, when the Edit SQL dialog box is opened, it becomes clear that the query has been customized with a WHERE clause limiting the year to 2004. To maintain the custom SQL and prevent the report from reverting back to the universe-generated SQL, I clicked the Do not generate SQL before running box when I authored the query.

When the Report Conversion Tool is used to convert this document, it does not create a new universe. It continues to use eFashion but also customizes the Web Intelligence SQL. As with Desktop Intelligence, this customization is not obvious from the query panel.

However, as with Desktop Intelligence, the customized SQL is visible from the View SQL dialog.

The good news is that in both cases, the Report Conversion Tool was able to convert the Desktop Intelligence report to Web Intelligence. However, there is some bad news, particularly for the free-hand SQL report. First, the Report Conversion Tool creates a cryptically named universe and places it in the Report Conversion Tool universe folder. If your environment has lots of free-hand SQL reports, you are going to end up with a large collection of small universes. It’s a support nightmare – the BI equivalent of suburban sprawl. In addition, reports that contain prompts with lists of values (LOV) will generate some pretty nasty LOV queries from the derived table. Frequently, the performance of the LOV queries is sub-par.

Although the universe LOVs can be modified to run faster, you should be always ask yourself if a universe is truly needed anytime Designer is opened. You’ll be much better served by a smaller number of universes that know how to answer many business questions and not a multitude of universes that each power a single report. When I help customers with migrations, I  prefer to perform an initial run of the Report Conversion Tool on all reports except the Free-Hand SQL reports (by leaving the “Convert reports containing free-hand SQL” box unchecked). The Report Conversion Tool will flag the Free Hand SQL reports as “not converted”. Then, a simple query on the RCT audit table will generate a list of these reports. Next, I’ll work with the customer to see if any of the reports can be retired or redesigned in Web Intelligence. Ideally, some of these reports can be recreated using a universe instead of one generated by the RCT.

If you decide that the new derived SQL universe must stay, take a moment to look at the WHERE clause of the derived table. Try to move as many restrictions as possible out of the universe and into the Web Intelligence report as Query Filters. Fewer restrictions will make the universe more generic and capable of answering more questions (and satisfying future report requests) than the original report. For commonly used restrictions, add predefined filters in the universe to make report creation easier.

If the odds are unlikely that nobody outside of IT will directly use the universe generated by the Report Conversion Tool, evaluate if it makes more sense to replace the free-hand SQL Desktop Intelligence document with a Crystal Report. Honestly, I’ve been surprised that Crystal Reports is barely mentioned by SAP when discussing Desktop Intelligence. There’s even talk of adding free-hand SQL to a future (post BI 4.0) release of Web Intelligence (see related article, Free-Hand SQL Isn’t Free). So perhaps my advice to choose Crystal Reports is ill-advised? Would love to hear everyone’s thoughts.

It’s the end of the world as we know it— time to send Desktop Intelligence reports into retirement. But thankfully, the Report Conversion Tool, even in XI R2 and XI 3.1, can help us reach our goal. Happy conversions!