Extracting Custom Data from Oracle Fusion Cloud ERP Using BI Publisher SQL Data Models

When you need to pull custom datasets from Oracle Fusion Cloud ERP that standard reports cannot provide, the most reliable approach is to create a SQL‑based Data Model in Business Intelligence (BI) Publisher. This lets you query the underlying Fusion schemas directly, shape the XML output, and automate delivery without relying on pre‑built reports.

The Mechanism: SQL Data Model and Layout

BI Publisher separates concerns into three parts: the Data Model defines the SQL query that returns raw data; the Layout transforms that XML into a final format such as CSV, JSON, or PDF; and the Bursting option (if used) splits the output for distribution. For extraction, the Data Model is the primary artifact.

Example Configuration: Extracting Order Header and Line Details

Place the following SQL in the SQL Query editor of a new Data Model. It returns order header fields together with line‑level item information for orders created after a given date.

SELECT
    oha.order_number        AS "Order_Number",
    oha.order_date          AS "Order_Date",
    oha.customer_id         AS "Customer_ID",
    gle.line_number         AS "Line_Number",
    item.item_sku           AS "SKU",
    gle.ordered_qty         AS "Quantity",
    gle.unit_price          AS "Price"
FROM
    ra_orders_headers_all   oha
JOIN
    ra_order_lines_all      gle ON oha.header_id = gle.header_id
JOIN
    inv_system_items_bvl    item ON gle.inventory_item_id = item.inventory_item_id
WHERE
    oha.creation_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD')
    AND oha.flow_status = 'ORDERED';

Automating Delivery with Data Bursting

To avoid manual downloads, configure a Bursting Query within the same Data Model. The bursting query must return four columns that BI Publisher interprets as:

• KEY – a value that groups rows (e.g., customer_id or business_unit).
• TEMPLATE – the delivery method ('SFTP' or 'EMAIL').
• DELIVERY – the destination (SFTP path or email address).
• FORMAT – file extension ('csv', 'pdf', etc.).

An example bursting query that sends a CSV file per customer to an SFTP folder:

SELECT
    oha.customer_id               AS "KEY",
    'SFTP'                        AS "TEMPLATE",
    '/outbound/fusion/orders/' || oha.customer_id || '.csv' AS "DELIVERY",
    'csv'                         AS "FORMAT"
FROM
    ra_orders_headers_all oha
WHERE
    oha.creation_date >= TO_DATE('2023-01-01', 'YYYY-MM-DD');

Performance Limits and Common Mistakes

Direct SQL execution is powerful but must respect the BI Server’s resource boundaries.

• JVM Heap Limits – The BI Server runs with a fixed Java heap. If the result set produces an XML document larger than the available memory, you will see an OutOfMemoryError. Mitigate by filtering aggressively in the WHERE clause or by paginating bursts.
• Missing Indexes – Queries that scan large tables without using indexed columns (e.g., creation_date without a function‑based index) can cause long runtimes and high CPU usage. Verify that your WHERE columns are indexed in the Fusion schema.
• Schema Changes – Subject Area‑based views and even base table definitions can shift during quarterly Fusion updates. A custom SQL model that references a column that gets renamed or dropped will fail after an update. Schedule a regression test after each update cycle.
• Timeouts on Large Exports – Even if memory is sufficient, the BI Server may enforce an execution timeout (default often 30 minutes). Very large bursts can exceed this limit. Check the BI Server logs for messages like Query timed out and consider splitting the date range or adding more restrictive keys.

Practical verification steps:

1. In the BI Publisher editor, click View Data on the Data Model to see the raw XML and note the execution time displayed at the bottom.
2. After saving a bursting configuration, run a test with a single recipient (or a test SFTP folder) and confirm the file arrives with the expected name and content.
3. Navigate to the Fusion Console → Monitoring → BI Server Logs and look for the report’s job ID; review any warnings about memory, CPU, or elapsed time.