Skip to Content

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:

/wp-content/uploads/2014/11/pic1_591044.png

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.

Picture2.png

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

Picture3.png

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

Picture4.png

Picture5.png

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

To report this post you need to login first.

10 Comments

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

    1. Felicia Shafiq Post author

      Hi Abdul,

      In BI4.1.5, when you convert the FHSQL Deski report to Webi, there is no universe created. I know in XI3.1, a universe is created.

      Unless I misunderstood and you are asking if you can edit the Webi report. And while technically you can edit the Webi report, you can’t edit the SQL. That functionality is not in SP05.

      With the FHSQL Extension Sample, you can create new FHSQL Webi reports. But if you put incorrect SQL, you can’t edit it. You have to create a new SQL query.

      Hope this helps.

      (0) 
  1. Abdul Haseeb

    Hi Felicia,

    thanks for you reply how can i install extension  of FHSQL in SP05 and i am getting two types error while converting reports from 3.1 SP5 to BI4.1SP5 ie.

    “Failed : Error on intelligence server “

    “Not converted : missing references”

    can you please guid me how can i solve these problem .

    (0) 
    1. Felicia Shafiq Post author

      Hi Abdul, without knowing your environment, and your reports, its difficult to answer this, but I did a KB search:

      Search for: RCT Failed : Error on intelligence server

      Causes: at least 2 KBs that stood out. 1 could be that the report was saved with the option to not generate the SQL before running. (1924617 – Report Conversion Tool (RCT) gives error: Failed: Error on Web Intelligence server

      Or you need Administrative privileges on the machine you’re using RCT on:
      1586910 – Report Conversion Tool unable to convert Desktop Intelligence report to Web Intelligence report

      2081597 – Unable to convert Desktop Intelligence Report to Web Intelligence Report

      Some questions I would ask are:
      1) What DB are these reports based off?
      2) Are you using RCT on the server that has 4.1.5? Or on a client machine? If on a client machine, do you have that machine upgraded to BI4.1.5 as well?
      3) Are the DB drivers on the machine where you’re converting the same as the source environment?
      4) Have you tried running RCT as Administrator?
      5) Do these reports contain anything else besides FHSQL?

      If all else fails, then we look at logs.

      Also, here is the link to the FHSQL Extension Sample: http://scn.sap.com/docs/DOC-59985

      The Sample won’t fix your issues, but it will allow you to create new Webi reports with FHSQL as a data source. You could test your SQL here, and see if there are any issues.

      Hope this helps.

      (0) 
      1. Abdul Haseeb

        Felicia Shafiq ,

        thanks for reply here is detail of my setup we have two BO Server  and Two DB Server

        1.  BO Server 3.1 SP 5

        2.  BO Server 4.1 SP 5

        1.  DB for BO Server 3.1 SP 5

        2.  DB for BO Server 3.1 SP 5

        we have 130 FHSQL DESKI reports and 10 Universe DESKI base reports all the universe files converted successfully but Deski all reports not converted(20) other DESKI gave error “Failed: error on web intelligence server” all these DESKIreports are same structure only location filter different.

        All the DESKI based on FHSQL  reports that converted  gave error of “OLEDB Error SQL Structure” for this error i created ODBC connection on windows but all in vain

        Please advices

        (0) 
        1. Felicia Shafiq Post author

          Hi Abdul, I didn’t test with OLEDB. I tested with ODBC.

          Please confirm if this is what you did since OLEDB wasn’t working:

          1) In XI3.1, you created a new FHSQL Deski report using an ODBC connection.

          2) You migrated that report to BI4.1.5

          3) You used RCT to convert the report

          4) And the report still failed to convert?

          Did you try to convert the FHSQL Deski reports based off OLEDB in XI3.1? Did it work?

          I did a small test by creating a new OLEDB FHSQL Webi report in my BI4.1.5 environment, but I had some trouble with my SQL 2008 server. I won’t have time to test it further today, but I’ll dedicate some time to it on Monday. Please do let me know if the above workflow is what you followed.

          (0) 
  2. Rizwan Ahmed

    Felicia Shafiq,,

    thanks for reply

    See below what i did

    1. Migrated all the report from BO 3.1 SP5 to BO 4.1 SP5 
    2. Then Converts DESKI FHSQL OLEDB Reports to Webi
    3. and some reports failed to convert gave “Failed: error on web intelligence server” and other DESKI FHSQL converted successfully converted gave “OLEDB Error SQL Structure” error.

    am i need to create OLEDB connection on new BO Machine how can i do and how can i overcome these error

    (0) 
    1. Felicia Shafiq Post author

      Hi – sorry, I’m still testing this feature, and awaiting information from our Developers, but in the meantime, could you confirm that you’ve tried this:

      1) In XI3.1, you created a new FHSQL Deski report using an ODBC connection.

      2) You migrated that report to BI4.1.5

      3) You used RCT to convert the report

      4) And the report still failed to convert?

      ALSO

      Did you try to convert the FHSQL Deski reports based off OLEDB in XI3.1? Did it work?

      Thanks,

      Felicia

      (0) 
    2. Felicia Shafiq Post author

      I tested this on my own systems:

      in 3.1 – created FHSQL Deski report based off oledb conn to SQLSVR2k8

      Imported the report to 4.1

      Converted it successfully

      Webi version of the report ran successfully

      It might be that this issue you are seeing is report specific. If you’d like me to take a closer look at this, then I highly recommend creating a Support Incident. That way we can get your reports in-house and test it against your db. Also, tracing will help. Please make sure to take a look at the KBs mentioned in my initial comments as well.

      (0) 

Leave a Reply