Skip to Content

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.

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

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