DEV Community: Mary Ngure

How I connected PowerBI to PostgreSQL locally and on Aiven

Mary Ngure — Sun, 05 Jul 2026 14:12:28 +0000

Data rarely lives inside Excel forever. As datasets grow, they're typically stored in databases where they can be managed efficiently and accessed by multiple applications. That's why learning to connect Power BI directly to SQL databases is an essential skill for every data analyst.

This week, I explored two different ways of connecting Power BI to PostgreSQL databases:

A local PostgreSQL database
A cloud-hosted PostgreSQL database using Aiven with SSL encryption

For this exercise, I used PostgreSQL, DBeaver for database management, Aiven for cloud database hosting, and Power BI Desktop for data visualization.

Although both connections ultimately bring data into Power BI, the setup process is quite different. Here's what I learned.

Why Connect Power BI to a Database?

Instead of importing CSV files every time new data becomes available, Power BI can retrieve data directly from a database. This makes reporting more scalable, reliable, and suitable for production environments.

Some benefits include:

Centralized data storage
Improved security
Easier collaboration
Faster report updates
Support for larger datasets

Part 1: Connecting Power BI to a Local PostgreSQL Database

Before working with a cloud-hosted database, I first set up a local PostgreSQL environment. Using DBeaver, I connected to my local PostgreSQL instance and imported the JCars Logistics dataset. DBeaver provided a convenient interface for managing the database and verifying that the data had been imported successfully.

With the dataset in place, I connected Power BI to the local PostgreSQL database by entering the server details and authentication credentials. Once the connection was established, Power BI loaded the tables, making them available for analysis and reporting.

This local setup served as a useful foundation before connecting to a cloud-hosted PostgreSQL database on Aiven, where additional security measures such as SSL certificate configuration were required.
Using Dbeaver, I connected Postgresql and uploaded my data.

Part 2: Connecting Power BI to an Aiven PostgreSQL Database

Unlike a local database, Aiven hosts PostgreSQL in the cloud. This means the connection requires additional security through SSL certificates.

Step 1: Import the Dataset into Aiven Using DBeaver

Instead of importing the dataset directly through the Aiven web interface, I used DBeaver as my database management tool. After connecting DBeaver to my Aiven PostgreSQL instance using the connection details provided by Aiven, I imported the JCars Logistics dataset into the target database.

Using DBeaver made it easier to manage the database, verify that the import was successful, and inspect the tables before connecting them to Power BI.

DBeaver connected to the Aiven PostgreSQL database and JCars Logistics tables successfully imported and visible in DBeaver.

Step 2: Obtain the SSL Certificate

Because Aiven encrypts database connections, Power BI requires the CA SSL certificate before it can establish a secure connection.

I downloaded the CA certificate from the Aiven service dashboard and saved it locally for use during the connection process.

SSL certificate download page in Aiven.

Confirmed that the SSL was downloaded and in my certificates management.

Step 3: Connect Power BI to Aiven

Inside Power BI, I again selected PostgreSQL Database, but this time entered the cloud connection details provided by Aiven, including the hostname, port, database name, username, and password.

I then configured the SSL certificate, allowing Power BI to establish a secure encrypted connection to the cloud database.

PostgreSQL connection with PowerBI.

Step 4: Load the Tables

Once the connection was authenticated, Power BI displayed all available tables from my Aiven database. I selected the required tables and loaded them into the report for analysis.

Navigator showing tables from the Aiven PostgreSQL database.

What I learned

Connecting Power BI to a local database is mostly about getting the server
name and credentials right. Connecting to a cloud database adds two extra
considerations: the non-standard host/port from your provider's dashboard,
and SSL, which cloud providers enforce by default for security. Once you've
done it once, the pattern is the same for any provider; Aiven, AWS RDS,
Azure Database, or Google Cloud SQL all follow this same shape: get
connection details from the console, ensure SSL is respected, connect via
Power BI's native connector, and choose Import mode so your report doesn't
depend on the database staying reachable.

Key Takeaways

This exercise showed how Power BI can connect seamlessly to both local and cloud-hosted PostgreSQL databases. While local databases are simpler to configure, cloud platforms like Aiven introduce additional security measures such as SSL certificates that are common in production environments.

Using DBeaver alongside Aiven also simplified database management by making it easy to import the dataset, inspect tables, and verify that everything was ready before connecting to Power BI.

As organizations continue moving their data infrastructure to the cloud, understanding how to connect business intelligence tools like Power BI to secure cloud databases is becoming an essential skill for every data professional.

PowerBI; Relationships, Schemas & Dashboards

Mary Ngure — Mon, 29 Jun 2026 06:06:38 +0000

When I started learning Power BI, I thought dashboards were mostly about choosing the right charts.

Then I discovered data modeling.

Everything clicked.

I realized that dashboards aren't powered by visuals, they're powered by relationships.

The Mistake I Used to Make

Coming from Excel, I was used to keeping everything in one large table.

It worked... until the data became larger and more complex.

Duplicate information increased.

Performance slowed down.

And maintaining the dataset became frustrating.

Power BI introduced me to a completely different way of thinking.

Instead of one giant spreadsheet, data is organized into related tables.

Understanding Relationships

Relationships allow tables to communicate using common fields, often called keys.

For example:

A Sales table stores transactions.
A Products table stores product information.
A Customers table stores customer details.

Rather than repeating product names thousands of times, Power BI connects the tables using Product IDs.

This keeps the model cleaner, faster, and easier to maintain.

Discovering Star Schema

One concept that completely changed my understanding was the Star Schema.

Instead of random tables connected everywhere, the model is organized into:

Fact Table

Contains measurable business events such as:

Sales
Revenue
Quantity Sold
Profit

Dimension Tables

Contain descriptive information like:

Customers
Products
Locations
Dates

The fact table sits in the center, while the dimension tables surround it—forming a star.

It's surprisingly elegant.

And it's one of the reasons Power BI performs so efficiently.

Why Good Models Create Better Dashboards

Once relationships were configured correctly, dashboard creation became much easier.

Instead of manually combining data for every visual, Power BI automatically understood how everything connected.

Slicers filtered multiple visuals correctly.

Measures returned accurate results.

Cross-filtering worked naturally.

It felt like the model was doing half the work for me.

Dashboard Design Is More Than Pretty Colors

Building dashboards taught me another important lesson.

A dashboard isn't a decoration.

It's a decision-making tool.

Now, whenever I design one, I ask:

What question should this answer?
What KPI matters most?
What action should someone take after seeing this visual?

Those questions have become more important than choosing between a bar chart or a line chart.

My Growing Power BI Workflow

As I continue learning, my process has become surprisingly consistent:

Understand the business problem.
Clean the data.
Build relationships.
Design a star schema.
Create measures.
Build visuals.
Refine the dashboard until it tells a clear story.

Each step builds on the previous one.

Skipping one almost always creates problems later.

Final Thoughts

Power BI has taught me that great dashboards aren't created in the report view.

They're created through thoughtful data modeling.

The visuals may be what users notice first, but relationships are what make those visuals meaningful.

I'm still learning, but every project reinforces the same lesson:

Clean data is important.

Well-designed models are powerful.

And dashboards are simply the final chapter of a much bigger story.

Learning Power BI; Where it all begins

Mary Ngure — Mon, 29 Jun 2026 05:59:39 +0000

When I first opened Power BI, I thought the exciting part would be building beautiful dashboards. I was wrong. It is such a powerful tool.

The real work and arguably the most important work starts long before creating charts. It starts with messy data.

As someone transitioning deeper into data analytics, I've realized that data cleaning isn't the boring step people make it out to be. It's where you develop the mindset of an analyst. Every duplicate, missing value, incorrect data type, or inconsistent entry tells a story about the quality of your data.

And if the data is wrong, your dashboard will simply visualize bad decisions.

My First Encounter with Power Query

The first thing that surprised me was that Power BI separates data preparation from visualization through Power Query.

Instead of editing the original dataset, Power Query records every transformation as a reusable step. This means that when new data is added later, I don't have to repeat the cleaning process manually.

That alone completely changed how I thought about data preparation.

Cleaning Data Isn't Just Removing Errors

One lesson I quickly learned is that cleaning data is about making it analysis-ready.

Some of the common tasks I performed included:

Removing duplicate records
Changing incorrect data types
Renaming columns for clarity
Replacing missing values
Splitting and merging columns
Creating calculated columns
Filtering unnecessary rows
Standardizing inconsistent text values

Each transformation made the dataset a little more trustworthy.

Functions That Made My Life Easier

As I continued practicing, I became more comfortable using functions inside Power Query.

Some of my favorites include:

Text functions for cleaning inconsistent names
Date functions for extracting months and years
Conditional Columns for categorizing records
Replace Values for fixing repeated errors
Group By for summarizing data quickly

These small tools eliminated hours of manual work.

The Biggest Lesson

Before learning Power BI, I assumed analysts spent most of their time creating reports.

Now I know that a significant portion of analytics happens before a single chart is built.

Clean data creates confidence.

Confidence creates better decisions.

What's Next?

Now that I'm becoming comfortable cleaning data, I'm moving into one of the most interesting parts of Power BI, building relationships between tables and designing dashboards that tell meaningful stories instead of simply displaying numbers.

Because data isn't valuable until someone can understand it.

And that's exactly where Power BI starts to shine.

How Excel is used in Real World Data Analysis

Mary Ngure — Sun, 07 Jun 2026 08:05:09 +0000

I’ve always known Excel as a tool for creating tables and performing simple calculations. However, after spending a week learning its fundamentals, I now understand why Excel remains one of the most widely used tools in data analysis.
Microsoft Excel is a spreadsheet application that allows users to collect, organize, clean, analyze, calculate, and visualize data. Its user-friendly interface and powerful features make it a valuable tool for individuals and organizations across different industries.
One way Excel is used in real-world data analysis is in business decision-making. Companies collect large amounts of data on sales, customers, and operations. Analysts use Excel to sort and filter this data, helping managers identify trends, monitor performance, and make informed decisions. For example, a retail business can sort products by sales volume to identify its best-selling items.
Excel is also widely used in financial reporting. Businesses use it to track expenses, calculate profits, prepare budgets, and generate financial reports. With formulas and formatting tools, financial data can be organized in a way that is easy to understand and analyze.
Another common application is marketing performance analysis. Marketing teams collect data from campaigns, websites, and social media platforms. Excel can be used to analyze campaign results, compare performance metrics, and identify which strategies are generating the best outcomes.
Throughout this week, I learned several Excel features and formulas that are useful in data analysis. The first is filtering, which allows analysts to display only the data that meets specific criteria. This is useful when working with large datasets and looking for particular information. I also learned about data validation, which helps maintain data quality by restricting the type of information users can enter into cells. This reduces errors and improves data accuracy.
In addition, I learned functions such as SUM(), AVERAGE(), and COUNT(). SUM() helps calculate totals, AVERAGE() finds the mean value of a dataset, and COUNT() determines how many numerical values exist within a range. These functions make it easier to summarize and understand data quickly. I also found text functions such as TRIM() and PROPER() useful for cleaning and standardizing data before analysis.
Learning Excel has changed the way I see data. Before, I saw data as a collection of numbers and text. Now, I see it as information that can tell a story and support decision-making when properly organized and analyzed. Excel has shown me that effective data analysis begins with understanding how to clean, structure, and explore data. As I continue my journey in data science, I can already see how these foundational Excel skills will support my learning of more advanced tools and techniques.