PostgreSQL To Power BI: 3 Best Ways To Connect

on Data Integration, Tutorials • July 3rd, 2020 • Write for Hevo

Introduction

Most BI tools allow connections to several databases and APIs. Microsoft Power BI is a very extensively used BI tool and PostgreSQL is a very popular database. The goal of this article is to outline the common methods used to connect PostgreSQL to Power BI in order to perform analytics and generate reports seamlessly. 

What Is PostgreSQL?

PostgreSQL is known to be one of the most advanced open-source databases. It is robust and very well suited for BI applications. It is scalable and has a great design. This is what makes it so popular in the market. You can download PostgreSQL here.

What Is Power BI?

Microsoft Power BI Desktop is a state-of-the-art Business Intelligence tool built for the data analyst. Using Power BI you can connect to any data source and then transform and visualize the data. You can download Power BI Desktop here.

Hevo, A Simpler Alternative To Integrate Your Data For Analysis

Hevo offers a faster way to move data from databases like PostgreSQL or SaaS applications into your data warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code. In fact, even the maintenance required is minimal.

Check out some of the cool features of Hevo:

  • Completely Automated: The Hevo platform can be set up in just a few minutes and requires minimal maintenance.
  • Real-time Data Transfer: Hevo provides real-time data migration, so you can have analysis-ready data always.
  • 100% Complete & Accurate Data Transfer: Hevo’s robust infrastructure ensures reliable data transfer with zero data loss.
  • Scalable Infrastructure: Hevo has in-built integrations for 100 plus sources that can help you scale your data infrastructure as required.
  • 24/7 Live Support: The Hevo team is available round the clock to extend exceptional support to its customers through chat, email, and support call.
  • Schema Management: Hevo takes away the tedious task of schema management & automatically detects schema of incoming data and maps it to the destination schema.

If you want to see for yourself how intuitive and easy it is to use, you can try it for free here.

How To Connect PostgreSQL To Power BI?

The most straight forward method to connect PostgreSQL to Power BI is to click on ‘Get Data’ on the Home page of Power BI and pick a source. Unfortunately, when you try this, you are likely to encounter an error informing you that the connector will need additional components.

Let us look at some of the most popular methods to handle this issue:

  1. Using Npgsql
  2. Using An ODBC Connection
  3. Through The Configuration Of PostgreSQL And Power BI

 Using Npgsql

Download and install as Administrator the latest version of Npgsql on your computer while enabling GAC Installation. Restart your computer and open your Power BI Desktop tool. Click on ‘Get Data’ and select ‘PostgreSQL database’.

PostgreSQL to Power BI : Get Data

Enter the Server name, Database name, User name, and Password. In the Navigator window, select the table you need and Load it.

PostgreSQL to Power BI : Connecting to the database.

If this does not work for you, try using an ODBC connection.

Using An ODBC Connection

Open Database Connectivity (ODBC) is a standard API for accessing DBMS. (Wikipedia). ODBC has been designed to be independent of databases and operating systems.

How do you connect PostgreSQL to Power BI Desktop using ODBC?

First of all, download and install psqlODBC from the official site.

There are two setups you need to do next.  One from Power BI and another from PostgreSQL. After downloading and installing psqlODBC_x64, open Power BI Desktop and click on ‘Get Data’.

PostgreSQL to Power BI : Get Data

Search for and select ODBC. Click on Connect.

PostgreSQL to Power BI : ODBC

Now when you have to select your Data Source, select None. In the connection string, enter the non-credential properties (Driver, Server, Port, and Database).

This is how the connection string looks like:

Driver={PostgreSQL ANSI(x64)}; Server=localhost; Port=5433; Database= my_database

Note: If you have already entered your credential information in the past, you might want to clear the permissions. To do this, go to Options and settings, select Data source settings, and clear permissions.

After this, on the credential screen, select Database, enter the User name and Password. Now you may select the table you want to work with, in the data structure.

If your database is located on the cloud, you have to configure the remote connection as well. This you have to do by editing the pga_hba.conf file and the postgresql.conf file.

This is how you should edit the pg_hba.conf file:

PostgreSQL to Power BI : pg_hba.conf file

This is how you should edit the postgresql.conf file:

PostgreSQL to Power BI : PostgreSQL.conf file

After editing these files, you have to restart PostgreSQL. From your cloud source, you can also restrict who can access the database using the IP address.

There is another interesting method for Windows Users.

Through The Configuration Of PostgreSQL And Power BI

In order to connect PostgreSQL to Power BI, you need to configure both of them.

Configuring PostgreSQL

You need to first edit the pg_hba.conf file :

sudo nano path/to/your/file/pg_hba.conf

You need to allow remote connections from a host and allow it to all databases. All PostgreSQL users will be allowed and connections from anywhere around the world will be allowed. You can, of course, edit it according to your own requirements limiting the databases and range of your networks.

This is what your pg_hba.conf file should look like:

PostgreSQL to Power BI : pg_hba.conf file

Now you have to edit your postgresql.conf file.

You have to change the line ssl = true to ssl = false. Power BI by default only trusts applications that have the SSL certificate. To get the certificate, you need to change the SSL settings. You can avoid all of that by simply disabling SSL.

sudo nano path/of/your/file/postgresql.conf
ssl = false

Next, you need to restart PostgreSQL in order to let it know about the changes.

You can restart from the command line using this command:

sudo service postgresql restart

Configure Power BI

Open Power BI Desktop. Click on ‘Get data’ and choose the option ‘More’.

PostgreSQL to Power BI : Get Data

In the dialog box, select ‘PostgreSQL database’ under the database option and click on ‘Connect’.

PostgreSQL to Power BI : Select PostgreSQL

In the next dialog box, you can enter the server IP and the database name.

PostgreSQL to Power BI : Connecting to the database

After this, in the final dialog box, you can enter the user name and the password and click on ‘Connect’.

PostgreSQL to Power BI : Connecting to the database

In the ‘Navigator’ dialog, select and load the table you want to work with. Click on ‘OK’ if Power BI warns you about encryption.

Note that this method is for Windows Users.

Conclusion

In this tutorial, you have seen some of the most common ways to connect PostgreSQL to Power BI.

If you are dealing with multiple sources of data, ranging from databases such as PostgreSQL to SaaS applications like Google and Facebook Ads, you might find it difficult to integrate all the disparate data. Hevo is an excellent data integration tool that lets you move data from several such sources to any destination in real-time. If you want to see how it works first hand, please sign-up for a 14-day free trial. You will absolutely love it.

Share your experiences connecting PostgreSQL to Power BI in the comments!

Easily visualize your PostgreSQL data in Power BI