Oracle Data Integrator (ODI) for HCM-Cloud: a Knowledge Module to Generate HCM Import Files

Introduction

For batch imports, Oracle Cloud’s Human Capital Management (HCM) uses a dedicated file format that contains both metadata and data. As far as the data is concerned, the complete hierarchy of parent and children records must be respected for the file content to be valid.

To load data into HCM with ODI, we are looking here into a new Integration Knowledge Module (KM). This KM allows us to leverage ODI to prepare the data and generate the import file. Then traditional Web Services connections can be leveraged to load the file into HCM.

Description of the Import File Format

HCM uses a structured file format that follows a very specific syntax so that complex objects can be loaded. The complete details of the syntax for the import file are beyond the scope of this article, we only provide an overview of the process here. For more specific instructions, please refer to Oracle Human Capital Management Cloud: Integrating with Oracle HCM Cloud.

The loader for HCM (HCL) uses the following syntax:

  • Comments are used to make the file easier to read by humans. All comment lines must start with the keyword COMMENT
  • Because the loader can be used to load all sorts of business objects, the file must describe the metadata of the objects being loaded. This includes the objects name along with their attributes. Metadata information must be prefixed by the keyword METADATA.
  • The data for the business objects can be inserted or merged. The recommended approach is to merge the incoming data: in this case data to be loaded is prefixed with the keyword MERGE, immediately followed by the name of the object to be loaded and the values for the different attributes.

The order in which the different elements are listed in the file is very important:

  • Metadata for an object must always be described before data is provided for that object;
  • Parent objects must always be described before their dependent records.

In the file example below we are using the Contact business object because it is relatively simple and makes for easier descriptions of the process. The Contact business object is made of multiple components: Contact, ContactName, ContactAddress, etc. Notice that in the example the Contact components are listed before the ContactName components, and that data entries are always placed after their respective metadata.

COMMENT ##############################################################
COMMENT HDL Sample files.
COMMENT ##############################################################
COMMENT Business Entity : Contact
COMMENT ##############################################################
METADATA|Contact|SourceSystemOwner|SourceSystemId|EffectiveStartDate|EffectiveEndDate|PersonNumber|StartDate
MERGE|Contact|ST1|ST1_PCT100|2015/09/01|4712/12/31|STUDENT1_CONTACT100|2015/09/01
MERGE|Contact|ST1|ST1_PCT101|2015/09/01|4712/12/31|ST1_CT101|2015/09/01
COMMENT ##############################################################
COMMENT Business Entity : ContactName
COMMENT ##############################################################
METADATA|ContactName|SourceSystemOwner|SourceSystemId|PersonId(SourceSystemId)|EffectiveStartDate|EffectiveEndDate|LegislationCode|NameType|FirstName|MiddleNames|LastName|Title
MERGE|ContactName|ST1|ST1_CNTNM100|ST1_PCT100|2015/09/01|4712/12/31|US|GLOBAL|Emergency||Contact|MR.
MERGE|ContactName|STUDENT1|ST1_CNTNM101|ST1_PCT101|2015/09/01|4712/12/31|US|GLOBAL|John||Doe|MR.

Figure 1: Sample import file for HCM

The name of the file is imposed by HCM (the file must have the name of the parent object that is loaded). Make sure to check with the HCM documentation for the the limits in size and number of records for the file that you are creating. We will also have to zip the file before uploading it to the Cloud.

Designing the Knowledge Module

Now that we know what needs to be generated, we can work on creating a new Knowledge Module to automate this operation for us. If you need more background on KMs, the ODI documentation has a great description available here.

With the new KM, we want to respect all the constraints imposed by the loader for the file format. We also want to simplify the creation of the file as much as possible.

Our reasoning was that if ODI is used to prepare the file, the environment would most likely be such that:

  • Data has to be aggregated, augmented from external sources or somehow processed before generating the file;
  • Some of the data is coming from a database, or a database is generally available.

We designed our solution by creating database tables that matched the components of the business object that can be found in the file. This gives us the ability to enforce referential integrity: once primary keys and foreign keys in place in the database, parent records are guaranteed to be available in the tables when we want to write a child record to the file. Our model is the following for the Contact business object:

Data Model for HCM load

Figure 2: Data structure created in the database to temporary store and organize data for the import file

We are respecting the exact syntax (case sensitive) for the table names and columns. This is important because we will use these metadata to generate the import file.

The metadata need to use the proper case in ODI – depending on your ODI configuration, this may result in mixed case or all uppercase table names in your database. Either case works for the KM.

At this point, all we need is for our KM to write to the file when data is written to the tables. If the target file does not exist, the KM creates it with the proper header. If it does exist, the KM appends the metadata and data for the current table to the end of the file. Because of the referential integrity constraints in the database, we have to load the parent tables first… this will guarantee that the records are added to the file in the appropriate order. All we have to do is to use this KM for all the target tables of our model, and to load the tables in the appropriate order.

For an easy implementation, we took the IKM Oracle Insert and modified it as follows:

  • We added two options: one to specify the path where the HCM import file must be generated, the other for the name of the file to generate;
  • We created a new task to write the content of the table to the file, once data has been committed to the table. This task is written in Groovy and shown below in figure 3:

import groovy.sql.Sql
File file = new File('<%=odiRef.getOption("HCM_IMPORT_FILE_FOLDER")%>/<%=odiRef.getOption("HCM_IMPORT_FILE_NAME")%>')
if (!file.exists()){
file.withWriterAppend{w->
w<<"""COMMENT ##################################################################

COMMENT File generated by ODI
COMMENT Based on HDL Desktop Integrator- Sample files.
"""
  }
}
file.withWriterAppend{w->
  w<<"""
COMMENT ##########################################################################
COMMENT Business Entity : <%=odiRef.getTargetTable("TABLE_NAME")%>
COMMENT ###########################################################################
"""
  }
file.withWriterAppend{w->
w<<"""METADATA|<%=odiRef.getTargetTable("TABLE_NAME")%>|<%=odiRef.getTargetColList("", "[COL_NAME]", "|", "")%>
""".replace('"', '')
  }
// Connect to the target database
def db = [url:'<%=odiRef.getInfo("DEST_JAVA_URL")%>', user:'<%=odiRef.getInfo("DEST_USER_NAME")%>', password:'<%=odiRef.getInfo("DEST_PASS")%>', driver:'<%=odiRef.getInfo("DEST_JAVA_DRIVER")%>']
def sql = Sql.newInstance(db.url, db.user, db.password, db.driver)
// Retrieve data from the target table and write the data to the file
sql.eachRow('select * from  <%=odiRef.getTable("L","TARG_NAME","D")%>') { row ->
     file.withWriterAppend{w->
w<<"""MERGE|<%=odiRef.getTargetTable("TABLE_NAME")%>|<%=odiRef.getColList("","${row.[COL_NAME]}", "|", "", "")%>
""".replace('null','')
  }
 }
sql.close()

Figure 3: Groovy code used in the KM to create the import file

If you are interested in this implementation, the KM is available here for download.

Now all we have to do is to use the KM in our mappings for all target tables.

HCM KM in Use

Figure 4: The KM used in a mapping

We can take advantage of the existing options in the KM to either create the target tables if they do not exist or truncate them if they already exist. This guarantees that we only add new data to the import file.

Testing the Knowledge Module

To validate that the KM is creating the file as expected, we have created a number of mappings that load the 6 tables of our data model. Because one of our source files contains data for more than just one target table, we create a single mapping to load the first three tables. In this mapping, we specify the order in which ODI must process these loads as shown in figure 5 below:

Ensure data load order

Figure 5: Ensuring load order for the target tables… and for the file construction.

The remaining tables load can be designed either in individual mappings or consolidated in a single mapping if the transformations are really basic.

We can then combine these mappings in a package that waits for incoming data (incoming files or changes propagated by GoldenGate). The Mappings process the data and create the import file. Once the file is created, we can zip it to make it ready for upload and import with web services, a subject that is discussed in Using Oracle Data Integrator (ODI) to Bulk Load Data into HCM-Cloud.

The complete package looks like this:

 

HCM Load package

Figure 6: Package to detect arriving data, process them with the new KM and generate an import file for HCM, compress the file and invoke the necessary web services to upload and import the file.

With this simple package, you can start bulk loading business objects into HCM-Cloud with ODI.

The web service to import data into HCM requires the use of OWSM security policies. To configure OWSM with ODI, please Connecting Oracle Data Integrator (ODI) to the Cloud: Web Services and Security Policies

Conclusion

With relatively simple modifications to an out-of-the-box ODI Knowledge Module, the most advanced features of ODI can now be leveraged to generate an import file for HCM and to automate the load of batch data into the 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.

Acknowledgements

Special thanks to Jack Desai and Richard Williams for their help and support with HCM and its load process.

References

Add Your Comment