Create External Data Sources
External Data Sources allow workflow data to be read from or written to an external source, for example:
- An existing SQLite, SQL Server, DB2 or Access database table.
- An Excel spreadsheet.
- A CSV file.
- A custom data source defined by specifying a connection string.
An External Data Source may also be used to store performance history data associated with an RFC Destination. The External Data Source is created by uploading one or data files that have been downloaded from SAP using the ST03 or ST03N transactions. Performance History Data External Data Sources are stored as SQLite databases and provide application statistics only.
External Data Sources are accessible from the Data > External Data Sources folder in the LiveCompare hierarchy. This folder may contain child External Data Folders which allow related External Data Sources to be grouped together.
When an Access, SQLite, Excel or CSV External Data Source is created, the associated database, spreadsheet or text file is uploaded to the <LiveCompare user data dir>\public\ExternalDataFiles directory. SQLite databases don't need to be uploaded and may be created directly. SQL Server and DB2 External Data Sources are accessed directly from the machine running SQL Server or DB2.
If you have Administrator or LiveCompare Editor permissions, an External Data Source can be created by carrying out the following steps:
- Select the Data > External Data Sources folder in the LiveCompare hierarchy and choose New > External Data Source… from the context menu.
- Complete the Add External Data Source screen as follows:
| Field | Description |
|---|---|
| Name | A unique name that will identify the External Data Source. |
| Description | A description for the External Data Source. |
| REST API | Select Yes to create an External Data Source that is exposed for use by a Web Services client using a REST API. By default, REST API External Data Sources return a maximum of 1000 table rows. The REST API option is not available for XML External Data Sources. Select No to create an External Data Source that is accessible to LiveCompare only |
| Type | Select the type of External Data Source to create, then complete the remaining screen fields as described in the sections below. |
| Add to Resource Groups | If LiveCompare is Resource Group-controlled, deselect the appropriate checkboxes to exclude the External Data Source from one or more Resource Groups. If you have LiveCompare Editor permissions, the checkbox for your current Resource Group may not be deselected. If you have LiveCompare Administrator permissions, an External Data Source won't be accessible unless it is assigned to at least one Resource Group. |
Access External Data Sources
| Field | Description |
|---|---|
| Upload Choose existing |
Click Browse... to browse for an existing Access database with an .mdb or .accdb extension. The database file will be uploaded to the LiveCompare server. Alternatively, if any Access External Data Sources have already been defined, click Choose existing to select an Access database file that has already been uploaded. |
| Table name | The name of an existing or new Access table to be used as the External Data Source. If a new table name is used, it will be created with the appropriate column definitions when the Write External Data Source action is first used to write to the External Data Source. |
| User ID | If necessary, enter the User ID required to sign in to the Access database. |
| Password | The password for this User ID. |
CSV External Data Sources
| Field | Description |
|---|---|
| Upload CSV file | Click Browse… to browse for a filename. The CSV file will be uploaded to the LiveCompare server. |
Custom External Data Sources
| Field | Description |
|---|---|
| Connection String | Enter the custom connection string required to access the External Data Source. You should make sure that an appropriate provider is installed on the LiveCompare server. |
| Table Name | Enter the name of the table to be used for the External Data Source. |
DB2 External Data Sources
| Field | Description |
|---|---|
| Server name | The name of the DB2 server hosting the database to be used as the External Data Source. |
| Port | The DB2 port to use for the connection. Leave this field blank to use the default DB2 port (50000). |
| Database name | The name of an existing DB2 database registered on the DB2 server. |
| Table name | The name of an existing or new DB2 table to be used as the External Data Source. If a new table name is used, it will be created with the appropriate column definitions when the Write External Data Source action is first used to write to the External Data Source. |
| User name | The username that will be used to sign in to the database. |
| Password | The password for this username. |
Excel External Data Sources
| Field | Description |
|---|---|
| Upload Choose existing |
Click Browse... to browse for an existing Access Excel spreadsheet with an .xls or .xlsx extension. The spreadsheet file will be uploaded to the LiveCompare server. Alternatively, if any Excel External Data Sources have already been defined, click Choose existing to select an Excel spreadsheet that has already been uploaded. |
| Worksheet | The name of an existing worksheet to be used as the External Data Source. |
SQLite External Data Sources
| Field | Description |
|---|---|
| Upload Choose existing Create |
Click Browse... to browse for an existing SQLite database with a .db extension. The database file will be uploaded to the LiveCompare server. Click Choose existing to select an SQLite database file that has already been uploaded. Click Create and enter a filename with a .db extension to create a new SQLite database to be used as the External Data Source. |
| Table name | The name of an SQLite database table to be used as the External Data Source. If the ‘Upload’ or ‘Choose existing’ option is selected, this field should be set to an existing database table. If the ‘Create’ option is selected, this field should be set to the name of the database table to be created. If a new table name is used, it will be created with the appropriate column definitions when the Write External Data Source action is first used to write to the External Data Source. |
SQL Server External Data Sources
Note that SQL Server External Data Sources require the MSOLEDBSQL provider to be installed on the LiveCompare server.
| Field | Description |
|---|---|
| Server name | The name of the SQL Server machine hosting the database to be used as the External Data Source. |
| Port | The SQL Server port to use for the connection. Leave this field blank to use the default SQL Server port (1433). |
| Database name | The name of an existing SQL Server database registered on the SQL Server machine. |
| Table name | The name of an existing or new SQL Server table to be used as the External Data Source. If a new table name is used, it will be created with the appropriate column definitions when the Write External Data Source action is first used to write to the External Data Source. |
| Server Certificate | Select the Trust Certificate checkbox to allow LiveCompare to trust the SQL Server used for the connection without checking for a certificate. |
| Authentication Windows SQL |
Indicates whether a Windows username will be used to sign in to SQL Server (Windows authentication) or whether a separate SQL Server user ID will be used (SQL authentication). |
| Username for SQL login | For SQL Server authentication, a SQL Server username to sign in to the database. |
| Password for SQL login | For SQL Server authentication, the SQL Server password for this username. |
Performance History Data External Data Sources
| Field | Description |
|---|---|
| Select file(s) | Click Browse…, select one or more raw performance history data files download from SAP, and then click Open. In the ‘Choose File to Upload’ dialog, multiple files may be selected using the Ctrl key, and a range of files may be selected using the Shift key. |
| RFC Destination | Select an RFC Destination to be used to classify the names in the performance history data as either transactions (TCOD), programs (PROG) or BSP Applications (WAPA). If LiveCompare is Resource Group-controlled, only the RFC Destinations in the currently selected Resource Group are listed. |
XML External Data Sources
| Field | Description |
|---|---|
| Upload XML file | Click Browse… to browse for a filename. The XML file will be uploaded to the LiveCompare server. |
HANA Database External Data Sources
HANA Database External Data Sources allow RFC Destinations to retrieve data from an associated SAP HANA database, for use when performing an impact analysis for BW objects. To create a HANA Database External Data Source, follow these steps.
-
Install the SAP HANA ODBC client on the LiveCompare server.
-
In LiveCompare, complete the Add External Data Source screen as follows:
| Field | Description |
|---|---|
| Server | The SAP HANA database hostname. |
| Port | The port number of the HANA database server, for example, 30215. |
| Encrypt | Select Yes to turn on encryption, or No to connect without using encryption. |
| Validate SSL Certificate | Select Yes to validate the connection using an SSL certificate, or No to perform validation without using an SSL certificate. |
| Username | The SAP HANA database username to use for the connection. The user account must have the permissions required to access SAP HANA system tables. |
| Password | The SAP HANA database password to use for the connection. |
-
If LiveCompare is Resource Group-controlled, use the Add to Resource Groups section to add the External Data Source to one or more Resource Groups. Click Select All to select all Resource Groups, or Select None to select no Resource Groups. If you have LiveCompare Editor permissions, the External Data Source is always added to the current Resource Group, so the current Resource Group may not be deselected.
- Click Save to save the External Data Source. If an External Data Source of type Performance History Data has been selected, the specified RFC Destination is used to classify each name as either a transaction or program, and the usage counts for each object are aggregated. Objects that can't be classified are stored in the External Data Source in a table named AppStatsNotFound.
When an External Data Source has been created, it appears in the LiveCompare hierarchy underneath the Data > External Data Sources folder.
After a HANA Database External Data Source has been created, it should be associated with a new or existing RFC Destination by selecting the HANA Database External Data Source from the Connection dropdown list.
To access an External Data Source from a workflow, assign it to an External Data Source parameter.