Introduction
Oracle Fusion Applications Suite (Oracle Fusion Cloud Applications) is the most complete cloud applications suite that provides several options for extracting data in different formats for your business requirements.
In continuation to my blogs on implementing multi-pillar Fusion applications and Fusion applications cloud instance strategy and Global Single Instance guidelines, I wanted to share my experience on data extract options from Oracle Fusion Applications Suite.
One of the essential requirements for every application implementation is data extraction. This blog will help you understand available extract options, tools, and guidelines in Oracle Fusion Applications Suite.
Oracle’s Fusion Applications Suite is engineered to work together across business functions such as finance, human resources, supply chain, manufacturing, sales, customer service, and marketing. Oracle Fusion Applications Suite uses a single unified data model across all application modules (ERP, SCM, HCM, CX (Sales, Service)).
Data Extract / Export Use Cases
The most common data extraction use cases for Oracle Fusion Applications Suite are:
- Ability to extract initial/complete data set and incremental data
- Extract data from Oracle Fusion Cloud Applications
- To store in a cloud or on-premises enterprise data warehouse
- To Import to another Oracle application cloud / Database Cloud / on-premises
- To format data and mass update back into Oracle Fusion Cloud Applications
- For 3rd party data verification and enrichment
- Enable business users to extract required data (usually small amount) quickly for analysis
- To determine record identifiers of existing records to associate child records
Data Extract Tools & Options
Fusion Cloud Applications Suite has purpose-built data extract options that give customers flexibility in choosing the right tools for the implementation. The table below provides the extract options available within Fusion Cloud Applications.
Extract Option / Tool | Description | Supported formats |
|---|---|---|
| Fusion Desktop Integration (Refer to user guide documentation) |
Across all applications, many screens will have the export capability for users to export available data through the application. This option is not recommended for data extraction. |
Microsoft Excel, CSV |
| Export Management |
Move / Extract CX data out of Oracle Applications Cloud in the form of CSV files. |
CSV |
| HCM Extracts |
Extract and export HCM data elements. Using the built-in integration with Oracle BI Publisher, you can generate extracts in various formats. |
CSV, XML, Microsoft Excel, HTML, RTF, and PDF. |
| Financial Reporting Center Note: OTBI is a reporting tool and not recommended for data extraction |
The primary function for ERP Cloud enables end-users to access pre-built reports and export if available |
Microsoft Excel |
| Item / Product Data Publication |
Item Publication enables you to schedule and publishes item objects automatically. |
XML |
| OTBI (Online Transactional BI) Note: OTBI is a reporting tool and not recommended for data extraction. |
Built-in analytics & reports within every pillar for application users to gain real-time insight into transactional data, understand data patterns, and be alerted to key events and data anomalies. |
Microsoft Excel, XML |
| BIP (Business Intelligence Publisher) Note: BIP is a reporting tool and not a good practice for data extraction. |
Business Intelligence Publisher is the report generation and delivery engine for Oracle Fusion Applications Suite. |
Word, Excel, PDF, RTF, and HTML |
| BICC (BI Cloud Connector) |
Business Intelligence Cloud Connector (BICC) is used to extract data in bulk from Oracle Fusion SaaS applications and load it into designated external storage areas. |
CSV |
| REST API |
Use REST API’s to extract data |
JSON |
| SOAP Services |
Use Simple Object Access Protocol (SOAP) web services to extract. |
XML |
| Fusion Analytics Warehouse (“FAW”)* | Oracle Fusion Analytics Warehouse provides analytics for Oracle Cloud Applications, powered by Autonomous Data Warehouse and Oracle Analytics. | Pre-built, Cloud Native analytics applications |
* Additional subscription may be required
When you have complex data extraction, transformation, and integration requirements leveraging Oracle Cloud Infrastructure capabilities such as Compute, Data Integration, Functions, Object Storage, Autonomous Database, Analytics, and other capabilities will simplify seamless automation and integration.
The table below provides a high-level view of available data extract capability across all pillars in Fusion Cloud Applications. Note that HCM and CX pillar has purpose-built extract/export utilities best suited to corresponding pillar objects/entities.
| Extract Option / Capability | Typical requirement | Expected Volume | Best suited for | CX | HCM | ERP | SCM |
|---|---|---|---|---|---|---|---|
| Fusion Desktop Integration* |
Users to analyze specific data outside |
Low |
User interaction |
Not recommended for data extract |
|||
| Export Management |
Extract CX objects |
High |
Batch |
✓ |
|||
| HCM Extract |
Extract HCM objects |
High |
Batch |
✓ |
|||
| Financial Reporting Center* |
Financial statements |
Medium |
User interaction |
Not recommended for data extract |
|||
| Item / Product Data Publication |
Item data for bulk or asynchronous real-time integrations |
High |
Batch |
Not applicable |
✓** |
||
| OTBI (Online Transactional BI) * |
Seeded Subject area and analyses |
Low |
User Interaction |
Not recommended for data extract |
|||
| BIP (Business Intelligence Publisher) |
Seeded reports available in pillars ~ |
Medium |
Batch~~ |
✓ |
✓ |
✓ |
✓ |
| BICC (BI Cloud Connector) |
Extract bulk data to on-premises / reporting; incremental extract supported |
High |
Batch |
✓ |
✓*** |
✓ |
✓ |
| REST API / SOAP Services |
Get data mostly for real-time integrations |
Very Low |
real-time |
Intended for real-time integrations only, and not recommended for data extract |
|||
| Fusion Analytics Warehouse |
Extract bulk data from Oracle Cloud applications to FAW |
High |
Batch |
✓ |
✓ |
✓ |
✓ |
* Only for reporting purpose and not recommended for data extraction
** Product Hub only; can also be used for asynchronous real-time integrations
*** Supported only for Fusion Analytics Warehouse / Oracle Analytics Cloud; Refer to the detailed blog on Reporting and Data Extracts in HCM
~ BIP is a reporting tool and not recommended for data extract using custom SQL
~~Avoid using BIP for real-time integrations
The table below provides the consolidated Oracle documentation (21D) links for respective data extract options from every Fusion Cloud Application Suite pillar. As a best practice always use the latest documentation.
| Extract Option | CX | HCM | ERP | SCM |
|---|---|---|---|---|
| Fusion Desktop Integration |
Refer to User guides of corresponding pillar |
|||
| Export Management |
Not applicable |
|||
| HCM Extract |
||||
| Smart View / Web Studio |
Not applicable |
Not applicable |
||
| Item / Product Data Publication |
Not applicable |
|||
| OTBI (Online Transactional BI) |
||||
| BIP (Business Intelligence Publisher) |
||||
| BICC (BI Cloud Connector) |
BICC – Common guide for all pillars |
|||
| REST API |
||||
| SOAP Services |
||||
| Fusion Analytics Warehouse |
FAW – Common guide for all pillars |
|||
Data Extraction Guidelines
- Consider Fusion Analytics Warehouse option that provides native analytics applications with pre-built analytics for all pillars and ready-to-use insights to users for effective decision making. Refer to A-Team blog for additional details.
- Explore available data extract/export options and choose the right one based on your requirements.
- Understand the supported objects and volume/size limitations from pillar (HCM / CX / ERP / SCM) documentation based on your extract option.
- Consider the mode of integration (batch or real-time), type of integration (synchronous or asynchronous), frequency (daily, weekly, monthly, …), data volume (# of records to be retrieved/processed), expected file size, storage/retrieval options, duration, and performance while choosing the extract option.
- Document the data extract requirements and design the solution by considering usage of data, automation, and performance.
- Do not use OTBI for data extract. Note that OTBI is a reporting tool and not recommended for synchronous integrations. OTBI analysis has limitations on 25,000 records for exporting to Excel.
- Avoid developing BIP reports using custom SQLs for data extracts and integration requirements. Consider performance, timeout, file size, and formatting if you are developing custom BIP reports.
- BICC is the recommended option of extracting bulk data for ERP, SCM, CX in batch out of Fusion Cloud Applications for external applications/data warehouse/reporting. BICC supports incremental extract. For HCM BICC is used only with Fusion Analytics Warehouse and HCM analytics. Refer to the BICC Best Practices paper on customer connect.
- Consider Security, compliance, data privacy, encryption requirements while designing the data extraction/replication solution.
- Leverage Oracle Cloud Infrastructure Cloud (OCI) and Oracle Integration Cloud (OIC) capabilities for orchestrating and automating the data extracts and integrations.
- Avoid using REST API / SOAP services for extracting/exporting high-volume data set from Oracle Fusion Cloud Application. REST API / SOAP services are recommended only for real-time integrations.
- Always refer to the latest documentation to get updates on features.
- Research cloud customer connect if you find gaps and create ideas as needed.
- Log a Service Request with Oracle if any of the above extract options don’t meet your needs to get the right guidance.
Conclusion
To summarize the data extract option selection for your Oracle Fusion Application implementation, leverage the purpose-built standard extract/export capabilities (HCM Extracts for HCM, Export Management for CX, Item Publication for Products/Items). BICC is the recommended approach for large volume and incremental extracts for ERP and SCM. Avoid using OTBI, BIP for extracting data to synchronously integrate with other applications as they are built primarily for reporting.
I hope this blog will enable your organization’s data extraction processes efficiently.
References
Note: Always refer to the latest documentation.
- Business Intelligence Cloud Connector (BICC) Data Stores for Financials
- Reporting and Data Extracts for HCM
- Fusion Cloud Applications Data Extraction Approaches
- Best practices from Oracle Support
- Oracle Applications Cloud: Data Export Best Practices (Doc ID 2615185.1)
- Oracle Fusion Transactional Business Intelligence and BI Cloud Connector Performance Recommendations (Doc ID 2679006.1)
- Oracle BI Publisher Best Practices for SaaS Environments (Doc ID 2145444.1)
- BI Reports and Analytics Resource Center (Oracle CX Sales and B2B Service) (Doc ID 1624768.1)
- Using Seeded Data Extract Services for Oracle ERP Financial Cloud (Doc ID 2303683.1)
- Using External Data Integration Services for Oracle ERP Cloud (Doc ID 2102800.1)