X

Best Practices from Oracle Development's A‑Team

Extending Oracle Fusion SaaS with OCI - Integration

Angelo Santagata
Architect

Extending Oracle Fusion SaaS with OCI - Integration

Overview

Using OCI to integrate with Oracle Fusion SaaS Applications (aka Fusion SaaS) can take many forms

  • Inbound ingestion
  • Outbound data export
  • Streaming data from Fusion SaaS

VIrtually every Fusion SaaS installation will have some level of integration with other systems. This could be loading currency exchange rates into Fusion SaaS ERP, Exporting payslips to a payment service to importing Leads from Eloqua to Sales Cloud. Most inbound integrations will follow a similar pattern

Overview of Data Integration With Fusion SaaS

Inbound Ingestion

Depending on the pillar you are using you may/will have to use Fusion SaaS Data Loading tools and techniques. Each pillar uses a different file format and this needs to be known before you can  understand how to import the data correctly.

There are three primary ways to upload data into Fusion SaaS , the method used depends on the Fusion SaaS, or module, being used :

 

CX File Import

HCM Data Loader

  • HCM Data loader is specifically designed for Fusion SaaS HCM. Like other import techniques it accepts a csv file but this file as a specific format called HDL , HCM Data Loading, and this format is quite unique in that it not only allows you to insert data but also merge, delete and load data in the same file. It also allows the loading of binary data, e.g. pictures, within the same zip file. 
    For more information see https://docs.oracle.com/en/cloud/saas/human-resources/20b/faihm/introduction-to-hcm-data-loader.html

ERP File Based Data Loading (FBDI) 

  • The ERP product family has a specific file format called File Based Data Integration (FBDI) used for all data imports. The FBDI format is CSV based  and Oracle provides a Excel macro file (XLSM) which users can use to generate the a zip file containing a single file for each object.  

 All of the above data loading technologies provide REST/SOAP calls so that the data loading can be automated and scripted and many of our customers do automate the importing of data into Fusion SaaS.

Oracle Integration Cloud

Many of Oracle's customers successfully load data into Fusion SaaS using Oracle Integration (previously they might have used Oracle SOA Suite). Oracle Integration is a declarative based environment which is used for integration and can be used for loading data into SaaS. Whilst it is very popular some customers prefer code centric integration tools or building it themselves. 

The rest of this article focuses on loading data into Oracle SaaS using OCI Serverless Services. We will focus on loading data into Oracle ERP but the same technique can be used for any of the SaaS services.

ERP Data Loading In Detail

Let us recap how data is loaded into Oracle ERP

 

Raw Data
Transform
Load to 
Fusion
Fusion Apps
Monitor
Report back/
Deal with errors
Enrichment
Data

 
 

 

 

 

 

In more detail :

  • Raw Data comes into the system, often it's just CSV but can also be XML or some other custom file format..
  • The raw data will often need to be enriched ad transformed into the required format for Fusion SaaS. Sometimes this is simply a case of converting the data to the Fusion SaaS ERP file format but more often there will be some sort of enrichment , or validation, required. For example you may need to convert product Ids to a Fusion SaaS product Id or an advanced example would be that you want to check the data is valid by querying some external systems.
    It is best practice to prevalidate the datafile so that the import process doesn't yield functional errors which will need to be dealt with later
  • Once the file is ready you need to load the data into Fusion SaaS , this is done by using the various APIs available to us
  • Whilst the data is being loaded into Fusion SaaS (it's a batch process so doesn't happen immediately) , you will need to monitor the process and wait for it to finish or wait for the callback from Fusion SaaS which tells you it has finished..
  • Once the import process has finished you will want to download the log file, determine if it all loaded successfully and then take any remedial actions 

Loading Load Data into Fusion SaaS ERP using Serverless Technologies

Some customers don't want to use a declarative tool like Oracle Integration and purposely want a "code centric" approach. The reasons for this are many, sometimes they don't to be tied down to a proprietary product, but more often they want complete control , how it's deployed, how retries are done, how logging is performed and more importantly with the advent of serverless technologies they only want to pay when they are using the service...Using Serverless technologies offers this but the developers do need to be aware that they will be responsible for everything including error management, logging, dashboards etc.

 

 

The above diagram shows a pattern of loading data into Fusion SaaS ERP  using serverless technologies.

The pattern uses the following OCI objects:

  • Object OCI Storage Bucket : Stores the incoming, and inflight, files
  • Oracle OCI Cloud Functions : Does the processing of the files
  • Oracle OCI Notification : Used to send messages to users via email
  • Oracle OCI API Gateway : Used to receive the completion event from Fusion SaaS ERP and call a cloud function to process it
  • Oracle OCI Events :  Defined and emitted whenever a file is loaded into a OCI Storage Bucket
  • Oracle OCI Vault :  Stores the Oracle ERP Fusion SaaS password securely

The pattern works like this :

  1. The inbound file is loaded into an Oracle OCI Storage Bucket
  2. An event is emitted and picked up by a Oracle Cloud Function
  3. This function transforms the data, e.g. from JSON to the ERP file format and then places the resulting file into another OCI storage bucket
  4. A new event is emitted and a loading Function is called. This function loads the data into Fusion SaaS and provides Fusion SaaS with a callback endpoint to the API gateway
  5. When Fusion SaaS ERP finishes processing the file it calls a Function, via the API gateway, which determines if the file was processed or not 
  6. With this information, the function then moves the file to the right storage bucket (processed successfully or not) 

The above example demonstrates how one can create a serverless architecture for loading data into Fusion SaaS ERP. It demonstrates a pattern of using OCI Buckets, Events , Functions and other OCI Services to implement the integration pipeline. The pattern highlights how the pattern can be extended to include notifications and further enrich the integration.  This pattern is code based and totally serverless and unless running there is zero cost to the consumer (except the storage of the files in OCI Storage).  If a customer has a lot of integrations they wish to implement and these integrations will be changed frequently then perhaps Oracle Integration Cloud (OIC) is the right service. OIC provides a rich declarative development environment where integrations can be built , deployed quickly and scale to meet your needs. Additionally the OIC gives you a lot of extra functionality OOTB, such as error handling, retrying of messages , dashboards, reporting, state tracking of previous requests etc.

However you want a totally serverless approach, don't mind the code centric style of integration and relish the flexibility and extensibility this pattern provides then the Serverless Cloud is your oyster! The above example is available as open source sample code on github, see https://github.com/oracle/sample-serverless-saas-erp-dataload for more info and pull requests welcome. Also see this article for more information on this pattern  https://www.ateam-oracle.com/loading-data-into-oracle-fusion-saas-serverless-style-v2 ,.

 

Outbound Data Extraction

Within Fusion SaaS there are a number of tools that can be used to export data. The correct tool of choice depends on the pillar you are using.

 

  •  REST APIs :  Synchronous REST APIs
  • Oracle Business Intelligence Cloud Connector (BICC) : Batch oriented, a newer tool for extracting data from Oracle ERP, over time this tool will replace BIP
  • File Export : Batch oriented, set of online tools for exporting data from Oracle Engagement Cloud and related CX services
  • HCM Extracts : Batch oriented, specialised tool for extracting data from Oracle Human Capital Management (HCM)
  • Oracle Business Intelligence Publishing (BIP): Batch oriented, primarily used for pixel perfect reports and used for extracting data from Oracle ERP when data is not available through the other methods. BIP Publisher should be the last resort, only in batch mode and never synchronously 
     

Generally the following rules should be used when deciding on what tool to use :

  • If you need the data immediately (synchronously) and the amount of data is low (100s of rows, retrievable less than 2.5mins) then REST APIs are the approach you should take
  • If your data is large, e.g. several thousand rows, then use one of the batch oriented approaches suitable for your pillar
  • Do not use REST APIs for exporting large amounts of data
  • If none of the solution above fits your need, then use BI Publisher with queries as light as possible to limit the footprint on the database and always in batch mode and never asynchronously

Data Extraction and OCI patterns

Using A Database to Optimise Outbound Data 

We often see customers who need to query lots of data from their Fusion SaaS services , synchronously, and struggle with the REST APis. The issues they may face include performance, number of rest calls required, lack of complex query functionality with our REST APIs and so on. For example you need to query some data and then "join" it with other data, if you are lucky the "joined" data is available as part of the REST result , within a sub element (e.g. Within Opportunity you also get the customer name as part of the result) but sometimes you don't and need to query the dependant data separately. 

A solution: 

  • Regularly query the "lookup" data using the bulk export method for your pillar. Once this is done store it in a database you are using
  • Query your data by executing a PLSQL function in the Oracle Database, which in turn queries Fusion SaaS REST services and stores the result in a temporary database table (with a unique GUID)
  • The function then queries the data again (using the GUID as a key) and joins it with the local cache data
  • This data is then returned as a collection to the client
  • The client can call the PL/SQL Function via Oracle Rest Data Services (ORDS) or via JDBC/SQLNet

     

Fusion
1. PLSQL Call
ORDS cal
etc
 
Function
1. Query SaaS
2. Store Result in DB
3. Return joined Results
Cache 
Table
2. Store 
Cache
Data
2. Query SaaS
Oracle
DB
Data Sync

 
 

 

 

 

This approach uses the database as a middle-tier server. Its is very efficient given the cached data is already stored in this middle tier. If your "join" query is complex and involves many tables and perhaps advanced query constructs like 

 

Post Export Processing using OCI

 

If you are exporting bulk data out of Fusion SaaS using BICC you can choose to store the results in Oracle OCI Storage Buckets. If you do this then you can use Oracle OCI Events and Oracle Cloud Functions to further process this data . 

In this example pattern, we are delivering bulk data extracts to a mobile device. This data could be a list of products, current opportunities etc, something which takes time to export, usually large (maybe 10s of Mbs) and more importantly it is the same data for everyone (ie no data security except perhaps you need to be authenticated). Additionally if the export is done on a periodic basis then it only needs to be done once in every time period (e.g. every 6hrs) and not for every client.

Walking through this example

  • Data is exported from Fusion SaaS and stored in an OCI Storage Bucket
  • An event is emitted by OCI , which in turn calls an Oracle Cloud Function
  • This Cloud Function processes the data, in our example it converts it from CSV to JSON ,  enriches it and then stores a "zipped" version in another storage bucket
  • The mobile devices call a cloud function (via API Gateway) requesting the data. Because the data is larger than 6Mb the cloud function generates a pre-authenticated URL to the latest ZIP(Json) download and returns it to the mobile device
  • The mobile device can now download the zip file (small) and expand it on the local device using the device's resources.

 

 

This is another example where we can use Oracle OCI functionality to enrich our SaaS functionality in a seamless and efficient way. Much of the efficiency comes from the serverless resources only being used when they are needed, in turn reducing costs to our customers

Conclusion

As you can see there are many ways that Oracle OCI can be used to integrate with Fusion SaaS. In the first instance we would always recommend using the Oracle Integration Cloud service , it's designed for integrations, and then if needed use our OCI cloud native offerings. 

Next Steps 

As a team we have written, and still writing!, a number of articles where we cover many of the "Extending Oracle Fusion SaaS with OCI" topics. If you want to learn more navigate to the Extending SaaS with OCI Series post and checkout the other articles we have written. Do let us know if you have any other topics you would like us to cover do let us know!

Be the first to comment

Comments ( 0 )
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha