Intro

During the last month I had a deep dive into Fusion HCM Bulk Loading Tools.
I gained some practical experience and I’d like to share information about it with you.
Today I am going to tell about Fusion HCM File-Based Loader(FBL) and Spreadsheet Loader(SL).

I saw a lot of messages from customers and partners which say that FBL is very complicated and difficult in use. Yes it is a complicated tool, but at the same time it’s very powerful and flexible tool. You simply need to review documentation once more and choose right tool for your tasks.

• File Based Loader – For conversions and ongoing loads – supports initial and incremental loads.
• Spreadsheet Loader – Business user Loads – Create Work structures, Create/Update Person, etc.

Importing Data – Which tool to choose?

All initial historical data we decided to load by using FBL. Spreadsheet Loader is absolutely not suitable to load initial data because of mentioned limitations. In addition, it doesn’t support ability to provide custom object ids (GUIDs) for some objects (for example for Departments). It’s a nightmare to use system generated GUIDs in dependent objects.

Process Steps

In our case we loaded the following objects: Location, Business Unit, Job Family, Job, Department, Position, Person, Work Relationship, Profile. We used sequence mentioned above and loaded Person and Work Relationship objects in one batch, because loading Person without Work Relationship doesn’t have any sense. You will be able to see employee in the UI if he has assignment at least to Legal Employer.

Let me explain the FBL process steps:

• Preparing the Oracle Fusion HCM Environment. First of all I need to configure some parameters for the Load Batch Data Process. To ensure that our cyrillic data will be loaded correctly we need to change parameter ODI Language = AMERICAN_AMERICA.AL32UTF8. Also for increasing performance of batch process is useful to change parameter Loader Number of Processes = 4. You can do it in FSM Task: Manage HCM Configuration for Coexistence

• Define Oracle Fusion Business Objects. You have to provide minimal setup to hire employee. At least you must create Enterprise, Legal Employer, Business Unit.
• Generate the Mapping File of Cross-Reference Information. You generate cross-reference information for any referenced business objects that you defined in previous step. The cross-reference information comprises Globally Unique Identifiers (GUIDs) for those business objects. To generate cross-reference information, you submit the Generate Mapping File for HCM Business Objects process from the Manage HCM Configuration for Coexistence page:

The Generate Mapping File for HCM Business Objects process creates one or more data files (.dat files) for each business object. The .dat files are packaged automatically in a zipped data file that is written to the WebCenter Content server. To download the file: Open the File Import and Export page (Navigator->Tools->File Import and Export). On the File Import and Export page, set the Account value in the Search section to hcm/dataloader/export and in the search results click the file name. When prompted, save the file locally.

• Generate Sample FBL Files. You can use FBLdi to generate samples. See: File Based Loader Desktop Integrator (FBLdi) (Doc ID 1616867.1). Useful FBLdi features are:
  • No more searching workspaces for sample files now you can generate them using FBLdi.
  • You may also customize these sample files for your instance by using cross reference file.
  • FBL demo database consists of sample core hr transactions, work structures, history rows, multiple assignments, multiple manager, etc records.

Unfortunately, FBLdi doesn’t provide samples for Profile and Profile Items.

• Extract the Source Data. You have to prepare initial FBL files. For starting point you can use generated samples from previous step. Please see available DAT files for Person and Work Relationship objects:

The data that you extract from your source system for upload to Oracle Fusion must be delivered as a set of data files (.dat files), grouped by object type, in a zip file. Also you can validate .dat files using Data File Validator (see: Data File Validation for File Based Loader (Doc ID 1587716.1))

• Deliver Your Data to the Oracle Web Center Content Server. There are several methods of delivering your data to the Web Center Content server:
  • Oracle Fusion HCM File Import and Export interface (Navigator – Tools – File Import and Export).
  • Web Center  Content  Document Transfer Utility Interface.
  • Remote Intradoc Client (RIDC).
  • Even you can use PaaS HCM Connect Application.
• Import Source Data to the Stage Tables and Load. Once you have placed the zip file containing your .dat files in account hcm/dataloader/import on the Web Center Content server, you can either import or import and load the data:
  • From the Load HCM Data for Coexistence page (Navigator – Workforce Management – Data Exchange)
  • Using the LoaderIntegrationService web service.

• Fix Batch-Load Errors. You can review the attributes of a selected object and correct any errors directly in the stage tables using Batch UI. This approach is
  helpful if you want to be sure that the correction has fixed the original error before applying it to the source data or you do not need to maintain the source data. If you make corrections, then you can set the object status to Ready to Process and rerun batch. Also you can use very helpful diagnostic script to analyse and resolve batch errors. See screenshot below:

Please note: Diagnostic package is defined only for Person and Work Relationship

• Purge. You can use HCM Delete Utility to purge loaded data in Test environment. The HCM Delete Utility provides a front end to a set of delete scripts that allow an implementation team to easily remove data they have loaded into Fusion. It can also be used to remove specific data items, for instance where a data issue cannot be resolved through the product code and a clean load is the preferred option. In a Stage/Test environment the HCM Delete Utility can be accessed from the Diagnostics Framework (Help -> Troubleshooting -> Run Diagnostic Tests).

After you have verified that all your files are OK, you will be able to load them in production instance. Be careful, because production environment doesn’t have a magic HCM Delete script. And don’t forget to run “Update Person Search Keywords” concurrent process after successful load.

At the end let me briefly explain the process of generating a spreadsheet and uploading its contents using HCM Spreadsheet Data Loader:

1. On the Initiate HCM Spreadsheet Load page, you generate the spreadsheet for a specific business object.
2. You enter data in the spreadsheet, and click Upload. This action imports the data to a named batch in the Load Batch Data stage tables.
3. The Load Batch Data process, which loads data from the stage tables to the Oracle Fusion application tables, runs automatically when valid data rows are imported to the stage tables.
4. Errors from both stages of the process are reported in the original spreadsheet.
5. Once all data is successfully loaded, you can validate it in the Oracle Fusion interfaces.

Supported Objects:

Benefits of HCM Spreadsheet Data Loader include:

• Ease and speed of use
• Support for some key business objects
• Flexfield support
• Use of the standard Load Batch Data process (also used by HCM File-Based Loader), which provides error handling and is multithreaded
• Minimal configuration
• Mass error correction in spreadsheets
• Offline working

Note: Before using Spreadsheet Loader you have to install and setup ADFdi plugin for MS Office. See: Oracle ADF Desktop Integration Add-in for Excel (Doc ID 1672399.1)

Restrictions

Don’t combine your use of HCM File-Based Loader with HCM Spreadsheet Data Loader for the same data in a single environment. File-Based Loader keeps track of the data it loads to determine whether data is to be created or updated. If you load data either interactively or using HCM Spreadsheet Data Loader, File-Based Loader will be unaware of those changes. This may cause errors.

Many Oracle Fusion HCM business objects comprise a hierarchy of related entities. For example, a person object comprises not just the person entity but also person addresses, phones, names, ethnicity, and so on. When you update most of the complex business objects, you do not have to upload the complete object. For example, if a person’s address changes, then you can upload just the new address: you do not need to upload the entire person business object.
But partial row set during incremental updates is not supported for Work Relationship/Employment history. If you have sent N number of rows to Oracle Fusion HCM for an employment history, you must send (N+1) rows during an incremental update.

Multiple language translation is not supported by FBL. However, you can request scripts through a Service Request (SR).

Spreadsheet loader only supports the create mode, with exception of person and assignment where updates or corrections are supported.

Conclusion

So this tool should be used as the primary mechanism for loading HCM data (both initial load and incremental updates) into Fusion HCM. FBL could be considered as main tool for building intergation scenarios between On-Premise Third-Party Systems and Oracle HCM Cloud.

Also you can use power of Fusion HCM web services…but It will be another story…

Good Luck!

If you have any questions don’t hesitate to ask me.
I appreciate your time and ready to continue our discussion/collaboration in this area.

SY,

Volodymyr