DEV Community: Rodney Nyamu

Connecting Power BI to SQL Databases: Local SQL server and Cloud database in Aiven

Rodney Nyamu — Wed, 30 Sep 2026 10:13:41 +0000

Introduction
Being able to connect your Power BI to a database should not be complicated. Connecting Power BI directly to a PostgreSQL database offers several advantages. It eliminates repetitive manual imports, improves data consistency, supports larger datasets, and allows reports to be refreshed whenever data changes.

In this post, i will be explaining a step by step guide of how to Connect Power BI to an Aiven PostgreSQL database using SSL, with DBeaver helping us verify the connection first.

Part 1. Connect Power BI to a Local PostgreSQL Server
Step 1: Create Your PostgreSQL Database
Install PostgreSQL and create your database.

Open DBeaver and connect using:

Host: localhost
Port: 5432
Database: postgres
Username: postgres
Password: Your PostgreSQL password

Step 2: Import Your Dataset
Once connected:

Expand Schemas.
Open the public schema.
Right-click and select Import Data.
Choose your CSV or Excel dataset.
Complete the import wizard.
Your tables should now appear inside the PostgreSQL database.

Step 3: Open Power BI Desktop

Open Power BI Desktop.

Step 4: Select PostgreSQL Server

Go to:

Home → Get Data → PostgreSQL Server → Connect

Step 3 — Enter the connection details

Server: localhost (default port 5432)
Database: your database name Leave the connectivity mode as Import and click OK

Part 2: Connecting Power BI to Cloud-Based PostgreSQL Using Aiven
Cloud databases provide scalable and secure database solutions without requiring users to manage physical database infrastructure.
Aiven PostgreSQL is used as the cloud database platform.

Creating an Aiven PostgreSQL Database
An Aiven PostgreSQL service was created to host the dataset.
Aiven provided the required connection information:
Host address
Port number
Database name
Username
Password
SSL certificates

Step 1: Download the SSL Certificate
Cloud databases encrypt communication between your computer and the server.

Download the CA Certificate (ca.pem) from the Aiven dashboard.

Step 2: Import the Certificate into Windows
Search for:

Manage Computer Certificates

Navigate to:

Trusted Root Certification Authorities → Certificates

Right-click Certificates and select:

All Tasks → Import

Choose the downloaded ca.pem file and complete the Certificate Import Wizard.

Windows will confirm the import was successful.

In this dialogue box, navigate to Trusted Root Certification Authorities and click on the drop-down arrow, then right click on Certificates, then go to All Tasks, then Import. You will select the certificate downloaded from aiven, follow the procedure and you will receive a certificate import wizard stating it was successful

Connecting to your power BI
The process is more or less the same with the local Postgres SQL. Your connection details are in your Aiven postgres SQL service.The server box details are (hostname:port) while the rest of the details are input directly.

_
Load your tables_
Because Windows now trusts the Aiven certificate, the SSL handshake succeeds and the navigator opens with your tables. Select what you need and click Load or Transform Data.

In conclusion, connecting Power BI to PostgreSQL is straightforward. Local databases connect with localhost and standard credentials; Aiven adds one trust step, then behaves the same. From there you can model your tables, write DAX, and build dashboards that always reflect the latest data.

Relationships, Schemas, and Joins in Power BI: What I Learned as a Beginner

Rodney Nyamu — Wed, 30 Sep 2026 10:12:59 +0000

I'll be honest — when I first opened Power BI, I thought I could just drag in a spreadsheet, throw a chart on the canvas, and call it a day. Then my instructor gave us an assignment involving two tables instead of one, and suddenly nothing matched up. That's when I ran headfirst into the concepts of relationships, schemas, and joins — and this post is basically me explaining them the way I wish someone had explained them to me a earlier.

Why One Table Isn't Enough

In Excel, I got used to keeping everything in a single sheet. Power BI works differently — it wants you to split data into multiple tables (like a Sales table and a Products table) and then connect them. At first this felt like extra work for no reason, until I realized it stops you from repeating the same product name a thousand times across every row. That's when it clicked: this is basically how real databases are organized.

Relationships: The "Connecting Wires"

A relationship is just a link between two tables based on a common column — usually an ID. In my case, Products had a ProductID column, and so did Sales. Power BI lets you drag one onto the other in the Model view, and it draws a little line between them. That line means: "these two tables can now talk to each other."

Most relationships are one-to-many — one product can appear in many sales rows, but each sale only points to one product. Power BI shows this with a 1 on one side and a * on the other. Once I understood that symbol, the whole Model view stopped looking like spaghetti.

Schemas: Giving the Chaos a Shape

Once you have a few tables connected, you technically have a schema — just a fancy word for "how your tables are arranged and related to each other." The pattern I kept seeing recommended (and the one my course pushed us toward) is the star schema:

One central fact table (the "what happened" data — sales, transactions, orders)
Several dimension tables around it (products, customers, dates)

Picture the fact table in the middle and the dimension tables branching off it like points of a star — hence the name. I tried building a "snowflake" version once (where dimension tables connect to other dimension tables too), and honestly, for a beginner project, star schema was way easier to keep track of.

Joins: Where Excel-Me Got Confused

This is the part that tripped me up most, because Excel doesn't really have an equivalent. In Power Query (the "Get & Transform" section of Power BI), a join happens when you use Merge Queries to combine two tables before they even hit the model. The main types I used were:

Left Outer — keep everything from my main table, add matches from the other
Inner — keep only rows that match in both tables

I mixed these up constantly at first. My rule of thumb now: if I'm not sure, start with Left Outer, since it won't silently delete rows I didn't mean to lose.

Excel For Data Analytics

Rodney Nyamu — Sat, 06 Jun 2026 14:20:58 +0000

Introduction

Excel is a spreadsheet software that allows you to collect, organize, analyze, calculate, and visualize data efficiently.

In the first week of learning excel, I've learnt different ways in which excel is used in real world data analysis, different features and formulas and how they can be applied. This has had an influence to how I view excel differently in regards to its input to analyzing data.

_ Excel Interface_

Understanding Excel begins with having a basic knowledge of the interface. The user interface consists of various components that offer many tools. Some of Excel's interface components include;
Ribbon - toolbar across the top containing commands such as (Home, Insert, Page Layout, Formulas), Formula Bar, Name box, Rows, columns, and cells.

How Excel is used in real-world Data Analytics

One of the most important ways which excel is used in the real world is Data Formatting , Data Filtering and data cleaning. Number formatting changes how numbers display without altering their actual value. It improves readability and meaning. Raw data normally contains errors, duplicates, and missing values. With Excel it becomes easy to filter and sort the data before the analysis begins.

Another use of excel in a real world set up is analysis and calculations. This is aided by using aggregate and statistical functions such as Sum, Average, Count if, Average which helps analyst organize data, conduct calculations and extracting meaningful insights from datasets. In addition Excel has a conditional formatting feature which highlights cells automatically based on rules or criteria, helping spot trends. This might be of help in scenarios where sales may be below targets, it may flag overdue tasks etc.

Excel is widely used for data visualization and reporting. Charts, graphs, and PivotTables allow analysts to summarize complex information in a clear and understandable format.

Excel Features/Formulas

Useful Excel formulas commonly used are explained as follows;
SUMIF/SUMIFS allows you to add values based on one or multiple conditions, such as total sales for a specific region or product category (the criteria).

The IF function is used for logical testing. It returns one value if a condition is true and another if it is false, making it useful for decision-based analysis like classifying performance as “Pass” or “Fail.”

COUNTIF counts the number of cells that meet a specific condition, such as counting how many males employees appeared in each department. Together, these tools help in filtering, analyzing, and summarizing data.

Learning Excel has changed the way I see data by making it feel more organized, meaningful, and useful. Data looked like just numbers, but now I understand that it can tell a story and guide decisions. Excel has taught me how to sort, filter, analyze, and present information in a clear way. Excel has shown me that even simple formulas and tools can turn raw information into meaningful insights. It has also made me more careful and detail-oriented when handling data, because small errors can affect the results.