PostgreSQL To Power BI: 3 Best Ways To Connect
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.
Table of Contents
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; SaaS applications and 100+ data sources into your data warehouse to be visualized in a BI tool. Hevo is fully automated and hence does not require you to code.
Get Started with Hevo for FreeCheck 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.
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:
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’.
Enter the Server name, Database name, User name, and Password. In the Navigator window, select the table you need and Load it.
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’.
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_databaseNote: 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:
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.
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.confYou 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:
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 = falseNext, 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 restartConfigure 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 on ‘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.
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. Extracting complex data from a diverse set of data sources can be a challenging task and this is where Hevo saves the day!
Hevo, a No-code Data Pipeline helps you transfer data from a source of your choice in a fully automated and secure manner without having to write the code repeatedly. Hevo, with its secure integrations with 100+ sources & BI tools, allows you to export, load, transform, & enrich your data & make it analysis-ready in a jiffy.
Want to take Hevo for a spin? Sign Up for a 14-day free trial and experience the feature-rich Hevo suite first hand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.
Share your experiences connecting PostgreSQL to Power BI in the comments!
