DEV Community: Neema Kirui

From My Machine to the Cloud: Connecting Power BI to SQL Databases; PostgreSQL (Local vs Aiven)

Neema Kirui — Sat, 04 Jul 2026 21:26:12 +0000

Introduction

I used to think "connecting to a database" was one skill. Turns out it's two: connecting to a database chilling quietly on your own laptop, and connecting to one living in the cloud, behind a login, in this case, an SSL certificate that will not let you in until you treat it with respect.

This week I did both. Same tool (Power BI), same dataset, two very different vibes.

Grab a coffee, here's the full walkthrough local PostgreSQL first, then Aiven's cloud version, side by side, screenshots and all.

Part 1: Local PostgreSQL → Power BI

Step 1 : Create a schema

Nothing fancy, just giving my table a home:


CREATE SCHEMA powerbi;

Step 2 : Import the dataset

Right-click the new schema → Import Data in DBeaver, point it at your CSV, and let the wizard do its thing.

Step 3 : Check the table landed properly

A quick peek at the columns to make sure nothing got mangled on the way in.

Step 4 : Connect Power BI

In Power BI Desktop: Get Data → Database → PostgreSQL database.

In the Server field, type localhost (or 127.0.0.1) and your database name.

localhost

Choose Import, hit OK, and log in with your local username and password.

Click Load. That's it. That's the whole local experience.

Part 2: Aiven PostgreSQL (Cloud) → Power BI

Now for the part that actually taught me something.

Step 1 : Grab your connection details

Everything you need lives on Aiven's Overview page: Host, Port, Database name, User, SSL mode.

Your service URI will look something like this (don't worry, this isn't a real password, Aiven masks it in the console):

postgres://avnadmin:••••••••@pg-xxxxxxxx-yourproject.c.aivencloud.com:22016/defaultdb?sslmode=require

Step 2 : Import the dataset into Aiven

Same DBeaver wizard as before, just pointed at the Aiven connection instead of local.

CREATE SCHEMA powerbi;

Step 3 : Aiven's certificate.

Download the CA cert from the Overview page:

Now here's the part that actually tripped me up: Power BI's PostgreSQL connector doesn't have a field where you paste in a certificate file path (DBeaver does, Power BI doesn't). Instead, on Windows, you install the cert into Windows' own certificate store, since Power BI leans on the OS to handle SSL validation.

Once Windows trusts it, Power BI trusts it too. No settings to toggle inside Power BI itself.

Step 4 : Connect Power BI to Aiven

Same dialog, new server string:

pg-xxxxxxxx-yourproject.c.aivencloud.com

Database: defaultdb

Log in with your Aiven username and password. Since the cert is already trusted system-wide, no extra SSL prompts show up it just connects.

Local vs Aiven: Side by Side

Step	Local PostgreSQL	Aiven PostgreSQL
Server address	`localhost`	Full Aiven host string
Port	Default `5432`	Custom port from Aiven console
SSL/certificate setup	None	Download CA cert, trust it via Windows Certificate Manager
Power BI dialog	Identical	Identical
Credentials prompt	Username/password	Username/password (SSL handled invisibly behind the scenes)

Same tool, same steps on the surface. The only real difference is one extra layer of trust you have to set up before the cloud database will even talk to you and once it's set up, it disappears completely. You never think about it again.

The Day I Realized Power BI Isn't Just Pretty Excel: Understanding Data Modelling

Neema Kirui — Thu, 25 Jun 2026 18:01:47 +0000

When I first opened Power BI, I thought it was just a prettier Excel. Then I hit data modelling and it looked me dead in the eye and said "not today."

Data modelling is the backbone of any Power BI report. It decides how your tables connect, how fast your visuals load, and whether your numbers are actually trustworthy. It's what allows Power BI to understand that a sale belongs to a product, a customer belongs to a region, and a date belongs to a calendar.

Without a proper model, your visuals might look impressive while quietly lying to you. Once you get it? Everything clicks. Let me break it down simply.

Relationships: Getting Your Tables to Actually Talk

A relationship connects two tables through a shared column called a key. If your Sales table and Products table both have a ProductID column, that's your link right there.

Every relationship starts with two important columns:
Primary Key (PK) – a column whose values are unique. Think ProductID in a Products table where every product appears once.
Foreign Key (FK) – a column that references the primary key from another table. Think ProductID in a Sales table where the same product

The primary key identifies the record. The foreign key points to it. Together, they allow Power BI to connect the dots.

Type	What it means	Example
*One-to-Many (1:)**	One row on one side, many on the other	One product → many sales
Many-to-Many (:)	Both sides have repeats	Students ↔ Courses

  Products Table  ─────────────────  Sales Table
  ┌─────────────┐                   ┌─────────────┐
  │ ProductID   │──────── 1 : * ────│ ProductID   │
  │ ProductName │                   │ SaleAmount  │
  │ Category    │                   │ SaleDate    │
  └─────────────┘                   └─────────────┘

Relationships also have a filter direction.Think of it as a one-way street. Filters travel from the one side to the many side.

One thing I learned very quickly is that relationships matter more than you think. Without them, Power BI sees your tables as separate islands. That's why you might click a product in one visual and wonder why absolutely nothing happens in the others.

Star Schema vs Snowflake Schema

Your schema is the overall layout of your data model basically how all your tables are arranged and connected to each other.

Star Schema - The One You Want

              ┌─────────────┐
              │   DimDate   │
              └──────┬──────┘
                     │
  ┌────────────┐  ┌──▼─────────┐  ┌─────────────┐
  │DimCustomer │──│  FactSales │──│  DimProduct │
  └────────────┘  └──┬─────────┘  └─────────────┘
                     │
              ┌──────▼──────┐
              │  DimRegion  │
              └─────────────┘

One central fact table (your numbers in terms of sales amounts, quantities) with dimension tables around it (your context: dates, customers, products). Clean, fast, and exactly what Power BI is optimised for. DAX, Power BI's formula language, was designed with this layout in mind.

One thing that surprised me was how much Power BI prefers a star schema. At first, I wanted to keep breaking tables into smaller and smaller pieces because it felt more organised. But Power BI works best when dimensions stay relatively flat and connect directly to a central fact table. Fewer relationship paths mean faster calculations and fewer headaches when writing DAX.

Snowflake Schema - The Complicated One

  ┌────────────┐   ┌────────────┐   ┌────────────┐
  │ DimProduct │──▶│ DimCategory│──▶│DimSuperCat │
  └─────┬──────┘   └────────────┘   └────────────┘
        │
  ┌─────▼──────┐
  │ FactSales  │
  └────────────┘

This keeps splitting dimension tables into smaller sub-tables (e.g. Product → Category → Super Category). It looks tidy but makes your model slower and your DAX measures harder to write.

Joins: Combining Tables in Power Query

Joins happen in Power Query before data even reaches your model. You're deciding which rows from each table make the cut. The easiest way to picture this is with Venn diagrams:

Join Type	What you get
Inner Join	Only rows that match on both sides
Left Outer Join	All left rows + right matches
Full Outer Join	Every row from both tables

I once used the wrong join and ended up with sales figures in the millions when they should've been in the thousands. The data wasn't wrong I was just inviting the wrong rows to the party.

What Data Modelling Actually Taught Me

The biggest lesson wasn't how to create relationships or choose the correct join.

It was understanding that dashboards are only as good as the model underneath them.

A beautiful report built on a bad model will still give bad answers.

After working with relationships, schemas, joins, keys, and cardinality, I realised that data modelling isn't something you do before analysis. It is part of the analysis.

My biggest takeaways:

Build your model before building visuals.
Use a Star Schema whenever possible.
Understand your keys before creating relationships.
Be intentional with joins in Power Query.
If your numbers look wrong, check the model first. The chart may be what people see, but the model is what makes it trustworthy.

Data modelling isn't the flashiest part of Power BI nobody's putting their model diagram on a mood board. But get it right, and your visuals, your numbers, and your whole report will thank you.

How Excel is Used in Real-World Data Analysis

Neema Kirui — Fri, 05 Jun 2026 12:10:31 +0000

Introduction to Excel

In simple terms Excel is a spreadsheet program developed by Microsoft which is designed to organize, calculate, and analyze data. Excel is very collaborative when one is working with other tools in data analysis. It is also very effective when handling large datasets.

Ways Excel is used in real-world data analysis

In the real world, Excel is used for financial modeling, auditing, marketing performance,sales forecasting, business decisions among others.
Let's get into a few of them:

1. Financial Modeling
Excel serves as a a digital blueprint to map out the future of a business. Most institutions use Excel to strictly monitor their cash inflows and outflows, ensuring they always have enough money to pay bills while planning for future growth.
2. Business Decisions
Excel acts like a digital testing ground for business owners. You can change your sales and cost numbers on the screen to see how they affect your profits, helping you choose the best plan before spending real money.
3. Performance Tracking
Excel works as a digital report card for everyday operations. It stores daily sales data and uses built-in charts to show managers at a glance if the team is hitting targets or falling behind.

Features and Formulas

Conditional Formatting:
This feature automatically changes the color of a cell based on its value. For example, you can set Excel to turn a cell bright red if a project goes over budget, or green if sales goals are met, helping you spot critical details instantly.
Data Validation:
This feature sets strict rules for what users can type into a cell. You can use it to create drop-down menus or restrict cells to only accept specific items like dates or positive numbers preventing mistakes before they happen.
COUNTIF Function:
This tool counts cells only if they meet a specific rule. For example, it can look at a list of orders and count exactly how many times the word "Completed" appears.

Reflections

To be honest,knowing more about Excel has completely changed how I look at numbers. Playing with large data sets and making massive changes with just a single click is also really cool! It just shows me how fun it can be to play around with data, test out different features, and see what the numbers are trying to tell us. At the same time, it makes you realize how this work can be used for the greater good because data is really powerful! Yay!