One of the easiest and lowest maintenance approaches to feed Fusion Cloud Apps data into a data warehouse, is with Oracle Analytics Cloud (OAC) Fusion Business Intelligence Cloud Connector (BICC) Data Replication. Data Replication for Fusion Apps is a native feature of OAC Enterprise edition. If you’re migrating your on-premises application, such as E-Business Suite, to Oracle SaaS, you probably already realize that the capability to directly connect to the Oracle SaaS transaction database for data extraction, isn’t generally available, except for limited use with BI Publisher type reporting. However, Fusion BICC offers a robust approach to enable data extraction from Fusion Apps. BICC extracts data from Fusion App view objects into files stored on Oracle Cloud (OCI). OAC’s data replication from BICC facilitates the process of configuring, scheduling and monitoring the whole process of data extraction from BICC into Cloud storage and then importing the same data into a data warehouse. While this doesn’t really offer an ETL-like functionality, it does streamline the end-to-end process of extracting data from Fusion apps into relational table structures in an Oracle database. These target relational tables can then either be directly reported on or transformed for more complex analytics.
Here are, in my opinion, the key advantages of using OAC Data Replication from Fusion Apps:
- Oracle Managed: It is a built-in feature of OAC and therefore requires no software install or maintenance. Data Replication jobs are configured, scheduled, and monitored entirely from within an internet browser, within the OAC portal.
- Supports Extracts of Custom Fusion BICC Offerings and Custom PVOs: If your Oracle SaaS implementation team has setup custom data sets in Fusion, and therefore ended up with custom View Objects, these may also be exported with OAC Data Replication.
- Filter the Data Extraction: The configuration screens of OAC Data Replication allow for setting up filters that are enforced while the Fusion data is extracted. These filters may be useful to avoid pulling in older data, or to segregate organization-specific information into dedicated target databases.
- Support for Incremental Loads: The process of setting up an incremental data extract strategy from all the different Fusion data source views is a time-consuming task. However, this is all easy to setup with the configuration screens available in OAC Data Replication.
- Handles Deletes: While BICC natively doesn’t automatically take care of identifying which records in a source view got deleted, it has a mechanism to identify the primary keys of the views in their current state. But these keys will then have to be compared to the keys in the data warehouse target table, in order to identify any deleted records and therefore process the deletes. This whole process is automatically performed by OAC data replication by checking a box on the OAC configuration screen for a view object.
- Track Historical Changes: Some Fusion PVOs keep track of changes as certain attributes are updated over time. OAC Data Replication offers an option to maintain these changes in the data warehouse as well. This option enables linking fact data to dimensions that behave in a similar manner to traditional slowly changing dimensions (SCDs).
- Scheduling of Data Loads: OAC Data Replication allows for scheduling the data extracts and loads from Fusion Apps at various intervals. This may be once a day, but also multiple times throughout the day. In fact, more essential data extracts can be configured to run as frequent as on an hourly basis to offer near real-time reporting, when required on smaller data sets.
While OAC Data Replication for Fusion Apps does offer some great functionality, there are restrictions that may render it unsuitable, depending on how you envision the holistic view of your future state data warehouse. Here are some of the reasons why it may not be adequate:
- Lack of Data Transformation: Like the name infers, OAC Data Replication, solely offers an easy way to replicate Fusion SaaS data into a data warehouse. It doesn’t really allow for data transformation prior to the data load. Think of it this way: the result of the replication is a populated staging area of a data warehouse. If you need to apply transformation, another technique needs to be used after the Fusion data is replicated over. For instance, OAC Data Replication itself won’t be able to merge and transform the Fusion sourced data based on information sourced from outside Fusion. To do this, first replicate the Fusion data and then integrate with the non-Fusion data as a separate downstream process.
- Restriction to Load into Oracle Databases: OAC data replication from Fusion Apps only loads data into an Oracle Database or an Oracle Autonomous Database. So, if for example, you have a need to get Fusion data into Azure or another non-Oracle database, you will have to follow a two-step process to first replicate into an Oracle DB and then into your final destination. As a result, if your main destination is non-Oracle, you may want to consider one of the other approaches to extracting data from Fusion Apps, as described in my other blog post.
