DEV Community: Melvin

End-to-End Power BI Integration with PostgreSQL: Local and Aiven Cloud

Melvin — Wed, 22 Jul 2026 14:22:37 +0000

Introduction

Power BI is a business intelligence and data visualization tool that enables users to transform raw data into interactive dashboards and insightful reports.
PostgreSQL is a robust, open-source relational database management system widely used for storing and managing structured data.
Connecting Power BI to PostgreSQL allows analysts and business users to access live data, perform advanced analysis, and create data-driven visualizations for informed decision-making.

In this guide, you will learn how to connect Power BI to PostgreSQL in two common environments: a local PostgreSQL database running on your computer and an Aiven Cloud PostgreSQL database hosted in the cloud.

Establishing a Local PostgreSQL Database

The first method of connecting Power BI to a data source is by linking it to a local PostgreSQL database hosted on your computer.

Step 1: Open Power BI Desktop
Launch Power BI Desktop and select Blank report.

Step 2: Select Get Data

On the Home tab in the top-left corner of Power BI Desktop, click Get Data. This opens a window displaying the wide range of data sources that Power BI supports.

From the list of available data sources, search for or scroll to PostgreSQL Database, select it, and then click Connect to proceed.

Step 3: Enter the Database Connection Information

After selecting PostgreSQL Database, the PostgreSQL Database connection window will appear. Enter the required connection details as follows:

Server: localhost (or localhost:5432 if specifying the default PostgreSQL port) also 127.0.0.1:5432 works.
Database: postgres (replace this with the name of your PostgreSQL database)

Once you have entered the required information, click OK to proceed to the authentication step.

Step 4: Enter Your Database Credentials

After clicking OK, Power BI will prompt you to authenticate using your PostgreSQL database credentials.

Username: Your PostgreSQL username (for example, postgres)
Password: Your PostgreSQL password

Step 4: Load the Tables
Once the connection is successful, Power BI will open the Navigator window.
This window shows all the tables within the database.

The sample table is:

Kenya_Crops_Cleaned_Final

After selecting the required tables and clicking Load, Power BI imports the data from the local PostgreSQL database into the Power BI model. The loaded tables appear in the Data and Model views, where you can inspect the data, create relationships between tables and begin building reports and dashboards. This completes the data import process and prepares the dataset for analysis and visualization.

Integrating Power BI with an Aiven PostgreSQL Cloud Database.

Cloud-hosted databases provide a secure and scalable way to store and access data from anywhere.
Integrating Power BI with an Aiven PostgreSQL cloud database enables users to connect directly to cloud-based data, import tables, and create interactive reports and dashboards.
This integration supports real-time data analysis, centralized data management, and seamless business intelligence without relying on a local database.

Step 1

Open Aiven.io in your web browser and sign in to your account. If you don't already have an account, create one by selecting the Free plan and completing the registration process.
Once the service has been provisioned, ensure it is running by checking that its status is displayed as Running before proceeding.

Step 2

With the PostgreSQL service now running, the next step is to connect it to your preferred database management system (DBMS) so you can create, manage, and query your database using Aiven's connection string.

Enter the connection details provided by your Aiven PostgreSQL service into your database management system. These include the Host, Port, Database Name, and Username. Once the details have been entered, you can establish a connection to the cloud database.

Step 3

Ensure that the Aiven PostgreSQL service is running. A successful connection is typically indicated by a green check mark next to the database name in your database management system (DBMS).
Download the CA/SSL certificate provided by the Aiven service. This certificate is used to verify the server's identity and establish a secure, encrypted connection between your DBMS and the cloud database.
An SSL certificate acts as a digital credential that authenticates the server and helps protect data transmitted over the network.

Step 3.1 Installing the SSL Certificate into the Windows Certificate Store

Open the Windows search bar and type Manage computer certificates, then select the matching result to launch the Certificate Manager. Alternatively, press Win + R, type certlm.msc, and press Enter to open the Certificate Manager directly.
In the left-hand navigation pane of the Certificate Manager, locate and expand the Trusted Root Certification Authorities folder.
Right-click the Certificates subfolder under Trusted Root Certification Authorities, select All Tasks, and then click Import to launch the Certificate Import Wizard.
In the Certificate Import Wizard, click Next. When the file browser opens, change the file type filter in the bottom-right corner from X.509 Certificate to All Files (.). This allows the ca.pem file to be displayed and selected for import.
Select the ca.pem file and click Next. Accept the default certificate store location, continue through the remaining prompts, and then click Finish to complete the import. If Windows displays a security warning asking for confirmation, click Yes to proceed with installing the certificate.

Step 4: Accessing the Aiven PostgreSQL Cloud Database from Power BI

Launch Power Bi desktop and open blank report
Go to the Home tab and click Get Data. Select More, then choose PostgreSQL database from the list of available data sources.
Enter the Host and Port details provided by your Aiven PostgreSQL service into the Server field

Conclusion

Connecting Power BI to both local and cloud-based PostgreSQL databases is an essential skill for modern data professionals. While local database connections provide a convenient environment for development and learning, cloud-hosted platforms such as Aiven demonstrate how data is securely managed and accessed in real-world environments.
Mastering both local and cloud database connectivity ensures you are well-equipped to work with modern data platforms and create insightful, data-driven reports that support informed decision-making.

Data Modelling, Joins, Relationships & Types of Schemas in Power Bi.

Melvin — Sun, 21 Jun 2026 12:08:28 +0000

Introduction

In todays data-driven business environment, organizations generate and store huge amounts of data from various sources. To transform this raw data into meaningful insights, it is important to structure it effectively.
Data modelling provides a framework for representing data, defining relationships between data elements and ensuring consistency and accuracy within analytical systems.
In Power Bi, data modelling plays a critical role in enabling efficient data analysis and reporting.

Data Modelling

What is data modelling?

Data modelling is the process of organizing and connecting data tables together for analysis, visualization and reporting.
It involves creating relationships between tables, creating measures and calculated columns and structuring data in a way that makes analysis and reporting accurate.
In power Bi, a good model typically:

Uses dimension tables
Enables fast filtering and aggregation
Minimizes redundancy for perfomance and accuracy
Separates data into fact tables

A strong data model is the foundation for accurate dashboards and scalable analytics.

Joins

Joins define how two tables are combined based on a related column.

Types of joins:

Inner Join -It returns only the matching rows that exist in both the right and left tables. If a value doesn't exist in both tables, the row is thus dropped.
It is used when you only want records that are common in both tables.
Left Join - Returns all rows from the left table and matching rows from the rigt. Missing matches become null.
Right Join- Returns all rows from the right table and matches from left.
Full Outer Join - Returns all rows from both tables, matched where possible.

Relationships in Power Bi

Relationships in power Bi are the vital links that connect two or more data tables based on common columns (keys)
They dictate how filters propagate across the data model allowing you to slice and aggregate information from multiple tables into a single cohesive report without needing to merge them.

Types of relationships in Power Bi

One-to-Many(1:*)- Found when a primary table links to a secondary, larger dataset.
-one customer:many orders
Many-to-One(*:1)- It connects a dimension table with unique values to a facts table containing many instances of the same value.
-Its the most common and recommended relationship.
Many-to Many(:)- Used when both tables have repeating values.
-Requires carefull handling as it can cause ambiquity.
One-to-One(1:1)- Used when a value in either table appears exactly once. It is relatively rare.

Types of Schemas

What is a schema in power Bi?
A schema refers to the logical structure and organization of tables in a data model.
Star Schema-
A star schema is a foundational data modelling technique that organizes data into a central fact table sorrounded by dimension tables.
Key components

Fact table- its the central table in a data model. It contains numerical values that you want to anlyze and usually links to dimension tables through keys.
Dimension tables - They are tables that contain descriptive information about the business entities in the tables.
They provide contexts to the numbers stored in fact tables and are used for filtering, grouping and labeling data in reports.

Advantages

One central fact table
Multiple dimension tables
Fast query perfomance

Snowflake Schema
It is a data modelling structure where dimension tables are normalized and split into multiple, related sub-tables to save data storage space and reduce redundancy.
Advantages

Better data integrity
Reduced storage

Disadvantages

Slightly slower queries
Requires more joins

How Excel is Used in Real-World Data Analysis

Melvin — Sat, 06 Jun 2026 16:24:42 +0000

Microsoft excel is a spreadsheet program used to collect, analyze, store, calculate and visualize data efficiently.

Applications of Excel in real-world data analysis

Making data-driven business decisions: Companies usually collect data e.g sales numbers, customer behaviour. Excel allows that data to be cleaned and structured using tables,dashboards,filters and formulas. Once the data is organized, excel supports better decisions by making data easy to clean, visualize and interpret.
Financial reporting: Excel plays a critical role in financial reporting by transforming raw data into meaningful insights that helps businesses, stakeholders understand their perfomance and make data-driven decisions. It helps build financial models, create charts and dashboards used for reporting.
Marketing perfomance: Excel is used in marketing perfomance to measure results from campaigns like ads, email marketing and monitor engagement rates on social media platforms e.g facebook, instagram. Thus it's used to create charts, and create comparison on perfomance across different campaigns.

Formulas and features of Excel i've learned so far:

Example: A(product) B(region) C(sales)

SUMIF() function - it is used to add up values that meet specific condition. Example, if you do want to get the total sales of oranges only.

==SUMIF(A2:A6,"Orange",C2:C6)

COUNTIFS() function - it is used to count how many rows meet multiple conditions at the same time. Example, How many times did apple sell in Nairobi?

=COUNTIFS(A2:A7,"apple",B2:B7,"Nairobi")

Data validation - it is a feature in excel that controls what type of data can be enterd into a cell. It enables users enter only valid and accurate data. Example, if you only want to enter gender as only male and female.

Conclusion

In conclusion, learning excel has changed the way I view data because it has shifted my thinking from seeing raw and messy data to seeing cleaned, transformed data that's used in data-driven decion making.

DEV Community: Melvin

End-to-End Power BI Integration with PostgreSQL: Local and Aiven Cloud

Introduction

Establishing a Local PostgreSQL Database

Integrating Power BI with an Aiven PostgreSQL Cloud Database.

Step 1

Step 2

Step 3

Step 3.1 Installing the SSL Certificate into the Windows Certificate Store

Step 4: Accessing the Aiven PostgreSQL Cloud Database from Power BI

Conclusion

Data Modelling, Joins, Relationships & Types of Schemas in Power Bi.

Introduction

Data Modelling

What is data modelling?

Joins

Types of joins:

Relationships in Power Bi

Types of relationships in Power Bi

Types of Schemas

How Excel is Used in Real-World Data Analysis

Applications of Excel in real-world data analysis

Formulas and features of Excel i've learned so far:

Conclusion