Load Data into Incent

xSQL’s INCENT commands allow developers to manipulate data programmatically in the Incent application. INCENT commands can be invoked manually or added to scheduled steps and pipelines.

xSQL supports the following operations:

• Sales Orders (validate and upload)
  • Validate data in staging.order_item and staging.order_item_assignment tables.
  • Upload order data from staging into Incent.

• Incent HR Data Maintenance (upserts and deletes)
  • Users
  • People
  • Titles
  • Positions
  • Position Hierarchies
  • Named Relationship Hierarchies

• Incent Reference Data  (upserts and deletes)
  • Currency Exchange Rates
  • Customers
  • Geographies
  • Products

Use Incent’s Process Group features to purge sales order batches and to reset or execute commission calculations. The purge, reset, and calculation operations must be performed from within Incent’s Process Group functionality. These operations cannot be invoked from Connect if your implementation uses Process Groups.

In the following examples, we’ll learn how to load and validate orders. Then we’ll load different types of HR data into Incent. The lab lessons do not have examples for all the INCENT command uploads. Please refer to the xCL Reference Guide for more information about the commands.

Source Data Files

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

Sales Orders – source_staging_order.txt – upload this CSV file to your tenant’s Connect SFTP account. Target directory and file name = “/training_lab/sourc_staging_order.csv”.

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

 

Set Incent Credentials

Before you can execute xSQL commands to manipulate data in Incent, Connect requires you to register a valid Incent username and password. This is necessary because scheduled pipelines and steps are run by Connect’s scheduler; when the scheduler invokes a step or pipeline, it uses the stored Incent credential to establish the connection with Incent.

NOTE: Your Incent credentials might be different from the credentials you used to establish the session with Connect.

Connect stores your Incent credentials securely, and the stored credential spans sessions so that you can use it later to invoke Incent xSQL commands.

NOTE: An Incent administrator can add this privilege to user accounts via the Incent UI.

Let’s set the credential:

incent credential (Username='your_Incent_username', Password='your_Incent_password');

 

If your credentials are invalid, it will return an exception message:

 

Test the credential:

incent credential test;

Stage, Validate, and Load Orders

The INCENT commands in xSQL are the only mechanism to alter data within your Incent database and your xactly schema tables.

In our examples here, we’ll learn to load sales transactions into Incent. The process involves five tasks, two using xSQL and three using INCENT commands.

 

Clear Staging Tables (xSQL)

ETL jobs use staging tables (that is, predefined interface tables) to transform raw order data and prepare it for upload to Incent. Before we start a new upload, we need to purge the old data from those tables.

Delete Old Staging Orders

The standard interface table that stores information about sales orders and transactions is staging.order_item.

Delete the old data from staging.order_item:

delete from staging.order_item;

Delete Old Staging Order Assignments

The standard interface table that stores the employee assignments that are used in Incent crediting rules is staging.order_item_assignment.

Delete the old data from staging.order_item_assignment:

delete from staging.order_item_assignment;

 

Delete Previous Validation Error Messages

The standard interface table that stores validation errors is staging.order_item_validation_error. It stores information for rows that fail any validations against staging.order_item and staging.order_item_assignment.

Delete the old data from staging.order_item_validation_error:

delete from staging.order_item_validation_error;

Populate Staging Tables (xSQL)

Now that we’ve cleared our staging tables, we’re ready to insert new raw sales order data into them.

NOTE: For our examples, we are taking data from a CSV file that serves as a simple mock sales transactions data source.

 

Add New Orders to Staging

We’ll use INSERT to retrieve the sales order data we need from a CSV file and put it into staging.order_item. The CSV file contains sales order data for May 15, 2016.

insert into staging.order_item
(order_code,
item_code,
source_id,
amount,
amount_unit_type_name,
quantity,
incentive_date,
order_type_name,
batch_name,
period_name)
select
order_code,
item_code,
source_id,
amount,
amount_unit_type_name,
quantity,
ToDate(incentive_date),
order_type_name,
batch_name,
period_name
from ReadFile(FilePath='/training_lab/source_staging_order.csv',
FirstLineNames=true,
Separator=',',
Quote='"',
Trim=true);

Add New Order Assignments to Staging

This INSERT writes the employee-to-order assignments that are used in Incent crediting rules into staging.order_item_assignment:

insert into staging.order_item_assignment
(order_code,
item_code,
employee_id,
split_amount_pct)
select order_code,
item_code,
employee_id,
split_amount_pct
from ReadFile(FilePath='/training_lab/source_staging_order.csv',
FirstLineNames=true,
Separator=',',
Quote='"',
Trim=true);

 

Prepare Batches for Upload into Incent

All orders are processed in batches, so it is a best practice to ensure there are no conflicts in batch names.

 

Purge Batches

To delete any sales order transactions that might exist in Incent with the same staging batch name that we want to upload, we’ll use INCENT PURGE BATCHES. This command cannot be used in conjunction with Incent’s Processing Group features. Instead, use purge batch functionality found within the Process Group configuration UI. For the lab, we will use Connect’s purge batch command since we aren’t using Incent Process Groups.

incent synchronous purge orders BatchNames=(select distinct batch_name from staging.order_item);

 

Register Batch Name With Incent

Next, call INCENT to create batches to scan the staging order item table for a list of distinct batch names:

incent synchronous create batches;

These batch names are inserted into the Incent application table xactly.xc_user_batch.

Validate Staging Orders

Before we can upload our data from staging to Incent, we need to ensure there are no rows with validation errors, as the UPLOAD command will only load data if all staging rows have passed validation.

Use INCENT VALIDATE ORDERS to run standard validations against all rows in staging.order_item and staging.order_item_assignment:

incent synchronous validate orders;

 

Remove any invalid rows from the staging.order_item and staging.order_item_assignment tables before uploading. The lab example *should* not have any errors, but we will check anyway.

select *
from staging.order_item_validation_error;

NOTE: Your upcoming UPLOAD command will only load data if ALL staging rows have passed validation. Archiving the staging.order_item and staging.order_item_assignment rows flagged with errors in staging.order_item_validation_error is one way to ensure all remaining staging rows are valid. All staging rows in the lab exercise should pass validation.

 

Transfer Staging Orders into Incent

Finally, we can use INCENT UPLOAD ORDERS to upload our sales order data from the staging tables into Incent:

incent synchronous upload orders;

IMPORTANT: If even one row is invalid, UPLOAD will not transfer any of the data. Always validate your data and remove invalid rows from your staging tables before uploading.

 

Check Your Uploaded Orders

Run the following query to see the sales order rows you just uploaded into Incent. Alternatively, use the Incent UI to locate them.

select order_code, item_code, 
incentive_date, amount, 
created_date
from xactly.xc_order_stage os
join xactly.xc_user_batch ub on ub.batch_id = os.batch_id
where ub.batch_name in (select distinct batch_name from staging.order_item);