Building BI Reports in Oracle Fusion: Comprehensive Tutorial

In this blog, let’s have a look at how to Generate a BI Report in Oracle Fusion Using Custom SQL (BIP) for Extracting ERP Cloud Data from OTBI.

Custom SQL operations differ from logical and physical SQLs. Instead of traversing OTBI subject areas and the Fusion PVOs in the RPD physical layer, custom SQL is employed to establish custom data models in BI Publisher. This allows direct extraction of ERP Cloud data from the Fusion database tables.

The built-in Data Sync Oracle BI Connector doesn’t offer direct data extraction from BI Publisher reports. The good news, however, is that BIP reports are accessible as Web Services. With a little Java coding effort, a dedicated Web Service client can be crafted to execute and download BI Publisher reports as CSV data files, which can then be consumed by Data Sync mappings.

Follow these steps to extract ERP Cloud data from OTBI through custom SQL and BI Publisher reports:

• Create Custom Data Models
• Log in to Oracle Fusion Application.

When creating a “Data model” and “Reports” for the first time, follow these steps:

Step 1: Navigate to “Reports and Analysis.”

• Navigation: Navigator -> Reports and Analysis.

• Click the Browser Catalog Icon to access the BI Catalog for BI Publisher.

• To create a new BI Publisher Data Model, select “New” from the toolbar and then choose “Data Model.”

• From the new Data Set dropdown list, select “SQL Query.”
• The new Data Set shall be named “Business Unit”. Choose the appropriate data source.
• Build the query based on the subject area tables, not the physical tables.

• Paste the application SQL query to define application names, each mapped to a unique auto-generated ID, for users and roles. Then, click “OK.”

• After selecting “OK,” the columns from the query are displayed.
• Navigate to the Data tab.

• Click “View” to see a sample set of data. You can save this dataset as sample data.

• Save the Data Model in a custom folder to preserve your custom data models and reports during upgrades. Click the “Save” icon.

Step 2: Create BI Publisher Reports

• Now, let’s create the BI Publisher report based on the newly created Data Model.
• The Data Model is already selected. Click “Next.”

• Select the table layout and then include every data column for the report.

• Drag fields from Data Source for creating the table.

• You can customize the report as needed.
• Select “View Report” and click “Finish.”

• After finishing, select the path to save the report.

• After saving, the created report will be displayed, and you can export it to various formats.

By following these steps, you will be able to successfully generate a BI Report in Oracle Fusion Using Custom SQL (BIP) for Extracting ERP Cloud Data from OTBI.