How to connect PowerBI to the Benchling warehouse

The Benchling Warehouse is a database solution that tracks and allows you to view assay data, registry entities, and sample data using SQL queries. It can also connect to most third-party tools. For an overview of the warehouse, check out the Warehouse Overview or Warehouse Schemas ERD. To connect to the warehouse, visit Access your data warehouse.

PowerBI can be tricky with remote Postgres connections. Because most users are connecting to the warehouse via PowerBI using Windows, below are steps to take for installing Postgres locally on a Windows device.

Install Postgres

Install Postgres on your Windows machine. This will give you the correct pg_hba.conf and other system files that will allow you to connect to remote Postgres. You can install Postgres on Windows using https://www.postgresql.org/download/windows/. We recommend Postgres 13.

Note that you want to install Postgres itself, not necessarily the postgres odbc driver or command line tools (though those are useful for other reasons, they are not needed for PowerBI).

Configure pg_hba.conf

Once Postgres is installed you should see a Postgres folder within C:/Program Files. Open C:\Program Files\PostgreSQL\13\data\pg_hba.conf in a text editor.

Add an additional line in pg_hba.conf like so:

#Added for Benchling warehouse
hostssl     all         all             all                     md5

Set up the %APPDATA%\postgresql\root.crt file:

1. Download the CA certificate file to your Downloads folder and open that folder. You should see a new file named named global-bundle.pem

2. Open a second windows file explorer window (shortcut: Ctrl+N or go File > New Window) and type %APPDATA% in its address bar

3. If you see a folder called postgresql here, great! But if not, create a new folder named exactly that. Open this folder.

   1. If you can't see or make this folder, contact your IT team

4. Drag and drop the downloaded global-bundle.pem from the Downloads folder to the postgresql folder

5. Rename the global-bundle.pem file to root.crt to set it as the default (recommended)

Install the driver

Download and install the ODBC driver.

Configure an ODBC connection in PowerBI

You should now open up PowerBI and set up a new data connection.

From here, you can find ODBC.

Configuring the ODBC connection

This configuration can be very tricky - be sure to paste in exactly the correct values, otherwise it will not work.

In particular, you should be setting the driver, server, port, database, and sslmode in the connection string.

Driver={PostgreSQL ANSI}; Server=postgres-warehouse.*your_tenant_name*.benchling.com; Port=5432; Database=warehouse; sslmode=verify-ca

Be sure to replace *your_tenant_name* with the correct value. Lastly, you'll be asked to fill in your username and password. These can be retrieved from the credential creation page in your Benchling settings.

From there, you should be able to select the tables to load, and start your analysis!