Reporting

Web Intelligence and Free-Hand SQL

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.
SAPBI41_SP6_Webi_Free_Hand_SQL_01_A
Next, I’ll choose a universe connection.

SAPBI41_SP6_Webi_Free_Hand_SQL_02_400

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

SAPBI41_SP6_Webi_Free_Hand_SQL_03_400

 

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.

SAPBI41_SP6_Webi_Free_Hand_SQL_04

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.
SAPBI41_SP6_Webi_Free_Hand_SQL_05_400

That is why you’ll want to add aliases to your SQL statement.

SAPBI41_SP6_Webi_Free_Hand_SQL_06_400

You can manually rename objects, which is helpful for setting column headings. Here I changed Sales_Revenue into Sales Revenue.

SAPBI41_SP6_Webi_Free_Hand_SQL_07_400

And voila! The results are exactly what we expect. Except unlike data from the eFashion universe, measures aren’t well-formatted.

SAPBI41_SP6_Webi_Free_Hand_SQL_08

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?

 

SAPBI41_SP6_Webi_Free_Hand_SQL_09_A

Choose the desired numeric format.

SAPBI41_SP6_Webi_Free_Hand_SQL_10

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…

SAPBI41_SP6_Webi_Free_Hand_SQL_11_400

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.

SAPBI41_SP6_Webi_Free_Hand_SQL_12_400

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

What are your plans for Web Intelligence 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.

Related Posts

19 thoughts on “Web Intelligence and Free-Hand SQL”

  1. Exciting news,

    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

  2. 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.

  3. I wish I could post the ‘Prepare Yourself’ meme with ‘Freehand SQL is coming’ but you’ll just have to use your imagination 🙂

    1. Yoav,

      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.

      Regards,
      Dallas

  6. 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…..

    1. Randy,

      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?

      Dallas

      1. 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

  7. Pingback: Free-Hand SQL Isn’t Free

  8. 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…

    1. Datta,

      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.

      Dallas

  9. Dallas,

    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

    1. 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.

      1. Hello,
        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.
        Regards,
        GB

Comments are closed.