Search 800 + Posts

Mar 24, 2010

Oracle Reports to BI Publisher Reports Conversion

This Blog is about how ro convert rdf report to BI Publisher reports.

In this blog I will explain brief how to convert the Oracle report into BI Publisher report and then deploy in Enterprise BI Server or Oracle e-business Suite. (In this blog I will cover the Oracle BI Enterprise server, Steps are almost same for e-Business Suite).
In Oracle Report we are following the traditional approach, by saying that I mean
  1. Data Model
  2. Layout
  3. Translation

has binding and store in single file. Where as in BI Publisher we have separate file for Data Model, Layout and translation. For a Data Model system will pick the layout and translation at run time.
Conversion Utility that converts Oracle Reports to BI Publisher reports therefore generate several files when it converts oracle standard reports to BI Publisher reports.

Advantage Of BI Publisher reportsBI Publisher has ability to design/display highly formatted report from multiple data source.

BIPBatchConversion is the utility to migrate reports from Oracle Reports to BI Publisher.

This utility converts Oracle XML reports in BI Publisher reports, but if the reports are not in XML format then this utility will not converts them in one step.

Oracle Reports 9i onwards has option to store the reports in XML format, in this case there is no issue in conversion, but Oracle reports 6i are binary reports and stored in rdf format. To convert these reports customer need to install the Reports 9i on the same machine where they execute the conversion utility. During the conversion process BI conversion utility will call the executable rwconverter to convert binary reports in XML Reports.

UTILITY



As shown above conversion utility will generate the following output files for each report.
  • Data Template – Define data in XML format in BI Publisher (e.g. order.xml)
  • LOV - Parameter details of reports
    Above two are required if you want to use converted reports in Oracle E-business
  • .xdo file - Report definition file, can be used in Enterprise server and has meta data as well as data template. (e.g. order.xdo) (this is required only for Enterprise server).
  • . pls file – it has pl/sql package specification and body required to run the report. (e.g. order.pls)
  • . rtf file – It has Layout Template of report (e.g. order.rtf).
  • .log file – It will capture any thing that utility not able to convert, like format trigger. This utility will not convert the format trigger. And any trigger that this utility not able to convert will print in log file. (e.g. order. log).


What is Data Template?



Understanding of Data Template is very important. It is used by reports deployed in Oracle E-business as well as Enterprise Server.

It is XML format of defining data in BI Publisher. As shown above it has following section.
Parameter.

  • Sql Queries
  • Data Trigger
  • Data Structure.

Queries & Parameters -In Sql Query we can define our queries to pull the data. We can define multiple queries as well as queries with Master-Detail relationship .We can pass parameter to queries by defining parameters in Parameter section.
Triggers -We can include Format triggers here.
Structure –It is used to format the data. Data Engine interact with the database and retrieve the data in XML format based on the structure defined in this section.

Pre-Requisite
To run the conversion utilities you need the following jar files in your classpath:
  • Collections.zip -
  • xmlparserv2-904.jar -
  • xdocore.jar
  • aolj.jar
Conversion StepsRun the conversion Tool from Command Prompt
1.Load the PL/SQL package into Database
2.Upload report and template to BI Publisher Repository.
3.View Report
4.Check Log file for any manual step

Parameters for Conversion utility
-source — (required) Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.
-target — (required) Target directory to create Oracle BI Publisher report objects. This includes the Oracle BI Publisher Report file (.xdo), the layout template file (.rtf), the PL/SQL package, and log file.
-oraclehome — (optional) If your reports are in Oracle Reports XML format do not specify this parameter. If your reports are not in Oracle Reports XML format, specify the Oracle home path where Oracle Report Designer (9i or later version) is installed. BIPBatchMigration assumes that rwconverter is contained in the bin directory beneath the Oracle Home path.
BIPBatchConversion requires rwconverter from Oracle Reports to convert the report from RDF format to XML format.
-debug — (optional) To run the utility in debug mode and write debug statements to the log file
Capabilities
This utility easily converts
  • Tabular and Form based reports.
  • Report with repeating frames , headers and footers.
  • Single or Multi query reports with Grouping/Sorting, Parameters,Summy/formula colums, PL/sql functions etc.
Limitations
Customer may need to work on the layout to do some formatting as well as adding the Format Triggers.

For Demo /Further information please feel free to contact.

5 comments:

  1. Hi SR

    Do you have any recorded demo on conversion.We are on Oracle Reports 9i , and planning to migrate reports to BI Enterprise.

    Dean

    ReplyDelete
  2. This comment has been removed by a blog administrator.

    ReplyDelete
  3. I read the more elaborate thoughts that you posted about this in your blog (see the link in Franco’s comments above). I think your response is spot on and insightful. Thanks for weighing in on this so thoughtfully.

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete
  5. You mean if have report in 6i . to convert it to BI Publisher i'll use report 9i as a broker for convert . as you said above "but Oracle reports 6i are binary reports and stored in rdf format. To convert these reports customer need to install the Reports 9i on the same machine where they execute the conversion utility."

    ReplyDelete