Skip to main content


Using Custom SQL (BIP) to Extract ERP Cloud Data from OTBI

Custom SQLs work differently than logical and physical SQLs.  Instead of going through the OBTI subject areas and the Fusion PVOs in the RPD physical layer, custom SQLs are used to create custom data models in BI Publisher to extract ERP Cloud data directly from the Fusion database tables.  The built-in Data Sync Oracle BI Connector does not support direct data extraction from BI Publisher reports, but the good news is that BIP reports are available as Web Services.  With little Java coding effort, a specific Web Service client can be created to run and download BI Publisher reports as csv data files for Data Sync mappings to consume.

Use these five steps to extract ERP Cloud data from OTBI through custom SQLs and BI Publisher reports.

1. Create custom data models

Log in to OTBI Analytics, use the dropdown menu New->Data Model to create custom data models.  The Fusion databases are prefixed with ApplicationDB_*.

For example, the following custom SQL will create a custom data model for Suppliers.  Add the parameter LAST_RUN_DATE when is prompted and set the Date Format String to MM-dd-yyyy HH:mm:ss.

    Supplier.VENDOR_ID AS Supplier_Id,
    Party.PARTY_NAME AS Supplier_Name,
    Party.PARTY_NUMBER AS Supplier_Number,
    Party.DUNS_NUMBER_C AS Duns_Number,
    Supplier.VENDOR_TYPE_LOOKUP_CODE AS Supplier_Vendor_Type,
    Party.PARTY_TYPE AS Supplier_Type,
    Supplier.LAST_UPDATE_DATE AS Last_Update_Date
    HZ_PARTIES Party ON Supplier.PARTY_ID = Party.PARTY_ID

2. Create BI Publisher reports

Continue with the step 1 above to create a BI Publisher report using the new data model.  Choose the table layout and include all data columns for the report.

Edit the report to set the report output format to use Data (CSV) only.  Click View a list to make the changes.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

3. Create a simple Java WebService client

In order for Data Sync to be able to load the data from BI Publisher reports, a WebService call is needed to run and extract the report data to a flat file.  One of the many options is to use the wsdl2java utility in Apache Axis2 to generate the WebService stubs Java code, then create a very simple Java WebService client on top of the stubs to perform three tasks: (1) send a SOAP request to OTBI to run the BI Publisher report; (2) download the report output data file; (3) create a dummy signal file used by Data Sync for load automation.

The sample code illustrates a Java client using XMLBeans generated by wsdl2java utility.

public class BipWSClient {

    public static void main(java.lang.String args[]){

            // 1. create the SOAP request

            String reportAbsolutePath = "/Custom/X_BIP_DATA_TEST/X_BIP_SUPPLIERS_DATA.xdo";
            String userID = "OTBI.USER";
            String password = "password";
            String attributeFormat = "csv";
            boolean byPassCache = true;

            String reportDataFile = "./X_BIP_SUPPLIERS_DATA.csv";
            String signalFile = "./X_BIP_SUPPLIERS_DATA_CompleteSignal.txt";

            // use the stubs & xmlbeans generated by wsdl2java

            ReportServiceStub stub = new ReportServiceStub("");

            RunReportDocument doc = RunReportDocument.Factory.newInstance();
            RunReportDocument.RunReport req = doc.addNewRunReport();

            ReportRequest reportRequest = req.addNewReportRequest();

            // handle the LAST_RUN_DATE report parameter

            File lastReportData = new File(reportDataFile);
            if (lastReportData.exists()) {
                Date lastRunDate = new Date(lastReportData.lastModified());
                SimpleDateFormat dateFormat = new SimpleDateFormat("MM-dd-yyyy HH:mm:ss");
                String lastRunDateString = dateFormat.format(lastRunDate);

                ParamNameValues paramNameValues = reportRequest.addNewParameterNameValues();
                ParamNameValue paramNameValue = paramNameValues.addNewListOfParamNameValues().addNewItem();
                paramNameValue.addNewValues().setItemArray(new String[]{lastRunDateString});


            // 2. run and download the output data file

            RunReportResponseDocument res = stub.runReport(doc);
            ReportResponse reportResponse = res.getRunReportResponse().getRunReportReturn();

            byte[] bytes = reportResponse.getReportBytes();
            FileOutputStream fos = new FileOutputStream(reportDataFile);

            // 3. create the dummy signal file

            fos = new FileOutputStream(signalFile);
            fos.write((signalFile+" is extracted.").getBytes());

        } catch(Exception e){

Run the BipWSClient.  If all parameters are set correctly, it will output two files

    -   X_BIP_SUPPLIERS_DATA_CompleteSignal.txt

4. Create Data Sync mappings

Open Data Sync UI, create a new separate Data Sync project, let’s call it X_BIP_OTBI (a default Job X_BIP_OTBI-Job1 will be created as well when creating a new project); then create a new file data mapping that will load the X_BIP_SUPPLIERS_DATA.csv flat file.

Creating the data mapping in its own separate Data Sync project is necessary for the Data Sync file trigger to work on the given job without impacting any existing Data Sync projects, jobs and data mappings.

5. Create Data Sync file trigger for load automation

Go to <Data-Sync-Install-Home>/config-shared folder, modify the on_demand_job.xml file to set up a file trigger on the signal file X_BIP_SUPPLIERS_DATA_CompleteSignal.txt to automatically trigger the data mapping load.

<OnDemandMonitors pollingIntervalInMinutes="5">
    <TriggerFile job="X_BIP_OTBI-Job1" file="X_BIP_SUPPLIERS_DATA_CompleteSignal.txt">
        <TimeWindow startTime="23:00" endTime="02:30"/>

Assuming that there is a daily scheduled job to run BipWSClient at 1 am, the TimeWindow setting will enable the signal file check every five minutes between 11 pm and 2:30 am every night.

After restarting the Data Sync server, the file trigger will start listening for the signal file.  As soon as the signal file comes to existence, the configured will automatically start to run the configured data mapping job, and then delete the signal file.

For Incremental

Parameters can be used in the custom SQL to implement incremental data extraction through BI Publisher report, e.g. LAST_RUN_DATE is used in the example custom SQL for Suppliers data model.

The last modified date of the last csv data file is used for the LAST_RUN_DATE parameter.  If no csv file is found, it will not pass in the parameter, and the custom SQL will treat it as a Full load and select all data.

When using csv output file format, parameters will be included in the csv data file as extra columns.  Therefore these parameter columns, e.g. LAST_RUN_DATE, need to be unchecked for Load when creating such file data mappings.

Previous << Using Physical SQL …  |  Next >> Best Practices in Reality …

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Xiao

More from this Author

Follow Us