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. For an overview of the warehouse, check out the Warehouse Overview or Warehouse Schemas ERD.

The main article for how to connect to the warehouse is here: https://benchlinghelp.zendesk.com/hc/en-us/articles/9714802961421-Access-your-data-warehouse-Enterprise- but we've found that many of our users are connecting to the warehouse via PowerBI on a Windows machine. We wanted to add a few instructions & tips / tricks to help get those connections working properly.

PowerBI is a bit stubborn when it comes to remote Postgres connections. Most tutorials are expecting Postgres to be locally installed. Here are the steps you need to take:

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 rds-ca-2019-root.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 rds-ca-2019-root.pem from the Downloads folder to the postgresql folder

5. Rename the rds-ca-2019-root.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!