You can use Connect’s staging tables and INCENT commands to upload HR data into Incent.

While each of these data types can be maintained by comp admins in the Incent UI, many customers prefer to automate the flow of HR data from their HRMS databases into Incent.

NOTE: While Incent does not  apply an effective date to User accounts or version them, Incent does version the other HR data types: People, Positions, Position Hierarchy, and Position-Named Relationships.

Source Data Files

If you do not have access to a Connect training tenant, you may use the following file to create the HR data set used in this lab exercises. The data have already been uploaded to the training tenants.

HR Lab Data Set – delta_hr_lab – Create the table and run the inserts in this file to create the HR lab data set.

Load Users

Upload and manage User accounts in Incent.

NOTE: Anyone (admins, Connect developers, sales reps, among others) who requires access to the Incent UI must have a User account in Incent. Also, all Incent payees, that is People records, require a User account.

There are four tasks involved in this process:

• Clear the User staging tables of old data.
• Load User account rows from a raw data file extracted from an HRMS into the User staging tables within Connect.
• Upload the staging table rows into Incent.
• Check the User staging error table data.

Let’s get started.

Clear User Staging Tables

Before we can load new User data, there are four staging tables we need to clear.

First, staging.user, the primary staging table for User data:

delete from staging.user;

Now clear staging.user_business_group, where Users are assigned to business groups:

delete from staging.user_business_group;

NOTE: Business groups are defined in Incent. This exercise uses the default, Default Business Group.

Clear staging.user_role, where roles are assigned to Users to control levels of access.

delete from staging.user_role;

Finally, we’ll clear out the old exceptions, from staging.user_exception:

delete from staging.user_exception;

Now our staging tables are clear of any old data, and ready for a fresh upload.

Load HRMS Data into Staging Tables

Before we can load our Users into Incent, we must put them into the staging tables. There are three tables to populate.

NOTE: In our examples, we are using a mock HRMS source data, delta.lab_hr_file. You might have a different data set.

Load Users into staging.user:

insert into staging.user
(action,
name,
email_address,
login_profile,
locale)
select
'save',
employee_name,
employee_email_address,
'Xactly',
locale
from delta.lab_hr_file;

In our example, the SAVE (‘save’) action configures the upload command to perform an upsert operation. If the User already exists in Incent, it will perform an update. If the User does not exist, it will insert a new User.

Instead of SAVE, you can also use the DELETE (‘delete’) action, to remove a User. Users can only be removed if they are not tied to other Incent data, such as People records or compensation results, etc.

NOTE: The primary key for a User is email_address.

Now load User data into staging.user_business_group:

insert into staging.user_business_group
(email_address,
business_group)
select employee_email_address,
business_group
from delta.lab_hr_file;

And we need to add User roles, which control the level of access, to staging.user_role:

insert into staging.user_role
(email_address,
user_role)
select employee_email_address,
role
from delta.lab_hr_file;

Upload Users into Incent

Once our new HRMS data is in the Connect staging tables, we can upload it into Incent.

Upload staging rows into Incent:

incent synchronous upload users;

NOTE: The “action” found on each staging.user row, upsert or delete, determines how Connect transfers the data.

Connect runs the command asynchronously on the server. Even though control returns to the command prompt, it might still be running on the server.

To track the status of your upload, query the SHOW INVOCATIONS result set:

select id, state, status, description, created_instant, completed_instant
from (show invocations)
where id = 'ff8080815498a0ec0154a5f085bd24df';

If the upload process is still running, try the query again in 2-3 minutes.

Verify Upload

Connect does not have an explicit command for validating staging.user rows, but if an upload encounters any errors during execution, messages are written to the staging.user_exception table.

To confirm that there were no errors, we can query that table:

select * from staging.user_exception;

The fact that, in our example, the query returns 0 rows confirms there were no exceptions.

The upload writes the staging data to the xactly.xc_user table, which we can now query for our new User data:

select user_id, email, name, created_date, modified_date, created_by_name
from xactly.xc_user x
where "email" in (select email_address from staging.user);