First look at FHSQL reports in BI4.1
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.
Step 2: Create a connection to the reporting DB.
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.
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.
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.
Step 6: Once the Deski FHSQL reports are imported, use the Report Conversion Tool to convert them to Webi
Step 7: Confirm that the Webi reports have been created and published.
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!
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
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
We see following situation: Oracle FHSL is convertible - Teradata FHSQl is not converting at all?!
- Wobi
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
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
I usually recommend DeskI 3.1 > UMT > 4.1 > RCT into WebI
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
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
Thank your Henry for the tip, we are already in contact with SAP Support....
Wobi
Hello, I hope you've had success with the incident. I've also found a KB referencing the issue: http://service.sap.com/sap/support/notes/2165031
Thanks,
Felicia
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?
Hi Paul, please take a look at SAP Note: http://service.sap.com/sap/support/notes/2165031
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?
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
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
So: the fix will be public with SP6.3 -planned for CW 40 October 2015.
The private fix is availlbale via SAP Support at once!
We will try how it works!
yours
Wobi