DEV Community: Ken kimani

# How to Connect Power BI to PostgreSQL: Local and Aiven Cloud Setup

Ken kimani — Sun, 23 Nov 2025 13:09:31 +0000

Power BI is a powerful business intelligence tool that enables you to visualize and analyze data from various sources. PostgreSQL is a popular open-source relational database that can serve as an excellent data source for Power BI. In this article, we'll walk through two scenarios: connecting Power BI to a local PostgreSQL instance and connecting to PostgreSQL hosted on Aiven cloud.

Prerequisites

Power BI Desktop: Download and install from the Microsoft Power BI website
PostgreSQL: Either a local installation or an Aiven account
Npgsql: PostgreSQL data provider for .NET (required for Power BI connection)

Part 1: Connecting Power BI to Local PostgreSQL

Step 1: Connect Power BI to Local PostgreSQL

Open Power BI Desktop and follow these steps:

Click on Get Data in the Home ribbon
Search for PostgreSQL database and select it
Click Connect
In the PostgreSQL database dialog, enter:
- Server: localhost (or 127.0.0.1)
- Database: defaultdb
- Data Connectivity mode: Import or DirectQuery
Click OK
Enter your PostgreSQL credentials:
- User name: postgres (or your username)
- Password: Your PostgreSQL password
Click Connect

Step 2: Load Data into Power BI

After authentication, the Navigator window will appear showing available tables:

Select the products table from the list
Click Load to import the data directly, or Transform Data to open Power Query Editor for data transformation

Part 2: Connecting Power BI to PostgreSQL on Aiven

Step 1: Create a PostgreSQL Service on Aiven

If you don't have an Aiven account, sign up at aiven.io. Then:

Log in to your Aiven console
Click Create Service
Select PostgreSQL as the service type
Choose your cloud provider and region
Select a service plan (you can start with the free tier for testing)
Give your service a name (e.g., "powerbi-postgres")
Click Create Service

Wait for the service to start (this usually takes 2-5 minutes). The status will change to "Running" when ready.

Aiven console showing a running PostgreSQL service

Step 2: Retrieve Connection Information

Once your service is running, navigate to the service overview page to find connection details:

Host: Your Aiven PostgreSQL hostname (e.g., powerbi-postgres-project.aivencloud.com)
Port: Usually 12345 (varies by deployment)
User: avnadmin (default)
Password: Click the eye icon to reveal

This is the aiven dashboard.

Step 3: Open PowerBI

On opening power bi click on blank report and follow the images below to be able to upload data from my postgres database on aiven . Follow the images below to be able to upload focusing on the marked areas on the images.

leads to:

Set Data Connectivity to DirectQuery

leads to:

The error happens when Power BI tries to connect to PostgreSQL using SSL, but your PostgreSQL server does NOT have a valid SSL certificate.To fix this we download the CA certificate on aiven dashboard.Go to your local machine and search “manage computer certificates”,then click on trusted root certification authorities,right click on certificates,then to all tasks,move to import ,then browse to find the downloaded certificate then make sure you have a successful import.After this restart your PowerBI.

Then start all over the process of connecting powerbi to aiven from the first screenshot but now you wont encounter the error but you will get to see all tables you have in your postgres database,just as below:

you check the tables you need just like i have done on your left side of the screen., and we end up with this on PowerBI with the tables uploaded(on the right).

GETTING TO KNOW:STAR AND SNOWFLAKE SCHEMA.

Ken kimani — Thu, 09 Oct 2025 18:04:57 +0000

By: Kennedy Ngugi Kimani

INTRODUCTION

Terms to be familiar with:

Central Fact Table - is the core of a star schema in a data warehouse, containing quantitative measures of a business process, such as sales amounts or quantities, linked to surrounding dimension tables via foreign keys to provide business context for analysis and reporting.

2.Normalized dimension Tables -This break down attributes into separate tables to reduce data redundancy.

3.De-normalized Dimension Tables -A technique in dimensional modeling where redundancy is intentionally introduced to combine related data into a single, wide, "flat" table to improve query performance and usability in data warehousing and business intelligence.

UNDERSTANDING STAR SCHEMA AND SNOWFLAKE SCHEMA.

First of all this are data modeling techniques in data warehousing.

STAR SCHEMA.

A star schema has one main table in the center (the fact table) connected to several supporting tables (dimension tables) around it. These dimension tables are not split into smaller parts, which makes the data easier to understand and faster to analyze. This design is especially useful for creating reports and dashboards quickly.
This is a visual representation of Star schema.

At the center is the fact table, which contains measurable business data: sales, clicks, revenue, inventory changes, etc. Around it are dimension tables that add context to each fact: customers, products, dates, stores, and so on.

Advantages.

Simplified querying: Star schemas are easy to understand and implement. Their de-normalized structure reduces the number of joins required to retrieve data. This simplifies and leads to faster data aggregation and reporting.
Faster performance: The reduced join complexity and efficient indexing of fact and dimension tables enhance data retrieval. This is particularly important for decision-makers who require quick access to insights.
Intuitive analysis: Star schemas enable intuitive and straightforward data analysis. Users can easily understand relationships and hierarchies among dimensions.
Robust support: Star schemas provide support for OLAP structures such as data cubes – multi-dimensional arrays used to improve data analysis.

Dis-advantages.

Lack of integrity: Denormalization can cause data redundancy. Dimensional attributes are often repeated across multiple records within a dimension table which can cause data quality issues. Since data is duplicated in denormalization, frequent changes can also cause certain tables to display out-of-date information.
Increased costs: Adding redundant data increases computing and storage costs. This can be especially troubling when handling large datasets.
Limited flexibility: Star schemas are relatively less robust than normalized structures since they are built for specific use cases. Other approaches might be more effective for complex querying involving multiple joins.
Maintenance difficulties: As data changes over time, maintaining a star schema can become challenging. Updates to dimension attributes might require changes in multiple places.

When to use star schema.

You need dashboards that load quickly — fewer table connections make reports and filters run faster.
Your data doesn’t change structure often, so it’s easy to maintain.
Your users are business people or analysts who prefer simple data layouts that are easy to understand.
You’re using tools like Power BI or Tableau, which work better with straightforward, less complex data models.

SNOWFLAKE SCHEMA.

Snowflake Schema is similar to a basic setup where one main table (the fact table) connects to several supporting tables (dimension tables). The difference is that in a snowflake schema, those supporting tables are further split into smaller, related tables. This helps avoid repeating the same data and shows clear levels of information — like categories and subcategories.
This is a visual representation of Snowflake schema.

Advantages.

Small storage. The snowflake schema doesn’t require as much storage space due to its normalization.
High granularity. Thanks to its data normalization, it adds new layers of analysis.
Integrity. Due to normalization, the schema has a higher level of data integrity and low redundancies.

Dis-advantages.

Complexity: The presence of multiple dimension tables increases the complexity of query creation.
Slow Processing: Numerous lookup tables require additional JOIN operations, which can slow down query performance.
Hard to Maintain: Increased granularity results in greater complexity, making the schema more difficult to manage and maintain.

When to use star schema.

You want to avoid repeating the same data, which saves space and reduces storage costs.
Your data naturally fits into levels, like category → subcategory → product, showing clear hierarchies.
You need to make updates easily — changing one table automatically updates related information.
You’re handling very large datasets — the snowflake schema keeps main tables smaller and faster by storing detailed information separately.

CONCLUSION

In summary, the choice between a Star Schema and a Snowflake Schema is driven by each business need.

THE POWER OF POWER BI (BUSINESS INTELLIGENCE) IN DATA ANALYSIS.

Ken kimani — Wed, 08 Oct 2025 20:52:51 +0000

Introduction.

Power BI is a Microsoft-powered business intelligence tool that helps transform raw data into interactive dashboards and actionable insights. It allows users to connect to various data sources, clean and shape data and visualize it using charts, graphs and reports all with minimal coding.
It’s widely used in industries for data storytelling, decision-making and analytics and integrates well with tools like Excel, databases, Cloud and even Python. Power BI also excels in data storytelling, turning complex datasets into visual stories that make patterns and trends easy to understand for decision-makers at all levels

Importance of Power BI

.
Power BI is a tool that helps you understand your data better. You can:
a. Bring in data from different places like Excel files, SQL databases, CSVs, JSON files and even websites.
b. Clean and fix your data easily without writing code.
c. Create visuals like bar charts, line graphs, pie charts and dashboards to help you see patterns and trends.
d. Analyze your data using filters and slicers, so you can focus on specific details like sales by region or product performance over time.
e. Share your dashboards with your team or clients so everyone stays informed and can explore the data on their own.
f. Set up automatic updates so your reports always show the latest information without you having to do anything.
g. Track Key Performance Indicators (KPIs) over time using interactive dashboards, helping users monitor goals such as crop yields, sales growth, or operational efficiency in real time.

Understanding DAX: The Engine Behind Power BI.

Data Analysis Expressions (DAX) is the formula language used in Power BI for creating custom calculations. DAX has functions across that can be used in data manipulation and these are: Aggregation functions, Logical functions, Text functions and Date Time Functions.
i. Aggregation function performs calculation across a column of numbers and return a single value. They include SUM, AVERAGE, SUMX, AVERAGEX, MIN, MAX, COUNT and COUNTROWS.
For example:
Total Yield = SUM (Kenya crop Dataset [Yield (Kg)])
Total profits = SUMX (Kenya crop Dataset, Kenya crop Dataset [Yield (Kg)] * Kenya crop Dataset [Market Price (KES/Kg)])

ii. Text functions manipulate text strings and they include CONCATENATE, LEFT, RIGHT, MID, LEN, FIND, REPLACE, UPPER, LOWER and SUBSTITUTE.
For example:
Uppercase = UPPER (Kenya crop Dataset [Irrigation Method])
Len column = LEN (Kenya crop Dataset [Farmer Name])

iii. Date & Time functions work with date and time values and they include DATE, TIME, YEAR, QUARTER, MONTH, DAY, HOUR, MINUTE, SECOND, NOW and TODAY.
For example:
month = MONTH (Kenya crop Dataset [Harvest Date])
Quarter = QUARTER (Kenya crop Dataset [Harvest Date])

iv. Logical functions evaluate conditions and return a Boolean result (TRUE/FALSE) and they include IF, AND, OR, NOT and SWITCH.
For example:
season checker = IF (Kenya crop Dataset [Season]="Short Rains”, ‘Little rain’, “heavy rain”)
weather impact checker = IF (NOT (Kenya crop Dataset [Weather Impact] ="unknown"),"no idea", "known")

How Power BI and DAX Enhance Decision Making.

The integration of Power BI with DAX formulas creates a powerful analytical environment where data professionals can extract deeper insights from their datasets. Using our Kenya Crops dataset as an example, analysts can quickly identify production trends, compare regional outputs, and forecast future yields with precision. This enhanced analytical capability empowers stakeholders to make evidence-based decisions that can transform agricultural planning and food security policies in Kenya.
The ability of Power BI to create relationships between tables enables the integration of data from different sectors—such as weather, market prices, and production records—to derive deeper insights within the agricultural sector. By linking these diverse datasets, farmers and agribusinesses can identify trends, monitor performance, and make data-driven decisions that improve efficiency and profitability.
In my view, Power BI’s strength lies in its ability to turn complex agricultural data into clear visuals that inspire action — making it an invaluable tool for modern data-driven decision-making.