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:
For the initial release of the Freehand SQL Data Provider, the following features are planned
- Report Conversion Tool, will be enriched to convert FHSQL DeskI documents into Web Intelligence reports using the FHSQL Feature
- Web Intelligence Documents containing FHSQL can be refreshed within the WebI Clients
- 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.
- Support of @Variables for FHSQL Data Providers
The Freehand Extension Point sample embedded in Web Intelligence would go into the direction as shown below
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
Fantastic!
Dear Merlijn Ekkel ,
May I know from where can I download this pack & the procedural document for the same ?
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
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!
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
Thanks for sharing Keep Sharing!
Hi Merlijn,
Has there been any further movement on the availability of this?
Thanks in advance,
Steph
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
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
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
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
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?
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
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.
An apology for my English
Thanks in advance,
Oliva
CORRECT
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.
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.
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
Thanks in advance,
Oliva
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
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
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
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?
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
Is it an Application Access right or a User Group Security setting?
Thanks for sharing this useful Info. We are looking for the SP.
How to create on command line parameters in webi in free hand SQL
Please advise
Thanks
Krishna M
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
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
In WEBI FHSQL, the stored procedure expression in a SQL script depends on the target database connection.
For example, you will write:
EXEC my_stored_proc
EXEC my_stored_proc %argument_list%
CALL my_stored_proc
CALL my_stored_proc (%argument_list%)
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.
Superb 🙂
Thank you for sharing this information!