If you want to read more on this subject, please have a look at this prior blog entry: Oracle APEX as a Cloud ERP PaaS Solution.
A challenge to overcome is that your cloud database can’t directly access the tables in the ERP Cloud database. This means that you're unable to run SQL on tables such as PER_USERS or GL_LEDGERS.
Choosing Your Synchronization Strategy
So if you want to be working with employees, roles, projects or any other ERP Cloud information, then you will need a strategy for pulling this data down from Cloud. Here’s a few things to think about:
- Does the information need to be stored in your database? Information such as GL period statuses could be retrieved on the fly by a web service call and not stored in a custom table at all.
- When and how should data be synchronized? Does your data synchronization need to be close to realtime, or is a daily or weekly feed sufficient?
- Should APEX receive new transactions, be refreshed in its entirety, or refreshed for a given timeframe, e.g. the last month or week? We have found refresh strategies can be more fault tolerant than incremental flows and self-healing if there is an outage. The downside of this approach is that you tend to end up sending a larger volume of data each time the integration runs.
- Will you push or pull the data from ERP Cloud?. If the data set is vast or the extract process is slow, you’d be better off scheduling a BI Report to the Universal Content Management (UCM) server that comes with Oracle. APEX can then use the UCM web services pull down the data directly, or if middleware is available you can write an orchestration process to retrieve the data, zip it up and post it into an APEX REST web service.
- If pulling data, how will you achieve this? You could upload slowly changing or monthly data manually, but automated strategies are usually required, such as using APEX_WEB_SERVICE or APEX_EXEC to call custom BI reports or product specific web services. Our blog on How Version 18.1 will help APEX win the Cloud has good detail on how to use these.
- What user will you use to connect to Oracle to call the web service to retrieve the data? What roles and data access do they need?
- What is your data retention strategy? Space in a cloud database has to be managed carefully to make sure that you are well within your allocated quota. Older data can be purged or archived across to a compressed table.
At JMJ we've used custom BI reports to do the synchronization - this gives us the most flexibility to get the data we need. We have used this strategy in a Production environment for over a year and have found it to be reliable and scalable.
Best Practise Data Synchronization
Here’s some thoughts on how to do this successfully in the real world:
- You use the CLOUD_SCHEDULER package to schedule database jobs to pull data down from ERP Cloud. A restriction in a cloud environment is that you have a limit of 5 simultaneous jobs - so if you want to run 10 different jobs on different schedules a different approach is needed. Our solution was to use a single job that runs every 15 minutes. Each time it runs, it then checks to see what synchronizations are required and executes each in turn.
- Calls to any web service over the internet must be fault-tolerant. You should expect both intermittent connection errors and planned outages to result in a web service call failing. A retry strategy helps mitigate shorter outages, for example calling a web service three times before raising an error. Longer planned downtime such as ERP Cloud patching should be anticipated, and the system needs to pick up again once the downtime is over.
- Each web service call will result in data being passed over your internet connection from one database to the other. A key goal is to minimize the size of the data returned. Specifically:
- Return only the data you need. That applies both to rows of data, or individual fields contained on each row. A "SELECT * FROM" strategy is not a good one to choose here!
- If the web service you are calling allows it, return data in zipped format.
- Flat, non hierarchical data such as a list of GL accounts can be retrieved using a simple CSV format without headers. For structured or hierarchical data (e.g. a project and its tasks), an XML format may be more appropriate but the length of each tag name should be kept short, e.g. 3 characters.
- There is a risk with larger data sets that a BI Publisher Report will time-out before returning data. A process that successfully synchronizes 50 projects may start to error when another 20000 projects are created. We built our extracts to retrieve projects in batches of 500 and this has been found to be very reliable.
- Each of the synchronization jobs needs to be fully configurable. You’ll want the ability to schedule jobs at different times and disable/enable them. The parameters for each job should also be changeable, such as the number of days to refresh or the number of rows returned in each batch. We built a PaaS Administration application, using the APEX framework that comes the cloud database, giving us the ability to control these behaviours without code changes or table updates.
- All web service calls should be logged and a robust, debug framework should be put in place to make support simple and issues easy to troubleshoot. Again, an administration application built using Oracle APEX comes in handy here.
Once you have the data synchronizing you can start to have fun extending Oracle Cloud!
Look What We Built!
We recently developed a screen in ERP Cloud to allow users to upload transactions from Concur. The starting point is the Standard Accounting Extract (SAE) that is downloaded from Concur - this is mapped, validated and the costs sent across to Oracle GL and Oracle PPM. The data validation process depends on project, worker and application setup values being synchronized from Oracle using the BI publisher process discussed above.
A PaaS form embedded within ERP Cloud makes the upload process simple and quick - and once complete, a Tieback step runs to make sure that the records sent to Oracle actually made it all the way in and didn't get stuck for any reason.
As the SAE is a generic format produced by Concur, this solution will scale well for other ERP Cloud Customers.
If you want some other ideas of what you could do - have a look at our earlier blog here: ERP Cloud Extensions using APEX.
Matt Paine, Co-Founder JMJ Cloud