Case Study
Cloud Data Extraction Engine
Fusion Data Replication – Leveraging your OCI investment
- Services: Oracle Cloud Applications – Fusion
- Industry / Domain: Financials/Supply Chain
Business Requirements
As part of Oracle ERP/HCM implementation, there is always a need to extract data in bulk and near real-time into a staging table that can used as below
- Reference Data for Integrations to validate and derive values in bulk
- Data Warehouse Reporting
- Consumption for upstream/downstream applications/portal in bulk
Objective
Provide a configurable/manage-able data extraction strategy for various data needs
Evaluate and address real time and near real time requirements appropriately
Assist the customer with data identification and mapping (Lookups , domains, dimensional and factual data etc.)
Challenges
| HCM Extract | BIP | BICC | OACS | IACS and GGCS | Data Integration – Classic/Web | ||
Requirements | HCM Table/Business Objects | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ |
| FIN/SCM Table/Business Objects | 🗶 | ✔ | ✔ | ✔ | ✔ | ✔ | |
| Full Table Extract | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| Incremental Table Extract | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| Real time data not needed | ✔ | ✔ | ✔ | ✔ | ✔ | ✔ | |
| Raw Table Extract – All fields in a Table, No Views and Joins | 🗶 | ✔ | 🗶 | 🗶 | ✔ | ✔ | |
|
Maintenance | Upgrade Safe** | ✔ | 🗶 | ✔ | ✔ | 🗶 | ✔ |
| New Capability requested | Real Time Data Replication | 🗶 | 🗶 | 🗶 | 🗶 | ✔ | 🗶 |
|
Additional Cost | Additional License Required | 🗶 | 🗶 | 🗶 | ✔ | ✔ | ✔ |
| Oracle SaaS Execs Approval Required | 🗶 | 🗶 | 🗶 | 🗶 | ✔ | 🗶 | |
| Solution | Development Required | ✔ | ✔ | ✔ | ✔ | 🗶 | ✔ |
- There are many options available to extract data from ERP/HCM Cloud and each of them had own pros/cons
- REST Services
- OTBI / BIP Reports
- BICC
- HCM Extracts
- OACS Replication
- Integrated Access Cloud service (IACS)
- Data Integration – Market Place (ODI)
- Oracle Cloud Infrastructure Data Integration
- High volume data to processed at varying frequency presents particular difficulty in tracking (if not designed properly)
- The data to be used and displayed in external / third party facing portals and accuracy/reliability is of utmost importance
- Deep learning curve at the customer of new technologies
- Customers are migrating from oracle EBS and to ERP cloud and their reliance of many internal systems on the Database (direct query)
Risk Involved
- Service reliability concern
- Compliance to the Internal controls
- Resources allocation for this need or objective were not forecasted in the project upfront
Solutions
- At BTSS, we developed an application that leveraging the customer investment on Oracle Autonomous database
- The application (Cloud data engine extract) uses Oracle ATP/APEX on OCI to automate the data extract from SAAS cloud into ATP database
- Ability to configure via easy-to-use UI
- Ability to define the schedule
- Ability to Monitor & Report the data extractions
- Easy to setup and get it running
- Get your ERP data for your Data warehouse (usually minutes)
Process Followed
- Configure your BICC View that you would like extract data.
- Enter the View name and set the status “Pending” Save
CDEE derives the Metadata information (columns and data type) of the BICC view from ERP cloud and sets the Status to Complete
- Once Metadata is defined, add them to “Manage Programs” UI
- Select the BICC View (Data store) and enter the Target table Name and the frequency of refresh
- View also displays last synchronized date and rows synchronized from the last run.
- View the history of various extracts and their status
- You can filter and report for specific data store or table replication
- You can view the detailed debug log for each run
- You can drill down into Oracle ERP cloud BICC request ID.
Benefits
01
Leveraged existing customer OCI investments
02
Reliable solution for data replication – harnessing the power of Oracle Autonomous Database
03
Solution is written using PLSQL and OCI cloud services (object storage) and it was easy for the client to understand
04
Elegant solution leveraging the fusion capabilities to replicate data natively.
Results
Cost effectiveness
Streamlined
Optimized
Increased Productive with reduced implementation time
