A week ago I started to learn new bulk loader feature HCM Data Loader which was recently released (You can see details here HCM Data Loader: Notice of General Availability (Doc ID 2022629.1))
I got ready to spend a lot of time, but I was wrong. It wasn’t very difficult to understand how it works.
HCM Data Loader is very flexible and easy in use. And I would like to share a little experience working with HDL.
Some additional features available in HDL in comparison with FBL
- Finally you don’t need to think about internal GUIDs of business objects. (Supports user keys for all objects. Knowledge of Oracle HCM Cloud internal IDs is not required)
- HDL can both create/update records and DELETE records. (Of course if you don’t have references from dependent objects)
- You can use |SourceSystemOwner|SourceSystemId| Attributes to hold references from third-party systems
- Now you can load translations for translatable business objetcs
- You can load attachments
- You can bulk load photos for employees
How it works
First of all you need to check HCM Data Loader Configuration Parameters. As far as I know starting from Release 10 HDL will be the main tool for bulk loading data into the system. In Release 9 you can still decide what tool you wish to use (FBL or HDL). To make sure that HDL will be accessible you have to set HCM Data Loader Scope parameter to Full.
To load data using HCM Data Loader, you must have the Human Capital Management Integration Specialist job role.
In addition you have to add the HCM Data Loader Duty to the Human Capital Management Integration Specialist Job Role.
Preparation of DAT File
You can download template files for every business object supported by HDL form the system.
You can use HCM Data Loader: Business Object Documentation (Doc ID 2020600.1) as a start point for preparation of DAT files.
There are a lot of instructions and features you can use in DAT file. You can read complete list of features in documentation. But I’d like to note: Never put MERGE and DELETE instructions into one DAT file.
Please see my Worker.dat sample:
As you can see the structure of HDL file is quite simple in comparison with the FBL file structure. Attachments and photos you have to put into separate directory (BlobFiles, ClobFiles) in ZIP file.
HCM Data Validator
And for validation of DAT file we can use Data Validator tool. You can read about it here: Data File Validator Tool for HCM Data Loader (Doc ID 2022617.1)
Import and Load
Finally you need to upload zip file to WebCenter Content Server and submit Import and Load Process.
To achieve full automation you can use tools for transferring HDL files to content Server and invoke web service (https://<url-to-your-environment>/hcmCommonDataLoader/HCMDataLoader?WSDL ) to submit import process.
See details here: Automating HCM Data Loader
Submit Import and Load process you can from Data Exchange Work Area:
And finally you can see my successfully loaded worker with the personal photo:
After loading workers, you usually need to run ESS jobs to perform specific operations:
- Update Person Search Keywords
- Synchronize Person Records
- Refresh Manager Hierarchy
Good Luck and remember:
“When you find your path, you must not be afraid. You need to have sufficient courage to make mistakes. Disappointment, defeat, and despair are the tools God uses to show us the way.” – (Paulo Coelho)
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.
Nice article just what I was looking for. My client is moving from Peoplesoft and we need the .dat files to upload to fusion. How do we transform the data from peoplesoft to the .dat format, in your example the worker.dat? Any tips/document that you can give will help me.
Check this docs:
Co-Existence Advisor: Oracle Fusion HCM (Doc ID 1507783.1)
Integrating HCM 9.1 with Fusion Talent and Workforce Compensation (Doc ID 1480996.1)
Thank you Volodymyr. The links were helpful.
Quick question on rollback, lets assume that we loaded employee data tested and later found that there was an issue with source data. Can the loaded data be rolled back using the delete instruction in the dat file?
Please see notes from HCM Data Loader Guide:
Before deleting an object, ensure that other business objects do not refer to it.
– You can delete complete business objects and individual complete business-object components. You cannot
delete individual date-effective records.
– When you delete a parent object, its child objects and any translation objects are also deleted. For example, to
delete a grade and its child business objects, you create a DELETE instruction for the Grade discriminator. To
delete only a grade rate value child component, you create a DELETE instruction for the GradeRateValue
– You cannot delete worker objects from the production environment, though you can delete some child
components of the worker object. For example, you can delete the person e-mail component of a worker object.
– You cannot include DELETE instructions in translation data files.
– Deletion means complete removal of the object from Oracle Fusion HCM. A deleted object cannot be
– To delete a date-effective object referenced by user keys, the EffectiveStartDate and EffectiveEndDate
attributes are mandatory.
– To delete a date-effective object using source keys, Oracle Fusion GUID or surrogate ID the effective start and
end dates are not mandatory.
– You must not supply a DELETE instruction for an object that has a MERGE instruction in the same file. HCM
Data Loader will not know in which order to process the two instructions.
The Doc Id provided by you talks more on loading PeopleSoft data using FBL. Is Oracle giving a similar tool/patches to extract PeopleSoft data in HDL file format.
Hi Volodymyr Faranosov
Thank you so much for your post, its been extremely helpful.
I’m fairly new to HCM Fusion (been working with fusion about 10months now), and I’ve had no experience using the File-based loader. I’ve loaded data using the spreadsheet loader which was pretty simple. I’m really trying to grasp a good handle how to use the HDL, would It be possible of you to put a few or even one detailed worker dat. file. I would love to study how the dat file is put together, since its one of the only things I’m really having a hard time understanding. I’ve done plenty of reading, but I would like to see some examples if its possible.
Thank you for the article. Very helpful for HDL newbies.
Issue I faced with when repeated your exercise is that I can’t see worker I’ve created via HCM UI. Can you suggest what could be wrong?
As I have mentioned in my article, You have to run “Update Person Search Keywords” ESS job to update the person search keywords table with HDL Batch ID you have just completed.
Thank you, this action helped. Now I can see person in Person Gallery and manage it from there. But I can’t access person from “Workforce Management|
Thanks one more time.
Pingback: HCM Data Loader automation examples | Volodymyr Faranosov
I have the same issue with Vladimir (worker imported into the system and is in the database but not not showing in HCM UI) and I have done the ESS job, but the issues is still not fixed. Please aid in this matter.
Check if the WorkRelationship of the worker has been created successfully. Because you won’t be able to see worker in UI if WR wasn’t created.
Also you have to submit ESS job with parameter of batch id otherwise search index won’t be updated for imported worker.
This is a very nicely compiled artricle, many thanks for that.
Can you please provide sample .dat files for Worker?
I have customer’s specific dat files but I suppose it will be not very good idea to share them. But you can use HDLdi as a start point for learning HDL structure of dat files. This tools provides samples for almost all objects.
See MOS Document for details: HCM Data Loader Desktop Integrator (HDLdi) (Doc ID 2056538.1)
Hope this helps.
Your is very good and has helped me understand how HCM Loader is suppose to work. I’m slowly teaching myself how to use HCM Loader and have run into a problem I think.
How long did it take you to generate the Worker.dat template from Cloud? I submitted the process a while back (about 4 hours ago) and it has not yet completed. I’m not sure why it is taking so long.
Thank you for your article. I seem to be having trouble generating the Worker.dat file it kicks off the process, but seems to run for a very long time. Any suggestions as to what is going on?
Pingback: Fusion Tipster
This is really good articles related to HCM Data loader, thanks a lot for sharing it everyone.
But how to find the source system id and source system owner .
The article is great! I wanted actually the template for absence data load using HDL for maternity leave records. Can you provide with those. I am using Fusion version R11.
I am getting error when adding AssignmentSupervisor Object in my Worker.Dat file.
Kindly solve my problem.
Thanks & Regards
I am doing tests with HDL and I see that the worker is well loaded, but users are not created automatically.
When I consult the worker and enter the “Manage User Account” option I see the following:
User Name: In Progress
Account Status: Requested
And to create the user I have to do it through the option “actions”, and recently there the user is created
You know why this happens? Or in any case, is there another way to create users automatically with HDL?
Thanks a lot for your support.