Oracle ERP Cloud Data Extracts With BI Publisher and OTBI

Getting Data Out of ERP Cloud

Unlike EBS where you can query tables directly, ERP Cloud data access is through published interfaces. The two primary reporting and extraction tools are BI Publisher for scheduled, formatted reports and OTBI (Oracle Transactional Business Intelligence) for real-time ad-hoc analysis. Understanding both is essential for building dashboards and data feeds.

BI Publisher Reports

BI Publisher creates formatted reports (PDF, Excel, CSV) from data models that query ERP Cloud’s data. For data extraction, use a BI Publisher report with CSV output scheduled to run and deliver the file to an SFTP server or Object Storage:

Create a Data Model in BI Publisher using a SQL query against the ERP Cloud’s reporting tables. Design a simple layout template (or use a CSV template for raw data). Schedule the report with the desired parameters and delivery channel. Consume the output file from your integration process.

OTBI for Real-Time Analysis

OTBI provides a semantic layer over ERP Cloud’s transaction tables with pre-built subject areas for each module. Users can build analyses using a drag-and-drop interface without knowing the underlying table structure. For developers, OTBI analyses can be embedded in custom pages or consumed through REST APIs:

-- Fetch OTBI analysis results via REST
GET /xmlpserver/services/rest/v1/reports/{reportPath}/data
    ?format=json
    ¶meters=p_org_id:204,p_date_from:2025-01-01

REST API Extracts

For programmatic data extraction, use the ERP Cloud REST APIs directly. The financials, procurement, and HCM modules all expose list endpoints that support filtering, pagination, and field selection. For large extracts, use the “expand” parameter to include child records in a single request rather than making separate calls for each parent record.

Choosing the Right Approach

Use BI Publisher for large, scheduled extracts where you need formatted output or where the data requires complex transformations that are easier in a SQL query. Use OTBI for self-service analysis and embedding in dashboards. Use REST APIs for real-time, event-driven integrations and when you need to write data back to ERP Cloud. For initial data migration and large bulk extracts, use the FBDI extract templates which are optimized for high-volume data movement.