Data Integration

Express Data Loader (XDL) is a cloud-based tool that allows you to securely import and export data to and from the System without the need for coding. XDL enables the compensation team to synchronize data with the core system.

XDL can be used to import data, such as Transactions or Payees, from the CRM, or Product Data from the ERP system. You can also use XDL to export data, such as payout data, to a payroll system or provider.

To start using Express Data Loader, the first step is to configure file requirements. This had three steps: global settings, inbound file type settings, and outbound file type settings.

Global settings are default settings that apply to both inbound and outbound file transfers. Global settings include such options as the default time zone, character encoding, compression type, and the email recipient for success and failure notifications.

Inbound file type settings include the standard settings and metadata of each file that is imported. A file type must be configured for each template file, which means that every template must have an associated file type. Settings include the file type, import options, and target staging table. You can also override the default character set and delimiter by setting these in the file type settings.

Outbound file type settings include the standard settings and metadata of each file that is exported, such as payment data. Settings include the outbound file type, source stored procedure, and compression mode. As with inbound file type settings, you can override the default character set and delimiter by setting these in the file type settings.

1. From the application picker, select Express Data Loader.
2. Select Configuration – Global Settings.
3. Set the compression mode. The default is Gzip.
4. Set the time zone. This is used for the timestamp on the job.
5. Select a character encoding attribute (Charset). The default is UTF-8.
6. Select a delimiter. The default is tab delimited.
7. Select a retention period for error logs and jobs. The defaults are 30 days and 90 days, respectively.
8. Enter an email for notification of success and failure emails.
9. Using the Autorun switch, indicate whether the import should run automatically once the file upload is executed.
10. Select an escape delimiter. This setting specifies if comma separated files which have a delimiter as part of its data can be loaded. When this option is enabled, you can use double quotation marks for importing values which have a delimiter as part of the field ("").
11. Select Save.

1. Select Configuration – File Type Setup.
2. Scroll or search for the file type. The example below shows the setup for sales transactions.
3. Select a character encoding attribute (Charset) or leave it at the global default.
4. Select a delimiter or leave it at the global default.
5. Select an import type. More information on import types is covered in the next topic.
6. Select the switch to proceed with an import if errors are found in validation.
7. Select the Header switch if your data file has headers.

Various templates are provided to upload data using XDL. Each template is specific to an inbound file type and relates to a type of data.

All the templates are in .xls format and are available as a downloadable .zip file. They can be downloaded from the online help here.

The following video shows how to find and download the XDL templates, how to populate the templates, and how to name and save them using the naming convention.

To upload a data file:

1. In Express Data Loader, selectJobs .
2. SelectUpload.
3. Navigate to the import file and selectOpen .
4. SelectExecute .

Note

If the Autorun option is selected in Global Settings, this step can be skipped as the job will execute automatically.

Once the templates are in place, it’s time to upload the data. This is done from the Jobs tab in XDL.

When loading data from external sources, stage tables help us validate the data before it is moved into the production tables. It also provides an opportunity to back data out of the system, if we see any problems.

The stage tables can be found under Review Calculations – Run – Stage Tables.

The Validate and Transfer process can be initiated from Express Data Loader, or it can be run independently after the data is loaded. A Validate and Transfer process is initiated as a Pipeline task by selecting the Import Data from Stage task.

The easiest way to initiate a Validate and Transfer task is to select the Import Type option during the File Type Setup in XDL. Setting the Import Type to None will not run Validate and Transfer. Other Import Type options are Validate, Validate and Transfer, and Validate and Transfer if All Valid.

Once stage data has been imported, there is often no need to keep it in the stage tables. Therefore, it’s a good idea to regularly purge data from the stage tables. Purging data has two advantages: it increases performance of the XDL data upload process, and it reduces the amount of stored data.

Data can be purged in the user interface or by using XDL.

Learn more in this blog post.

In addition to uploading data from source systems, XDL can also be configured to extract payment data for integration with payroll and accounts payable systems. This has the added advantage of allowing the administrator to customize the output to meet the needs of the target system.

Before creating an outbound configuration, the user should have access to the following:

• Permission to run a Pipeline
• A file explorer that allows a connection to the tenant database, such as WebIDE, Hana Studio, or Eclipse
• The ability to create Custom Tables and Custom Stored Procedures on the tenant database
• If using Secure FTP for the file transfer, access to the Outbound folder on SFTP dropbox
• RestAPI v2 and Odata API Access for debugging

To create an outbound configuration in XDL:

• Using WebIDE or another HANA database access application, create a custom table containing the attributes to extract. For this example, this table will be called PAYMENTEXTRACT.
• Create a stored procedure to extract the data. For this example, the stored procedure will be called EXTRACTPAYMENTS.
• Go to Configuration – File Type Setup and create an extract configuration.
• Select the Outbound tab
• Create a new File Type using the + sign on the Toolbar

• In the Create New File Type dialog box:

  • Set the type to Outbound
  • Enter the source PAYMENTEXTRACT
  • Enter an optional description
  • Enter the output file type txt
  • Enter the source PAYMENTEXTRACT
  • Enter the Source Stored Procedure EXTRACTPAYMENTS
  • Leave the Compression Mode at the default
  • Set the Character set (Charset) to Utf-8
  • Set the delimiter to Tab
  • Set the Header switch to Yes
  
• Run a Pipeline for the Data Extract Stage. For the Data Extract, enter the name of the custom table. In this example, we use PAYMENTEXTRACT.

Learn more about outbound file transfers in this blog post.

• Excel Data Loaders are used for ad hoc data loads on the end user level. It is not designed for high volume data transfers.
• The Express Data Loader (XDL) is an ideal solution for small to medium sized businesses. This tool is included with the system and uses a graphical user interface to upload high volumes of data.
• Smart Data Integration (SDI) is designed for high data volume and data transformation. It uses Web IDE, a graphical tool, used to define flowgraphs for data transformations.