Loading data using Theobald Xtract Universal

Introduction

This page describes how to use Theobald Xtract Universal (XU) to load data from your SAP source system into a process app in Process Mining (Automation Suite). Refer to the official Theobald Software website for more information on Theobald Xtract Universal (XU).

Theobald Xtract Universal provides fast data access and extractions to SAP ECC and SAP HANA data sources. For general instructions on how to use Theobald Xtract Universal (XU), see the official Theobald Software documentation Getting Started with Theobald Xtract Universal.

Prerequisites

It is assumed that you have:

• a valid Theobald Xtract Universal license.

• installed Theobald Xtract Universal. See the official Theobald Software documentation for more information on installing an updating Theobald Xtract Universal and customizing the function module for table extraction in SAP.

Note:

Loading data using Theobald Xtract Universal requires that the command line tool (xu.exe) can be executed without additional authentication.

Setting up Theobald Xtract Universal

Theobald Xtract Universal is the SAP certified extraction tool used for data extraction from SAP source systems, integrating with SAP through Remote Function Calls (RFC). Theobald Xtract Universal contains a read-only custom function module that is to be installed in SAP. Use the following link to download the custom module: Z_THEO_READ_TABLE-transport1.zip.

Note:

The license is provided via UiPath for the purpose of Process Mining.

Follow these steps to set up Theobald Xtract Universal.

1. Download the latest trial version of Theobald Xtract Universal from our website, see Download Trial Version. Install Theobald Xtract Universal on a Windows Server / Virtual Machine.

2. Check if the network access to your SAP system is open, see SAP TCP/IP Ports.

3. Grant SAP user rights to access to the SAP instance, see Authority Objects - SAP User Rights for Table.

4. The following components in Theobald Xtract Universal require customization in SAP: Z_THEO_READ_TABLE.

5. Configure Theobald Xtract Universal for the required app template(s). See App Templates.

Note:

Steps 2-4 will require validation from your SAP team.

Setup credentials for the SQL Server database

Uploading data to Process Mining (Automation Suite) requires access to the SQL Server database location to which the extracted data needs to be uploaded. To set up a SQL Server destination connection you need the following setup parameters to authenticate access to the SQL Server database location.

• Server
• Database
• Schema
• Role

See Retrieving the SQL Server database parameters.

Loading Data Using Theobald Xtract Universal

Setting up data load using Theobald Xtract Universal requires several steps to be performed.

1. Setup the SQL User to upload the data;
2. Import the template extractions;
3. Configure the source;
4. Configure the destination;
5. Configure the extraction script;
6. Run the extraction script.

The steps are described in detail below.

Setting up the SQL user to upload the data

Each process app that makes use of the option to upload data via the extractor, needs a dedicated SQL Server user with specific access to the schema associated with the process app.

Follow these steps.

Step	Action	Syntax
1	Create a login. Note: The login needs to be created in the master database.	CREATE LOGIN <SQL_login_name> WITH PASSWORD = '<Password>'
2	Create a database user for the login. Note: This user needs to be created in the AutomationSuite_ProcessMining_Warehousedatabase.	CREATE USER <User_name> FOR LOGIN <SQL_login_name>
3	Assign the user to the correct database role.	SP_ADDROLEMEMBER <Role>, <User_name>
4	Set the default schema for the created user. Attention: This step is only required for loading data using Theobald Xtract Universal.	ALTER USER <User_name> WITH DEFAULT_SCHEMA = <Schema>

Importing the Template Extractions

Follow these steps to import the template extractions in Theobald Xtract Universal.

Step	Action
1	Go to the App Templates page and open the documentation page for your app template.
2	Locate the section Loading data using Theobald Xtract Universal and download the XU_template_extractions_as.zip file for your app template. See App Templates. This XU_template_extractions_as.zip file contains the folders destinations, extractions, and sources.
3	Copy the folders from the downloaded XU_template_extractions_as.zip file to the config folder of Xtract Universal. The location of this folder is [XU_installation_path]/config.
4	Open Xtract Universal Designer and click on the refresh button in to load all extractions.

Note:

You can log in using the default user.

A list of all extractions that each extract one table from the SAP system is displayed. The template source is named SAP and the template destination is named SQL Server.

Configuring the source

Follow these steps to configure the SAP source in Theobald Xtract Universal.

Step	Action
1	Go to Server - Manage sources and edit the SAP source.
2	Fill in the settings on the General and Authentication tabs to connect to your SAP system.
3	Test the connection.

Configuring the destination

Follow these steps to configure the SQL Server destination in Theobald Xtract Universal.

1. Go to Server - Manage destinations and edit the SQL Server destination.
2. Fill in the settings to connect to your SQL Server destination.

Configuring the Extraction Script

Follow these steps to configure the extraction script.

Step	Action
1	Go to the App Templates page and open the documentation page for your app template.
2	Locate the section Loading data using Theobald Xtract Universal and download the extract_theobald_as.zip file for your app template. This extract_theobald_as.zip file contains the extract_theobald.ps1 script and the config.json file.
3	Unzip the content of the extract_theobald_as.zip file on the server where Theobald Xtract Universal is installed.
4	Configure the variables for the script in the config.json as listed below.

Variable	Description
xu_location	The Xtract Universal installation folder. This is the folder where you can find the xu.exe. For example: C:\\MyFolder.
xu_server	Address of the server where Xtract Universal is installed. The default is localhost.
xu_port	Port of the server where Xtract Universal is installed. The default port of an installation is 8065.
as_server	The part of the URL which denotes the server on which the app is created. See Retrieving the SQL Server database parameters.
as_organization	The part of the URL which denotes the organization of the created app. See Retrieving the SQL Server database parameters.
as_tenant	The part of the URL which denotes the tenant the created app. See Retrieving the SQL Server database parameters.
as_app_ID	The app ID of the created app.
extraction_start_date	Start date to be used to limit extractions. Only tables where a date filter is applied are filtered on this date. Format is YYYYMMDD. The default is no limitation indicated by the start date 19700101. Note: extraction_start_date must have a value.
extraction_end_date	End date to be used to limit extractions. Only tables where a date filter is applied are filtered on this date. Format is YYYYMMDD. The default is no limitation indicated by the end date 20991231. Note: extraction_end_date must have a value.
language	The language in which data is extracted from the source system. The default is E.
exchange_rate_type	The exchange rate type that is used for currency conversion. The default is M.

Running the extraction script

To load the data and upload it to for use in Process Mining you must run the extract_theobald.ps1 file.

The time taken for this task will depend highly on the data volumes loaded.

Starting the file manually

Follow this step to start the data run.

Step	Action
1	Run the extract_theobald.ps1 file to start the data run.

The data run also creates a log file that can help in case of unexpected results or if the data upload with the data uploader fails. The file extraction.log contains the logs of the last data run. For each extraction that failed, a return code is displayed. See the official Theobald Software documentation Call via Commandline for more information. When something with the setup is incorrect, this is mentioned in the error message.

Schedule a task for the data run

Instead of running the file manually, you can use Windows Task Scheduler to schedule a task that runs the batch script for automated data refreshes.

Note: It is recommended to select the local SYSTEM user account for running the task and to select the Run with highest priority option in the Create Task window when creating the task in Windows Scheduler.

Important:

If you add an interval for which there is no data available, the dashboards will show an error message.

When the extraction is successful, data ingestion may fails because of some faulty data being extracted. You can filter out this data should using Theobald Xtract Universal using the WHERE clause. Refer to the official Theobald documentation for more information on the WHERE Clause.

Note:

If you have a WHERE clause that uses variables (for example the extraction date) you cannot filter via the UI.

Troubleshooting

The data run also creates a log file that can help in case of unexpected results or if the data upload with the data uploader fails. The file extraction.log contains the logs of the last data run. For each extraction that failed, a return code is displayed. See the official Theobald Software documentation Call via Commandline for more information. When something with the setup is incorrect, this is mentioned in the error message.

Extraction Issues With CDPOS for Non-HANA Systems

In some older, non-HANA systems, CDPOS is a cluster table and may result in a “join with pool table” error such as below.

The extraction of the CDPOS table includes by default a join with the CDHDR table to limit the number of records that need to be extracted. Follow these steps to overcome this error.

1. Edit the CDPOS extraction in Xtract Universal Designer to remove the CDHDR from the tables.
2. Replace the CDHDR~UDATE between @extraction_start_date and @extraction_end_date with:

   CHANGENR between @CHANGENR_min and @CHANGENR_max

3. In the extract_theobald.ps1, replace &"$xu_location\xu.exe" -s "$xu_server" -p "$xu_port" -n "CDPOS" -o "extraction_start_date=$extraction_start_date" -o "extraction_end_date=$extraction_end_date" with the code displayed below.

&"$xu_location\xu.exe" -s "$xu_server" -p "$xu_port" -n "CDHDR_CSV" -o "extraction_start_date=$extraction_start_date" -o "extraction_end_date=$extraction_end_date"
CheckExtractionError('CDHDR_CSV')
$CDHDR = Import-Csv -Path "$xu_location\<app_template_specific>output\CDHDR_CSV.csv" -Delimiter "`t"
$CDHDR_sorted = $CDHDR | Sort-Object -Property CHANGENR 
$CHANGENR_min=$CDHDR_sorted.GetValue(0).CHANGENR
$CHANGENR_max=$CDHDR_sorted.GetValue($CDHDR_sorted.Count-1).CHANGENR
&"$xu_location\xu.exe" -s "$xu_server" -p "$xu_port" -n "CDPOS" -o "CHANGENR_min=$CHANGENR_min" -o "CHANGENR_max=$CHANGENR_max"

Note: this is a generic example, the tables names can be different for your app template.

Note: Replace the output folder <app_template_specific> with the the output folder specified for you app template in extract_theobald.ps1.

Other Extraction Issues

Duplicate key

When uploading data using Theobald Xtract Universal, the following error message can occur:

Possible cause

A field used for the primary key on the table is not retrived in the extraction.

Solution

To solve this, you need to check if all the fields used for the primary key on the table are retrieved in the extraction.

Below is an example from the EKKN table. For this table, the primary key is not complete, because the field ZEKKN is not retrieved. The issue can be fixed by making sure this field is also used in the extraction.

Tip:

Primary keys in SAP tables can be recognized by the key symbol displayed in front of the field name.