An ODBC (Open Database Connectivity) Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. This topic outlines using an ODBC connection with those tools:
Overview
Step 1: Before you can set up any of the tools covered in this topic, an administrator must have configured LabKey Server to accept ODBC connections as covered in this topic:
Step 2: Your local client machine must also have been setup following the directions in this topic, written for a Windows client. A similar process will need to be followed for other client machines
Step 3: Configure the external tool to use that data source, as described in this topic for several common tools. Other tools offering ODBC support may also be able to connect using similar steps.
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 at least the Reader role to access/query the data. Only read access is supported; data cannot be inserted or updated using the virtual schema over an ODBC connection.
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.
- The Database field in the data source configuration should contain the container path to the project and folder on the LabKey Server. Shown below, the project named "Testing".
- 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, 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; shown below it is named "MyLabKeyData". This DSN includes both the path to the intended container and credential to use.
- You can optionally click Advanced Options and enter a SQL query now.
- Click OK/Next.
- Note that if you are asked to reenter your credentials when you click Next, you can select the Windows tab to have the option to Use my current credentials (i.e. those assigned in the DSN).
- Click Connect to continue to the Navigator dialog where you can select a table to open or directly see the results of the query you specified.
- 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.
To use SSRS, you must
install both the 32-bit and 64-bit ODBC drivers on your Windows machine. Visual Studio and Report Builder are 32 bit and recent versions of SSRS and MS SQL Server are 64 bit.
Important: give both the 32-bit and 64-bit ODBC data sources the same name.
Setting up both the Reporting Services and Visual Studio are covered in this topic:
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.
JMP
JMP is statistical analysis software with a flexible visual interface. You configure JMP to use existing local Data Sources on your machine, and can also define a new DSN, perhaps with a different container path, directly from within JMP.
- Install and open JMP.
- From the File menu, choose Database (updated), then Open Table...
- Click New Connection...
- Switch to the Machine Data Source tab and choose the Data Source Name to use.
- You could also define a new one from here by clicking New...
- Click OK.
- On the next screen, confirm the connection is to the expected "Database" (container path on LabKey Server). You can update the credential here if desired.
- Click OK.
- You can now select a schema and table, then click Open Table. The table will open into a JMP data table.
- Or instead of opening a table directly, click Advanced... and construct a query.
PowerBI
Connecting from a desktop installation of PowerBI to data managed by LabKey is supported.
- When you open PowerBI, click Get Data.
- Choose Get Data from Another Source.
- In the popup, click Other, then choose ODBC.
- Click Connect.
- Select the desired Data source name (DSN) and click OK.
- Note that if you are asked to reenter your credentials when you click Next, you can select the Windows tab to have the option to Use my current credentials (i.e. those assigned in the DSN). This setting will be saved for future uses of this DSN in PowerBI.
- Select the desired data using the Navigator and Load for your analysis.
Note that server-based installations of PowerBI, such as via AWS, may experience timeouts and inconsistent behavior due to gateway interactions. Only the desktop version of PowerBI is considered supported.
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.
Troubleshoot Common Errors
If your ODBC connection and external tool are not working as expected, return to
the setup instructions to confirm proper configuration. In particular, you may need to update the Postgres ODBC driver to the latest version and confirm that it is compatible with the tool you are using.
| Error Messages (may vary by tool) | Possible cause(s) and solutions |
|---|
| ERROR: database path … could not be resolved to an existing container | Check that the path ("Database" in your Data Source Name configuration) exists, and that your credential has access to it.
Confirm that there are no leading or trailing spaces in the path in your DSN configuration. |
| Password authentication failed | Check that the password is correct.
This error also occurs when your DSN configuration does not include a port number. |
| Connection refused Is the server running on host "hostname.com" and accepting TCP/IP connections on port 5435? | Ensure that the LabKey Server has enabled ODBC connections, that you have the right host name and port, and that firewalls or other network configurations are not blocking TCP network traffic. |
| ODBC: ERROR [IM014] The specified DSN contains an architecture mismatch between the driver (32-bit) and Application (64-bit) | Either a driver of the wrong architecture is in use, or no driver is installed on the local machine. |
| DataSource Error: ODBC: ERROR [HY000] Error while executing the query | Check the server logs; this error may indicate a query with a syntax error or other problem further described in the log. You may also see this error if you need to update the driver. |
| SSL error: certificate verify failed | Your client tool is attempting to validate the server's certificate and does not trust it.
Try the "require" option for the SSL Mode, and ensure that the server's certificate has been added to your root.crt file. |
| SSL error: unsupported protocol | The server and client failed to negotiate a TLS protocol.
Ask your server admin to check the protocol and ciphers setting, and validate that the configured keystore is valid.
Or switch the LabKey Server ODBC configuration to use the self-signed certificate option. |
| Failure to Decrypt Credentials. The data source credentials could not be decrypted. Please restart the program. If you continue to see this message try resetting permissions. | This error (or similar) may be raised after a Windows upgrade or other machine maintenance. Try reinstalling the ODBC driver and redefining your DSN. |
Debug with Loggers
To get additional detail about ODBC connections, you can temporarily turn on
DEBUG logging for the following loggers:
- org.labkey.connectors.postgresql.SocketListener : listener for incoming ODBC connections. This logger will record new connections and the port accessed (whether or not said connections are actually successful).
- org.labkey.connectors.postgresql.SocketConnection : See more detail about packets, protocols, and queries. This logger will record credential authentication issues, "Database" path accessibility issues, and other details that may point to details to correct.
For example, if you use a "Database" path that exists, but includes a trailing space, the connection will fail, but the client error reported may collapse such white space. Using debug logging you can see the issue more clearly (in the quoted string):
DEBUG SocketListener 2022-02-09T08:45:36,304 PostgreSQL Listener : New connection for Postgres socket 5435
DEBUG SocketConnection 2022-02-09T08:45:36,324 Postgres wire protocol 4 : First packet protocol version: 1234.5679
DEBUG SocketConnection 2022-02-09T08:45:36,327 Postgres wire protocol 4 : Protocols set in server config - TLSv1.2
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : First packet protocol version: 3.0
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : Properties: {database=/Tutorials/HIV Study , user=[username]}
DEBUG SocketConnection 2022-02-09T08:45:36,351 Postgres wire protocol 4 : Sending error to client: database path "/Tutorials/HIV Study " could not be resolved to an existing container
Tableau, Date Fields, and Escape Convert Errors
If you are working with date fields in Tableau Desktop and see an error like
Error message: "Bad Connection..." Bad Connection: Tableau could not connect to the data source.
ODBC escape convert error
<snip>Generated SQL statement is shown here</snip>
Edit the DSN you are using for connecting Tableau Desktop and confirm you selected "Extract". See
above for a screenshot.
Related Topics
