Skip to Content
Author's profile photo Merlijn Ekkel

Freehand SQL (FHSQL) is coming to Web Intelligence

Dear All,

For all report developers and users that currently are using Desktop Intelligence and especially the well beloved Freehand SQL (FHSQL) function, there is some good news around the corner..

Today (Up to BI 4.1 SP04), the only option to adopt Web Intelligence, while using a Freehand SQL would be to convert your Desktop Intelligence document using a (automatically) generated Universe. This universe would then contain a derived table containing your FHSQL statement from the Desktop Intelligence document. Many users are not preferring this approach as it increases the time to enhance/maintain the SQL Statement as well as increases the complexity of the solution.

The great news I can share is that we have received approvals for the development of the FHSQL Function within Web Intelligence. With Freehand SQL in Web Intelligence it would enable you to (finally) convert your Desktop Intelligence documents with a FHSQL Data Provider to Web Intelligence. The implementation of the FHSQL function will be staged (so do not expect all features of DeskI FHSQL straight away)..

The current planned release of FHSQL for Web Intelligence will be as in the diagram below:

FHSQL in WebI.jpg

For the initial release of the Freehand SQL Data Provider, the following features are planned

  1. Report Conversion Tool, will be enriched to convert FHSQL DeskI documents into Web Intelligence reports using the FHSQL Feature
  2. Web Intelligence Documents containing FHSQL can be refreshed within the WebI Clients
  3. A sample Extension Point will be provided to enable report developers to create New WebI documents containing an FHSQL data provider or remove a Freehand Data Provider (NO EDIT). This sample Extension point needs to be deployed manually after the installation of the BI4.1 Service Pack 05.
  4. Support of @Variables for FHSQL Data Providers

The Freehand Extension Point sample embedded in Web Intelligence would go into the direction as shown below

FHSQL Labs Preview.jpg

After BI4.1 SP05, FHSQL will be further enhanced with additional features to close the gap between the DeskI Freehand SQL option including an integration into the UI.

The FHSQL Sample Extension (allowing creation of new / removing existing FHSQL queries) can be found here: Free-Hand SQL Extension Sample for Web Intelligence for 4.1 SP5

UPDATE 30 January 2015:

Currently the plan is to deliver the next round of features for the FHSQL Support in the BI4.1 SP06 release.. This would include the ability to create and modify FHSQL Statements within Web Intelligence documents. The existing FHSQL Extension Sample will be revisited and embedded within the WebI Clients (both Web and Rich Desktop) enabling you to fully utilize the FHSQL Solution with out-of-the-box options.

Once more details become available, I will update my blog post with the latest changes.

Kind Regards

Merlijn

Assigned tags

      29 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Gregory BOTTICCHIO
      Gregory BOTTICCHIO

      Fantastic!

      Author's profile photo Former Member
      Former Member

      Dear Merlijn Ekkel ,

                                        May I know from where can I download this pack & the procedural document for the same ?

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Ashish,

      BI4.1 SP05 is planned for release on CW47 of this year. For future reference, you can find out the release schedule via: http://service.sap.com/bosap-maintenance-schedule

      once SP05 is released, it's manuals will become available via help.sap.com. Once I received updates that matter in regards to FHSQL support in WebI, I will enrich this post.

      regards

      Merlijn

      Author's profile photo Former Member
      Former Member

      Hi Merlijn -

      We have converted deski documents with FHSQL using RCT 4.1 SP05 and don't understand how to work with the automatically generated FHSQL data sources and connections. 

      Now that BI4.1 SP05 has been released, is there any documentation and details available on how to obtain the FHSQL extension point?

      Thanks!

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi James,

      the Extension Point will be released on SCN in a few days.. I'm currently working with the team owning this on how to best expose this.

      I'll update you once the extension point has become available.

      regards

      Merlijn

      Author's profile photo Former Member
      Former Member

      Thanks for sharing Keep Sharing!

      Author's profile photo Former Member
      Former Member

      Hi Merlijn,

      Has there been any further movement on the availability of this?

      Thanks in advance,

      Steph

      Author's profile photo Henry Banks
      Henry Banks

      Hi,

      SP05 came out end of last year.  Did you want an update on the 'second wave' of this FHSQL implementation? 

      IMO I expect it will become part of the core packaged product with the advent of SP6. (why? because the delivery of significant new functionalities of this type aren't typically bundled into corrective Patches)

      Regards,

      H

      Author's profile photo Henry Banks
      Henry Banks

      Yes, that seems to be confirmed by the DEV colleagues over here: Free-Hand SQL Extension Sample for Web Intelligence for 4.1 SP5

      Regards,

      H

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Steph,

      For SP6 we plan to add the ability to add/edit/create new FHSQL queries within WebI.. SP6 is currently scheduled for Nov 2015

      Cheers

      ME

      Author's profile photo Former Member
      Former Member

      Hi Merlijn,

      Thanks for the clarification.  We have a very eager client base awaiting this as a mechanism to move to the latest platform.

      Cheers,

      Steph

      Author's profile photo Former Member
      Former Member

      Hi Merlijn,

      Is there an updated version of the RCT Guide for BO 4 SP5 detailing which DESKI features/formatting can not be successfully converted to WEBI?

      I also need to get details of the exact FHSQL functionality in WEBI 4 SP5/6 - is this available yet?

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Debbie,

      check the Report Conversion Tool Guide available via : http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp5_rct_user_en.pdf

      chapter 4.4.7.2, mentions the limitations

      Regards

      Merlijn

      Author's profile photo Former Member
      Former Member

      Hi Experts,

      I have a question regarding the process of Report conversion tool and I will  wish you could help me please.

      I have my reports "deski"  on environment 4.1.5, I am using the the Third Path  RCT guide,  but the reports can´t be converted to Webi.

      So, I have several questions about the Report Conversion Tool Guide.

      • "Designer" must be installed on the local machine.
        •   Designer is the Universe desing tool?
      • Report Conversion Tool needs to use the secured connection to the database that is saved in the CMS.
        • This in necessary only when I audit but it is no necessary for convert to Webi?
      • FHSQL_CreateUniverse=1
        • This is only necessary if i need the universe?


      An apology for my English


      Thanks in advance,


      Oliva

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author
      • "Designer" must be installed on the local machine.
        •   Designer is the Universe desing tool?

      CORRECT

      • Report Conversion Tool needs to use the secured connection to the database that is saved in the CMS.
        • This in necessary only when I audit but it is no necessary for convert to Webi?

      DeskI documents require to use a secured connection.. as you connect to the BI Platform for conversion, the connections available for WebI are only Secured Connections.. For auditing you can only use a secured connection within the Platform configuration.

      • FHSQL_CreateUniverse=1
        • This is only necessary if i need the universe?

      Correct.. If you want RCT to create a Universe from your existing FHSQL statement (derived table) you'll need to set this flag to generate a UNV and have the converted WebI report being pointed to the generated Universe.

      Author's profile photo Former Member
      Former Member

      Hi Merlijn,

      Thanks for the clarification.

      Perhaps my confusion was because when I did the connection in UDT and later in the CMC I did this:

      Log into CMC, click > Applications>Report Conversion Tool > Properties > select a connection for auditing and click Update.

      I understood that one be created only to audit no for convert, I need to do something else for mi conexion in CMC?

      So, When said

      • You must install Designer™ if you want to create an audit log or if you want to detect Free-Hand SQL and Stored Procedure reports.
        • This is only in client machine where I run the RCT or  in server machine too.
      • What happens when I have several reports  dependent on a BD and some more to another BD ,I can convert this "deskis" if i create different connections to differents DB in the UDT and  modify the RCT in CMC?


      Thanks in advance,


      Oliva

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Oliva

      the connect you defined under CMC > Applications > RCT is the one where RCT will store it's audit data (if selected during the RCT process). This connection has nothing to do with conversion of FHSQL..

      For RCT to enable the creation of a UNV, it requires the UDT SDK (part of the Universe Designer installation).

      We do not recommend to install UDT or any client tool on the servers for these kind of use cases.. the only reason to deploy the client tools on a server would be for quick validation of system health.

      if your source DeskI Documents are connecting to a secured connection, they are promoted by the UMT into the BI4.1 system and recognized (CUID based) by the RCT. All DeskI docs do migrate based on this model

      Cheers

      Merlijn

      Author's profile photo Former Member
      Former Member

      Hi Merlijn,

      Thank you for the post. This ll definitely help many.

      I have one query here: I am confused between 2 things DCP (Desktop Compatibility Pack) and Web Intelligence Free-Hand SQL (FHSQL) support. Can you please clarify if these 2 things are part of one solution or these are 2 different solutions as DCP may be a temporary solution (2017) and FHSQL is a permanent solution (it converts deski into webi).

      Regards,

      Mohit Bansal

      +91 9958519393

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Mohit,

      DCP is a solution enabling you to move the conversion phase to after an upgrade into BI4.x and is supported by the XI31 maintenance stream. This indeed means it's support will end by 2017 (end of 2015 for mainstream support).

      WebI FHSQL is a product feature that will keep existing in the WebI product-line, enabling a higher success rate of DeskI to WebI Conversions.. Many customer have been using the FHSQL feature in DeskI and do want to have them converted in a similar model to WebI. with the inclusion of FHSQL in WebI (full version to be released with BI41 SP06) these customers will be able to continue using FHSQL with their new client solution.

      Hope this helps

      Regards

      Merlijn

      Author's profile photo Former Member
      Former Member

      We have upgraded from 4.1.4 to 4.1.5 to 4.1.6 patch1.  There is no option for Freehand SQL. Is there anything as far as access that needs to be set in order to see FHSQL as an option for data sources?

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hi Randy,

      please check the security settings applied in the CMC. FHSQL has added a security setting, which by default is set to "not specified"

      Regards

      Merlijn

      Author's profile photo Former Member
      Former Member

      Is it an Application Access right or a User Group Security setting?

      Author's profile photo Former Member
      Former Member

      Thanks for sharing this useful Info. We are looking for the SP.

      Author's profile photo Mohan Krishna Seemakurthi
      Mohan Krishna Seemakurthi

      How to create on command line parameters in webi in free hand SQL

      Please advise

      Thanks

      Krishna M

      Author's profile photo Merlijn Ekkel
      Merlijn Ekkel
      Blog Post Author

      Hoi Krishna,

      in the WebIntelligence User Guide, Chapter 2.9.3, you will find the explanation of using the @Prompt and @Variable, which enable you to create "parameters" using FHSQL.

      you can find the manual via :  http://help.sap.com/businessobject/product_guides/sbo41/en/sbo41sp6_webi_user_guide_en.pdf

      kind regards

      Merlijn

      Author's profile photo Mohan Krishna Seemakurthi
      Mohan Krishna Seemakurthi

      i tried but nothing works out for me  can you throw me some example

      I have three prompts in Stored Procedure

      character, Date ,Date

      EXEC Stored_procedure  'USA','01/01/2015','01/11/2015'

      How to write this in Free hand SQL in WEBI Reports

      Please Advise

      Thanks

      Krishna M

      Author's profile photo Olivier TSOUNGUI
      Olivier TSOUNGUI

      In WEBI FHSQL, the stored procedure expression in a SQL script depends on the target database connection.

      For example, you will write:

      • SQL Server / Sybase

      EXEC my_stored_proc

      EXEC my_stored_proc %argument_list%

      • Teradata / DB2 / Informix

      CALL my_stored_proc

      CALL my_stored_proc (%argument_list%)

      • Oracle

      BEGIN my_stored_proc; END;

      BEGIN my_stored_proc (%argument_list%); END;

      etc.

      Sometimes, the stored procedure expression can also depend on the middleware type (native or jdbc or oledb). For example, the syntax {call my_stored_proc } is also supported on SQL Server from JDBC

      If your stored procedure contains DATE parameters, make sure to write its values in the DATABASE DATE FORMAT.

      Other point. Note that WEBI FHSQL also supports the extended SQL generated by UNV Stored Procedure Universes in order to facilitate the migration.

      Cheers,

      Olivier T.

      Author's profile photo Former Member
      Former Member

      Superb 🙂

      Author's profile photo Former Member
      Former Member

      Thank you for sharing this information!