27Introduction to HCM Spreadsheet Data Loader

This chapter contains the following:

Guidelines for Using HCM Spreadsheet Data Loader

You can load data to the HCM Data Loader stage tables from either a data (.dat) file or a spreadsheet. Your approach depends largely on how often you load data and the complexity of the data that you're loading. This topic describes some key features of data loading from spreadsheets. This information helps you to identify the best approach for your business case.

Spreadsheet Templates

You must generate the spreadsheet from a spreadsheet template. Some spreadsheet templates are predefined. To create templates, you can either copy a predefined template and edit it or create a template from scratch.

When to Use Spreadsheets

Spreadsheets offer many advantages. For example:

  • Spreadsheets are easy to use.

  • You can create spreadsheet templates and generate spreadsheets tailored to your business case. For example, you can omit attributes, change attribute order, change attribute labels, and add help text. You can also define different templates for creating and maintaining the same business objects.

  • You can enable business users to load data for selected spreadsheet templates.

  • You can save data periodically from a spreadsheet to the stage tables. Therefore, you can load large volumes of records without being prevented by limits on spreadsheet size.

  • Errors are reported in the spreadsheet, and you can correct them there.

  • You can mix your use of spreadsheets with use of .dat files. For example, you can load data from a spreadsheet but maintain it using .dat files.

  • You can use spreadsheets to delete data.

  • In specific cases, spreadsheets are the simpler option. For example, to load an element entry for an element with 5 lines from a .dat file, you supply:

    • 1 record for the element entry

    • 1 record for each name and value

    In a spreadsheet, you supply just 1 spreadsheet line where the values are individual columns on the same line.

Spreadsheet Restrictions

When loading data from a spreadsheet:

  • You can't upload data to attributes whose data type is binary large object or character large object, like images and file attachments.

  • HCM Spreadsheet Data Loader doesn't recognize source keys. Therefore, objects that you load from a spreadsheet have default source keys. To maintain such objects using .dat files, you supply user keys or you can retrieve the source key values to use, using the Integration Object User Key Map Extract.

  • You can't create objects or object components, such as Profile Item, that don't supply user keys. However, you can update such objects if you define a list of values on the local surrogate ID attribute to identify the record to update.

  • You can't update the values of the user-key attributes if they identify the record to update. Therefore, you're recommended to define separate spreadsheets for creating and updating objects. In the spreadsheet for updating an object, you can identify the record to update in one of these ways:

    • Include the local surrogate ID attribute, which has a predefined LOV.

    • Include the primary user key attribute, but configure an LOV for the attribute value. With this approach, the user sees a recognizable value in the spreadsheet.

Note: Whether you load data from a spreadsheet or a .dat file, no approval processes exist for loaded objects.

Setup Requirements

All users must set up desktop integration for Microsoft Excel to use HCM Spreadsheet Data Loader.

When you click Upload in a spreadsheet, your data is uploaded to Oracle HCM Cloud. Behind the scenes, upload is a two-stage process. In the first stage, HCM Spreadsheet Data Loader imports the data to the HCM Data Loader stage tables. In the second stage, HCM Data Loader loads valid business objects to the application tables.

This figure summarizes the process of uploading data using HCM Spreadsheet Data Loader.

The figure starts with the Run Spreadsheet Data
Loader task, which you use to generate a spreadsheet from a selected
spreadsheet template. Data that you load from the spreadsheet is imported
first to stage tables. Objects that import successfully to the stage
tables are loaded automatically to the application tables. Any errors
from the import and load stages are reported in the spreadsheet. You
can correct import and load errors in the original spreadsheet and
upload corrected objects.

How Spreadsheet Data Is Loaded

The details of this process are:

  1. You use the Run Spreadsheet Data Loader task in the Data Exchange work area to generate a spreadsheet for a business object from a spreadsheet template directly. You can also access spreadsheet templates directly from the business objects pages, such as Job, Location, Position.

  2. You click Create Data Set in the spreadsheet to create a data set for the data that you want to load.

  3. You enter data in the spreadsheet and click Upload. This action imports the spreadsheet data set to the HCM Data Loader stage tables.

  4. Some validation occurs in the stage tables. Any import errors are reported in the Progress and Message columns of the Spreadsheet Line Status section of the spreadsheet when you click Refresh.

    Errors at this stage are rare, as the spreadsheet format enforces business-object structure and rules.

  5. As each object loads successfully to the stage tables, HCM Data Loader calls the relevant object service to load the object to the application tables.

  6. The object service validates the object. Any errors are reported in the Progress and Message columns of the Spreadsheet Line Status section of the spreadsheet when you click Refresh.

  7. You correct any import or load errors in the spreadsheet and click Upload again. This action imports the corrected data to the stage tables again.

Tip: You can review the status of an HCM Spreadsheet Data Loader data set on the HCM Data Loader Import and Load Data page. Search for data sets by file type to list those loaded from spreadsheets.

The Instruction worksheet of the spreadsheet may contain instructions on how to use the spreadsheet, while the Messages worksheet holds a summary of all import and load messages generated for this data set.

How You Test the HCM Spreadsheet Data Loader Process Flow and Connections

Before you attempt to load your own data, you may want to test HCM Spreadsheet Data Loader end-to-end processing without creating unwanted data. This topic describes how to test HCM Spreadsheet Data Loader end-to-end processing.

Testing End-to-End Processing

To test end-to-end processing, you use:

  • A process, Test HCM Data Loader Process Flow and Connections

  • An object, Example Object

When you run the Test HCM Data Loader Process Flow and Connections process, you set the Action parameter to Test HCM Spreadsheet Data Loader. The process:

  1. Generates a .csv file of data for the example object

  2. Initiates HCM Spreadsheet Data Loader to import and load the file

  3. Creates a log file, which you can access on the Scheduled Processes page

Approximately 200 hundred records of example data are imported and loaded to the object's own tables. No data is saved to the application tables.

The Example Object

The example object:

  • Has child and grandchild components

  • Supports create and update actions

  • Has attributes of most data types

A predefined spreadsheet template is provided for the example object. The generated .csv file is based on this template.

Deleting the Test Data

You're recommended to run the Test HCM Data Loader Process Flow and Connections process periodically to delete the test data and associated data sets. When you run the process, you set the Action parameter to Delete test data. The process deletes all example data, regardless of whether the data was created by HCM Data Loader or HCM Spreadsheet Data Loader. It generates a file of HCM Data Loader DELETE instructions, adds the file to the DeleteExampleObject||<date and time>||.zip file, and uploads it.

Users who have the predefined Human Capital Management Integration Specialist job role can create and maintain spreadsheet templates for any business object. They can also generate spreadsheets from templates and perform all data-set operations, such as create and upload. By default, no other role has this access. This topic describes how to enable other roles to:

  • Create and import spreadsheet templates.

  • Maintain spreadsheet templates.

  • Generate spreadsheets and create, save, and upload data sets from those spreadsheets.

  • View all data sets in spreadsheets generated from a specific template.

It also introduces some security setup that you must perform to enable users to access relevant tasks in the Data Exchange work area.

Creating and Importing Spreadsheet Templates

To enable users to create and import spreadsheet templates, you perform the Spreadsheet Business Object Access task. Using this task, you can:

  • Select a role and assign one or more business objects to it.

    For example, you can select a custom Compensation Administrator job role and assign all compensation objects to the role. Users who have the job role can create and import spreadsheet templates for all compensation objects.

  • Select a single business object and assign one or more custom roles to it.

    For example, you can select the Job object and assign custom Human Resource Specialist and Human Capital Management Application Administrator job roles to it. Users who have either role can create and import spreadsheet templates for the Job object.

In both cases, users can't create and import spreadsheet templates for other business objects, unless they have other custom roles that provide that access.

Tip: When you assign business objects individually, you must assign the translation objects explicitly, if required. They're not assigned by default with the base object.

Maintaining Spreadsheet Templates

While spreadsheet templates are in Draft status, the users who created them can maintain them. When a spreadsheet template is in other statuses, then only the roles listed in the Template Maintenance section of the spreadsheet template can maintain it.

Generating Spreadsheets and Performing Data Set Operations

Users who can create and import spreadsheet templates determine who can generate spreadsheets and perform data set operations. On the Manage Role Access tab of the spreadsheet template, they identify the roles that can:

  • Create, save, and upload data sets from spreadsheets generated from the template.

  • View all data sets in spreadsheets generated from the spreadsheet template.

For example, a user with a custom Compensation Administrator job role may create a spreadsheet template named US Compensation Grades for loading Grade objects. On the Manage Role Access tab of the template, that user selects a custom Compensation Analyst role and selects the Create, Save, and Upload options. Therefore, any user with the custom Compensation Analyst job role can:

  • Generate spreadsheets for the Grade object from the US Compensation Grades spreadsheet template.

  • Create, save, and upload data sets from the generated spreadsheets.

Note: The Role Access tab of the spreadsheet template is enabled only if the Assign Roles to Template field is set to Enabled. This field appears in the Template Security section of the Define page of the spreadsheet template.

Enabling Task Access

Users who have the predefined Human Capital Management Integration Specialist job role can access the Data Exchange work area and perform these tasks:

  • Spreadsheet Templates

  • Run Spreadsheet Data Loader

Other users need access to the same tasks so that they can:

  • Create, import, and maintain spreadsheet templates.

  • Generate spreadsheets and perform permitted data set operations.

To provide this access, you add relevant privileges to the custom roles that are authorized to use HCM Spreadsheet Data Loader. You must have the IT Security Manager job role or privileges to perform this task.

Perform the Spreadsheet Business Object Access task to enable selected roles to create and import spreadsheet templates for selected business objects. You can secure this access by either role or business object:

  • To secure access by role, you select a single role and assign one or more business objects to it.

  • To secure access by business object, you select a single business object and assign one or more roles to it.

This topic describes both methods. In the Setup and Maintenance work area, go to the following for your offering:

  • Functional Area: HCM Data Loader

  • Task: Spreadsheet Business Object Access

Securing Access by Role

To secure access by role, follow these steps:

  1. On the Spreadsheet Business Object Access page, select the By Role option in the page title.

  2. In the Job and Abstract Roles section of the page, enter the name of the role in the Role query-by-example field and press Enter. For example, enter Compensation Administrator Custom.

  3. In the results, select the role.

    The Assigned Business Objects section title is updated automatically to include the role name.

  4. In the Assigned Business Objects section, select an entry in the Assign menu.

    If you select Assign Individual Business Objects, then:

    1. Search for and select the business objects in the Search and Select Business Objects dialog box. For example, search by the Compensation product area and select specific objects.

    2. Click Add to add the selected business objects to the role. An entry appears in the Assigned Business Objects section for each selected business object.

  5. If you select Assign All Business Objects in a Product Area, then:

    1. Select the product area in the Select Product Area dialog box. For example, select Compensation.

    2. Click Add. A single entry appears for all compensation objects in the Assigned Business Objects section.

  6. If you select Assign All Business Objects, then a warning message appears. Click Add to close the warning and continue. A single entry appears for all business objects in the Assigned Business Objects section.

  7. Click Save.

Users who have the selected role can now create and import spreadsheet templates for the selected business objects.

Securing Access by Business Object

To secure access by business object, follow these steps:

  1. On the Spreadsheet Business Object Access page, select the By Business Object option in the page title.

  2. In the Business Object section of the page, enter the name of the business object in the Business Object query-by-example field and press Enter. For example, enter Job.

  3. In the results, select the business object.

    The Assigned Roles section title is updated automatically to include the object name.

  4. In the Assigned Roles section, click the Add icon.

  5. In the Search and Select Roles dialog box, search for and select one or more roles. For example, select Human Resource Specialist Custom.

  6. Click Add.

    An entry appears in the Assigned Roles section for each of the selected roles.

  7. Click Save.

Users who have the selected roles can now create and import spreadsheet templates for the selected business object.

How You Enable Access to HCM Spreadsheet Data Loader Tasks

You can perform the following tasks in the Data Exchange work area using the predefined job roles Human Resource Specialist and Human Capital Management Integration Specialist:

  • Spreadsheet Templates

  • Run Spreadsheet Data Loader

  • Delete Spreadsheet Stage Table Data

This topic explains how to give other roles access to these tasks. You must have the IT Security Manager job role or privileges.

Access to the Spreadsheet Templates Task

On the Spreadsheet Business Object Access page, you can authorize selected custom roles to create and import spreadsheet templates for selected business objects. You must also enable those custom roles to perform the Spreadsheet Templates task in the Data Exchange work area. To enable this access, you edit the custom roles to add the following aggregate privileges:

Aggregate Privilege Name Aggregate Privilege Code

Load Data Using HCM Spreadsheet Data Loader

ORA_HRC_LOAD_DATA_USING_HSDL

HCM Spreadsheet Data Loader Templates

ORA_HRC_MANAGE_HSDL_TEMPLATES

In addition, if the Manage Data Exchange Work Area (HRC_MANAGE_DATA_EXCHANGE_PRIV) function security privilege isn't already granted to the role, then you must grant it.

Access to the Run Spreadsheet Data Loader Task

On the Manage Role Access tab of a spreadsheet template, you can authorize selected custom roles to generate spreadsheets and perform data-set actions. You must also enable those custom roles to perform the Run Spreadsheet Data Loader task in the Data Exchange work area. To enable this access, you edit the custom roles to add the following aggregate privileges:

Aggregate Privilege Name Aggregate Privilege Code

Access HCM Spreadsheet Data Loader Templates

ORA_HRC_ACCESS_HSDL_TEMPLATES

Load Data Using HCM Spreadsheet Data Loader

ORA_HRC_LOAD_DATA_USING_HSDL

In addition:

  • Roles that are authorized to maintain a spreadsheet template must have the HCM Spreadsheet Data Loader Templates (ORA_HRC_MANAGE_HSDL_TEMPLATES) aggregate privilege. You authorize a role to maintain a spreadsheet template on the Manage Role Access tab of the template.

  • If the Manage Data Exchange Work Area (HRC_MANAGE_DATA_EXCHANGE_PRIV) function security privilege isn't already granted to the role, then you must grant it.

Access to the Delete Spreadsheet Stage Table Data Task

To enable custom roles to perform the Delete Spreadsheet Stage Table Data task in the Data Exchange work area, add the following function security privilege:

Function Security Privilege Name Function Security Privilege Code

Delete HCM Spreadsheet Data Loader Stage Data

HRC_DELETE_HSDL_STAGE_DATA_PRIV

In addition, if the Manage Data Exchange Work Area (HRC_MANAGE_DATA_EXCHANGE_PRIV) function security privilege isn't already granted to the role, then you must grant it.

FAQs for HCM Spreadsheet Data Loader

Why can't I access HCM Spreadsheet Data Loader?

You may not have the correct permissions. You must have the Human Capital Management Integration Specialist job role or privileges.

In addition, the HCM Data Loader Scope configuration parameter must be set to Full.

Can I delete data using a spreadsheet?

Yes. In the spreadsheet template, you set the Supported Action value to Delete only. If the action isn't available, then the selected object doesn't support deletion of any of its components. On the Design page of a template for deleting objects, only those components that can be deleted appear.