Connecting PostgreSQL to Power BI

Connecting PostgreSQL to Power BI will open up powerful possibilities for analyzing and exploring your business data. According to a survey by Stackoverflow, PostgreSQL is one of the industry leaders in terms of the most preferred databases, with 45.55% of the votes. On the other hand, Power BI has over 5 million subscribers globally. Both technologies are crucial to most tech stacks; therefore, learning how to connect PostgreSQL with Power BI becomes vital.

This blog provides a step-by-step guide for connecting PostgreSQL to Power BI, allowing users to leverage these tools for comprehensive data analysis and business reporting.

What is PostgreSQL?

PostgreSQL is an open-source object-relational database system. It uses and extends the SQL language and has many features, such as: 

• Tablespaces
• Asynchronous replication
• Nested transactions
• Online/hot backups
• Fine-grained query planner/optimizer

These features are designed to assist developers in building applications and administrators in protecting data integrity and building fault-tolerant environments.

What is Power BI?

Power BI is a suite of software services, applications, and connections that work together to turn disparate data from different data sources into coherent, visually immersive, and interactive insights.

It doesn’t matter whether your data resides as an Excel spreadsheet or a mixture of cloud-based and on-premises hybrid data warehouses. Power BI lets you quickly access your data sources, find what’s important, and share through visualizations.

How To Connect PostgreSQL To Power BI?

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

Let us look at some of the most popular methods to connect PostgreSQL database to Power BI.

• Method 1: Using Npgsqs to connect PostgreSQL to Power BI
• Method 2: Using An ODBC Connection for PostgreSQL to Power BI
• Method 3: Through The Configuration Of PostgreSQL And Power BI

Method 1: Using Npgsqs to connect PostgreSQL to Power BI

Step 1: Download and install 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’.

Step 2: Enter the Database information

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

Note: You might get the following message if your connection is not encrypted.

For secure connectivity, enable database encryption before connecting. Using unencrypted connections risks exposing sensitive data. Review the encryption configuration guide to implement security measures properly. If you understand and accept the risks, select OK to connect without encryption.

Step 3: Select the Database Information

To import data, use Navigator to locate the desired database information. Select Load to directly import the data or Transform Data to further process the data in Power Query Editor before loading.

You have now successfully connected PostgreSQL to Power BI.

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

Method 2: Using An ODBC Connection for PostgreSQL to Power BI

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

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

Step 1: Download and Install

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’.

Step 2: Connect ODBC

Search for and select ODBC. Click on Connect.

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.

Step 3: Select the Database

After this, on the credential screen, select Database and enter the Username 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:

This is how you should edit the 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.

Try out similar migrations:

Method 3: 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 first to 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 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:

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 to inform it 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’.
• In the dialog box, select ‘PostgreSQL database’ under the database option and click ‘Connect’.
• In the next dialog box, you can enter the server IP and the database name.
• After this, in the final dialog box, you can enter the user name and the password and click on ‘Connect’.
• 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.

Why connect PostgreSQL to Power BI?

Connecting PostgreSQL to your Power BI offers various benefits that can empower your business to make informed decisions. Some key benefits include:

• Connecting your PostgreSQL database to PowerBI helps bring all your data to a single place and perform holistic analysis. 
• Power BI provides extraordinary visualization features that turn heavy data from PostgreSQL into simplified reports, graphs, and maps. These interactive visualizations make business reporting easier.
• PostgreSQL’s ability to handle large volumes of data, combined with Power BI’s optimized performance, guarantees that your analytical solutions can scale up to meet business growth.

Additional Resources for PostgreSQL Integrations and Migrations

• How to load data from postgresql to biquery
• Postgresql on Google Cloud Sql to Bigquery
• Heroku for Postgresql to Bigquery
• Explore more on Best Power BI Dashboard Examples

Conclusion

• In this tutorial, you have seen some of the most common ways to connect PostgreSQL to Power BI.
• The seamless compatibility between PostgreSQL Power BI services facilitates real-time analytics and comprehensive reporting.
• Suppose you are dealing with multiple data sources, ranging from databases such as PostgreSQL to SaaS applications like Google and Facebook Ads. In that case, integrating all the disparate data might be difficult.
• Extracting complex data from diverse data sources can be challenging, and this is where Hevo comes in handy! Sign up and enjoy the 14-day free trial to know more!

Frequently Asked Questions on Connecting PostgreSQL to Power BI