- Release notes
- Before you begin
- Getting started
- Managing access
- Working with process apps
- Creating process apps
- Loading data
- Uploading data
- Retrieving the SQL Server database parameters
- Setting up a SQL Server account for data upload using an extractor
- Loading data using Theobald Xtract Universal
- Customizing process apps
- Data transformations
- TemplateOne app template
- Purchase to Pay app template
- Order to Cash app template
- Basic troubleshooting guide
Loading data using Theobald Xtract Universal
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.
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.
xu.exe
) can be executed without additional authentication.
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.
The license is provided via UiPath for the purpose of Process Mining.
Follow these steps to set up Theobald Xtract Universal.
-
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.
-
Check if the network access to your SAP system is open, see SAP TCP/IP Ports.
-
Grant SAP user rights to access to the SAP instance, see Authority Objects - SAP User Rights for Table.
-
The following components in Theobald Xtract Universal require customization in SAP:
Z_THEO_READ_TABLE
. -
Configure Theobald Xtract Universal for the required app template(s). See App Templates.
Steps 2-4 will require validation from your SAP team.
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
Setting up data load using Theobald Xtract Universal requires several steps to be performed.
- Setup the SQL User to upload the data;
- Import the template extractions;
- Configure the source;
- Configure the destination;
- Configure the extraction script;
- Run the extraction script.
The steps are described in detail below.
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. |
|
2 |
Create a database user for the login. Note:
This user needs to be created in the AutomationSuite_ProcessMining_Warehousedatabase. |
|
3 |
Assign the user to the correct database role. |
|
4 |
Set the default schema for the created user. Attention:
This step is only required for loading data using Theobald Xtract Universal. |
|
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. |
1 |
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 .
|
2 |
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.
|
3 |
Open Xtract Universal Designer and click on the refresh button in to load all extractions. |
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.
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. |
Follow these steps to configure the SQL Server destination in Theobald Xtract Universal.
- Go to Server - Manage destinations and edit the SQL Server destination.
- Fill in the settings to connect to your SQL Server destination.
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 |
---|---|
|
The Xtract Universal installation folder. This is the folder where you can find the
xu.exe . For example: C:\\MyFolder.
|
|
Address of the server where Xtract Universal is installed. The default is
localhost .
|
|
Port of the server where Xtract Universal is installed. The default port of an installation is
8065 .
|
|
The part of the URL which denotes the server on which the app is created. See Retrieving the SQL Server database parameters. |
|
The part of the URL which denotes the organization of the created app. See Retrieving the SQL Server database parameters. |
|
The part of the URL which denotes the tenant the created app. See Retrieving the SQL Server database parameters. |
|
The app ID of the created app. |
|
Start date to be used to limit extractions. Only tables where we 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.
|
|
End date to be used to limit extractions. Only tables where we 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.
|
|
The language in which data is extracted from the source system. The default is
E .
|
|
The exchange rate type that is used for currency conversion. The default is
M .
|
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.
|
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.
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.
If you have a WHERE clause that uses variables (for example the extraction date) you cannot filter via the UI.
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.
In some older, non-HANA systems, CDPOS is a cluster table and may result in a “join with pool table” error such as below.
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.
- Edit the CDPOS extraction in Xtract Universal Designer to remove the CDDHR from the tables.
- Replace the
CDHDR~UDATE between @extraction_start_date and @extraction_end_date
with:
CHANGENR between @CHANGENR_min and @CHANGENR_max
extract_theobald.ps1
, replace &"$xu_location\xu.exe" -s "$xu_server" -p "$xu_port" -n "P2P_CDPOS_raw" -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 "P2P_CDHDR_raw_CSV" -o "extraction_start_date=$extraction_start_date" -o "extraction_end_date=$extraction_end_date"
CheckExtractionError('P2P_CDHDR_raw_CSV')
$CDHDR = Import-Csv -Path "$xu_location\p2p_output\CDHDR_raw_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 "P2P_CDPOS_raw" -o "CHANGENR_min=$CHANGENR_min" -o "CHANGENR_max=$CHANGENR_max"
&"$xu_location\xu.exe" -s "$xu_server" -p "$xu_port" -n "P2P_CDHDR_raw_CSV" -o "extraction_start_date=$extraction_start_date" -o "extraction_end_date=$extraction_end_date"
CheckExtractionError('P2P_CDHDR_raw_CSV')
$CDHDR = Import-Csv -Path "$xu_location\p2p_output\CDHDR_raw_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 "P2P_CDPOS_raw" -o "CHANGENR_min=$CHANGENR_min" -o "CHANGENR_max=$CHANGENR_max"
- Introduction
- Prerequisites
- Setting up Theobald Xtract Universal
- Setup credentials for the SQL Server database
- Loading data using Theobald Xtract Universal
- Setting up the SQL user to upload the data
- 2: Importing the template extractions
- Configuring the source
- Configuring the destination
- 5: Configuring the extraction script
- Running the extraction script
- Troubleshooting
- Extraction issues with CDPOS for non-HANA systems