Power BI Database Connection

Power BI is a powerful graphing tool from Microsoft. It is possible to connect to the AQA database via Power BI to support charting data in a variety of ways.

To do this, you must have

• Power BI installed on your computer. Microsoft provides different versions, including a community version that can be used for free.
• A database account. This is different from your AQA account. To obtain one, contact the AQA system administrator. These accounts have full access to the database, but are read-only to prevent accidental modifications.

This guide includes a simple SQL SELECT query to verify that the connection is working. More practical queries can be found on the Database Queries guide.

Note that this guide has only been tested for 64 bit desktop machines.

There major steps to connecting are:

• Install the PostgreSQL driver
• Install the AWS certificate
• Create a PostgreSQL data source
• Set up data source in Power BI

Install the PostgreSQL driver

Go to the Postgres driver page.

Download the latest driver which will be at the bottom of the page. Download the 64 bit version. In the past, users have downloaded psqlodbc_13_01_0000-x64.zip . It can also be downloaded from this site.

Unzip it, and run the MSI file.

Install the AWS certificate

Communications to the database are done securely over an SSL connection. This requires that Power BI know and trust the AWS certificate. The certificate (a PEM file) can be downloaded from this site or directly from AWS. AWS provides a full list and more information about their certificates.

Click the Microsoft Windows Start button and type MMC.

Click File → Add/Remove Snap-in...

Click Certificates then Add >

Click Computer Account then Next >

Click Local Computer... then Finish

Click OK and you should now see

Expand Certificates and click Trusted Root Certification Authorities then right-click All Tasks > Import...

Which should bring up the Certificate Import Wizard. Click Next.

Browse to the saved us-west-2-bundle.pem file’s folder. Click All Files(*.*) in the pulldown in the lower right corner.

Select Place all certificates in the following store

Click Next, then Finish.

Create a PostgreSQL data source

Click the Microsoft Windows Start button and type ODBC. Choose ODBC Data Sources (64-bit).

Allow it to make changes to your computer.

Select the User DSN tab and then click Add...

Select PostgreSQL Unicode(x64) then click Finish.

Set up data source in Power BI

Start or restart Power BI.

Click Get Data (top left of ribbon) → ODBC

Click PostgreSQL35W

Enter database user name and password.

Select Advanced Options and fill in the fields as shown below by cut-and-paste with:

Server=aqa.cek8wjwn06iu.us-west-2.rds.amazonaws.com;Database=AQA;

SELECT "top_cu", "bottom_cu", "left_cu", "right_cu", "center_cu" FROM public."symmetryAndFlatness" LIMIT 10;

Click OK, then enter your database user name and password:

Click Connect, and the data should be visible: