As many have heard, free-hand SQL is making its long awaited debut in BI4.1, in the form of SP05. As an engineer in the Webi Product Support team, I was pretty excited to have access to an early release of the product, so I decided to test it out.

I’ve been with the company since the BO6.5 days and like many of our loyal customers, I liked Deski. It was feature rich and many times, for whatever reason, worked faster than Webi. One of the features it had was the ability to create reports using free-hand SQL.

One of the many benefits of using free-hand SQL was the fact that a Universe wasn’t needed. All a report designer needed was a connection to the reporting database and their SQL skills. Custom SQL was directly entered into a dialog box in Deski and data was returned. No muss, no fuss.

Because of the deprecation of Deski in BI4.0, and free-hand SQL not yet a part of the features in Webi, many of our customers refrained from upgrading. Now I know upgrading isn’t fun, especially a robust product like SAP BusinessObjects Enterprise, but at least we can cross off the inability to use reports created using free-hand SQL as a reason. I had limited time with the pre-release of Support Package 05, so I was able to do a simple test. I’ve documented my experience and recommend extensive testing for anyone interested.

*Note* You can’t actually refresh a migrated Deski report in BI4.1. I just want to make it clear that Deski isn’t back. 🙁 However, with Support Package 05, we now have the ability to convert FHSQL Deski reports into Webi reports. Also – before you migrate or convert, take a look at this fantastic document that talks about some things to be aware of: BI 4 How To: Planning the Desktop Intelligence conversion to Web Intelligence

Step 1: Create a Deski report in XI3.1 with FHSQL. Ahh the good ‘ol days. Unfortunately, I realized my SQL skills are a little rusty. So I created a very simple one. I chose Free-hand SQL as my datasource.

Picture1.png

Step 2: Create a connection to the reporting DB.

Picture2.pngPicture3.png

Step 3: Enter the custom SQL in the dialog box and click Run. Voila! Save and export the report to the repository. I created a second report with an @variable defined. In my case a prompt for Country.

Picture4.pngPicture5.png

Step 4: Create a .biar file with my reports. The Source system is XI3.1 and the destination is a .biar file. Because this was a simple test, the things I brought over are just the 2 reports.

Picture6.png

Picture7.png

Picture8.png

Picture9.png

Step 5: Bring the *.biar file to the BI4.1.5 environment and use Upgrade Management Tool to import the FHSQL reports. I’m sure Promotion Management Tool would work too, but because this was a simple test with just 2 reports, UMT was used.

Picture10.png

Picture11.png

Picture12.png

Picture13.png

Picture14.png

Step 6: Once the Deski FHSQL reports are imported, use the Report Conversion Tool to convert them to Webi

Picture15.png

Picture17.pngPicture16.png

Picture18.png

Picture19.png

Step 7: Confirm that the Webi reports have been created and published.

Picture20.png

Step 8: Moment of truth! I refreshed the Webi report with the prompt to make sure it worked. I initially created the report with “US” as a prompt value (shown). I changed it to France to make sure that it without a doubt refreshed!

Picture21.png

Picture22.png

I was quite happy and impressed with how well it worked. Don’t get me wrong, I ran into a few issues which I will talk about in a separate blog, but once I overcame those challenges, things worked beautifully. Back in the Deski days, Report Conversion wasn’t a trouble-free task. Regular reports didn’t convert for a number or reasons let alone FHSQL reports. SAP has brilliant customers that do amazing things with our product. Things we maybe didn’t think of doing, or testing. I remind you again that even though the above test worked awesomely, they really were simple reports. So if you’re considering upgrading now that Support Package 05 is available, TEST, TEST, and TEST some more. Good luck!

My next blog in this series: FHSQL in BI4.1.5 – Limitations, Gotchas, and Troubleshooting

For the latest information about Web Intelligence, check out the WebI Bulletin

To report this post you need to login first.

15 Comments

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

  1. Wolfgang Bidner

    Dear Felicia,

    we are in a migration project at the moment and we have 4.1.6 in use – which supports FHSQL in Webireports  too – but the conversion of our old deski FHSQL fails with: Free-hand SQL connection problem Not available in Web Intelligence

    Since we could  convert a simple FHSQL with the emoden connection it is possible but we think our deski connection is not usable for the conversion with RCT – what would you suggest to check on the old FHSQL connections? Yours Wobi

    (0) 
      1. Felicia Shafiq Post author

        Hello Wobi, I’m sorry for the delayed reply. I was away on vacation. There are a few things you could check.

        1. The Teradata driver version needs to be the same as the XI3.1 version. You need to have both the 32bit and 64bit installed.

        2. This could be a potential bug – I’ll have to find the SAP Note.
        3. Right click RCT – choose to run as Administrator

        4. In RCT, is your source and destination 4.1? Or are you converting from 3.1 into 4.1? Try migrating 1 or 2 reports into 4.1 and see if that works.

        5. Do you have the Deski Compatibility Pack installed? Can you run your Teradata report there? See if the report/connection works in DCP.

        6. Can you create a new webi report in 4.1 using the Teradata connection? If you’re using 4.1 SP6, you should now be able to create a FHSQL webi report. See if that works.

        These are a few things I could think of. Please take a look at this as well:

        Calculation Engine Changes: http://scn.sap.com/docs/DOC-39973
        http://wiki.scn.sap.com/wiki/display/BOBJ/Conversion+from+Desktop+Intelligence+to+Web+Intelligence?original_fqdn=wiki.sdn.sap.com

        Upgrade and Conversion: http://scn.sap.com/docs/DOC-41571

        Blogs:
        http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/11/19/first-look-at-fhsql-reports-in-bi41
        http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/09/29/freehand-sql-fhsql-is-coming-to-web-intelligence

        (0) 
        1. Wolfgang Bidner

          Dear Felicia,

          thank you for your valuable Response – we tried RCT from BOXIr3 onto 4.1 and also upgrade first from Xi to 4.1 and do RCT then – and our current Situation is, that we have the same terradata Driver Versions on all these … –

          We may test the DCP … I will study your links and if we come up with any findings update this thread!

          Yours

          Wobi

          (0) 
          1. Wolfgang Bidner

            Within our sandbox System we tried all different TD Driver Versions – it did not work. Did anyone ever succeed in converting a TD FHSQL Report from BOXr3 to Bi 4.1.6?

            – Wobi

            (0) 
            1. Henry Banks

              Please do contact Technical Support via an incident if you think you’ve trapped an bug. Alternatively they can help you resolve it step by step 

              regards,

              H

              (0) 
  2. Paul Sitch

    We’ve recently got a test area for BI4.1 SP6, I am following the instructions above and getting the same error as Wolfgang, for both Oracle and Teradata FHSQL.

    Can anyone advise things to look out for?

    (0) 
      1. Paul Sitch

        Thanks Felicia, however, I am unable to open that link, I get a grey screen saying ‘Document is not released’ Would you be able to tell me what is says or advise how I can access it?

        (0) 
        1. Felicia Shafiq Post author

          Hi Paul – The SAP Note is being modified, so it’s not externally visible at this time. It was before, but I believe they are updating it with more information either about the issue or a possible fix.

          HTH,

          Felicia

          (0) 
  3. Wolfgang Bidner

    I was on vacation – that delayed my Feedback: meanwhile it is confirmed: it’s a bug – at least what is related to teradata. We are waiting for a possible date for a bugfix! … I will update asap I get News from SAP Support!

    – yours

    Wobi

    (0) 

Leave a Reply