Automate Oracle Cloud BI Publisher Report Execution and Export

| 5 minutes of reading | Getting Started Guides, Oracle Cloud API Integration, Oracle Cloud ERP Automation, Technical Tutorials

BI Publisher Automation, BI Publisher Best Practices, CLI Automation, Data Extraction, Data Warehouse Integration, ERP Data Export, Fusion Toolkit CLI, Java ERP Toolkit, Oracle API Automation, Oracle BI Publisher, Oracle Cloud ERP Integration, Oracle ERP Automation, Oracle ERP CSV Export, Oracle ERP Data Export, Scheduled Reporting

When working with Oracle Cloud ERP, data extraction is critical for various business operations. From financial reporting to integrations with third-party tools and data warehouses. Oracle Cloud ERP provides several ways to extract data:

  • REST APIs
  • Default export objects in Oracle Cloud
  • BI Publisher

Among these options, BI Publisher stands out due to its flexibility and precision. It allows users to execute custom SQL queries directly against the Oracle database. However, automating these data exports efficiently often requires additional tooling. Enter Fusion Toolkit CLI.

However, while BI Publisher excels in flexibility, it still poses significant automation and integration challenges. Especially when you must repeatedly extract data at scheduled intervals, such as nightly batch updates or hourly integrations into a data warehouse.

In this article, we’ll explore how to efficiently automate data extraction from Oracle BI Publisher reports using the Fusion Toolkit CLI.

Read the prerequisite for using fusion toolkit and more in the documentation.

Fusion Toolkit CLI: Streamlined BI Publisher Automation

Fusion Toolkit CLI simplifies Oracle BI Publisher automation by allowing ERP consultants and developers to manage report execution entirely via command-line interfaces (CLI). It provides seamless automation and exporting functionalities directly on your infrastructure.

Key Benefits of Fusion Toolkit CLI for BI Publisher:

  • Automated Scheduling: Use Linux cron or Kubernetes CronJobs or your ETL for predictable report execution.
  • Parameter Passing: Easily pass parameters like date ranges, account IDs, or any SQL-bound parameters to BI Publisher reports.
  • Seamless Integration: Export CSV file directly to your local file system or middleware server for simple consumption by other systems.
  • Fully Secure: No external data transfers or cloud storage; all data remains securely within your infrastructure.

Example: Automating an AP Invoice Lines Extraction

Let’s illustrate how you can easily automate an Oracle BI Publisher report using Fusion Toolkit CLI. We’ll use a straightforward SQL query with a parameter for the last update date.

Step 1: Create the Data Model and Report in BI Publisher

Screenshot Fusion Toolkit AP Invoice lines BIP data model

In BI Publisher, first create a data model with your custom SQL query. For instance, if you want to extract invoice lines updated since a specific date, your query might look like:

SELECT 
    inv.*
FROM AP_INVOICE_LINES_ALL inv
WHERE inv.LAST_UPDATE_DATE >= :P_LAST_UPDATE_DATE

In this query:

  • :P_LAST_UPDATE_DATE is a bind parameter allowing you to dynamically pass in a date value at runtime.
  • This parameter ensures that only records updated since the last extraction are retrieved, optimizing performance and reducing redundant data processing.

Save and test your data model in BI Publisher, verifying that it correctly returns data when provided with various parameter values.

Step 2: Creating the BI Publisher Report

After creating and testing your data model, create a BI Publisher report called AP_INVOICES_ALL_R linked to your data model AP_INVOICES_ALL.

  • Select the data model you created.
  • Define the report layout to CSV.
  • Save your report with a clear naming convention and place it in a logical folder structure (e.g., /Custom/SQL/AP_INVOICES_ALL_R.xdo).

Your BI Publisher report is now ready for automated execution.

Automating Report Execution Using Fusion Toolkit CLI

Now, let’s automate this extraction using Fusion Toolkit CLI. This automation enables scheduled exports to your filesystem, where further processing can integrate the data into your data warehouse or downstream systems.

On your middleware server, Fusion Toolkit should already be configured with credentials and Oracle Cloud host information securely encrypted.

Use the following command-line example to execute your BI Publisher report and export it directly into a CSV file:

java -jar ./fusion-cli-toolkit.jar bi-publisher-run-report \
     --reportPath="Custom/SQL/AP_INVOICES_ALL_R.xdo" \
     --format="csv" \
     --outputFile="./AP_INVOICES_ALL_R.csv" \
     --parametersXml="<pub:item><pub:name>P_LAST_UPDATE_DATE</pub:name><pub:values><pub:item>2025-01-01-00-00-00</pub:item></pub:values></pub:item>"

Running BI Publisher report: Custom/SQL/AP_INVOICES_ALL_R.xdo
Connection status: 200 | Connection response: OK | Post data size: 1637
Report run successfully. File saved to: ./AP_INVOICES_ALL_R.csv

Explanation of the CLI parameters used:

  • --reportPath: The absolute BI Publisher report path. Start at Custom and not at Shared
  • --format: Desired output format (here, CSV for easy consumption).
  • --outputFile: Path on the filesystem where the resulting CSV file will be stored.
  • --parametersXml: Dynamically passes values to your report parameters; adjust the date/time accordingly during each run to implement incremental data extraction.

This CSV file now contains all invoice lines updated since the specified date. You can now implement automated shell scripts, cron jobs, or Kubernetes CronJobs to schedule and automate regular data exports from BI Publisher.

Integrating Exported Data into Your Systems

With your data now exported into CSV, automated downstream integration becomes straightforward:

  • Use standard middleware scripting (shell scripts, Python scripts) to transform or filter the data as needed.
  • Automate loading of transformed data into your data warehouse, analytics system, or other ERP applications.
  • Schedule regular extraction (e.g., nightly, hourly) to maintain up-to-date synchronization between Oracle ERP and downstream systems.

Example workflow:

  1. Fusion Toolkit CLI generates CSV nightly.
  2. Middleware scripts (shell/Python/Java) read CSV, validate, transform, and enrich data.
  3. Automated upload scripts move cleaned data into data warehouse or cloud storage.
  4. Data warehouse processes ingest CSV data into tables accessible by business intelligence tools and applications.

Benefits of Using Fusion Toolkit CLI for BI Publisher Automation

Automating BI Publisher reports and exports using Fusion Toolkit CLI provides several tangible benefits:

  • Increased Efficiency: Eliminate manual extraction processes, reducing errors and freeing up staff resources.
  • Data Consistency: Parameterized extracts ensure accurate, incremental data integration.
  • Secure Integration: Data remains fully secure and never leaves your infrastructure.
  • Enhanced Reliability: Automation ensures consistent data availability and timely integration.

Oracle BI Publisher provides powerful data extraction and reporting capabilities, but its native automation and integration tools fall short of enterprise requirements. Fusion Toolkit CLI closes this gap by automating the execution of BI Publisher reports, enabling seamless, secure, and scheduled CSV exports directly into your infrastructure.

By following the practical steps in this article, you have everything required to automate your BI Publisher data exports effectively, reliably, and securely—achieving true ERP integration automation that empowers your business processes.

Related Posts

Scroll to Top