Introduction
Power BI supports connectivity to a wide range of data sources. This includes files, relational databases, cloud platforms, web services and online applications. In this article, we will focus specifically on connecting Power BI to PostgreSQL database. This can be done either locally or using the cloud.
A Database is is a highly organized, centralized digital system designed to store, manage, secure, and instantly retrieve massive amounts of structured data.
A database is managed by a software engine as known as the database management system (DBMS), like SQL Server or PostgreSQL. When Power BI asks for data, this engine does the heavy lifting: filtering, aggregating, and sorting millions of rows in milliseconds before sending just the necessary results back to Power BI.
We will explore two scenarios:
- Connecting Power BI to a postgreSQL database hosted locally.
- Connecting Power BI to a cloud-hosted postgreSQL database using Aiven with SSL configuraion
Before proceeding, it will be important to compare the two scenarios:
| Attribute | Local PostgreSQL | Cloud-Hosted PostgreSQL |
|---|---|---|
| Primary Location | Host machine (localhost or local IP) | Public cloud server instance |
| Security Setup | Minimal setup; generally unencrypted over local networks | Strict Aiven Console CA certificate download and SSL configuration (sslmode=require) |
| Accessibility | Limited to the local device or a restricted local network | Accessible anywhere over the internet |
| Maintenance | User-managed (manual updates, scaling, backups) | Fully managed by Aiven (automated patches, easy hardware scaling) |
Connecting Power BI to a local SQL database
A local database is a database server installed and run entirely on your own local computer rather than a remote cloud or external server.
Process 1: Connecting a new localhost database in DBeaver.
Step 1
To get started with the process, we need to download all the tools that we need for the process.
- The postgreSQL from our browser. Clink this link to download from the official site, PostgreSQL download link. We then install it to our computer.
- DBeaver which is a free, open-source database management tool recommended for personal projects. Used to manage and explore SQL databases like MySQL, MariaDB, PostgreSQL, SQLite, Apache Family, and more. We will download and install it in our local machine.
Step 2
We then open the DBeaver and create a new local database connection with PostgreSQL Database.
When you open the debeaver, a the Home screen will open as shown below will open:
To create a new database connection, we will press the CTRL + SHIFT + N open or alternatively we can click the new connection wizard button 
in the toolbar. This will open a new window as shown below.
Step 3
We will then click on the postgreSQL icon and it will take us to a new window to configure the connection. Since we are using localhost, we will not be required to change the configurations. We only need to input our password which we had set while installing our postgreSQL database.
Step 4
After inputing our password, we will be required to test our connection by pressing the test connection button in the bottom left conner of the window pane. If the connection is sucessful, we will the press the finish button in the bottom right conner to complete the process.
Step 5
In the home screen of DBeaver, a section will appear that will show our new connection as shown below.
Process 2: Importing data to our database
After confirming that we already have our database connection working in DBeaver, we will need to import data to our database.
This is an important step that we will need in the later stages.
To import data to DBeaver, on the left side expand on databases > defaultdb > schemas > either create a new schema or use the public schema.
We will first create a databse schema where now we will be able to put our data.
To create a schema we will use the sql command:
create schema schemaName;
Inside our schema, we will import our data from the browser.
Right click on your preffered schema and click import data.A window will po up as shown below.
Go to -> Input file(s), Browse and select your data
After selecting the file to be imported, you will click next until the end where you you will click proceed. This will complete the process of importation. Congratulations, you will now have completed the process of data importation. Your data will appear as shown below;
Process 3: Getting data in power BI
We will now open our Power BI desktop app, we will click on the blank remote to work with it.
Expand the view in the Get data section as shown below.
Click on more... to expand our options to open a dialog box.
In the dialog box, search for PostgreSQL database under the Database category and click Connect.
After clicking connect, a dialog box will appear that will require our server and database name. We get this information from the time we connected our PostgreSQL database in DBeaver.
Server: localhost:5432 / 127.0.0.1 : 5432 .
(Note: 5432 is our port number.)
Database: postgres
After imputing the values, we proceed by clicking the ok buttton
A new dialog box will appear that requires our User name and password.
User name: postgres
Password: **** (requires the password you set while installing PostgreSql.)
Proceed by clicking the button connect.
This wil take us to a new window where we can access all the tables we had already imported in DBeaver. Choose the tables you want to work on. if you need to clean you can using the transform button which will direct you into power query where you will be able to clean your data. Otherwise, you can load the tables directly so you can start working on them.
Congratulations you have successfully connected Power BI to your localhost postgreSQL database!
Let's now look into how you can connect to a cloud based
Connecting Power BI to a cloud-hosted postgreSQL database using Aiven with SSL configuraion
STEP 1: Setting up our aiven account.
What is Aiven.
Aiven is a fully managed cloud data platform that provides open-source technologies, such as PostgreSQL and Apache Kafka, as a service. It automates complex infrastructure tasks like setup, security patching, backups, and scaling so developers can focus strictly on building applications. The platform allows you to deploy these managed databases seamlessly across major cloud environments, including AWS, Google Cloud, and Microsoft Azure. By offering an accessible free tier, it makes it easy for developers to launch and manage a PostgreSQL database globally with minimal effort.
To setup our aiven account,we will need to go to our browser an type aiven.io
When aiven.io opens, click on get building to proceed to set up our aiven account.
You will need to sign up into your account and then you login to your account. You will be taken to a window that will require you to create a service.
Choose PostgreSQL as your service and click the create service buttom in the bottom right of the window.
You will be taken into the projects tabs where we can see our service details. This details are important to help us later when we open Power BI.
Note: Make sure the services builds until you see it's status changes to running.
STEP 2: Connecting our database in DBeaver.
To establish a cloud database connection in dbeaver, we will start by opening dbeaver console.
Using the new connection button or the shortcut SHIFT + CTRL + N to establish a open a dialog window.
In the dialog box, choose postgreSQL as our database and click next.
Next, we configure the database based on the details we generated from the service we created in aiven.io
Host: according to the service you created in aiven.
Port: according to the service you created in aiven.
Database: defaultdb
Username: avnadmin
Password: according to the service you created in aiven.
Test the connection by clicking on the button Test connection in the bottom left corner then click finish.
After finishing the action, you will be able to see a new connection in the General tab under connections.
Step 43: CA cerificate download and Importation into our Trusted root crtificates
The proceed without doing this step we will generate this error.
We need the Aiven PostgreSQL Service Certificate because Aiven enforces strict, mandatory SSL/TLS encryption for all database connections by default, and your computer needs to trust Aiven's identity to establish a secure link.
Therefore we will need to download and import the CA certificate into our trusted root certifications.
To proceed we will download the CA certificate in aiven.io.
After dowloading the CA certificate, we will need to import the certifictae to our managed user certificates.
Open the managed user certificate control panel in your computer, and under the Trusted root certification, right click on certificates , under all tasks click on import, click next then browse the VA certificte you downloaded and proceed until you click the finish button.
To confirm if the process is successful, a success message box will appear.
STEP 4: Connecting PowerBI to our aiven PostgreSQL database.
We will now open our Power BI Desktop App, we will click on the blank report to work with it.
Expand the view in the **Get data **section as shown below.
Click on more... to expand our options to open a dialog box.
In the dialog box, search for PostgreSQL database under the Database category and click Connect.
A dialog box will appear that will require us to input the server and Database box with values that we obtain while creating the service in aiven.io.
Server: Host name : port number (Depending on your service)
Database: defaultdb
Press ok to proceed.
A new dialog box will appear that will require our username and password. Refer to our service conection details in aiven.ai.
Username: avnadmin
Password: (as provided in the service you created)
Click connect to to proceed
This will take us to a new window where we can access all the tables we had already imported in DBeaver. Choose the tables you want to work on. if you need to clean you can using the transform button which will direct you into power query where you will be able to clean your data. Otherwise, you can load the tables directly so you can start working on them.
Congratulations you have successfully connected Power BI to your aiven postgreSQL database!
Hope you enjoyed this blog. Until next time.
Top comments (0)