FHSQL in BI4.1.5 – Limitations, Gotchas, and Troubleshooting

In my previous blog post, I talked about my experience with converting free-hand SQL Deski reports in BI4.1 SP05. The resulting Webi reports worked flawlessly. That’s not to say I didn’t run into any issues.

The fact that this feature is available is great. There are, however, a few limitations.

We’ll start with what the feature allows you to do:

1. Using Report Conversion Tool, you can now convert a Deski report created using FHSQL into a Webi report.
2. The FHSQL data provider can contain items like prompts, variables, etc… There aren’t any restrictions.
3. The resulting Webi report can be refreshed successfully. 😎

Here are a few limitations and gotchas:

1. The FHSQL feature allows you to convert a FHSQL Deski report to Webi, but you CANNOT edit the SQL in Webi.
2. At the moment, without the extension point, you cannot create new FHSQL Webi reports.
3. Please make sure that the database drivers used to create the FHSQL connection in XI3.x are installed (and supported) in the BI4.1.5 environment.
4. If you’re using the Report Conversion Tool on a client machine, please make sure that machine is upgraded to SP05 as well.

Troubleshooting Tips:

During my initial tests, I ran into a major snag. My Deski reports just would not convert. They would fail with an error: “Failed: Error on Migration Engine” & “Not Converted : FHSQL1 – .wid document could not be generated due to unknown reason”. Here are things I tried to resolve my issue.

1. Client side tracing. I followed KB: 1586166 – How to enable tracing for BI4.x client applications to turn on client side tracing while trying to convert the reports.
2. I tried using Conversion Audit option in RCT
3. I thought I needed other things in the biar file, so imported things like Repository Objects, Universes, etc…
4. I played around with the settings in RCT like Read Cell as Text
5. I retraced my steps from creating the report in XI3.1 to converting it in BI4.1.5

Client Side Tracing:

This KB 1586166 – How to enable tracing for BI4.x client applications is very useful. I copied the BO_Trace.ini file from the dir: <\SAP BusinessObjects\SAP BusinessObjects Enterprise XI 4.0\conf>

I followed Option 2: Tracing via environment variable. I initially just enabled regular tracing:

//==========================================================

//Trace Syntax and Setting

//

//uncomment the lines below to enable tracing via ini file.

//This will override the trace setting via command line

//

//==========================================================

sap_trace_level = trace_error;

sap_log_level = log_error;

//keep = false;

//size = 100 * 1000;

But I found that this didn’t get me the information I wanted. So I changed the following lines:

sap_trace_level = trace_error;

sap_log_level = log_error;

to

sap_trace_level = trace_debug;

sap_log_level = log_info;

This gave me verbose trace logs. Now don’t be surprised if there are multiple files generated. I saw the following:

All these files are important. The Report Conversion Tool calls are in the TraceLog file, but it’s a java process hence the java logs, and it also invokes the Universe Designer Tool, we believe, for connectivity information. If you run into any problems with RCT, and the issue isn’t obvious, then I highly recommend verbose tracing and collecting all these logs.

Now at the time of writing this blog, the reason for my issue was not apparent in the logs. We saw error messages, but it didn’t scream “I did it”. We have an open dialog with our development team about possibly improving our error messages.

Just recently we also discovered that the RCT might also be requesting information from the APS. I figured out the reason why my reports were not converting, which I will talk about, but I will update this blog after I run a new trace and this time include the APS in the tracing process. Stay tuned!

Conversion Tool Audit

Since I was not having any luck with trace logs, and my patience was wearing thin, I decided to try a different avenue of troubleshooting. My server has Auditing enabled so I thought I’d audit the RCT activity to see if it clearly states why my reports were not converting.

This KB is what I used as a guide: 1846268 – How to enable Report Conversion Tool Audit in BI 4.0

Things to keep in mind:

1. You need to enable auditing and have an auditing db.
2. The KB talks about using Universe Design Tool, so make sure you have 32bit drivers for your DB installed.
3. If you update the universe before you run the RCT, you will not see the appropriate table. Run RCT once with Auditing enabled(image below) and then you will see the table in UDT.
4. Don’t forget to enable the Audit settings in RCT as shown below every time you run RCT. I wish it stayed on all the time, but I can see how that’s not always appropriate or necessary.

Here is what the Report Conversion Tool Report looks like after converting 2 bad reports and 2 good reports.

Let’s take a look at the “Not Converted” report tab.

While the report gives good information, it didn’t tell me exactly why my reports were failing to convert.

Migrating other objects

I tried migrating other objects like Repository Objects, Universes, Rights, etc… Because my test was really simple, I really didn’t need anything but the reports. I can understand that as a customer, more likely than not, you would do a full system migration. Please note that while that’s perfectly fine, if you’re testing reports, using the method I mentioned in my previous blog is just fine.

Changing the settings in RCT

At this point I was grasping at straws, so I thought I’d change the settings in RCT. This was pointless as all I really needed was to check the check box that read: Convert documents containing free-hand SQL / stored procedures.

Retraced my steps

Finally I decided to retrace my steps. In doing so, I realized my mistake. I had created the FHSQL Deski report using a MS Access 2003 ODBC driver. In my BI4.1.5 environment, I didn’t have that driver installed. So I created a new FHSQL Deski report (although updating the connection would have worked too) using MS Access 2007 ODBC driver, migrated the reports and converted them SUCCESSFULLY!

Knowledge Base Articles

I will try to update this Blog with any helpful KBAs I come across.

1. 2101036 – BI 4.1 SP05- A Free-hand SQL based WebI report converted from DeskI fails to refresh with “login failed for user” error
2. 2110412 – Report Conversion Tool does not convert more than one free-hand SQL Data Provider
3. 2257621 – Report Conversion Tool fail to convert Free Hand SQL Desktop Intelligence – This bug is about Deski reports that have Oracle or Teradata Native Client connections, and when trying to convert them in 4.1, it fails. There is a workaround to use ODBC connection in the source report, but if there are 100s or FHSQL reports with the native connection, that might be difficult to do.

It took some trial and error, but we figured it out. In the process we were able to identify areas that need improvement, we defined a few possible troubleshooting techniques, and actually celebrated the fact that once it worked, it was awesome.

Other Resources

BI 4 How To: Planning the Desktop Intelligence conversion to Web Intelligence
Conversion from Desktop Intelligence to Web Intelligence

DeskI is not back… but here is something that will help you to move to BI 4

SAP BusinessObjects Web Intelligence 4.1: Calculation Engine Changes

Upgrade | SAP BusinessObjects Business Intelligence Solutions

First look at FHSQL reports in BI4.1

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