An ODBC Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. Encrypted connections using TLS are supported and are recommended for production deployments. For details see
Secure ODBC Connections.
Tested and supported clients include:
Other clients which may be compatible are listed
below.
The underlying exposure mechanism is an implementation of the
PostgreSQL wire protocol. Each LabKey container (a project or folder) is surfaced to clients as a separate PostgreSQL "database". These "databases" expose the LabKey virtual schema (the same view of the data provided by the Query Schema Browser).
Queries through an ODBC connection respect all of the security settings present on the LabKey Server container. Clients must have the Reader role at least to query the data.
Only read access is supported; data cannot be inserted or updated using the virtual schema over an ODBC connection.
Note that ODBC connections are not supported in cloud hosted server environments, including trial instances of LabKey Server.
ODBC Connection Set Up
By default ODBC connections are disabled; to enable them, follow the instructions below.
- Select > Site > Admin Console. Click Settings. Under Premium Features, click External Analytics Connections.
- On the page Enable External Analytics Connections, place a check mark next to Allow Connections.
- By default the server will listen for client requests on port 5435. If desired, you can change the port number within the range: 1 to 65535.
- Click Save.
Windows: Install PostgreSQL Driver
- On the client machine, install the latest version of the PostgreSQL ODBC driver.
- Downloads for Windows are available at https://www.postgresql.org/ftp/odbc/versions/msi/
- Note that there are 32-bit and 64-bit drivers available. You can install both, or install the version that matches your client tool, not your host machine. For example, if you have a 32-bit version of Excel, then install the 32-bit ODBC driver, even if you have a 64-bit machine.
Windows: Create a Data Source Name (DSN)
On the client machine, create a "data source name" (DSN) to wrap a data container on LabKey Server. Creating a "system" DSN, as shown below, makes it available to various clients. Client tools use the ODBC driver to query this DSN.
- On Windows, open the ODBC Data Source Administrator.
- Click the System DSN tab.
- Click Add....
- Select the PostgreSQL driver you installed above and click Finish.
- Data Source - This is the name used by the client tool.
- Description - This can be any text.
- Database - A LabKey container path, that is, the project or folder you are connecting to. Include a leading slash in the path, for example, "/Home" or "/Home/MyDataFolder".
- SSL Mode - Set to "disable".
- Server - The server you are connecting to, for example, www.labkey.org or localhost.
- Port - This number must match the port enabled on the server. 5435 is the default used by LabKey Server.
- User Name - The user this connection will authenticate against. This user should have at least the Reader role in the LabKey Server container.
- Password - The password for the above user.
- Click Test to ensure the connection is successful.
- Click Save to finish.
Tableau Desktop
To load data into Tableau Desktop:
- In Tableau Desktop, go to Data > New Data Source > More… > Other Databases (ODBC).
- Place a checkmark next to DSN and select your DSN in the dropdown. Click Connect.
- Search for and select the Schema - 'core' is shown in the screenshot below.
- Search for and select the Table - 'Modules' is shown in the screenshot below.
We recommend that you set the Connection to "Extract" instead of "Live". (This helps to avoid the following errors from the ODBC driver: "ODBC escape convert error".)
Excel
To load data into Excel:
- In Excel (Office 365 version), open an empty sheet and click the Data tab
- Select Get Data > From Other Sources > From ODBC. (Note this path may vary, depending on your version of Excel.)
- In the From ODBC popup dialog, select the system Data Source Name (DSN) you created above. Optionally, you can enter a SQL query under Advanced options.
- If you chose not to provide a SQL query, select the table to load using the Navigator dialog. Select the desired table and click Load.
- The data will be selected from the server and loaded into the worksheet.
Controlling Excel Data Loading
To control the SQL SELECT statement used by Excel to get the data, such as adding a WHERE or JOIN clause, double-click the table/query in the
Queries and Connections panel. In the
Power Query Editor, click
Advanced Editor.
To control the refresh behavior, go to
Data tab > Connections > Properties. The
Refresh control panel provides various options, such as refreshing when the sheet is opened.
Note that saving a sheet creates a snapshot of the data locally. Use with caution if you are working with PHI or otherwise sensitive data.
Access
Access can be run in snapshot or dynamic modes. Loading data into Access also provides a path to processing in Visual Basic.
See the Microsoft documentation at
Add an ODBC Data Source
Microsoft SQL Server Reporting Services (SSRS)
SQL Server Reporting Services is used for creating, publishing, and managing reports, and delivering them to the right users in different ways, whether that's viewing them in a web browser, on their mobile device, or via email.
For detailed setup instructions, see
ODBC Data Sources and SQL Server Reporting Service (SSRS).
MATLAB
- In MATLAB, click the Apps tab.
- Open the Database Explorer app. (If you don't see it, install the Database Toolbox.)
- In the Database Explorer, click New Query.
- In the Connect to a Data Source select your DSN and provide a username and password.
- In the popup dialog, select the target Schema. The Catalog (a LabKey container) is determined by the DSN.
- Select a table to generate SQL statements.
Troubleshooting
Error message: "Bad Connection..."
The following error may occur from the ODBC driver. This error has been seen especially with Tableau Desktop when working with date fields.
Bad Connection: Tableau could not connect to the data source.
ODBC escape convert error
<snip>Generated SQL statement is shown here</snip>
Resolution
When connecting Tableau Desktop to the DSN, select "Extract". See
above for a screenshot.
Other Tools
These other external tools have not been extensively tested and are not officially supported, but have been reported to be compatible with LabKey using ODBC connections.
Related Topics
