DOWNLOAD Oracle BI Publisher

TAGS bipublisher, bi, All

Getting Started with BI Publisher ToolBox

Learn how this free utility can make running BI Publisher reporting components incredibly easy.

Published October 2009

Ever wished that using the Oracle BI Publisher APIs could be easier to use? Ever wanted a single-click template load application for EBS Template Builder? Ever wanted to be able to test your reports with a single click? Look no further! These features and many more are provided in the BI Publisher ToolBox, a free configuration management tool I created for Oracle BI Publisher .

For those of you unfamiliar with it, Oracle BI Publisher is a tool that generates high-fidelity documents and reports. Also, it provides a rich set of APIs that can generate and deliver content to a variety of platforms ubiquitously.

Figure 1 Oracle BI Publisher overview

ToolBox increases the speed of development because a developer can now connect to any environment, any disparate data source, or any ERP system. This functionality has the added benefit of not requiring you to create or key any system config, copy files to servers, or wait for concurrent manager requests (a plus for E-Business Suite developers).

Not only will ToolBox make your life easier, it will also save you valuable development time—a lot of it. To illustrate this point, below is an example of how a typical BI Publisher report is developed and unit tested for Oracle E-Business Suite developers:

1. Copy the Reports6i report to the server or upload a Data Template: 5 seconds
2. Upload the Format Template (assuming you are logged-in already): 5 seconds
3. Copy a concurrent request and resubmit: 10 seconds
4. Wait for the concurrent request to start and finish: 30 seconds
5. Review the log and output file: 10 seconds
6. Repeat

What Is It?

ToolBox is a lightweight Swing app that allows developers to run the BI Publisher APIs locally on their PC with a nice, front-end GUI. (See Figure 2.) There is nothing to deploy and no OAF or Java knowledge is required. As a result, it allows a developer to quickly create and test BI Publisher Data Templates, Format Templates, Burst Control Files, etc.

Figure 2 Sample configuration to run BI Publisher templates

• Format Templates - rtf conversion, applying xsl templates to xml files
• Data Templates - generating xml files (Reports6i replacement)
• ETEXT - Generating EFT and EDI documents
• Bursting - Delivery documents via http, email, ftp, filesystem, etc.
• Uploading Format Templates (E-Business Suite)
• Uploading Data Templates (E-Business Suite)

Installation

This section explains the technical setup to successfully run BI Publisher ToolBox.

1. Install the Java runtime environment (JRE) version 6. The download for the JRE version 6 can be found here.
2. Verify that your PC has 500+ MB of memory. (Note: 1 GB is preferable)
3. Download BIPublisherToolBox.zip
4. Download BIPublisherToolBoxSrc.zip (optional)
5. Extract the BI Publisher ToolBox.

ToolBox can be extracted into any directory. DO NOT move the executable, Batch File, or Shell Script to another directory. You may not copy the executable out of the extracted directory, either. In doing so, the jar files and their paths will not be found for this application. With that in mind, you may move the entire extracted folder (BIPublisherToolBox) to your choice of locations.

Examples

ToolBox comes pre-packaged with basic examples that a developer can run. In order to run the Data Template examples, the scott/tiger schema must be installed and you must have a working data source. It is recommended that the developer load the examples before proceeding to the "Configuration" section.

1. Run the executable - Navigate to the BIPublisherToolBox\bin directory. Double click on the BIPublisherToolBox.exe. The ToolBox screen should come up.
2. Open the BIPublisherToolBox_SampleConfig.xml file - Click the Open Folder button, navigate to the BIPublisherToolBox\corporate_examples directory. Double click on the BIPublisherToolBox_SampleConfig.xml. The ToolBox screen should now be populated with the examples. (See Figure 3.)

   
   Figure 3 Loaded BIPublisherToolBox_SampleConfig.xml file

   Overview of each configuration line:

   Line 1: Bursting with a Data Template generating the xml
   Line 2: RTF template working with data generated from a Data Template
   Line 3: RTF Template working with a previously generated xml file
   Line 4: Data Template (Stand alone) generating xml
   Line 5: ETEXT document with a previously generated xml file
   Line 6: Bursting a document with an ETEXT format. Note that the output format is ignored. It is just there for clarity.

Configuration

Navigate to the BIPublisherToolBox\bin directory. Double click on BIPublisherToolBox.exe. The ToolBox screen should come up.

Before running ToolBox, the first step is to set up the data sources. To configure the data sources, simply key in the required information for the data source and then click the Add button. All the fields are required and there is no validation for the data source setup. If information is entered incorrectly, resolve this by creating a new file or edit the existing xml configuration file. (See Figure 4.)

Figure 4 Configuring and adding a data source

There is an outstanding issue with the drop-down not refreshing after adding a data source. To resolve this issue, close the application and re-open your configuration file. There is no limit to the number of data sources that can be saved or created.

Note: This application has not been tested against non-Oracle databases, so it is unlikely that a SQL Server and DB2 data sources will run without the right jdbc drivers.

There are very few steps needed to run BI Publisher components such as Data Templates, Format Templates, and Bursting. This can be done without creating or updating any new configuration in the e-Business Suite or other relevant systems; the template configuration allows for all variants of BI Publisher technologies. There are hard-coded business rules that enforce the process execution. Below is a listing of the required fields. (Note: There are tool tips for every field. The tool tips display a brief message stating what should be entered into the highlighted field. )

The following fields are required for all configurations:

• Template Type
• Output Format (N/A for bursting)
• Output Directory
• Output File

General rules:

• Never leave blank spaces in the Output directory path to your output files.
• The Output directory field should end with a slash ( \ ).
• Never leave spaces in the Format and Data Templates names.
• The Output directory must contain the Format and Data Templates. There are no multiple directory configurations. Do not append file paths to these files (Data/Format Templates). It will not work.
• Never have file extensions appended on the Output file. The extensions will be ignored or appended with relevant extensions.
• To save you must either click the disk or hit CTRL-S (click out of the field for CTRL-S)

Step 3: Creating/Removing a configuration row

New Button - In order to create a new configuration row, the New button must be clicked. Defaults are initially loaded. Clicking New will always create a new row at the bottom of the table and, in some instances, you may need to scroll down to see it.

Duplicate Button - Clicking the Duplicate button will duplicate the current row selected. You may need to scroll down to see the new row at the bottom of the table.

Delete Button - Clicking the Delete button will remove the existing row. Clicking the Save button will make the row permanent.

Clear Log Button - Clicking the Clear Log button will clear the output console. This does not clear the existing log file and it will be redisplayed. (If you know how to solve this, please email me at the address in my bio. This is a known issue.)

Step 4: Configuring Templates

An assumption is made in the following sections that the developer is trying to figure out what the possible configuration combinations are after selecting the Template Type. The selected Template Type has very specific and applicable business rules for the execution of configuration line. Below is an overview of the Template Type field and configuration row.

Template Types

The second step of setting up your report is selecting the applicable Template Type. There are three options to select: RTF, BURST, or ETEXT. There is a business rule that is common across all Template Types, so whenever a Data Template is available, that will be the source generating the xml output file. Figure 5 shows an overview of what happens with each Template Type.

Figure 5 The template types that can be run are RTF, BURST, ETEXT. There is no type for Data Templates, any can be selected.

RTF Template (Format Template)

If a Data Template is provided, the ToolBox will generate the specified output file. If a Data Template is not provided, it is assumed the output xml file exists. The ToolBox will call the RTFProcessor & FOProcessor API and process the output file. (See Figure 6.)

Figure 6 Highlighted line is a sample configuration of RTF - Format Template

Data Template

A maximum of 20 parameters can be configured for Data Templates. (This is a limitation of ToolBox, not BI Publisher.) If the Format Template field is blank, the xml output file will only be created by calling the DataProcessor API. If a Format Template is provided, ToolBox will generate the specified output file and apply the Format Template using the RTFProcessor & FOProcessor API (see RTF Template). (See Figure 7.)

Figure 7 Highlighted line is a sample configuration of a Data Template. The Template Type has no bearing on the processing because a Format Template or Bursting file is not provided.

Bursting Template (Format Templates)

If a Data Template is provided, ToolBox will generate the specified output file using the Data Template. If a Data Template is not provided, it is assumed that the output xml file exists. ToolBox will call the BI Publisher DocumentProcessor API and process the output file. (See Figure 7.) The Format Template field should be populated with the bursting configuration file. (Note: Do not append an xml file extension to the output file; it will cause the ToolBox bursting engine to error out.)

Figure 8 Highlighted line is a sample configuration of Bursting.

ETEXT Template

If a Data Template is provided, the ToolBox will generate the specified output file using the Data Template. If a Data Template is not provided it's assumed the output xml file exists, ToolBox will call the EFTProcessor API and process the output file. ETEXT must be the selected output format and the file extension will be always be .txt. (See Figure 9.)

Figure 9 Highlighted line is a sample configuration of ETEXT.

select * from xdo_lobs where lob_code like :1