Data Integration

Objectives

After completing this lesson, you will be able to:

  • Determine the best data integration option for an implementation
  • Configure an integration scenario using Express Data Loader (XDL)
  • Upload data using XDL
  • Use the validate and transfer pipeline task to import data from staging to production
  • Purge import data
  • Extract payment data using XDL

Options for Data Integration

Typically, the data used to calculate compensation originates in various source systems. The Data Integration process is used to ensure that this data is property mapped and updated regularly before each calculation is run.

For example, payees enter their sales transactions into a CRM tool. The transaction data is integrated into the system, where the payments are then calculated.

When using most data integration options, inbound data moves to a series of staging tables.

  • Excel Data Loaders are used for ad hoc data loads on the user level. It is not designed for high volume data transfers.
  • Express Data Loader (XDL) is an ideal solution for small to medium-sized businesses. This tool is included with the system and uses a secure dropbox to load high volumes of data. The data transfer is completed with HANA procedures.
  • DataSphere is an SAP tool designed for high data volume and data transformation. It runs on SAP Business Technology Platform (BTP) and requires technical expertise to accomplish data integrations.
  • SAP Integration Suite is a cloud based integration platform. It can be used to build complex integration scenarios, manage APIs, and extend connectivity to both SAP and non-SAP cloud applications.. SAP Integration Suite runs on SAP Business Technology Platform (BTP).
  • REST APIs are new configured APIs. Typically, APIs are used for smaller volumes of data, with real-time integration.

Exercise: Import Data using the Excel Data Loader

Business Example

In this exercise, you will import Organization data, Transactions, and Classification data into Incentive Management using the Excel Data Loader. You will use this data to build a compensation plan throughout this course.

Steps

  1. Import Organization data.

    1. Select the Participants icon in the Manage Organization tile.

    2. Select the Upload Excel Data icon on the toolbar.

    3. Select Choose File.

    4. Select the BikesInMotion Organization Data.xlsx file.

    5. Select Upload Excel Data.

    6. When the import is complete select the Quick Search icon to refresh the workspace.

  2. Import Transaction data.

    1. Return to the home screen and selectCalculations - Transactions in the Review Calculations tile.

    2. Select the Upload Excel Data icon on the toolbar.

    3. Select Choose File.

    4. Select the BikesInMotion Transaction Data.xlsx file.

    5. Select Upload Excel Data.

    6. When the import is complete select the Quick Search icon to refresh the workspace.

  3. Import Classification data.

    1. On the home screen, select Plan Data - Classification icon in the Manage Plans tile.

    2. Select the Upload Excel Data icon on the toolbar.

    3. Select Choose File.

    4. Select the BikesInMotion Transaction Data.xlsx file.

    5. Select Upload Excel Data.

    6. When the import is complete select the Quick Search icon to refresh the workspace.

Configuring Express Data Loader (XDL)

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.

Global and file type settings

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.

Exercise: Configure Global Settings

Business Example

In this exercise, you will use the XDL Global Settings to ensure a successful data transfer.

Steps

  1. From the Application picker, select Express Data Loader.

    1. Express Data Loader will open in a new tab.

  2. Update the Global Settings.

    1. Select the Configuration icon.

    2. Select Global Settings.

    3. Update the fields in the dialog box as shown:

      • CompressionMode: Gzip
      • Time Zone: Any
      • Charset:Utf-8
      • Delimiter: Tab
      • Success Emails Recipient:admin.user@email.com
      • Failure Emails Recipient:admin.user@email.com

        Note

        Optionally, use your own email for the email recipients to see what the emails look like.
      • Autorun: No
      • Escape Delimiter: Yes
  3. Select Save.

Express Data Loader Templates

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.

Exercise: Uploading Transactions using Express Data Loader

Validate and Transfer

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.

Purging Data

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.

Using Express Data Loader to extract payments

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.

Best Practices for Data Integration

  • 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.

Log in to track your progress & complete quizzes