As organizations are moving faster to reduce their inhouse infrastructure, database and application management overhead, many are migrating their legacy on-premises ERP, SCM, HCM, and CX applications to Oracle Fusion Cloud Applications. And it is very common to see such organizations already invested in a consolidated data warehouse. A data warehouse is typically a common target of all data sets from various applications. Therefore, migrating applications to Oracle SaaS requires a good understanding of the future state of the data warehouse and reporting, as these co-exist with the overall systems architecture. Some organizations have evaluated keeping their on-premises data warehouse as-is, and therefore feeding it data from Fusion apps. While this may sound like an easier to do approach, in comparison to building a data warehouse from the ground up, it does have challenges. As a result, many lean more towards investing in a more futuristic approach to analytics altogether, an approach that is compatible with the modernity of the Fusion apps and provides the flexibility, governance and advanced functionality expected by the business community.
Whether the decision is to keep your current data warehouse and feed it Fusion Cloud apps data, or build a new one altogether, in this blog I will present different approaches to extracting data from Fusion Cloud apps. Each of the approaches has certain advantages over the rest, depending on your future state architecture and the source applications involved in data consolidation for reporting. My aim here is to present these differentiating characteristics of each approach and therefore help in finding the most suitable platform to feed Fusion data into a data warehouse.
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.
The table below compares six different platforms to accomplish this type of integration. A few remarks on this analysis:
- Data Sync is listed below as one of the options. While Oracle Data Sync 2.6.1 is still available from Oracle for download and is currently supported, it is considered an outdated tool. For your roadmap, you are highly encouraged not to rely on Data Sync for your future state analytics solution.
- Fusion Analytics Warehouse (FAW) is not listed below as one of the options. However, FAW should be a very viable option for you to implement if you are on Fusion Cloud Apps. It offers pre-built ETL from Fusion Apps to an Autonomous Data Warehouse. In addition, its extensibility framework allows for adding in customization at the data warehouse, semantic model and dashboard levels. It is excluded from this analysis since it offers an end-to-end solution and is fully managed by Oracle.
- BI Publisher: BI Publisher allows exporting data from Fusion apps, however, its good at handling limited use cases. It is not recommended as a general approach to integrating Fusion apps data into a data warehouse, due to the complexity of managing and maintaining a large number of such data exports.
| OAC Direct Query | OAC BICC Replication | Oracle Data Sync | OCI Data Integration | ODI Web (Data Transforms) | Incorta Oracle Cloud App Connector | |
| Requires Additional Software Install & Maintenance | No | No | Yes | No | Yes | No |
| Cloud Infrastructure | Yes | Yes | Yes (IaaS is optional) | Yes | Yes | Yes (IaaS is optional) |
| Cloud Platform | Yes – Oracle Managed | Yes – Oracle Managed | No – Platform is Customer Managed | Yes – Oracle Managed | No – Platform is Customer Managed | Yes (PaaS is optional) |
| Leverages Fusion OTBI | Yes: Real-time | No | Yes | No | No | No |
| Leverages Fusion BICC | No | Yes | Not natively, can read from OCI Object Storage if BICC is configured outside of Data Sync | Yes | Yes | Yes |
| Supports Extracts from Custom Fusion PVOs | No | Yes | Requires manual config through BICC | With Parameters | Yes | Yes |
| Supports Real-time Reporting on Fusion Data | Yes | No | No | No | No | No |
| Supports Incremental Loads | Not applicable | Yes | Yes | Yes | Yes | Yes |
| Supports Handling of Deletes | Not applicable | Yes – native automatic handling | No | Yes – Requires custom handling of deletes | Yes – Requires custom handling of deletes | Yes – Requires custom handling of deletes |
| Supports Historical Changes Tracked in Fusion | Not applicable | Yes | No | Yes, if source PVO includes historical updates | Yes, if source PVO includes historical updates | Yes, if source PVO includes historical updates |
| Automatic Management of BICC Runs and exported CSV Files | Not applicable | Yes | No | Yes | Yes | BICC runs are managed directly in BICC, CSV Files are managed by Incorta Connector |
| Supports Filtering Extracts | Yes | Yes | Yes | Yes | Yes | Yes |
| Supports Scheduling of Data Extracts | Not applicable | Yes | Yes | Yes | Yes | Yes |
| Allows for Data Transformation (such as joining Fusion to non-Fusion data while loading) | Not applicable | No | Yes (also supports sourcing from Amazon, Redshift, Apache Hive, Azure SQL DB, SQL Server, MySQL, Amazon S3, OCI Object Storage, PostgreSQL) | Yes (also supports sourcing from Parquet, Azure, SQL Server, PostgreSQL, Apache Hive and Amazon) | Yes (also supports sourcing from Oracle DB, MS SQL Server, MySQL, Salesforce, NetSuite, Cassandra, Hypersonic SQL, IBM DB2 UDB, Informix) | No (transformation is possible post data ingestion) |
| Supported Data Load Targets | Not applicable | Oracle DBs (including Autonomous) | Oracle DBs (including Autonomous), MySQL, OCI Object Storage (CSV, JSON, Parquet, Avro) | Oracle DBs (including Autonomous), OCI Object Storage (CSV, JSON, Parquet, Avro) | Oracle DB, MS SQL Server, MySQL | Incorta Shared Storage (Parquet Files) |
