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

To report this post you need to login first.

29 Comments

You must be Logged on to comment or reply to a post.

      1. James Ball

        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!

        (0) 
        1. Merlijn Ekkel 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

          (0) 
    1. 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

      (0) 
  1. Merlijn Ekkel 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

    (0) 
  2. Stephen Cowan

    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

    (0) 
  3. Debbie Fisher

    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?

    (0) 
  4. Oliva Ramos

    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

    (0) 
    1. Merlijn Ekkel 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.

      (0) 
      1. Oliva Ramos

        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

        (0) 
        1. Merlijn Ekkel 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

          (0) 
  5. Mohit Bansal

    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

    (0) 
    1. Merlijn Ekkel 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

      (0) 
  6. Randy Hancock

    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?

    (0) 
    1. Merlijn Ekkel 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

      (0) 
      1. 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

        (0) 
  7. 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.

    (0) 

Leave a Reply