Oracle EBS Cloud import data into OS Cloud

[agent09]

Hi,

We want to import data from Oracle EBS Cloud (v.12.2.9) into OneStream Cloud. What would be the best way to do this? Should we use REST API?

Thanks!

[NicolasArgente]

You are correct. Webservices like Rest Api are commonly used to import data from external source. It is also a really good way to import data in a modern and secured way. I have personally used that a lot...
Please give a kudo if it helps

[JoakimK]

REST API is absolutely the most common method to have these two clouds talk to each other. Normally we would prefer to use a direct ODBC connection, but with cloud databases that is not always an option.

However, other options can also be to use a middleware solution, be it a file storage solution or a Datawarehouse, and have your Oracle Cloud push data there, then OneStream can go and retrieve tha data from the same. This is typically used when the size of the datasets are on the larger side and therefore not suited for a REST API approach.

Feel free to submit a ticket to our support desk, and they can route it to us in AAS Integrations to discuss the options you have available.

[franciscoamores]

Hi,

my two cents here for the Oracle Financials Cloud integration

- you would need to define what data set you need. 

- Assuming you need GL balances, be aware that the REST API for GL balances has limitations (ex: 10000k records). Same applies to many others. You may end up with inconsistent logic to run multiple REST API calls behind the scenes to get a full data set (some kind of pagination)

- If you need journals, everything overcomplicates because, in addition to limitations, you introduce journals details being in 3 tables (3 REST APIs): journal batch, journal header and journal lines.

- The best solution to me, would be to consume a BIP Cloud report via REST API or SOAP. That's the only way you can extract data from relational underlying database for Oracle Financials cloud. You could even do a delta journal extract.

With this approach, data would be in the web service response. There also limitations (i think 25k rows) but pagination would be much easier than the REST API one above. You can find some good references in the internet.

- Another approach that can be used it's to execute a ESS job via REST which generates data in UCM and then download the output file via REST. This is much more flexible but the solution architecture gets more complex. No limitations for rows as ESS job runs as a background process. You need an async process which 1) triggers the ESS job and 2)checks UCM for file being created and 3)download the file when it is created.

All above are options for direct pull from Oracle Financials Cloud. Another option, as mentioned in the other reply, is to have a process exporting data from OFC to data warehouse and then feeding OS from there. You need to have a good synchronization between Refresh and Feed processes to get near-real time data.

HTH

[NicolasArgente]

Please allow me to add that it is probably not good practice to load to much data in stage. You probably want cumulated value there. Remember that the Drill Back functionnality can always help your to retrieve the value from source at a more granular level. If you still want to load such a high level of data, you might need BI Blend to the rescue 🙂