Oracle Reports Migration to BI Publisher for EBS – Part 1

BI Publisher (XML Publisher) in EBS

Oracle EBS is the first application which had integrated with BI Publisher more than 5 years ago and converted its main reporting platform to BI publisher from Oracle Reports and now it's providing over 2000 pre-seeded BI Publisher base reports. So the integration is pretty mature and robust. Because of this long history BI Publisher is still called as ‘XML Publisher’ in EBS, which is the original product name before changing it to ‘BI Publisher’.

 

Two Parts of BI Publisher Reports Definition

As you know BI Publisher has separated the report query and layout to different layers and uses Data Template for the query portion and RTF Template for the layout so users can focus on just the data query or just the report layout without bothering the other. If you want to change the report layout you can make such modification in the RTF Template without touching the Data Template. This has brought a much flexible reports development approach and reduced the maintenance cost.

This two parts of the reports definition means you can use just one of them with another technology to generate final reports output. For example, if you already have an application or framework to produce a data in a XML format then you can just use BI Publisher RTF Template to generate reports on top of the XML data. This also applies to the Oracle Reports base EBS reporting, where you can use Oracle Reports to generate the XML data and use BI Publisher to generate report outputs based on the data.

Complete Conversion vs. Halfway Conversion?

In EBS you have two options to convert your Oracle Reports base reports. One is a complete conversion, which converts Oracle Reports RDF to BI Publisher RTF Template and Data Template. After the conversion you will have just BI Publisher to generate the data and the report outputs. Another is a halfway conversion, which converts only for the layout portion to RTF Template but keeps the data query portion so Oracle Reports still keep taking care of querying and generating the data and BI Publisher will take the XML data and marry with RTF template to generate final report outputs.

Both options have pros and cons. The complete migration option would more make sense if you think about a long term. Most importantly you don't want to have two different tools to develop one report, which would cause you to keep Oracle Reports resources and BI Publisher resources including machine resources, human resources. Also BI Publisher's query generation engine in general produce better performance, typically 40% or more so why not just convert everything to BI Publisher ?

On the other hand, BI Publisher doesn't offer UI base Data Template builder so you need to develop necessary SQL queries by using your SQL tools while Oracle Reports offers a UI base Query Builder where you can define queries, parameters, grouping, etc graphically. So some users who might have been used to the graphical tool to develop such data model might have a hesitation to give it up. Also, converting the data query portion means more work hence higher cost for the entire conversion project itself in a short term. If the main objective of the migration is to take advantage of the BI Publisher's flexible and easy pixel perfect production ready reports layout development capability then you might want to convert only the layout portion first then convert the data model part later.

I’m going to focus on how to convert the Oracle Reports base reports to BI Publisher base reports including both the query part (Data Template) and layout part (RTF Template) first. The detail of how to register the migrated Data Template and RTF Template will be discussed separately in another post.

Reports Conversion Process

The migration process involves the following steps

1. Obtain the original Oracle Reports report definition file (either as an .RDF file or an XML output of the report) that needs to be migrated to BI Publisher.
2. Run the BI Publisher Reports conversion utility
3. Deploy the PLSQL package to the database. (Optional)
4. Validate and Update Data/RTF Template
5. Add additional layout/formatting requirements to RTF Template (Optional)
6. Register the Data/RTF Template to XML Publisher Repository in EBS

Conversion Tool Overview

The conversion process uses a reports conversion utility, which is a Java command line tool and automatically converts Oracle Reports definition files to BI Publisher related files. Here is a list of the files generated by the tool after the conversion.

1. BI Publisher Report definition file that includes the Data Model (.xdo)
   
   This is an xml formatted file that contains BI Publisher report definition. This is required only if you want to use BI Publisher Enterprise Server (Standalone version) instead of EBS embedded version to manage and run the reports.
   
2. BI Publisher Data Template file (.xml).
   
   This is a xml file containing the report data model definition. This is required when you want to convert to a full BI Publisher. If you want to keep your Oracle Reports to generate XML data then you don’t need this file.
   
3. PL/SQL Package specification and body creation file (.pls)
   
   This is a PLSQL package file, which contains the plsql code used in Report triggers. This PLSQL package should be deployed to the database. This package will be called in the BI Publisher report from the Data Template as Before/After triggers to provide the same functionality as the Before/After triggers of Reports file. Again, you need this only for the complete migration.
   
4. BI Publisher RTF Layout Template (.rtf)
   
   This is the RTF Template file, which has the report layout definition. Users can open this file with MS Word and modify the layout and formatting if required.
   
5. Log file (.log)
   
   This is a log file that contains issues/warnings during the conversion and a log of unconverted objects from the Reports definition file (RDF)
   
6. List of values file (.xml)
   
   This file is generated when the parameters in the Oracle Reports file use List of values.

Manual Tasks after the Conversion

Although the conversion utility does the reports conversion and allows the overall conversion experience much easier, it doesn’t complete an end to end conversion process. After the automatic reports conversion with the tool you will still need to perform the post conversion tasks, which include deployment of the PLSQL package, manual layout adjustment, additional conditional formatting and calculations for the RTF Template, etc. Depends on how complex the original reports are and the result of the conversion by the tool the time required for the post conversion tasks would be different and it would go longer. Therefore, before you start the conversion it is very critical to review and define the original reports requirements so that you can validate the converted report outputs based on the defined requirements and meet your business requirements efficiently with the conversion process without spending too much time on something that is not required anymore in today’s business environment.

Here is a list of typical manual tasks required for RTF Template. :

1. PLSQL Format Triggers – In Oracle Reports you can use PLSQL format triggers to format data in the reports. The utility creates a placeholder for the all PLSQL format triggers but without adding any functionality. You need to write code in the BI Publisher RTF Template to enable the formatting trigger functionality.
2. Color Coding of the Layout - You need to add all color coding manually.
3. Number/Date Formatting - The BI Publisher report uses its default data formats and not the formats in the original report. Items need to be assigned formats manually.
4. Images – Images are not converted. You need to manually add the images.

Converts RDF or XML?

With the BI Publisher reports conversion utility you can start the conversion with either Oracle Reports definition files (RDF) or XML format report definition files which can be generated with Oracle Reports 9i or later version. If you want to convert from the RDF files then you need Oracle Reports Developer to be installed on your machine where you will run the BI Publisher reports conversion utility. The conversion utility internally calls the Oracle Reports Developer and convert the RDF files to XML files first then it will convert the XML files to a set of BI Publisher report related files. If you don’t have the Oracle Reports Developer installed yet you can download it from here.

In this post I will use the RDF file. You can copy the RDF file under the EBS Apple Top on the file system. Once you obtain the RDF file you’re ready to start the conversion process. I’ll cover the step-by-step process tomorrow, so stay tuned!