Validation

Content validated on 5/13/2021 with

• ODI Version 12.2.1.4.200721.1540

• Fusion 21A (11.13.21.01.0)

Introduction

With its capacity to handle complex transformations and large volumes of data, and its ability to orchestrate operations across heterogeneous systems, ODI is a great tool to prepare and upload bulk data into HCM Cloud.

In this post, we are looking at the different steps required to perform this task.

Overview of the integration process

There are three steps that are required to prepare and upload data into HCM:

• Transform data and prepare a file that matches the import format expected by HCM. Then ZIP the generated file;

• Upload the file to UCM-Cloud using the appropriate web service call;

• Invoke HCM-Cloud to trigger the import process, using the appropriate web service call.

We will now see how these different steps can be achieved with ODI.

Preparing the data for import

We will not go into the details of how to transform data with ODI here: this is a normal use of the product and as such it is fully documented.

For HCM to be able import the data, ODI needs to generate a file that has to be formatted according to HCM specifications. For ODI to generate the proper file, the most effective approach is to create a custom Knowledge Module (KM). The details for this Knowledge Module as well as an introduction to the HCM file format are available here: Oracle Data Integrator (ODI) for HCM-Cloud: a Knowledge Module to Generate HCM Import Files. Using this KM, data can be prepared from different sources, aggregated and augmented as needed. ODI will simply generate the import file as data is loaded into a set of tables that reflect the HCM file’s business objects components.

Once the file has been generated with regular ODI mappings, the ODI tool OdiZip can be used to compress the data. You need to create a package to define the sequence of mappings to transform the data and create the import file. Then add an OdiZip step in the package to compress the file.

the name of the import file is imposed by HCM, but the ZIP file can have any name, which can be very convenient if you want to generate unique file names.

Uploading the file to UCM-Cloud

The web service used to upload the file to UCM is relatively straightforward. The only element we have to be careful with is the need to timestamp the data by setting a start date and a nonce (unique ID) in the header of the SOAP message. We use ODI to generate these values dynamically by creating two variables: StartDate and Nonce.  Both variables are refreshed in the package.

The refresh code for the StartDate variable is the following:

select to_char(sysdate,'YYYY-MM-DD') || 'T' || to_char(systimestamp,'HH:MI:SSTZH:TZM')
from dual

This formats the date like this: 2016-05-15T04:38:59-04:00

The refresh code for the Nonce variable is the following:

select dbms_random.string('X', 9) from dual

This gives us a 9 character random alphanumeric string, like this: 0O0Q3LRKM

We can then set the parameters for the UCM web service using these variables. When we add the OdiInvokeWebService tool to a package, we can take advantage of the HTTP Analyzer to get help with the parameters settings.

To use the HTTP Analyzer, we first need to provide the WSDL for the service we want to access. Then we click the HTTP Analyzer button: ODI will read the WSDL and build a representation of the service that lets us view and set all possible parameters.

If not obvious, for the Analyzer to work, you need to be able to connect to the WSDL.

The Analyzer lets us set the necessary parameters for the header, where we use the variables that we have previously defined:

We can then set the rest of the parameters for the web service. To upload a file with UCM, we need the following settings:

IdcService: CHECKIN_UNIVERSAL (for more details on this and other available services, check out the Oracle Fusion Middleware Services Reference Guide for Oracle Universal Content Management)

FieldArray: we use the following fields:

The screenshot below shows how these parameters can be entered into the Analyzer:

In the File Array we can set the name of the file and its actual location:

At this point we can test the web service with the Send Request button located at the bottom of the Analyzer window: you see the response from the server on the right-hand side of the window.

If you want to use this test feature, keep in mind that:
- Your ODI variables need to have been refreshed so that they have a value
- The ODI variables need to be refreshed between subsequent calls to the service: you cannot use the same values twice in a row for StartDate and Nonce (or the server would reject your request).

A few comments on the execution of the web service: a successful call to the web service does not guarantee that the operation is successful. You want to review the response returned by the service to validate the success of the operation. Make sure that you set the name of the Response File when you set the parameters for the OdiInvokeWebService tool to do this.

All we need to validate in this response file is the content of the element StatusMessage: if it contains ‘Successful’ then the file was loaded successfully. If it contains ‘not successful’ then you have a problem. It is possible to build an ODI mapping for this (creating a model for the XML file, reverse-engineering the file, then building the mapping…) but a very simple Groovy script (in an ODI procedure for instance) can get us there faster and can throw in the ODI Operator log the exact error message returned by the web service in case of problems:

import groovy.util.XmlSlurper

// You can replace the following hard-coded values with ODI variables. For instance:
// inputFile=#myProject.MyResponseFile
inputFile = 'D/TEMP/HCM//UCMResponse.xml'
XMLTag='StatusMessage'
fileContents = new File(inputFile).getText('UTF-8')
def xmlFile=new XmlSlurper().parseText(fileContents)
def responseStatus= new String(xmlFile.'**'.find{node-> node.name() == XMLTag}*.text().toString())
if (responseStatus.contains('Successful')) {
// some action}
else {
throw new Exception(responseStatus)
}

This said, if all parameters are set correctly and if you have the necessary privileges on UCM Cloud, at this point the file is loaded on UCM. We can now import the file into HCM Cloud.

Invoking the HCM-Cloud loader to trigger the import process

The HCM web service uses OWSM security policies. If you are not familiar with OWSM security policies, or if you do not know how to setup ODI to leverage OWSM, please refer to Connecting Oracle Data Integrator (ODI) to the Cloud: Web Services and Security Policies. This blog post also describes how to define a web service in ODI Topology.

Once we have the web service defined in ODI Topology, invoking the web service is trivial. When you set the parameters for the ODI tool OdiInvokeWebService in your package, you only need to select a Context as well as the logical schema that points to the web service. Then you can use the HTTP Analyzer to set the parameters for the web service call:

In our tests we set the ContentId to the name of the file that we want to load, and the Parameters to the following values:

FileEncryption=NONE, DeleteSourceFile=N.

You can obviously change these values as you see fit. The details for the parameters for this web service are available in the document HCM Data Loader Preparation.

Once we have set the necessary parameters for the payload, we just have to set the remainder of parameters for OdiInvokeWebService. In particular, we need a response file to store the results from the invocation of the web service.

Here again we can use Groovy code to quickly parse the response file and make sure that the load started successfully (this time we are looking for an element named result in the response file).

Make sure that the user you are using to connect and initiate the import process has enough privileges to perform this operation. One easy way to validate this is with the HCM user interface: if you can import the files manually from the HCM user interface, then you have enough privileges to execute the import process with the web service.

The final ODI package looks like this:

This final web service call initiates the import of the file. You can make additional calls to check on the status of the import (running, completed, aborted) to make sure that the file is successfully imported. The process to invoke these additional web services is similar to what we have done here to import the file.

Conclusion

The features available in ODI 12.2.1 make it relatively easy to generate a file, compress it, upload it to the cloud and import it into HCM-Cloud: we have generated an import file in a proprietary format with a quick modification of a standard Knowledge Module; we have edited the header and the workload of web services without ever manipulating XML files directly; we have setup security policies quickly by leveraging the ability to define web services in ODI Topology. Now all we have to do is to design all the transformations that will be needed to generate the data for HCM-Cloud!

For more Oracle Data Integrator best practices, tips, tricks, and guidance that the A-Team members gain from real-world experiences working with customers and partners, visit Oracle A-team Chronicles for Oracle Data Integrator.

References

• Oracle Fusion Middleware Services Reference Guide for Oracle Universal Content Management

• HCM Data Loader Preparation

• Fusion HCM Cloud Bulk Automation

• HCM File-Based Loader Example Data Files

• Oracle HCM Data Loader: User Guide

• Oracle HCM Cloud – Bulk Integration Automation Using SOA Cloud Service