X

Best Practices from Oracle Development's A‑Team

What you should know when extending SaaS with VBCS – Part 2 Getting Data in and out of SaaS

Angelo Santagata
Architect

In this second part of this blog series we explore some of the patterns we have seen in getting data into Oracle VBCS. Oracle VBCS strictly speaking consumes REST APIs and our Fusion SaaS also supports REST APIs but sometimes we either don’t have access to the REST APIs (e.g because they don’t exist, because they don’t exist or are performance constrained). Depending on the usecase at hand you can choose to either query the data directly or via some of the many bulk apis available.

Oracle Fusion supports tools and APIs for getting data out of Fusion SaaS, each one optimized for the pillar its associated with. Most of the time one would use the “native” data movement tool of the pillar if possible but there are always exceptions .

For Oracle Fusion the data movement tools are best summarised as follows:

If you are using HCM then the logical choice for extracting data is HCM Extracts and HCM import to get the data into Oracle Fusion HCM. For Oracle Fusion ERP you can use a combination of BI Publisher for outbound data and SOAP APIs (ImportBulkData in the ERPIntegration webservice) to load data ointo SaaS.

Sales Cloud has a rich synchronous REST API and for data which isn’t available via the REST API you can get it via BI Publisher just like you do for ERP.

Using a Database to stage (or Cache) the Data

As previously discussed when the Fusion SaaS product doesn’t support REST APIs, or the REST APIs aren’t suitable for consumption by the VBCS application, then a common solution is to use an database as a staging location. With this pattern the developer populates a real Oracle Database with data from Oracle Fusion SaaS which can then be read from via a REST API by VBCS.  This database can hold all sorts of data, ranging from transactional data (opportunities, benefits etc) to static lists of data like List of Values data and product information – ie stuff that doesn’t change often.

If you are exporting data into a cloud Oracle database then there are few things you should be aware of specifically by extracting the data out of Oracle SaaS into a custom database you are effectively bypassing SaaS security – you need to implement data security. It is also worth noting that if you put security controls in the VBCS application to restrict the querying of data, users (developers) can easily by pass this using the browser JavaScript console so the security controls need to be done in the middle tier, something we cover later in this series.

Choice of Database?

With the database approach there are immediately two databases which can be used:

  • The Oracle Database (Bare metal, VM based, ExaData or Autonomous version)
  • Business Objects within VBCS

Both have their pros and cons and generally our guidelines are:

  • Use the Oracle Database if you have lots of data, or need to execute complex queries on the data. If this is the case then the Oracle Database is going be your best bet as you will get full access to SQL in the database to perform complex queries, ability to add custom indexes , partition data and much more. This approach does mean however you need to ensure the database is backed up and secured correctly – no different to any other database in the cloud.

  • Use the VBCS “Built-in” Business Objects

If you don’t have a lot of data and the queries you need to execute are simple, or straightforward, and the schema you will need to create isn’t too complex. If this is the case then the built-in VBCS Business objects may be a better fit. The primary benefit of using VBCS business objects are that the database is already preinstalled, created, access is declarative via the UI and best of all its totally autonomous and managed for you – just like your SaaS instance. The primary disadvantage of using the inbuild BOs are the limited amount of data that can be stored (at the time of writing it is 5Gb) and you cannot create complex queries, and no queries across business objects.

Data can be inserted into the business objects database via the VBCS REST APIs using the standard single row REST api as well as a bulk import API.

Possible Architectures for extracting data into a Database include:

Getting data out of the SaaS solution into the database will almost certainly involve some level of coding or scripting, depending on the pillar being used, the technologies you have access to will determine which one is adopted.

This table details a number of options, with relevant advantages/disadvantages associated with them.

Approach General Steps Advantages Disadvantages

Using a Compute Cloud Instance and custom code

 

  •  Within HCM/ERP/CX Cloud Create and Schedule Bulk Extracts to occur on a regular basis, placing the file into WebCenter Content (UCM)

  • Using custom code (Python/Java etc)

  • Monitor for the creation of the data file

  • Once the data file is created Download the CSV Data file, parse and load it into the Oracle Database into a custom schema

Ultimate flexibility, the developer can choose the language of their choice (Java, Python etc)

 

 

 

This approach involves not only writing code but also scheduling the process and ensuring it runs.

 

 

 

 

 

Using Oracle Integration Cloud (HCM variant)

 

Within OIC

  • Create a new integration which queries data from Oracle SaaS HCM using the HCM Adaptor

  • The insertion of data into the Database would be done either using the Database adaptor (Database) or REST API for VBCS Business Objects depending on the database being used

See link for details on how to connect and query data in HCM

 

Use of OIC massively reduces complexity of the code and environment setup.

OIC can also help with Scheduling and error mgmt which itself industrializes the solution

Requires an OIC Licence

Not suitable for large loads of data

 

 

Oracle Integration Cloud (ERP variant)

 

Within Oracle Fusion SaaS configure BIP Reports to extract the data required and place the results into Oracle Webcenter Content

  • Using OIC UCM adaptor detect when the new files are crated and download the data to OIC

  • Insert the data into the Oracle Database using the database adaptor or direct into VBCS using REST APIs

 

 

Use of OIC massively reduces complexity of the code and environment setup.

OIC can also help with Scheduling and error mgmt. which itself industrializes the solution

Requires a OIC Licence

Not suitable for constantly dealing with large loads of data  

BICC

BICC is a Fusion Side technology , licenced to all Fusion users, which gives the users the ability to schedule exports of data and put the data (csv files) into the Fusion UCM server.

Common solution across pillars, data placed in UCM

 

Limited coverage of Fusion objects but its range is growing month by month.

 

Architecture diagram showing potential architecture with technology options (in blue)

Unless your application is primarily querying the data then you will also need to cope with loading the data back into SaaS. If a REST API exists, then the general advice is to load data directly into SaaS with the API. If there is no REST API then code will need to be built which packages up the changes and uploads it to SaaS using a bulk API method of the SaaS pillar (HDL Import etc).

General Guidelines

  • Come up with a reasonable synchronization schedule, you want the data to be relatively up-to-date but not so frequent that the synchronization is constantly running

  • Keep in mind that when the data is in an intermediate database you no longer have the benefit of Oracle SaaS Data security, you need to manage data security

  • Continuing on the data security side of things, make sure the database is secure itself and conforms to data guidelines such as GDPR, PII and data residency.

    • E.g. if you customer is in Holland and they have a requirement that all data resides in Holland storing a “cache” of data in Frankfurt isn’t going to go down well

  • Ensure you have a strategy for getting data back into SaaS as soon as possible. If you decide to write the data to a temporary location, like the database, or mark the record as “changed”, then you will need to monitor this and execute a job to write the data back into SaaS using the appropriate bulk upload functionality as soon as possible.

  • When writing data back into SaaS try to “batch” loads together so that you are not running an import job for every record.

  • Make sure you handle all data loading errors!!

Next Steps

Click here to proceed to the next article in this series, What you should know when extending SaaS with VBCS – Part 3 Exposing the Database

 

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