To effectively extract data from Oracle Fusion Cloud ERP, it’s crucial to align your extraction method with the specific use case and destination requirements. This article offers insights into various scenarios and provides guidance on selecting the most suitable data extraction tools for Oracle Cloud ERP. Your organization might possess a diverse range of systems, including Oracle Cloud ERP, alongside third-party applications and data warehouses. There might be a need to amalgamate transactional data from third-party systems with Oracle Cloud ERP data for comprehensive analysis within a data warehouse environment.
In instances where there’s a change in status for a particular transaction, it becomes imperative to synchronize the status values (e.g., egress/ingress) between Oracle Cloud ERP and third-party systems, such as master data management or billing systems.
Key extraction strategies include
- Extracting data from Oracle Cloud ERP and channeling it into the Oracle Fusion Data Intelligence Platform on a daily basis.
- Extracting data from Oracle Cloud ERP to serve as a foundation for integration with third-party data warehouse systems, also performed on a daily basis.
By leveraging appropriate data extraction methodologies, organizations can streamline their data integration processes and derive actionable insights from their Oracle Fusion Cloud ERP data.
For extracting medium to high volumes of data to a data warehouse, Business Intelligence Cloud Connect (BICC) is the recommended tool, which comes as part of the Oracle Applications Cloud subscription. The process involves executing an initial data extract, followed by incremental data extracts based on predefined BICC Data Stores for Oracle Cloud ERP. The BICC Console allows scheduling of both one-time and recurring data extracts.
For example, organizations may extract substantial amounts of data from Oracle Cloud ERP to an external data warehouse, leveraging BICC Data Stores tailored for Financials, Procurement, and Supply Chain. These Data Stores provide numerous view objects (VOs) and their attributes for data extraction. It’s essential to note that BICC supports extraction solely from Fusion-defined BI Business objects (BI ADF VOs) within the BICC Data Stores. The performance of the data extraction process depends on the chosen business objects and attributes.
To enhance performance, it’s advisable to conduct an initial data extract followed by incremental extracts. Daily schedules for BICC data extracts are supported, with ad-hoc extracts recommended to be limited to four per day. However, the frequency of data extraction should align with specific business requirements. For instance, for fixed assets, where depreciation is periodic, incremental data extraction may only be necessary once a month, typically at period end. Moreover, Oracle Fusion Data Intelligence Platform utilizes BICC for extracting data to power Fusion ERP Analytics dashboards.
Utilize REST APIs for Integrating Low Volume Real-Time Transaction or Master Data Values with External Systems
When it comes to integrating low volume real-time transaction data or master data values between Oracle Cloud ERP and external systems, REST APIs are recommended. These APIs offer efficient web services for retrieving (getting) real-time transaction or master data values. In cases where certain REST APIs are unavailable for specific master or transaction data, you can consider exploring SOAP web services as an alternative. SOAP web services can be used to integrate real-time transaction data between Oracle Cloud ERP and external systems.
The table provided below offers a summary of Oracle Cloud ERP data extraction tools along with their key capabilities:
|
Data Extract Tool / Key Capabilities
|
Business Intelligence Cloud Connector (BICC)
|
REST API
|
Simple Object Access Protocol (SOAP) Web Services
|
|---|---|---|---|
|
Recommended Use Case Examples
|
Extract transaction data for the day (e.g., incrementally from Oracle Cloud ERP to an external system)
|
Extract release status from Oracle Cloud ERP for integration with an external system when an Invoice Hold is released for a supplier invoice
|
Extract the identifier of a valid code combination from Oracle Cloud ERP for integration with an external system
|
|
Not Recommended Use Case Examples
|
Avoid cumulative data extraction every time; opt for an initial extract followed by incremental extracts
|
Avoid extracting high volumes of data, such as all supplier invoices, from Oracle Cloud ERP to an external system via REST API
|
Avoid extracting high volumes of data to integrate with an external system via SOAP Web Services, similar to REST API
|
|
Volumes Optimized For
|
Medium to High
|
Low
|
Low
|
|
Web Service Invoked
|
Yes
|
Yes
|
Yes
|
|
To External Repository
|
Yes (including Oracle Fusion Analytics Warehouse)
|
Yes
|
Yes
|
|
Support Incremental Extract
|
Yes
|
N/A
|
N/A
|
|
Scheduling Options
|
Yes
|
No
|
No
|
|
Secured
|
Yes
|
Yes
|
Yes
|
Utilize BI Publisher (BIP) for Reporting Purposes Only
BI Publisher (BIP) offers pre-built operational reports and allows users to create custom reports as needed. It’s essential to optimize and fine-tune SQL queries when developing custom SQL-based BIP reports. BIP supports various output formats, including CSV, enabling business analysts to conveniently review reports in MS Excel.
However, it’s not recommended to use BIP reports for general data extraction purposes. For extracting medium to high volumes of data, Business Intelligence Cloud Connector (BICC) is the preferred tool. BIP is primarily designed for reporting rather than data extraction, as elaborated in this blog post: Using Synchronous BIP for Extracting Data – Don’t.
Limit Oracle Transactional Business Intelligence (OTBI) Usage to Online Inquiry and Reporting Only
Oracle Transactional Business Intelligence (OTBI) is best utilized for online inquiry and reporting purposes exclusively. It’s not recommended for data extraction tasks as it is specifically designed and intended for real-time online reporting functionality.
Conclusion
Extracting data from Oracle Fusion Cloud ERP requires tailored approaches depending on volume, frequency, and integration needs. For medium to high volumes, Business Intelligence Cloud Connect (BICC) is recommended, offering scheduled extractions. REST APIs suit low volume real-time data needs, while SOAP web services can be an alternative. BI Publisher (BIP) is best for reporting, while Oracle Transactional Business Intelligence (OTBI) is for online inquiry. Aligning tools with specific requirements ensures efficient data extraction and integration, empowering organizations to derive actionable insights from their ERP data.
VE3 can play a crucial role in optimizing data extraction and integration processes for Oracle Fusion Cloud ERP users. With its expertise in cloud technologies and data management, we offer customized solutions tailored to varying volume, frequency, and integration requirements. By leveraging our oracle solutions, organizations can enhance the efficiency and accuracy of their data extraction workflows, ensuring seamless integration and enabling them to derive actionable insights from their ERP data more effectively. To know more, explore our innovative digital solutions or contact us directly.
