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:
Tested and supported clients include:
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:
Your local Windows client machine must also have been setup following the directions in this topic:
- Windows: Configure ODBC Access: Set up the host machine to use the ODBC connection, including installation of the TLS access certificate for each user.
Overview
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.
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, 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.
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.
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
