SAP has put yet another nail in the coffin of Desktop Intelligence with Free-Hand SQL in the recent release of Support Pack 6 for SAP BusinessObjects Business Intelligence 4.1. Steve Yemm has put together an excellent tutorial on the SAP Community Network (see related SCN article, Web Intelligence Free Hand SQL (FHSQL)/Stored Procedures in BI4.1 SP06). I’d like to show just a couple of extra formatting nuances not in Steve’s article.
Free-Hand SQL isn’t a silver bullet (see related article, Free-Hand SQL Isn’t Free), but it is a bullet. It can help query data that no existing universe can access. Web Intelligence now uses a workflow that will seem very familiar to Desktop Intelligence users.
I’m going to use the Web Intelligence Java applet and choose the new Free-Hand SQL option for creating a new document.
Next, I’ll choose a universe connection.
For my query, I’ll paste SQL from an eFashion query for Year, State, and Sales Revenue into the Query Script editor, essentially a large text box.
SELECT Agg_yr_qt_rn_st_ln_ca_sr.Yr, Agg_yr_qt_rn_st_ln_ca_sr.State, sum(Agg_yr_qt_rn_st_ln_ca_sr.Sales_revenue) FROM Agg_yr_qt_rn_st_ln_ca_sr GROUP BY Agg_yr_qt_rn_st_ln_ca_sr.Yr, Agg_yr_qt_rn_st_ln_ca_sr.State
The Query Script “editor” provides the same editing features as its Desktop Intelligence predecessor- none. However, it is possible to validate that the SQL you pasted from elsewhere is valid.
Notice that Web Intelligence inferred that the aggregate function SUM should be interpreted as a measure object. However, the object naming isn’t terribly creative.
That is why you’ll want to add aliases to your SQL statement.
You can manually rename objects, which is helpful for setting column headings. Here I changed Sales_Revenue into Sales Revenue.
And voila! The results are exactly what we expect. Except unlike data from the eFashion universe, measures aren’t well-formatted.
Simply right-click on any value in the Sales Revenue column and choose Format Number. It’s near the bottom of what seems to be the world’s longest right-click menu. Does anyone else hope that Web Intelligence 4.2 will have shorter right-click menus?
Choose the desired numeric format.
And there you have it, a Web Intelligence document that uses Free-Hand SQL.
Some additional observations. The new Free-Hand SQL is also available in the Web Intelligence Rich Client…
However, it is missing from the HTML panel.
UPDATE: The Free-hand SQL option now appears in the BI 4.2 SP4 HTML panel, where only the Analysis View query option is missing.
This fact isn’t surprising, since Excel, Analysis View, Text and Web Service options are also missing from the HTML panel. Let’s hope that SAP announces some clear plans on how it intends to bring the HTML panel to feature parity with its two peers (see related SAP blog, SAP BI 4.2 SP3: What’s New in Web Intelligence). Someday, I hope that new Web Intelligence features first appear in the HTML panel.
In addition to creating new Web Intelligence reports from Free-Hand SQL, this feature provides new capabilities to the Report Conversion Tool, which were actually introduced earlier in Support Pack 5. Prior to SP5, Desktop Intelligence documents with free-hand SQL were converted by placing the SQL into a derived table of a new universe (see related article, Retiring Desktop Intelligence Free-Hand SQL). This approach could become problematic when hundreds of Desktop Intelligence documents were spawning hundreds of new single-use universes. The Report Conversion Tool no longer needs to create a universe to successfully convert free-hand SQL documents.
Additional Resources
- Web Intelligence Free Hand SQL (FHSQL)/Stored Procedures in BI4.1 SP06 by Steve Yemm on SAP Community Network
What are your plans for Web Intelligence Free-Hand SQL?