An ODBC Connection exposes the LabKey schema and queries as a data source to external clients for analysis and reporting. Tested and supported clients include:
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.
The underlying exposure mechanism is an implementation of the PostgreSQL ODBC 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).
Only read access is supported; data cannot be inserted or updated using the virtual schema over an ODBC connection.
ODBC Connection Set Up
By default ODBC connections are disabled; to enable them, follow the instructions below.
- Navigate to > Site > Admin Console. Click Admin Console Links. 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" dns, 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.
- 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 "require" if the target server uses TSL.
- Server - The server you are connecting to, for example, www.labkey.org or localhost.
- User Name - The user this connection will authenticate against. This user should have at least the Reader role in the LabKey Server container.
- Description - This can be any text.
- Password - The password for the above user.
- Port - This number must match the port enabled on the server. 5435 is the default used by LabKey Server.
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 DNS 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.
To load data into Excel:
- In Excel, open an empty sheet and click the Data tab
- Select From Other Sources > From Data Connection Wizard.
- In the Data Connection Wizard, select ODBC DSN and click Next.
- Select your data source and click Next.
- Select a table and click Next.
- Enter a description, if desired, and click Finish.
- On the Import Data dialog, click OK.
- 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, go to Data tab > Connections > Properties > Definition tab
. The SQL shown in the Command text pane is editable.
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 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)