OIC: minimize impact for online users by utilization of ESS job for running BIP queries

Business Need

Using SQL queries executed by BI Publisher engine against Fusion SaaS database is a common practice in integration development, however it should be used carefully by taking into consideration its potential impact on online users.

In this article we will see how to utilize the SaaS ESS jobs to run the same queries in a asynchronous mode causing less load on the UI layer serving online users.

Solution Overview

1. Fusion SaaS:
   1. Develop a BIP report model you need to invoke from an OIC integration
   2. Save sample data and generate report by using report editor (and not wizard)
   3. Create an XSL template (can use the generic template below)
   4. Define an ESS job pointing to the BIP report created, add parameters if needed
2. OIC Integration:
   1. Use Fusion Cloud connector to submit the ESS job defined above
   2. Loop until the ESS job is complete by using getEssJobStatus
   3. In case of successful completion – download the ESS output to a staging file
   4. Unzip the ESS job output file, extract the report results
   5. Perform business logic as needed based on data fetched by BIP report

Detailed Example

Fusion Saas:

1. Create a BIP report model
   
2. View Output, Save Sample Data, Generate Report, Use Report Editor and Save
3. Create a new XML template (default layout is needed to simplify ESS job submission and result extract)
   
   Generic XSL Code:
   <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/"><xsl:copy-of select="."/></xsl:template>
</xsl:stylesheet>
4. Define ESS job
   

OIC Integration:

1. Submit ESS Job by using Fusion connector ERP Integration Service
   
   1. Map “/oracle/apps/ess/custom/BIP/” to Job Package Name
   2. Map “MY_USERS_REPORT” to Job Definition Name
   3. Map parameters if needed by duplicating Param List
      
2. Wait until the ESS Job is complete by using the same connector ERP Integration Service and getEssJobStatus method
   
3. Download the output file by using Fusion Connector ERP Integration Service and downloadESSJobExecutionDetails
   
   Make sure you specify only OUT for download option – ensure only output in the resulting ZIP file:
   
4. Use staging file write, unzip and list to get the reference to the output file
   
5. Process the file based on your business logic