Oracle Reports Migration to BI Publisher in EBS – Part 2

Run the Conversion Command Line Tool

The conversion utility is a Java command line tool and requires a set of BI Publisher related Java library (JAR) files, which you can find from your BI Publisher Server or Desktop installation. In order to run the conversion utility you need to set CLASSPATH with the library files first. If you have BI Publisher server installed then the below is an example location where you can find such JAR files.

e.g. D:\OracleBI\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib

Here is a list of the library files that are required to be set in the CLASSPATH.

• Collections.jar
• xmlparserv2-904.jar
• xdocore.jar
• aolj.jar

The conversion command line tool takes the following parameters:

Parameter Name	Required	Description
source	Must	Source directory for Oracle Reports files. All reports must be in the same format - either RDF or XML.
target	Must	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. This folder must exist before the command is run.
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.
debug	Optional	To run the utility in debug mode and write debug statements to the log file.

Here is an example of how you can run the command line tool. First make sure you have set the CLASSPATH, then run the tool.

>set CLASSPATH= D:\lib\collections.jar; D:\lib\xmlparserv2-904.jar; D:\lib\xdocore.jar;D:\lib\aolj.jar >java.exe oracle.apps.xdo.rdfparser.BIPBatchConversion -source D:\Reports\ARCUST.rdf –target C:\Reports\output -oraclehome D:\DevSuiteHome_1012 –debug

After you run the tool successfully you should get a set of files including Data Template, RTF Template, PLSQL package files, log file, etc. Now you need to review the log file, validate each file and perform the post conversion tasks.

Validate and Compile PLSQL Package

PLSQL Package specification and body files are generated from the conversion tool. This PLSQL package can be called from BI Publisher Data Template as a report trigger. It can be called before the report run (Before Trigger) or after (After Trigger). This PLSQL package needs to be reviewed and you need to manually run the files against a database to create. This is required only when you use Data Template to generate the reporting data. If you will keep using Oracle Reports to generate the data then you don’t need the PLSQL package to be created in the database.

 

Validate Data Template

Now you need to validate the Data Template file. Make sure all the SQL queries are right and the SQL columns and XML elements are mapped appropriately. When you have multiple queries and they have parent-child relationship the XML structure definition might get complex so you want to make sure the grouping structure in the XML structure definition is defined correctly. Also, the Before/After triggers are called in this Data Template so you need to review the PLSQL package and function names. Lastly the parameter definition needs to be reviewed carefully. Any parameters which are used in the PLSQL package are also need to be defined here correctly.

Validate RTF Template

The layout and formatting are very hard to be precise especially when the reports are pixel-perfect and have complicated logics. So you want to review the converted RTF Template and start updating it to mimic the original report layout or follow the newly defined report layout requirements.

When you validate the RTF Template you would need a XML data file to preview the report final output in a certain format such as PDF. You can download the XML data file by running the original concurrent program after changing its output format to ‘XML’.

Post Migration Tasks - Check Log File

When you look at the log file, which is generated by the conversion tool, you will find the issues encountered during the conversion and manual changes required to complete the report. The below is a part of an example log file content, which shows one object called ‘F_COMM’ was not converted by the tool. 

The following format triggers which are the part of RDF, has not been supported in this Template: -------------------------------------------------------------------------- Object Name : F_COMM ========================================================= PL/Sql code function F_COMMFormatTrigger return boolean is … …

This is because the ‘F_COMM’ uses ‘F_COMMFormatTrigger’ formatting trigger, in Oracle Reports and that is not supported by the conversion tool to be converted. So you need to manually convert this functionality into the RTF Template file by using ‘Conditional Formatting’ functionality.

So make sure that you review the log file and see what is not converted and manually add such functionality to the RTF Template file if necessary. Not being converted by the tool doesn’t mean BI Publisher can not support such. Any functionality available in Oracle Reports can be supported by BI Publisher, it’s just a different way to implement the same functionality. One thing to note, since BI Publisher report consists of two parts, one is a data generation and another is layout and formatting part. So something to do with data such as calculation, aggregation, condition, etc can be done by either Data Template or RTF Template. It really depends on the requirements. In general it’s better to do such in Data Template because 1) you can use the database power to do such calculation hence the performance tend to be better 2) once you have implemented the logic in the Data Template then you can reuse with multiple RTF Template files instead of implementing it in each RTF Template files.

Post Migration Tasks – Others

There might be some other items that you want to add to the converted RTF Template file than the ones you find in the log file. For example you might want to add images or do an appropriate number/date formatting. When you find some differences after the validation of the RTF Template file this is the time to fill the gap by implementing such functionality with BI Publisher methodology.

Next Step

After you have completed all the tasks above now you are ready to start registering the converted BI Publisher report template files to Oracle EBS environment to complete the overall conversion process. I will cover how to register the files to EBS tomorrow. So stay tuned!

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!

BI Publisher Reports Conversion

So, we are back after almost 2 months long break! :) We were working on many other BIEE/BI Publisher projects and realized it’s already August! hence haven’t updated this blog for that long…

Anyway, back to the subject, today I’m going to talk about the BI Publisher reports conversion. As most of you have already known, BI Publisher is a Oracle’s strategic Fusion Apps reporting platform. We had many different reporting technology such as Oracle Reports for Oracle E-Business Suite, Crystal Reports for PeopleSoft, Actuate for Siebel, etc, but Oracle had decided to use BI Publisher as a standard reporting platform and have converted each application’s reporting architecture to BI Publisher based architecture.

BI Publisher is tightly integrated in each application and offer UI pages to create, manage, and generate reports sourcing data out from the application transaction system. If you’re interested further here is a link for more information.

Also, Oracle has already converted the pre-seeded reports from the old technology base to new BI Publisher base reports so you can see many of the reports being provided with BI Publisher reports templates (RTF Template) and can generate them with the new reporting architecture.

Now, you can start developing new reports with BI Publisher’s RTF Template and Data Template (Data Template is only available for EBS) upload them to the application. But how about the existing reports that you have already developed with the old reporting platform ? Depends on how long you have your application and your specific situation but most likely there should be numbers of existing reports already and obviously you don’t want to keep two types of reports with two different reporting platforms.

And here comes the BI Publisher reports conversion. I will talk about this topic over the next couple of days to cover the following types.

• Oracle Reports to BI Publisher for E-Business Suite
• Actuate Reports to BI Publisher for Siebel
• Crystal Reports to BI Publisher for PeopleSoft

So stay tuned!

JNDI Data Source for Oracle BI Server

In the previous couple of posts you saw how to create the JNDI data source for a database data source and use it from BI Publisher. But what if you want to connect to Oracle BI Server as the data source? You can create the JDPBC connection pool for the BI Server and register it as a JNDI entry, then use it as JNDI data source in BI Publisher just as same as you do with the database.

Today, I’ll talk about how to create the JNDI data source for Oracle BI Server by using OC4J as J2EE Server as an example.

Oracle BI JDBC Driver Registration

In order to create a JNDI connection for Oracle BI Server you need to register a BI-JDBC library to an OC4J server first. You can do this by manually updating one of the OC4J configuration file called, application.xml. Once you register the additional library file then the rest of the steps are the same as the one for the database.

Register BI-JDBC Library to OC4J Server

1. Identify a location where bijdbc.jar is located under OC4J instance. The jar file comes with the BI Publisher Enterprise Server installation and can be found at, %OC4J_HOME%/j2ee/home/applications/xmlpserver/xmlpserver/WEB-INF/lib/bijdbc14.jar
   (where OC4J_HOME is a home folder (or directory) for the OC4J. e.g. C:\OracleBI\oc4j_bi)
2. Go to %OC4J_HOME%/j2ee/home/config folder (or directory)
3. Open the application.xml file
4. Add the following line under this entry “<web-module id="defaultWebApp" path="../../home/default-web-app" /> “
   
   <library path="C:\BIP_Home\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\bijdbc14.jar"/>

Sample:

<orion-application xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:noNamespaceSchemaLocation=http://xmlns.oracle.com/oracleas/schema/orion-application-10_0.xsd autocreate-tables="true" default-data-source="jdbc/OracleDS" schema-major-version="10" schema-minor-version="0"> <web-module id="defaultWebApp" path="../../home/default-web-app" /> <library path="C:\BIP_Home\oc4j_bi\j2ee\home\applications\xmlpserver\xmlpserver\WEB-INF\lib\bijdbc14.jar"/> <web-module id="jmsrouter_web" path="../../home/applications/jmsrouter.war" /> <ejb-module id="jmsrouter_ejb" path="../../home/applications/jmsrouter-ejb.jar" /> <connectors path="./oc4j-connectors.xml"/>

Once you updated the file then save it and restart the OC4J Server to take the change in effect.

This is the only extra step to create the JNDI connection for Oracle BI Server. I’ll go through the steps to create the JDBC connection pool and JNDI for the BI Server, but it’s going to be pretty much the same as the one you have probably already seen in the previous post…

Create Connection Pool

1. Login to Oracle Enterprise Manager (OC4J Admin Console)

2. Click ‘Administration’ tab

3. Click on the ‘Go To Task’ link of ‘JDBC Resources’

4. Click on the ‘Create’ button under Connection Pool

5. Click ‘Continue’ button (Leave all the fields as default)

6. Enter the following information

Name	BIServerDS
Connection Factory Class	oracle.bi.jdbc.AnaJdbcDriver
JDBC URL	jdbc:oraclebi://localhost:9703/
Username	Administrator
Use Cleartext Password	Check
Password	<Password>

7. Click on the 'Test Connection' button, which is located under URL section

8. Type the sample query and Click ‘Test’ button (OBI doesn’t support ‘dual’ table concept so you need to use an actual query here. You can get the query from Answers request under its ‘Advance’ tab.)

9. Click on the ‘Test’ button.

If you get an error message make sure all the values entered at the above steps are correct. If you get a success message then click ‘Finish’ button to save.

Create Data Source

1. Click on the ‘Create’ button under Data Sources

2. Click on the ‘Continue’ button (Leave all the fields as default)

3. Enter the following information

Name	BIServer
Connection Factory Class	jdbc/APEXDS
Transaction Level	Leave as default
Connection Pool	BIServerDS

4. Click on the ‘Finish’ button

Now you have created a JDBC connection pool for the BI Server and register it with JNDI. That means, you can use that as the JNDI data source in BI Publisher Enterprise Server. As you might have remember, in this way you can take advantage of the connection pool. Especially when you have many queries against BI Server for one single report, for example you have many LOVs that goes against BI Server, this option will make a significant impact.

JNDI Connection for WebLogic Server

I have already talked about how to create the JNDI data source for OC4J and how to use it in BI Publisher. The same configuration for the OC4J can be also applied to Oracle Application Server. But how about Oracle WebLogic Server?

Today I’ll talk about how to create a database connection pool and register with JNDI on WebLogic Server.

JDBC Data Source with JNDI Creation on WebLogic Server

Here is a list of steps to create the JDBC/JNDI data source.

1. Login to WebLogic Server Administrative Console

2. Click ‘JDBC’ link within ‘Domain Configuration’ section

3. Click ‘New’ button

4. Type the data source name

5. Type JNDI Name

6. Select Database type

7. Select Database Driver

8. Click ‘Next’ button

9. Leave all the parameters as they are and click ‘Next’ button

10. Type Database Name (SID)

11. Type Database Hostname

12. Type Database Port

13. Type Database Username/Password

14. Click ‘Next’ button

15. Click ‘Test Configuration’ button to test the database connection

16. Note: Make sure you get ‘Connection test succeeded’ message.

17. Click ‘Next’ button

18. Select ‘AdminServer’ checkbox and click ‘Finish’ button

Once you get a message below your JNDI data source is now created at the WebLogic server.

At this point you should have a working JNDI data source on the WebLogic server so you can register it in BI Publisher as shown in the yesterday’s post, Register JNDI Connection in BI Publisher, and start using it!

Register JNDI Connection in BI Publisher

So yesterday you created a JDBC connection pool and register it with JNDI at the OC4J server. Today I’ll go through how to register the JNDI data source so that you can use that as a data source for your reports.

Once you register the JNDI data source it’s exactly the same in terms of how to use it when you develop your reports. In fact, the reports developer wouldn’t even recognize the difference when they choose the data source from the data source list drop down list.

Register JNDI Connection from Admin Window

1. Login to BI Publisher Enterprise Server and go to Admin page
   
2. Click on the ‘JNDI Connection’ link
3. Click on the ‘Add Data Source’
   
4. Type Data Source Name (e.g. Financial)
5. Type JNDI Name. This must be the same name as the JNDI name you created at your J2EE Server such as OC4J, WebLogic server, etc. 
   
6. Click on the ‘Test Connection’ button to test if the JNDI connection works.
   
   Once you get the success message then click on the ‘Apply’ button to save.
   

Now you can select the JNDI data source for your reports!

Tomorrow, I’ll talk about how to create the JDBC connection pool and register as JNDI for Oracle WebLogic Server, so stay tuned!

JNDI Connection for OC4J

Today I’m going to go through on how to create the Database Connection Pool and JNDI on OC4J. If you’re not sure about the Database Connection Pool and JNDI please check the previous post.

I’ll use Oracle database as a data source and Oracle Enterprise Manager (EM) that comes with OC4J as a tool to create the Database Connection Pool and JNDI.

Access to Enterprise Manager (EM)

First you need to create a Connection Pool and then you can create a JNDI Data Source mapping to the Connection Pool. You can do such by using the EM so first let’s login to the EM. Here is the URL for the EM for the OC4J.

http://hostname:port/em

If you installed OC4J as part of your BI EE or BI Publisher installation then the port number is the same for your BI Publisher application, such as ‘9704’.

e.g. http://knishida-pc:9704/em

Once you have logged in to your EM you should see the window like the below.

Login to Oracle Enterprise Manager (OC4J Admin Console)

Create Database Connection Pool

Now let’s create a Database Connection Pool. You need a JDBC connection information for your database for this task.

1. Click on the ‘Administration’ tab

2. Click ‘Go To Task’ link of ‘JDBC Resources’

3. Click ‘Create’ under Connection Pool

4. Click on the ‘Continue’ button (Leave all the fields as default)

5. Enter the following information

Name	Financial
Connection Factory Class	oracle.jdbc.pool.OracleDataSource
JDBC URL	jdbc:oracle:thin:@<hostname>:<port_number>:<sid>
Username	<database_schema_name>
Use Cleartext Password	Check
Password	<Password for the database_schema)

6. Click 'Test Connection' button, which is located under URL section

7. Click ‘Test’ button

If you get an error message make sure all the values entered at the above steps are correct.

8. Click on the ‘Finish’ button

Now you have created one Database Connection Pool at your OC4J server. Now you need to register it with JNDI.

Create Data Source (Register with JNDI)

Now you are going to create a ‘Data Source’, which is basically that you’re registering the previously created Database Connection Pool with JNDI so that you can access to the connection pool through the JNDI. This example will create a Financial JNDI data source.

1. Click on the ‘Create’ button under Data Sources

2. Click on the ‘Continue’ button (Leave all the fields as default)

3. Enter the following information

Name	Financial
Connection Factory Class	jdbc/FIN
Transaction Level	Leave as default
Connection Pool	Financial

You can enter any name for the JNDI location, but this will later be referenced as a JNDI data source so you want to a standard naming rule such as jdbc/<database_name> or something like that.

4. Click ‘Finish’ button

You can click the ‘Test Connection’ icon for the Data Source you have just created to make sure that it works. If it works fine that means now you have created one JNDI connection for your database!

I’ll talk about how to register the JNDI connection in BI Publisher tomorrow, so stay tuned!