Integration, Process and Visual Builder

OIC makes integration easy with ODI

We know OIC (Oracle Integration Cloud) is capable of file based integration for ERP over API.
And we do know that ODI (Oracle Database Integrator from Data Integration Platform Cloud) is capable of ingesting large file and processing it for ERP through the database layer aka ETL / ELT.

Okay, what if those cloud services work together and give you a simple pattern which can be applied to typical use case such as large number of big files integration with ERP through the database layer.

Following scenarios would be typical integration use case scenarios;

Financial transaction records from branches / retail merchants come in large number of big files
Files have to be ETL / ELT before cut-off time
Business / IT want to know what’s happened for last batch job processing or current job happening
Financial transaction records have to be kept for certain periods for internal audit and legislative requirements

Now let me show how those integration use cases are implemented by OIC and ODI.

Firstly ODI (Oracle Database Integrator) will do take heavy workloads such as ingesting large files, transforming raw transaction records and load them to target database. Luckily OdiInvokeWebService Tool is available for ODI as an API and Integration (from OIC) will invoke this API.

Secondly Process (from OIC) will orchestrate ODI jobs. Some of jobs could initiate only after certain jobs finished. Again, Process will consume Integration to interact with ODI.

Thirdly, business object from Visual Builder will show status of ODI job using one of Oracle JET web components.
Process will set job status using Integration with REST API to business object.

The above is a snippet of available SOAP web service operations from OdiInvokeWebService. Operations invokeStatScen, getSessionStatus are the ones Process invokes through Integration. Setting up and using OdiInvokeWebService tool requires ODI hands-on knowledge, please go to https://docs.oracle.com/middleware/1212/odi/ODIDG/web_services.htm#ODIDG480

REST API could be used as well and that would another blog soon for that.

Integration

1. ODI integration

Most of cases, OIC connectivity agent has to be configured at Linux or Windows host which is able to reach the host runs ODI agent mostly residing in private network. Then making connection to ODI agent host is very straight forward by using WSDL (ex: http://ODI_AGENT_HOSTNAME:PORT/oraclediagent/OdiInvoke?WSDL).

We’ve got two Integration. One is kicking off ODI job aka scenarios. The other is get status of job by session ID. Again, it’s pretty straight forward, it’s calling invokeStatScen, getSessionStatus operations from the connection configured just before.

The sample JSON payload for Start Job Integration is as below.

Get Job Status Integration will response what ODI returns as below.

Session Status is well documented below;

https://docs.oracle.com/en/middleware/data-integrator/12.2.1.3/odiad/running-integration-processes.html#GUID-D4A5C51C-4127-4822-AE9A-615F36AE295F

2. Process Initiation

This Integration is initiating main Process based on file polling from target SFTP server.Target SFTP server provides source files to be consumed by ODI agent. This integration will check the existence of source file from this SFTP server as well.  When file exists, it will kick off main process to orchestrate ODI job through Integration.

Now published processes are available to Integration like other connections as below.

Process just needs to be selected from wizard and then proper data mapping needs to be made. That’s it. No more manual REST API or SOAP web service connection for Process needed!

Now we’ve got basic Integrations ready for Process to orchestrate ODI job.

Orchestration

So here is the job management Process. It visually explains that

• Start a ODI job and get session No X.
• Get this ODI job result by Session No X from sub process
• If this ODI job ran successfully, run next job. Otherwise exit from job running.

The first job here is moving file from SFTP server and next job is to do ETL the source file to target ERP database. It’s very simple process with just one swim lane.

If we need to put Human Task such as Approval task, it can be easily extended. Custom exception handling is possible as well.

Basic integration done, now what?

We’ve achieved simple job management done from OIC & ODI.

Let’s recap it.

1. Files are dropped top SFTP server
2. Main process is initiated by Integration which polls the SFTP server
3. Process orchestrates ODI jobs for getting files and ELT file to database

We can check what’s happening or happened from Tracking and Processes flow as below.

 

Visual Builder, another value in OIC

So far so good but I found checking Tracking to see Integration status and again checking Processes flow to see process status bothered me a bit.

What if there is one simple dash board tells me about just job status without taking lots of time to develop. I decided to use the ODI job response as a job status business object and VISUALISE it from VBCS.

Defining business object is very easy from Visual Builder. I just created like the response for ODI job.

And even I put some values manually.

One of features in Visual Builder is REST API is automatically available for business object. Here is the REST API for Job Status business object.

POST / PATCH do what I need for job status creation / update. I added those two REST API calls in one Integration and let it be consumed from main Process to set job status.

For main Process, it just needs to invoke Integration for business object with specific status.

Now business object reflects what’s happening or happened.

Let’s visualise it.

I used Train widget inside List View as above. And used SessionStatus for Steps in Train. The page design is really based on Oracle JET. I haven’t done much but added simple configuration from HTML code generated by Visual Builder below.

This is what it called LOW CODE feature of Visual Builder – what I’ve done is mainly configuration management 🙂

Here is the simple dashboard about job status.

Thanks for reading and get your hands integrated with Oracle Integration Cloud!