There is an elephant in the room, or at least a logotype with an elephant. The product is called PostgreSQL and is a capable, open source, relational database engine. More info can be found here.

To tame this beast, you can find several ways of hosting it, AWS and Azure both have offerings and you can also choose different packages on the Azure Marketplace for hosting it on its own server. Personally, I always go for the PaaS version of anything.

In this scenario a colleague of mine used the PaaS version, but hosted on AWS, and we needed to connect PowerBI to it, which means using the on premise data gateway, which is a bit of a personal favorite of mine. The setup might seem to have a bit too many moving parts, but the requirements where clear: make the data in this database available to PowerBI.

The setup

From left to right, PowerBi connects to the on premise data gateway (OPGW) server using HTTPs. The gateway server is hosted in Azure and contains the data gateway. The data gateway connects to the PostgreSQL database service on Amazon. The OPGW server is allowed access by configuring the local service firewall and by sending the correct credentials.

The OPGW needs to be updated with binaries to enable connectivity to any PostgreSQL database. It is supported out of the box, it just needs the right binaries to work. Luckily, they are available on GitHub.

Since we are using the OPGW to expose the data to PowerBI, the same OPGW can be used from Logic Apps to access the same database server.

Note that the OPGW server could be placed in Amazon as well, we just didn’t want to.

The steps

Make sure to read ALL these steps before starting. There are some things which might take time and needs to be planned.

Besides the obvious ones of getting a PostgreSQL database, and an Azure VM the steps where as follows:

1. Install the OPGW on the server.
2. Download the binaries for enabling connectivity with PostgreSQL from GitHub. At the time of writing the current version is 4.0.3
3. Install the binaries and restart the OPGW service.
4. Register the OPGW in the Azure subscription.
5. Get the settings for the PostgreSQL server from Amazon. The most important setting is called EndPoint and should look like: yourservername.randomkey.datacenterlocation.rds.amazonaws.com
6. The OPGW does not allow for non encrypted connectivity, like other tools like the popular PostgreSQL administration tool pgAdmin. Since you must encrypt you have to harden your Amazon database using this guide.
7. You should now be able to connect to your database straight from PowerBI. Make sure you use the right credentials, and data gateway.

Debugging

If you cannot connect you can always press that little button in PowerBI called “Test all connections”. That usually gives you good information. Here are some possible mistakes:

1. You have submitted the wrong credentials.
2. The firewall is not configured in Amazon.
3. The OPGW server is not running.

The error message from PowerBi is usually very helpful and to get more information, try to install pgAdmin on your OPGW server to make sure it can connect to the Amazon database.

Making your design

To make something publishable in PowerBI you have to create it PowerBI Desktop. Here, you can connect to the PostgreSQL database directly without any OPGW servers. These are the steps:

1. Download and install the binaries from GitHub (steps 2 and 3 above).
2. Create a systemwide DSN for your Amazon database, using the endpoint and credentials.
3. Create a new datasource in PowerBI and point to the new DSN.

Happy designing.