Connecting Power BI to SQL databases : Local PostgreSQL and Aiven cloud

Gideon Kiprono — Tue, 15 Sep 2026 16:54:13 +0000

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.

HOW EXCEL IS USED IN REAL WORLD DATA ANALYSIS

Gideon Kiprono — Sat, 06 Jun 2026 21:14:21 +0000

Introduction

What is Excel

Excel is an important tool used in Data analysis. Excel enables a data analyst to ineteract with raw data. Raw data are always messy and require cleaning. This is now the work of excel to help in cleaning, organizing, analysing and visualizing data.

Ways Excel is used in real world data analysis

Financial Modelling . Excel is used in building financial models that can be used to predict the performance of a business. Excel formulas can be used to calculate profits, revenue and costs.
Auditing.Auditors use excel in analysing financial records of an organization.They use it to check errors, detect any fraud and verify financial records.
Tasks Automation. Excel can be used to automate repetitive tasks using formulas.
Data Visualization. One can use excel to turn raw data into charts, graphs and dashboards for easier interpretation of the data.

Basic featuers of Excel

Excel as a tool has many features that enable it bto function properly, this includes;

Excel Ribbon

The Excel Ribbon is the toolbar located at the top of the excel interface that contains all commands and tools organized into tabs such as Home, Insert, Data, Formulas and View. Each tab groups related functions for easy access.

Data Entry and Formatting

Excel allows users to enter different kinds of data such as texts, numbers and dates. Formatting tools are also used for organizing data to improve readability.

Filtering and Sorting

Filtering helps in displaying specific data, while sorting arranges data in ascending or descending order. They help to in organizing large datasets.

Conditional Formating

This is a special feature that helps in highlighting data based on specific rules, making it easier to identify trends and patterns.

Functions in Excel

Excel has differnt kinds of functions that aid in data analysis;

Mathematical / Aggregate Functions: SUM(), AVERAGE(), MEDIAN(),MODE().
Statistical Functions.
Text Functions.
Lookup Functions.
Date and time functions.

Final thoughts

I am not going to lie, Before doing a deep dive into how excel works, my understanding was that excel was not that powerful in data analysis. To my suprise, I have come to know and appreciate excel as a powerful tool in the hands of any aspiring analyst.

DEV Community: Gideon Kiprono

Connecting Power BI to SQL databases : Local PostgreSQL and Aiven cloud

Introduction

Connecting Power BI to a local SQL database

Process 1: Connecting a new localhost database in DBeaver.

Process 2: Importing data to our database

Process 3: Getting data in power BI

Connecting Power BI to a cloud-hosted postgreSQL database using Aiven with SSL configuraion

HOW EXCEL IS USED IN REAL WORLD DATA ANALYSIS

Introduction

What is Excel

Ways Excel is used in real world data analysis

Basic featuers of Excel

Excel Ribbon

Data Entry and Formatting

Filtering and Sorting

Conditional Formating

Functions in Excel

Final thoughts