While Oracle ERP Cloud is growing ERP in the Software world and many organizations are adopting it, integrating it is not been so easy especially if one does not use middleware from Oracle to do so.
First thing first, if you are thinking or you already have Oracle ERP Cloud and thinking about integrating it with other system, here are few points that can help.
If you are looking for application to application (A2A) integration, if you are ok to subscribe to Oracle Integration Cloud Service(OICS) then mystery is solved. OICS as Oracle cloud adapter that can help you integrate without worrying much about complexity behind integrating Oracle ERP Cloud. Another blog coming up explaining ERP Cloud adapter on OICS.
But in case your use cases are different like you want to extract volume data from ERP cloud or you want to pull the data from ERP cloud and push it to data lake or data warehouse then OICS may not be best fit as OICS is charged based on message pack and also may not be efficient for volume data. Or if you do not want to invest into OICS subscription but interested on home grown Standard based integration, then continue reading.
This blog will focus on ways to extract data out from Oracle SCM cloud & Order Management and pros and cons of each approach. Same can be applied for most of ERP Cloud modules. I will writing part 2 explaining ways to insert the data into ERP cloud.
Ways to Extract Data out from Oracle SCM Cloud & Order Management
- Using BI Publisher
- Using BICC
- Using OTBI
- Using Data Sync Extract Tool
There are other ways to extract the data in case customer is using Oracle Analytics cloud or Warehouse powered by DBCS etc. but I am focusing on extracting data for external tools. Let’s list down the pros and cons of all 4 above approaches.
- BIP: The most popular ways to extract data since this gives direct access to ERP cloud databases and what integrators need to know or build is query. BIP is intended to provide an option for accessing real time data for ad-hoc reports, or for integrations that pull limited data sets, via web-service. BI Publisher is not intended to be used as a large scale extraction tool, and will not scale. As said BIP is not recommended because of following reason.
- BIP is not optimized to extract the records from the table that may have huge volume of data and if the column is not indexed on which condition is dependent. to exemplify, if integrator uses below query to extract the data from DOO_HEADER_ALL table where LAST_UPDATE_DATE is not index field, then query may take lots of time based on how many records are there in DOO_HEADER_ALL table. This is big one.
“Select HEADER_ID, ORDER_NUMBER, CREATION_DATE, ORG_ID from DOO_HEARDER_ALL where LAST_UPDATE_DATE in (StartDate, EndDate)“
- Second, BIP is not recommended for bulk extract. What this means that if issues were faces, specifically performance related issue, you may not get support from Oracle. There are rumors that Oracle product management are trying to find alternative of BIP so as they can stop BIP in the product. So development around BIP is at your own risks.
- BICC: BI Cloud Connector is inbuilt tool within Oracle Cloud ERP to extract the data in form of CSV files. These CSV files can be posted either to Oracle Cloud Storage or Universal Content Management (UCM) Server. Then These files can be downloaded from Cloud Storage or UCM into local storage and then those files can be translated into desired format before uploading into target applications. Entry point for BICC is using below URL
https://<SaaS Cloud Host>:<SaaS Cloud Port>/biacm
Here are features –
- Data extraction runs can be scheduled in ESS with a frequency of running annually to running daily. Minimum frequency could be hourly.
- Ad hoc runs are possible with the mode “Simple” that runs the extraction once at a certain time
There are following thing that can be configured from BICC Console
- Scope of data sources to extract
- Registration of custom objects
- Switch between full vs incremental data extract
- Storage location of extracted CSV files
As part of BICC extraction, content comes as compressed ZIP files and may be huge – especially when customers run a full extract for the first time or reset a data extract to “Full”.
Another very important point to note that admin user should be use to perform BICC. If user used does not have permission to run the extract, no data will be extracted and customer may not be able to understand why data is not coming. No errors are thrown.
- Data Sync Tool: