Scalable data extraction strategy: OIC for Oracle Fusion
Organizations often need to synchronize large volumes of transactional or master data from Oracle Fusion Cloud Applications to downstream systems for reporting, analytics, or operational processing. A common approach is to use Oracle Integration Cloud (OIC) to orchestrate these outbound data flows. However, performance and reliability become major challenges when dealing with thousands of records, especially if the integration relies on Oracle BI Publisher (BIP) reports.
In this post, we will discuss how to optimize the outbound data synchronization process from Oracle Fusion using the Schedule Report SOAP API in OIC, ensuring scalability, reliability, and performance even with high-volume datasets.
Key Challenges Addressed:
- Timeouts and performance bottlenecks when invoking large BIP reports.
- Memory consumption and payload handling in OIC for high-volume reports.
- Lack of real-time integration capability due to report generation delays.
- Downstream system failures due to unoptimized data structures or formats.
Proposed Solution:
- Leverage the Schedule Report SOAP Service to retrieve data from BIP reports asynchronously.
- The Schedule Report SOAP Service is a web service typically provided by Oracle BI Publisher (or similar reporting tools) that allows external systems to programmatically schedule, retrieve reports, check their status, and download results in chunks.
- Implement polling mechanisms in OIC to check for report readiness before download.
- Utilize chunking techniques to break down large reports into manageable parts.
- Design robust error-handling and retry logic in OIC to ensure reliability.
- Optimize mapping and transformation logic to minimize processing time.
Here is the workflow broken down to Invoke Schedule Report Webservice
Detailed Description of the above Steps as below.
- Connect to the Schedule service
Set up a connection to the Schedule service using this url: https://<server>/xmlpserver/services/v2/ScheduleService?wsdl.
- Schedule the report
Trigger the report service and retrieve the job status
- Check the status
Monitor the report status and obtain the output ID once the report is generated
- Download the report in chunks
Download the report data in smaller data sets or chunks
- Decode the Report Data
Map the output to the opaque element to decode the report bytes
Benefits of Mastering the Schedule Report Webservice
In Oracle, both “External Report Services” and “Scheduled Services” are related to automating and managing reports and processes, but they serve different purposes. Here’s a breakdown of a few differences:
External Report Service |
Schedule Service |
|
|---|---|---|
| How it works | The report runs immediately when the user invokes the service. The query executes in real time, fetching fresh data. | The report is generated asynchronously in the background. |
| Execution time/Performance | Longer execution time for large reports due to data fetching. | Execution time varies by task complexity but can be optimized through scheduling. |
| Preference | Best for report data under 50MB. | Handles large data volumes efficiently. |
| Time out | Data Model SQL query timeout is under 500 seconds. | Scheduler process uses dedicated JVM threads. |
| Data storage | Uses in-memory storage, which may cause OutOfMemory exceptions for large data. | Processes data using a temporary file system. |
Business Benefits:
- Improved System Performance: Reduced timeouts and memory issues enhance integration stability.
- Higher Scalability: Able to handle thousands of records without impacting runtime performance.
- Reliable Data Delivery: Ensures consistent and accurate updates to downstream systems.
- Operational Efficiency: Minimizes manual intervention and reduces the need for monitoring or reprocessing failures
CONCLUSION
In summary, optimized handling of BI Publisher reports and integrations can be designed to effectively update downstream systems with high data fidelity and minimal operational overhead. Ultimately, this approach enhances overall integration performance, supports business agility, and lays a foundation for robust enterprise data synchronization.
