Connect to ODBC as a data source

Process Intelligence allows uploading data from Open Database Connectivity (ODBC) compatible databases such as Oracle, Microsoft SQL Server, and PostgreSQL to the Process Intelligence repository.

If uploading using the ODBC data source fails on Timeline instances deployed on-premises on Linux systems, you need to download and install a patch file. For more information, see Patch installation.

Configure ODBC data source in the repository

To configure the ODBC data source:

  1. Log into Process Intelligence.

  2. Click View and then select Repository.

    The Repository screen displays.

  3. Click the Data Sources tab and select ODBC data sources.
  4. Click Add configuration.

  5. In the ODBC import configuration dialog, complete the following fields:

    1. Name – Enter a unique name that identifies the data source to which you are configuring the connection.

    2. Connection string – The string that is passed to an ODBC driver to initiate the connection to an ODBC compatible database.

      The ODBC connection string must not contain any line breaks.

      The configuration for connecting to ODBC databases is different between SaaS and on-premises environments.

      • Saas environments – Cloud and Kubernetes configurations
      • On-premises instances deployed on Windows Server

      • Postgresql 7.4 and later

        Format: Driver={PostgreSQL UNICODE};Server=<HOSTNAME_OR_IP>;Port=<PORT>;Database=<DATABASE>;Uid=<USERNAME>;Pwd=<PASSWORD>;

        Example: Driver={PostgreSQL UNICODE};Server=myHost;Port=5432;Database=postgres;Uid=myname;Pwd=x;

      • Microsoft SQL Server - all Long Term Support (LTS) releases

        Format: Driver={ODBC Driver 17 for SQL Server};Server=<HOSTNAME_OR_IP>;Database=<DATABASE>;Uid=<USERNAME>;Pwd=<PASSWORD>;

        Example: Driver={ODBC Driver 17 for SQL Server};Server=myHost;Database=mydb;Uid=myname;Pwd=mynewpswrd;

      • Oracle 10 and later

        Format: Driver={Oracle 21 ODBC driver};DBQ=<HOSTNAME_OR_IP>:<PORT>/<DATABASE_SID>;UID=<USERNAME>;PWD=<PASSWORD>;

        Example: Driver={Oracle 21 ODBC driver};DBQ=MyOracle/ORCL;UID=myadmin;PWD=mypswrd;

      1. Install an appropriate ODBC driver for the data source you want to connect to.

        The supported ODBC drivers are listed below. You may use other ODBC drivers, but proper operation of the ODBC data sources option is not guaranteed.

        • SQLite and SQLite3 ODBC Driver
        • PostgreSQL ODBC Driver (psqlODBC)
        • PostgreSQL ANSI
        • PostgreSQL Unicode
        • Microsoft ODBC Driver 17 for SQL Server
        • Oracle

      2. Retrieve the connection string for the driver you used.

        There are no built-in checks or restrictions on connection string formatting. To create a connection string for your ODBC driver, see your ODBC driver manual or check The Connection Strings Reference. To build and test the connection string for your ODBC driver on your Windows computer, you can use the ODBC Data Source Administrator applet in the Control Panel.

    3. Query – Specify a string of SELECT query to the ODBC data source to get data.

  6. Click Save.

Upload data manually

You must add an ODBC configuration before attempting to upload the data manually.

To upload your data manually:

  1. From the navigation menu in the repository, select Upload.

  2. Select External adapter.

    The Import and upload dialog opens.

  3. In the Adapter field, select the required adapter. All configured ODBC data sources will be added to this list.

  4. In the Table name field, select an existing table or enter a new table name.

  5. Click Upload.

    Once the upload is complete, the number of files processed and time taken to process the files display.

  6. Click OK to close the Import and upload dialog.

The ODBC data source is now available for selection in the Tables list in the repository.

Troubleshooting

The following are a few troubleshooting issues that might occur when configuring ODBC data sources.

General error due to connection string containing unsupported characters

A general error may occur if the connection strings contain the following characters:

  • Line breaks can be used, however, connecting using such a string may result in an error.
  • Misplaced whitespace characters.

If used in an ODBC connection string, a login or password must not contain the following characters: [] {}() , ; ? * ! @. These characters are used to either initialize a connection or separate connection values.

Character encoding issues

There are two versions of the PostgreSQL ODBC driver:

  • Unicode – A Unicode enabled driver works well in most cases.
  • ANSI – If your database runs in ANSI mode, it is recommended to use the PostgreSQL ANSI driver instead.