What are the integration tools in Oracle HCM Cloud?

Data extraction tools:

First, we’ll look at the part that’s generally the easiest to implement, the output interfaces of Oracle HCM Cloud.

BI Publisher Report:

With Oracle HCM Cloud, you have the possibility via Oracle BI Publisher to create data models and write SQL queries on all HCM Cloud tables (more than 3,000 tables) with both business and technical tables.

This solution will allow you to meet all your needs. You can choose the output format (csv, xml) and encrypt the file with a pgp key you define.

For the output channel, you can retrieve the file either by web service, which will be encoded in base 64, or import your ssh key and define an SFTP.

The BI Publisher report is the solution we prefer at S&H for our interface implementation projects because it is the most flexible approach. In addition, it allows you to put the intelligence and business rules into a third-party tool.

Benefits:

• All functional and technical data available
• Proven technical tool: SQL
• Ability to schedule and trigger reports via web service
• Choice of format and output channel
• Response time

Disadvantages:

• Requires development skills
• Everything is specific

HCM Extract:

This solution was designed to meet the needs of payroll interfaces but can be used in other contexts. Oracle offers pre-configured HCM Extract for sending to payroll. HCM Extract has many advantages with a group id mechanism to define extraction perimeters and especially the delta “change only” mode which is natively managed.

Benefits:

• HCM Extract provided by Oracle
• Delta mode
• Can be modified by less technical profiles than BI Publisher (graphic interface)

Disadvantages:

• More difficult to customize, requires understanding of how HCM Extract works
• Not all data is recoverable by HCM Extract. The data is based on entity view. Some require a BIP report.
• Response time not as good as BIP

Many possible filters on HCM Extract (HCM extract, filter criteria, fast formula). Business rules can be developed at several levels. Depending on the implementation, it is more complicated to maintain and develop.

You can retrieve the file via web service or SFTP. The main advantage ofHCM extract is the management of the native delta mode, thanks to the backup of the previous runs. But this may also be its weakness. Indeed, we have observed extraction times that increase gradually with time.

Unitary WEB SERVICES to consume and integrate data

Web service REST :

Oracle HCM Cloud provides REST APIs on many functional objects. The main interest of these APIs is for the consumption of real-time data by a third-party system such as a web portal or a mobile application.

It is an API based on the RESTful “standard” with the advantages and disadvantages that this entails. As these are unitary web services, it is necessary to go back and forth several times to implement a business transaction.

Benefits:

• WS ideal for mobile or web application development
• Performance (speed and lightness)
• Flexibility for developers
• Very complete documentation
• href links offered in response to the REST to retrieve other information

Disadvantages:

• WS unit per employee
• For use by developers only
• Not all objects are accessible

SOAP web service

There is a less known SOAP web service. The advantage, compared to the REST web service, is that it allows for a greater depth of history.

Benefits:

• WS SOAP integration
• More information than WS REST
• XML format

Disadvantages:

• Not all integration keys are available
• Inconsistency between get and update for payloads

Ideally, there should be one web service per integration type per key.

Web services allow for real-time integration and interfaces. It is the most efficient and modern solution. However, since these are unitary web services per transaction, the monitoring and interface setup can be more difficult to implement than a file-based interface.

Data integration tools:

Speadsheet Data Loader:

It is a business-usable tool that allows you to make bulk changes in HCM Cloud via Speadsheet Data Loader.

Benefits:

• Usable and understandable Excel
• Easier to use than HDL: all elements are online
• Input values can be controlled by lists

Disadvantages:

• No delete operation
• Not all integration keys are supported
• Difficult to secure

HCM Data Loader (HDL):

Since the end of the old FBL and PBL tools , HDL for HCM Data Loader is the main tool for integrating data by file. It is a tool that allows you to create, update and delete data on a large number of functional objects. It is designed to handle mass data.

There are several integration keys (user keys, source keys and surrogate keys). These keys determine the operation that will be performed following a “Merge” instruction: addition or correction of data. If you want to update HCM Cloud by interface afterwards, it is important to choose the right type of keys at creation. However after an initial load, it is now possible to correct the integration source keys with the SourceKey.dat object.

HDL has many options (#RETAIN, #NULL, SET PURGE FURTURE CHANGES N) to get the desired behavior (data cascade, value blanking, effect date propagation). HDL has two data controls. First, it checks the format and structure of the expected lines: this is the physical import part. Then it groups these rows into logical objects to insert the data into the different tables.

Errors can be present on both levels. All errors are accessible via the web interface (image below) but also in tables. A file can be imported both manually via the screens and by web service (zipped file, pgp, b64 encoded).

Benefits:

• Large number of objects and data
• Number of options
• Performance
• Monitoring
• Triggering and status recoverable by WS

Disadvantages:

• Complexity and handling of the tool
• Some error messages are difficult to understand for a novice

HDL is the central tool forintegration with Oracle HCM Cloud. It is used for data recovery as well as for the construction of interfaces.

Bonus

Payroll Batch Loader (PBL): This is a flat file based loader. This tool was mainly used for payroll elements and is hardly used anymore. It has been largely replaced by HDL.

File Based Loader (FBL): This is the tool that preceded HDL and was based on file-based loads. There was less intelligence than HDL. It was replaced many releases ago.

Oracle HCM Cloud provides a wide range of tools for integration projects. Some are not fully documented, but by combining them, it is possible to meet all needs. It is probably one of the solutions with the most integration tools on the market.

The choice to use one tool rather than another depends on each context: interfaces or data recovery, source system, technical maturity of the IT platform to consume web services…

This article is not exhaustive and gives a first overview of the integration capabilities. So if you’ve discovered a use case that isn’t covered or another feature, we’d be happy to talk with you to complete this article.

By Joffrey CHU