Complete guide to setting up Power BI connecting to Postgres w/ refresh enabled.

  • Articolo

You may or may not have seen but Microsoft announced a couple new database services, namely Postgres and MySQL. What's unique about these two database services is they work very similar to SQL Azure, in particular there is no VM for you to provision or manage. When you provision the resource you get an endpoint to connect to that allows you to create databases and the content on those databases. I was recently working on a scenario that needed to leverage the new Postgres service from Power BI, and while connecting everything works, it was not as straightforward as I expected.

 

In my endeavors I had to wade through four different sources to piece everything together, so in the interest of saving you time here are the complete steps to getting Power BI Desktop to connect to Postgres plus setting up the gateway service so you can refresh a published model from the Azure Postgres service.

 

As I mentioned, I pieced together a variety of sources to get to the correct steps.  I referenced a series of both community blogs and azure documentation to get this running, those sources are (and what I got from each):

 

Since there are a ton of steps here, I've broken it into a few logical parts. This keeps things better organized and helps you skip over sections that are irrelevant to your scenario. The parts are:

      1. Part 1 - Provision your Azure Postgres Database and install the components for connecting to Postgres
      2. Part 2 - Register the appropriate certificate so SSL connections to Postgres work.
      3. Part 3 - Connect to your Postgres database with Power BI Desktop.
      4. Part 4 - Setup and configure the Power BI Gateway for cloud refresh.

 

 

Part 1 - Provision your Azure Postgres Database and install the components for connecting to Postgres

 

In case you haven't provisioned an Azure Postgres database, here are the basic steps to creating a new instance of the Postgres database service via the Azure Portal.

 

      1. Login to the Azure Portal, and select "Create a resource"
      2. Search the marketplace for "Azure Postgres", and select the top search result. (Note: there are several other marketplace templates for Postgres, but for the purposes of this blog I'm focusing on the Azure Database for PostgreSQL)
      3. Click "Create"
      4. Fill out the form for the new server including the Server name, Resource group, Server admin login name, password, and click "Create". (Note: there are other settings here like the Postgres version and the number of cores, but they aren't important to this scenario).
      5. While your database is being provisioned there are a couple of things you can go ahead and setup.
      6. Install Npgsql 3.1.8 via the MSI (note this is version specific), you can find the installer here: https://github.com/npgsql/npgsql/releases/tag/v3.1.8
      7. By default the postgres service has SSL enabled. You need to download and trust a specific certificate so SSL communication will work. (you could turn off SSL for the server in Azure, but that's cheating).

 

Part 2 - Register the appropriate certificate so SSL connections to Postgres work.

 

There official version of these instructions can be found here: /en-us/azure/postgresql/concepts-ssl-connection-security. These directions are intended to be generic and therefore cover many scenarios.   Since the directions are a bit terse and interleave linux directions with windows directions, I decided to give you an abbreviated step by step specific to Windows (Note: Power BI and the Gateway presently only run on Windows):

      1. Download the encrypted cert from: https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt.
      2. Download and install openssl to decrypt the cert https://slproweb.com/download/Win32OpenSSL_Light-1_1_0h.exe.
      3. Run the command: openssl x509 -inform DER -in BaltimoreCyberTrustRoot.crt -text -out root.crt (Note: depending on where you install openssl and/or where you dowloaded the .crt file to you may need to amend this command to deal with paths.)
      4. Double click on the created root.crt file, you'll be presented the certificate info. Click "Install Certificate…"
      5. Select "Local Machine", and click "Next"
      6. Select "Place all certificates in the following store", and "Browse"
      7. Select "Trusted Root Certification Authorities", and click "Ok"
      8. Click "Next".
      9. Click "Finish".

 

 

Part 3 -   Connect to your Postgres database with Power BI Desktop

 

Now that we've installed all the prerequisites I'm going to walk through all the steps necessary to connect to the database.   Since this blog is largely about Power BI, I'm going to make the wild assumption that you already have Power BI Desktop installed. Once it's installed here are the steps:

 

      1. Let's go to the azure portal for a bit to grab and configure things, login to the portal if you don't still have it open, and navigate to the Postgres resource you created
      2. Take note of the "Server name" and "Server admin login name", you'll need these for the gateway config.       Second, click on "Connection security", and add a firewall rule for the IP address of your machine running Power BI Desktop. The easiest way to do this is with "Add Client IP", then make sure you save your changes.
      3. Now launch Power BI Desktop.
      4. Use the "Get Data" menu to select a data source and select "More…"
      5. Select "Database" and "PostgreSQL Database" and click "Connect"
      6. Enter the server name you made note of in step 1, and the database name. (Note: for my purposes I'm just using the default database postgres as I'm only proving the scenario works).
      7. Enter your username and password, and click "Connect" (Note: the username needs to be in the format username@servername)
      8. In the data source navigator, select the tables you want to include in the model and click "Load".
      9. Check the "Fields" pane to ensure you've got some tables in your data model.
      10. You're done, save a local copy of the file for later publishing, or publish directly to Power BI using the "Publish" button.

 

 

 

Part 4 - Setup and configure the Power BI Gateway for cloud refresh

 

At this point all you have a Power BI model connected to Postgres, but most companies I talk to actually want to publish their models, share them with others, and most importantly do some sort of a scheduled refresh on the data. In order to do this you're going to have to setup the Power BI Gateway somewhere.   This gateway can be installed on a Windows machine ranging from your personal desktop to a server. The most important things are that the machine should be on all the time, and it should have internet connectivity; therefore, installing it on your laptop is typically not a good idea. I chose to provision a Windows VM in my Azure subscription. Once you've decided where you're going to install the gateway here are the steps to get it running:

      1. Go through all the steps in "Part 2" again on the gateway host machine. (Pro Tip: You've already decrypted the certificate with OpenSSL on your Power BI Desktop machine, so you can bypass steps 2 & 3 by copying the root.cer file to the target machine).
      2. Download and install the Power BI Data Gateway from: https://powerbi.microsoft.com/en-us/gateway/ and click "DOWNLOAD GATEWAY"
      3. In the installer, you can accept all the defaults. In particular, make sure you select this option in the installer
      4. Once the install is complete you'll be taken through the configuration steps. First enter the email address you use to login to Power BI and click "Sign In"
      5. Next, select "Register New Gateway" (Note, the other option is useful if you need to migrate a gateway to another machine or are rebuilding a machine).
      6. Now you need to configure your gateway by giving it a unique name so you can find it in the Power BI Portal, and set your recovery password which is necessary if you ever need to move / restore your gateway.       (Note: for more advanced install scenarios you can add your gateway to a gateway cluster enabling high availability for the gateway).
      7. Gateway installation and configuration is complete, you should see a screen like:
      8. Now that the gateway is installed, we need to register the datasource with the gateway. This is done in the Power BI portal.
      9. Login to www.powerbi.com, and on your main screen select manage gateways, you'll find this under the settings gear.
      10. This will take you to the Add Datasource page, select the gateway corresponding to the name you gave the gateway in step 6.
      11. Click the ellipsis (…) and select "ADD DATA SOURCE"
      12. Name your datasource and pick the appropriate type.
      13. Just like we did with Power BI Desktop, lets jump over to the azure portal for a bit to grab and configure things. First, take note of the "Server name" and "Server admin login name", you'll need these for the gateway config. Second, click on "Connection security", and add a firewall rule for the IP address of your gateway server.
      14. Fill out the necessary server and database settings, for my purposes I just used the default database to prove it works.       (Note: Pay close attention to the fact that the admin login is username@servername), and click "Add"
      15. Note, if you get the following error its typically because you entered your password wrong, or you didn't add a firewall rule:

 

Ok, you're all finished. Now when you decided to publish your Power BI Desktop file to the Power BI portal, you'll be able to manually refresh or setup a scheduled refresh on the data. At refresh time Power BI will use the gateway server to connect to the database and issue queries using the correct driver and feed the results back to the Power BI Service.