Creating 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 do not 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 privileges, 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 ‘No’ to create an External Data Source that is accessible to LiveCompare only. 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. |
| Type: | Select the type of External Data Source to create, then complete the remaining screen fields as described in the sections below. |
| Add to Projects: | If LiveCompare is project-controlled, deselect the appropriate check boxes to exclude the External Data Source from one or more projects. If you have LiveCompare Editor privileges, the check box for your current project may not be deselected. If you have LiveCompare Administrator privileges, an External Data Source will not be accessible unless it is assigned to at least one project. |
For Access databases:
| 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 Data Source action is first used to write to the External Data Source. |
| User ID: | If necessary, enter the User ID required to log in to the Access database. |
| Password: | The password for this User ID. |
For CSV files:
| Field | Description |
|---|---|
| Upload CSV file: | Click ‘Browse…’ to browse for a filename. The CSV file will be uploaded to the LiveCompare server. |
Alternatively, click ‘Advanced’ to specify a custom connection string that will be used to access the CSV file.
For 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. |
For DB2 databases:
| 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 Data Source action is first used to write to the External Data Source. |
| User name: | The username that will be used to log in to the database. |
| Password: | The password for this username. |
For Excel spreadsheets:
| 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. |
For SQLite databases:
| 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 Data Source action is first used to write to the External Data Source. |
For SQL Server databases:
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 Data Source action is first used to write to the External Data Source. |
| Authentication: Windows SQL |
Indicates whether a Windows username will be used to log 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 log in to the database. |
| Password for SQL login: | For SQL Server authentication, the SQL Server password for this username. |
For Performance History Data External Data Sources
| Field | Description |
|---|---|
| Select files(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 project-controlled, only the RFC Destinations in the currently selected project are listed. |
For XML files:
| Field | Description |
|---|---|
| Upload XML file: | Click ‘Browse…’ to browse for a filename. The XML file will be uploaded to the LiveCompare server. |
Alternatively, click ‘Advanced’ to specify a custom connection string that will be used to access the spreadsheet, and the name of the worksheet to be used for the External Data Source.
-
If LiveCompare is project-controlled, use the ‘Add to Projects’ section to add the External Data Source to one or more projects. Click ‘Select All’ to select all projects, or ‘Select None’ to select no projects. If you have LiveCompare Editor privileges, the External Data Source is always added to the current project, so the current project 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 cannot 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.
To access an External Data Source from a workflow, assign it to an External Data Source parameter.