Connecting PostgreSQL to Power BI Using 2 Easy Methods

Connecting PostgreSQL to Power BI opens up extraordinary opportunities and insights for analyzing and exploring your business data. According to a survey conducted by Stack Overflow, PostgreSQL is the industry leader in terms of the most preferred databases, receiving 45.55% of the votes. On the contrary, Power BI boasts over 5 million subscribers worldwide. Both tools are vital to most tech stacks; thus, learning how to integrate PostgreSQL with Power BI is crucial.

In this blog, we have provided you with a step-by-step guide for integrating your PostgreSQL data with Power BI, allowing you to leverage these tools for comprehensive data analysis and business reporting.

What is PostgreSQL?

PostgreSQL is a popular object-relational database management system that provides your organisation with enterprise-grade features with a strong focus on extensibility.It is open-source, fully ACID-compliant, and supports foreign keys, joins, and other features in multiple languages. It is available in cloud-based deployments by most major cloud providers

What is Power BI?

Power BI is a suite of software services, applications, and connections that work together to turn heterogeneous data from different data sources into coherent, visually immersive dashboards, providing you with informative business reports with interactive insights. It doesn’t matter whether your data resides in an Excel spreadsheet or a hybrid of cloud-based and on-premises data warehouses. Power BI lets you quickly access your data sources, find necessary data, and share through infographic visualizations.

How To Connect PostgreSQL To Power BI?

The most straightforward method of connecting PostgreSQL to Power BI is to click “Get Data” on the Power BI home page and select a source. Unfortunately, when you attempt this, you will likely encounter an error informing you that the connector requires additional components. But, don’t worry, in this blog we are going to show you 2 easy methods on how you can connect your PostgreSQL to Power BI.

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

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

Method 1: Through The Configuration Of PostgreSQL And Power BI

You need to configure both to connect PostgreSQL to Power BI.

Step 1: Configuring PostgreSQL

You need to edit the pg_hba.conf file first:

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

You need to allow remote connections from the host and allow them 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 need to edit your postgresql.conf file.

You need to change the line’ ssl = true' to 'ssl = false'. By default, Power BI only trusts applications with SSL certificates. To obtain the certificate, you must update 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

Step 2: Configure Power BI

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

• In the dialog box, look for ‘PostgreSQL database’ under the database option and click ‘Connect.’
• In the next dialog box, enter the server IP address and the database name.

• After this, in the final dialog box, enter the username and password, and click ‘Connect.’

• In the ‘Navigator’ dialog, choose and load the desired table. If Power BI notifies you about encryption, click ‘OK’.

Note: This method is only for Windows Users.

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:

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 the 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 hosted on the cloud, you’ll also need to configure remote access. This involves updating the pg_hba.conf and postgresql.conf files to allow external connections.

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. You can also restrict who can access the database using the IP address from your cloud source.

Try out similar migrations:

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 Power BI helps bring all your data together in a single place and enables holistic analysis. 
• Power BI offers exceptional visualization features that transform complex data from PostgreSQL into intuitive 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, enables your analytical solutions to scale up to meet your targeted business growth.

Additional Resources for PostgreSQL Integrations and Migrations

• How to load data from PostgreSQL to BigQuery
• Postgresql on Google Cloud SQL to Bigquery
• Heroku for Postgresql to Bigquery
• Explore more on Best Power BI Dashboard Examples

Conclusion

In this blog, we have highlighted some of the most common methods for connecting PostgreSQL to Power BI. The seamless compatibility between PostgreSQL and Power BI services enables your organisation with real-time analytics and comprehensive reporting. Let’s assume you are dealing with multiple data sources, ranging from databases like PostgreSQL to SaaS applications such as Google Ads and Facebook Ads. In that case, integrating all the disparate data might be complex.

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