Oracle Analytics Cloud (OAC) Fusion Business Intelligence Cloud Connector (BICC) Data Replication is one of the simplest and least-maintenance methods for feeding Fusion Cloud Apps data into a data warehouse. OAC Enterprise edition includes Data Replication for Fusion Apps as a standard feature. If you’re converting an on-premises application to Oracle SaaS, such as E-Business Suite, you’re presumably aware that the ability to directly connect to the Oracle SaaS transaction database for data extraction isn’t normally available, except for limited use with BI Publisher type reporting. Fusion BICC, on the other hand, provides a robust technique to enabling data extraction from Fusion Apps.
BICC pulls data from Fusion App view objects and saves it to Oracle Cloud files (OCI). OAC’s BICC data replication simplifies the process of configuring, scheduling, and monitoring the entire process of extracting data from BICC into Cloud storage and subsequently importing the same data into a data warehouse. While this does not provide ETL-like capability, it does streamline the end-to-end process of extracting data from Fusion apps and storing it in relational table structures in an Oracle database. These target relational tables can then be reported on directly or modified for more advanced 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.