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.
- 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?
19 thoughts on “Web Intelligence and Free-Hand SQL”
I prefer the best of bread -enabling users to run free hand but with row level security ,I don’t think enabling them to analyze Sales, HR and sensitive data is a good scenario.
Although the build in Free hand capability is a new feature in SAP BI4.1 SP6, implementing a Free hand creation dynamically, secured and easily maintained solution is old news that can be delivered in version 3.1 as well
Converting an existing free hand report in Desktop Intelligence is another thing solved from BI.4 SP5
Great news! but little bit disappointed that FHSQL is not available in HTML version hope SAP has a plan to release it in the future.
I wish I could post the ‘Prepare Yourself’ meme with ‘Freehand SQL is coming’ but you’ll just have to use your imagination 🙂
Mike, I added one just for you. Thanks for writing!
LOL, thanks! 😀
I don’t see and edit query option ?
Look closely at one of my screen shots and you’ll see an “Edit SQL” button in the bottom left corner of the query panel.
Yes,i see it now,thanks
Are there any security or access settings for FHSQL to show up as a data source? We have upgraded to 4.1 SP6 – Patch 1 and FHSQL is not an option. SAP is wanting me to uninstall everything and reinstall a new SP6 fresh install. Needless to say I’m not wanting to do that yet…..
Thanks for writing. I’m not aware of any security settings. Remember that the HTML version of Webi doesn’t have FHSQL. Only the Java version and the Rich Client. You might want to try the latter as an experiment even if you don’t roll out Rich Client to the enterprise.
I’ve installed SP6 (no patches yet) for one customer and did not have this issue. Are there additional issues why SAP wants you to reinstall? Something that I might want to check on my own installation?
Thanks for the reply. I have tried applet and WRC versions as well and no FHSQL options. The new install is just SAPs suggested resolution. No specific reason
Hmmm. That’s just weird. Have you confirmed that SP5 wasn’t installed by accident?
Hi can any help me here……
I have open the report in view of and click on the edit query there is an error is showing “the data provider can not edited because it is based on free hand SQL” Can anyone help me resolve this.
Thanks in advance…
You’re using the a viewer that doesn’t not (yet) support Free-Hand SQL. You’ll need to use the Java applet or Rich Client.
Is there a limit on the length of the free-hand sql that could fit in the query editor? I couldn’t not find this info. anywhere on the web. Hoping you might have knowledge on this.
Thanks in advance
I do not know the answer, but let me see if I can get somebody from the Web Intelligence product team to answer your (great) question.
There is technically no limit on the length of the free-hand sql that could fit in the WebI FHSQL query editor.
But, some DBs may have some limits that the WebI FHSQL query editor will not know.
Thank you for the clarification. Appreciate it!!
Comments are closed.