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

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.

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

  • At runtime you can find out what security roles the user has by querying the SCIM APIS getUser() API , see (https://docs.oracle.com/en/cloud/saas/applications-common/19a/farca/op-hcmrestapi-scim-users-id-get.html ). The response payload for the user will return their assigned roles.

• 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