Oracle Cloud ERP offers several built-in reporting capabilities to address the needs for operational reporting. Implementing more elaborate reporting use cases though requires that we extract data out of Oracle Cloud ERP. This is in part because the real-time Fusion APIs are more tailored for transactional interfaces and therefore not adequate to scale to a multitude of transactions and in a cross-functional manner. In addition, there is very limited capability to directly tap into the Fusion ERP relational database, and that is for a good reason to prevent any adverse impact to the application. As a result, just like with all SaaS, the need to incorporate Oracle Cloud ERP data into an enterprise data lake house is essential for the success of company-wide data and analytics initiatives.
While I had presented several options of extracting data from Oracle SaaS in my previous blog posts, in this post, I am focusing on an accelerator solution tailored for an OCI data lake with Oracle Autonomous Data Warehouse. With the Perficient pre-built data lake solution, your Oracle Cloud ERP data gets replicated into an Oracle relational database that one can easily connect to, run SQL queries on, and integrate into any downstream processes for other systems and applications. Basically, Cloud ERP data is made easily accessible by any platform, or anyone, who can issue a SQL statement, and is privileged to do so.
How is it done?
The Perficient solution leverages the following OCI technologies:
- Oracle Autonomous Data Warehouse (ADW): This is an Oracle database that is managed by Oracle and therefore runs with minimal administration. Hardware changes, installs, backups, patching, and upgrades are all Oracle-managed thus enabling enhanced productivity without being occupied with database administration tasks. As a columnar database, Oracle ADW is optimized for batch-type querying and data processing. In addition, through its tight integration with other OCI services, such as OCI Object Storage, we chose to use ADW for an OCI Data Lake solution for Oracle Cloud ERP.
- Oracle Data Integrator (ODI): This is an Oracle data integration platform that offers native integration with each of the following:
- Oracle BI Cloud Connector (BICC) layer for ERP data extraction
- OCI Object Storage for managing and archiving extracted ERP data files
- ADW for ingesting Object Storage data stores into the relational database staging and then merging into the final replication areas.
Our pre-built solution for an Oracle Cloud ERP data lake deploys ODI pre-built data pipeline and orchestration artifacts onto an ODI Marketplace instance hosted on OCI Cloud Compute.
What is included?
Deployed on OCI services, the Perficient data lake for Oracle Cloud ERP accelerates the implementation of an end-to-end solution for data replication. Following is what is included:
- More than 100 most commonly used Oracle Cloud ERP Public View Objects (PVOs) covering several functional areas, including General Ledger, Accounts Payable, Accounts Receivable, Fixed Assets, Expenses, and Purchasing.
- ADW Staging schema for optimized data extract and load incremental refresh.
- ADW Replication schema populated with Oracle relational tables of curated copies of ERP data stores.
- Data pipeline patterns for ODI ELT processes that cover the end-to-end process of building data replications for Cloud ERP data. These patterns cover ERP data extraction via the Oracle BI Cloud Connector, OCI Object Storage file management, data ingestion into staging and replication ADW schemas, and the automated handling of deleted transactions in the source application.
- Detailed logging and exception handling to facilitate maintenance and enhancement rollout.
- Automated management of Oracle Cloud Object Storage data files cleanup and archiving.
Streamlined Data Orchestration
Managing the data pipeline out of Oracle Cloud ERP, through OCI Object Storage, into ADW and then further data curation within ADW involves different data patterns. Therefore, in our solution we have implemented an ODI Load Plan to orchestrate the overall process. Following are key aspects of the Load Plan that can be readily deployed:
- The same load plan can handle both initial and incremental data refresh into ADW.
- Leverages Oracle BI Cloud Connector ODI Knowledge Module (KM) which handles the data extraction from Oracle Cloud ERP for individual data stores.
- The same load plan leverages SOAP Web Service calls to Oracle Cloud BI Cloud Connector and ensures the data integrity of replicated data stores, in comparison to the source application. This process eliminates any data from the data warehouse that has been cleaned up or intentionally deleted from the source application.
- Replicated data integrity is maintained with table constraints that avoid the potential for data duplication or corruption.
- The pre-built Load Plan offers an optimized data pipeline performance leveraging direct data ingestion from OCI Object Storage into ADW. And incremental processing of data leverages native Oracle database merge capability.
Extensibility
Besides an accelerated deployment, key to the success of a data lake initiative is the velocity of extending the initial footprint as business stakeholders demand new data stores for their evolving analyses. Accordingly, what is deployed initially lays the foundation upon which we can expand the data lake to incorporate additional data stores from any Oracle Cloud Application module. Our existing ODI patterns can easily be applied to additional data stores leveraging the same methodology that has been tried and tested. Whether it be patterns for Oracle Fusion data integration with OCI Object Storage, for files import into ADW, or SOAP Web Service calls to Oracle Fusion, there are mappings and procedures that are reusable to load an unlimited number of Oracle Cloud Applications data stores.
When the intention is to integrate Oracle Cloud Applications data into non-Oracle databases, we can orchestrate this by customizing the same ODI load plan. ODI’s built-in connection adaptors to a variety of non-Oracle databases enables us to easily copy the replicated data into other databases, when necessary.
Whether you’re just starting your journey with Oracle Cloud Applications or already there and looking for ways to propagate ERP data into other OCI platforms and beyond, the Perficient data lake solution offers an accelerated route. Our experts in Oracle Fusion Applications data and analytics solutions will enable a quick deployment. As a result, the effort that would have been necessary to implement new technologies are instead well spent on real business solutions.
