DEV Community: Joseph Okwemba

Power BI Meets PostgreSQL: A Practical Guide to Local and Cloud Database Connections

Joseph Okwemba — Sun, 05 Jul 2026 17:28:46 +0000

Most people build their first Power BI dashboard on a CSV or Excel file. But real
business data lives in a database and connecting Power BI directly to a SQL
database instead of exporting spreadsheets means your reports can refresh
automatically as the underlying data changes.

In this guide I connect Power BI to PostgreSQL two ways: a local installation
running on my own machine, and a cloud-hosted instance on Aiven, which
requires SSL. I'll use a car sales and logistics dataset as the working example.

Why connect Power BI directly to a database?

Real-time-data — refresh the report and it pulls the latest rows, no manual re-export.
Automation — scheduled refresh means the dashboard stays current without anyone touching it.
Single source of truth — everyone builds on the same underlying table instead of copies of a spreadsheet.

Part 1: Connecting to a Local PostgreSQL Database

Install PostgreSQL locally (https://www.enterprisedb.com/downloads/postgres-postgresql-downloads) and confirm the service is running the default port is 5432.
Make sure the target database already contains the table you want to visualize.
Open Power BI Desktop, Get Data, More, Database, PostgreSQL database and Connect.
Enter:
- Server: localhost:5432
- Database: your database name
Choose Import or DirectQuery, then enter your PostgreSQL username and password when prompted.
The Navigator window lists your tables select the ones you need and click Load, or Transform Data if you want to clean first.

Opening the Get Data window in Power BI Desktop and selecting the PostgreSQL database connector.

The Navigator window listing the tables available in the local PostgreSQL database after connecting.

No SSL setup is needed here since the connection never leaves your machine.

One limitation worth knowing: if you publish this report to the Power BI Service later and want scheduled refresh, a local database needs an On-Premises Data Gateway installed, since the cloud service can't otherwise reach localhost.

Part 2: Connecting to Cloud PostgreSQL on Aiven

Aiven is a managed cloud database provider. Unlike a local database, Aiven
enforces SSL on every connection your credentials and data are encrypted in
transit, which matters a lot more once the database isn't sitting on your own
laptop.

Step 1 Get your connection details from Aiven

Host
Port
Database name (usually defaultdb)
Username (usually avnadmin)
Password

The Aiven PostgreSQL service dashboard showing the host, port, database name, and username needed to connect.

Step 2 Download and trust the CA certificate

Because Aiven uses a self-signed certificate authority, your machine needs to
explicitly trust it before Power BI's driver will accept the connection.

On the same Overview page, click Download CA Certificate
Rename it
Double-click and Install Certificate, Local Machine, Place all certificates in the following store, Trusted Root Certification Authorities and Finish.
Restart Power BI Desktop so the trust store change takes effect.

Downloading the CA certificate from the Aiven console, required to establish a trusted SSL connection.

Confirmation that the Aiven CA certificate was successfully installed into the Trusted Root Certification Authorities store on Windows.

Step 3 Connect Power BI to Aiven

Get Data, PostgreSQL database, Connect.
Server: your-host.aivencloud.com:port (host and port combined with a colon).
Database: defaultdb.
Expand Advanced options and set SSL mode to require (try verify-ca if you hit certificate errors).
Click OK, then in the credentials prompt choose the Database tab (not Windows) and enter your Aiven username and password.

Entering the Aiven host and port into Power BI's PostgreSQL connector, with SSL mode set to require.

The Navigator window listing the jcars_sales table after successfully connecting Power BI to the Aiven-hosted database.

Once connected, the table loads exactly like a local one Power Query, data
modeling, and DAX all work identically regardless of where the data physically lives.

Troubleshooting

"Unable to Connect" connection refused

Confirm the Aiven service status is "Running," not "Rebuilding."

"Remote certificate is invalid" SSL errors

Confirm the certificate was actually installed into Trusted Root Certification Authorities, not just downloaded.
Restart Power BI Desktop after installing the certificate.

Authentication failures

Re-copy the password directly from the Aiven console typos are the most common cause.
Confirm you selected the Database authentication tab, not Windows credentials.

Local vs Cloud: Quick Comparison

A local PostgreSQL database doesn't require SSL, since the connection never leaves your machine, while Aiven always requires SSL because the data is traveling over the internet to a cloud server. This also makes local setup slightly simpler overall, since Aiven adds the one-time certificate step.

There's also a difference when it comes to scheduled refresh. If you publish your report to the Power BI Service and want it to refresh automatically, a local database needs an On-Premises Data Gateway installed so the cloud service can reach it. Aiven, being cloud-hosted already, works directly with scheduled refresh no gateway required.

In practice, a local database is a good fit for personal projects or local testing, while a cloud database like Aiven is better suited for team projects and production dashboards that other people need to access.

Conclusion

Connecting Power BI straight to PostgreSQL whether it's sitting on your laptop
or hosted on Aiven turns a one-time spreadsheet export into a living
dashboard. The core workflow (Get Data, PostgreSQL database, enter
connection details, Navigator, Load) is identical in both cases; the only
extra step for the cloud is trusting Aiven's SSL certificate once. After that,
Power Query, data modeling, and DAX work exactly the same either way.

Understanding Relationship Schemas and Joins in Power BI (A Beginner's Guide, Using Real Kenyan Crop Data)

Joseph Okwemba — Sun, 05 Jul 2026 11:40:51 +0000

Introduction

Imagine you're a farmer in Kiambu, Kenya. You keep one notebook listing all the farmers in your cooperative, another notebook recording every harvest how much was planted, how much was yielded, and the profit made and a third notebook listing the different crop types and varieties grown in the region. Each notebook makes sense on its own, but the real value comes when you can flip between them and answer a question like: "Which county produced the most profitable potato harvest last season?"

That's exactly the problem Power BI solves with relationships and joins. This article uses a real dataset of over 300 Kenyan crop harvest records covering counties, farmers, crop types, seasons, and profits. To walk through what these terms mean, why they matter, and how to set them up. No coding background required.

What Is a "Relationship Schema"?

A schema is simply a map of your data it shows what tables you have and how they connect. Think of it like a family tree, but for spreadsheets.

Our original dataset arrived as one flat spreadsheet, with 313 rows and 19 columns every harvest record repeating the farmer's name, county, crop type, and contact number over and over again. That's fine for a small file, but it makes analysis clumsy and error-prone: if "Kiambu" is spelled two different ways anywhere in those 313 rows, Power BI will treat them as two different counties.

The fix is to split that single flat file into separate, connected tables a relationship schema. For this dataset, that means:

Fact_Harvest the core transactional table: one row per harvest, holding measurements like yield, revenue, cost, and profit.

Dim_County one row per Kenyan county (Kiambu, Meru, Nakuru, and so on).

Dim_Farmer one row per farmer, with their name and contact number.

Dim_Crop one row per crop type and variety combination (e.g., Potatoes Organic).

Dim_Season one row per growing season (Long Rains, Short Rains, etc.).

The Two Common Schema Shapes

Star Schema
Picture one central table Fact_Harvest with the smaller reference tables branching out from it like the points of a star: Dim_County, Dim_Farmer, Dim_Crop, and Dim_Season. Each "point" table connects directly to the central fact table through an ID column.

This is the layout used below, and it's the one Power BI performs best with.

Power BI Model view showing the star schema built from this dataset

Power BI Model view showing Fact_Harvest connected to Dim_County, Dim_Farmer, Dim_Croptype, and Dim_Season

Snowflake Schema
This is a star schema broken into smaller pieces. For example, Dim_Crop could itself be split further into a separate Dim_CropCategory table. It's more normalized but adds complexity best avoided unless there's a clear need.

What Are "Joins" (Relationships) in Power BI?

In everyday database language, these connections are often called "joins." In Power BI specifically, they're called relationships, and you build them visually no code required.

A relationship links a column in one table to a matching column in another table. In our dataset: the CountyID column in Fact_Harvest links to the CountyID column in Dim_County. The FarmerID in Fact_Harvest links to FarmerID in Dim_Farmer. And so on for CropID and SeasonID.

Types of Relationships

This sounds technical, but it just describes how many matching records exist on each side.

One-to-Many (most common): One county (Kiambu) can appear against many harvest records in Fact_Harvest, but each harvest record belongs to only one county. This is exactly the relationship between Dim_County and Fact_Harvest in our model.
One-to-One: Each record in Table A matches exactly one record in Table B (rare not present in this dataset).
Many-to-Many: Records on both sides can repeat. Power BI supports this, but it should be used carefully since it can make totals harder to trust.

The "Edit relationship" dialog in Power BI, showing the relationship between Fact_Harvest and Dim_County

Power BI Edit relationship dialog showing Fact_Harvest joined to Dim_County on CountyID, cardinality Many to one

Notice the cardinality is set to "Many to one (*:1)" many harvest records point to one county and the cross-filter direction is "Single," meaning filtering by county correctly filters the harvest records, not the other way around.

Cross-Filter Direction

This determines which table "controls" the filtering of the other. In our model, filtering a report by County (say, selecting only "Meru") will correctly filter Fact_Harvest down to only Meru's harvests. Power BI sets this automatically for star schemas, so as a beginner you rarely need to change it.

How to Create a Relationship in Power BI

Load the split tables into Power BI Desktop: Fact_Harvest.csv, Dim_County.csv, Dim_Farmer.csv, Dim_Crop.csv, and Dim_Season.csv.
Click the Model view icon on the left-hand sidebar (it looks like three connected boxes).
You'll see each table laid out as a card with its column names listed inside.
Click and drag CountyID from Fact_Harvest directly onto CountyID in Dim_County.
Power BI draws a line between them and automatically detects the relationship type.
Double-click that line to open Edit relationship, where you can confirm the cardinality and filter direction, exactly as shown in Screenshot 7 above.
Repeat for FarmerID, CropID, and SeasonID.

Why Does This Matter?

If CountyID in Fact_Harvest isn't properly connected to Dim_County, Power BI has no way of knowing that "CountyID 3" refers to Kiambu. This leads to:

Profit totals that don't split correctly by county in a chart
Slicers for crop type or season that don't filter anything
Reports that look fine but are quietly giving wrong answers

Setting up a clean star schema Fact_Harvest at the center, with Dim_County, Dim_Farmer, Dim_Crop, and Dim_Season branching out is like organizing farm notebooks so that when you ask "which county had the highest average profit per acre this season," every notebook can be consulted together and give one accurate, trustworthy answer.

Conclusion

Relationships and schemas might sound like advanced database jargon, but at their core they're about connecting related pieces of information so they can talk to each other. Using this real dataset of 500 Kenyan crop harvests, we went from one unwieldy flat file to a clean star schema and once that connection is in place, questions like "which crop is most profitable in the Short Rains season" become a simple drag-and-drop report instead of a manual spreadsheet headache.

How I Learned Excel in My First Week Of Data Science - Real-World Uses Explained

Joseph Okwemba — Sat, 06 Jun 2026 09:34:30 +0000

When I started learning Data Science, I thought the first thing I would learn was Python. I expected to spend my first week writing code and learning about machine learning.

Instead, our instructor introduced us to Excel.

At first, I didn't understand why we were starting with a spreadsheet. I had always thought Excel was mainly used to enter numbers and create simple tables. But after working with an employee dataset in class, I realized there was much more to it.

As we practiced sorting data, filtering records, and using formulas like SUM(), AVERAGE(), and COUNT(), I began to see why Excel is still an important tool for data analysts. By the end of the week, I understood that before you can analyze data with programming languages, you first need to know how to organize and understand the data itself.

Looking back, starting with Excel made a lot more sense than I expected. It gave me a strong foundation and made me feel more confident about the next stages of my Data Science journey.

Real-World Uses of Excel

Excel is widely used across industries for handling and analyzing data. Some of the most common uses include:

Business Analysis - Tracking sales and identifying trend
Accounting and Budgeting - Managing Expenses, Profits and Financial reports
Marketing Analysis - Measuring campaigns performance and customer behavior
Data Entry and Management - organizing large datasets efficiently

Businesses rely on Excel because it helps turn raw data into meaningful insights for decision making.

Key Excel Features I Learned

In my first week, I explored several important Excel Features that help with data organization and analysis:

Excel Interface Overview - I first explored how Excel is organized, including Ribbon, Worksheets, Cell, Row, Columns, and formula bar. this helped me understand how to navigate the tool before working with data.

Excel interface

Excel Cell indicated in yellow

Excel Row indicated in Green A2

excel column indicated in yellow

Data Sorting - Organizing data by numbers, Text and Dates

Filtering - Showing only relevant data based on condition

Data Validation - Ensuring accurate and consistent data entry

Freeze Panes - Keeping header Visible while scrolling through large datasets.

These features make working with data much easier, faster and more structured.

Basic Excel Functions I learned

I was also introduced to some basic Excel functions used in Data Analysis.
Aggregate Functions

-SUM - Add all values in a range
Sum salary of employee =sum(E2:E870)

-AVERAGE - Calculate the mean of a dataset
Finding Average salary we use =Average(E2:E870)

-COUNT - Counts numerical entries in a dataset
Count employee with ages recorded =count(G2:G870)

Conditional Functions

-SUMIF() and SUMIFS() - Add values that meets one or more conditions
-COUNTIF() and COUNTIFS() - Counts records that match specific criteria
-AVERAGEIF() and AVERAGEIFS() - Calculate average based on selected conditions

These functions help analysts answer business questions such as total sale by product, customer count by region, or average performance across departments.

OTHER FUNCTIONS
I also learned about:
-TEXT FUNCTIONS such as LEFT(), RIGHT(), LEN(), and CONCAT()
-DATE and TIME FUNCTIONS such as TODAY(), NOW(), DAY(), MONTH(), YEAR()
These functions help analyst clean data, identify Trends, and create meaningful reports.

What this changed for me

Before this week, I thought Excel was just a tool for entering data and doing simple calculations. After working with an employee dataset and practicing different formulas, I realized how much you can learn from well-organized data. Even simple tasks like sorting, filtering, and calculating totals became much easier than doing them manually.

The biggest lesson for me wasn't just learning new formulas—it was understanding the importance of organizing data before trying to analyze it. That foundation will be useful as I continue learning Data Science.

Final Thoughts

Although this was only my first week, I now understand why many Data Science and Data Analytics courses begin with Excel. It has helped me build confidence in working with datasets and understand the basics before moving on to programming.

My next goal is to learn more advanced Excel features like Tables, XLOOKUP, and Dashboards before continuing with Python and data visualization. I'm excited to keep learning and see how these skills connect as I progress.

What was your first experience with Excel like?
If you're learning Data Analytics or already working in the field, I'd love to hear about your experience and any tips you have for someone just getting started.

Schemas and Data Modeling in Power Bi

Joseph Okwemba — Sun, 01 Feb 2026 16:49:07 +0000

The blue print for High-perfomance Analytics
Effective data modelling is foundation of reliable analytics. Regardless of how advanced your visualisation are, a poor model leads to slow reports and inaccurate insightst. This article explore the esseential architecture of Power BI: Schemas, Tables, and Relationships.

The core Building Blocks

Power BI uses the Vertipaq in memory engine, Which thrive on structred data. To leverage this, you must categorize your table into two roles:

Fact Tables: These store quantitative events (e.g., sales, Temparature rreading, or Tickets). They are long "long," containing millions of rows with numeric values to be aggregated.
DImension Table: These provide context (e.g.,Date, Product, or Geography). They are "wide," containing descriptive attribute used for slicing and dicing.

Choosing The Roght Schema

The Star Scheme (Best Practise)

In the star schema, A central FAct table is surrounded by Dimension tables.

-Why it works: it's the most effecient for POwer BI. It minimize the number of "joins" the engine must perform, resulting in lighting-fast calculations.

-The verdict: Always aim for this.

The Snowflake Schema

A snowflake shema occurs when dimensions tables are normlized(e.g., a product table links to a category table).

The Downside:While it saves space, It forces Power BI to perform multiple joins, which can drag down performance and make DAX measure much harder to write.

The Power BI Audit Checklist

Feature	Best Practice	Red Flag (Avoid)
Relationship	One-to-Many (1 : *)	Many-to-Many (* : *)
Filter Direction	Single (Dim → Fact)	Bi-directional (Both)
Schema Type	Star Schema	Flat / Wide Tables
Granularity	Consistent across tables	Mixed levels of detail

Common Pitfalls and How to Fix Them

Problem "My totals are wrong when i use a specific slicer."

The Fix:Check your Relationship Direction. Ensure your dimension id filtering the fact table, not the other way round.

Problem: :Power BI says there is a 'circular Dependancy'"

The Fix: Thisusually happens with Bi-directional filters. Change your cross-filter to "single" to restore a logical data flow.

Why This Matters for Your Business

Perfomance: Well-modeled load in milliseconds, even with millions of row.
Accuracy A star schema prevent "double-counting" and ensure DAX logic remains simpleand predictable.

3.Self-Service: when the model is clean, non-technical users can build their own charts without breaking the data.

Visual Aid Recommendation

Introduction to Micrososft Excel for Data Analytics

Joseph Okwemba — Sat, 24 Jan 2026 17:31:59 +0000

Microsoft excel is one of the easiest tool to use when starting with data analytics. It helps to organise data, perfom simple analysis, and present result clearly. In this article, I explain how excel can be used for basic data analysis using simple examples.

Opening and viewing data on excel

📸 Screenshot 1: Excel workbook with dataset opened
The first step is data analysis using excel is opening dataset. Once the file is opened, the data appears in the raw and columns. Each column represesnts a type of information, such as names, location, or values.
Having data well arranged make it easier to work and analyze.

Understanding Raw, Columns, and Cells

📸 Screenshot 2: Excel sheet showing column headers and rows
Each box in excel is called a cell. Row run horizontally, while column run vertically. column headers help identify what type of data is stored in each colum.
This structure allow data to be organized clearly.
Example of rows, columns, and a selected cell in Excel

Sorting Data

📸 Screenshot 3: Sort option selected from the Data tab
Sorting is used to arrange data in a specific order. For example, Data can be sorted alphabetically or Numeric.
Sorting helps quickly identify highest, Lowest, or grouped values.
Data sorted in Excel to organize information more clearly.

Filtering Data

📸 Screenshot 4: Filter applied to a column
Filtering allows users to display only specific information. For example filtering can be used to show data from one location only.

this help focus on relevant data without deleting anything.
Filtering data in Excel to display only specific records.

Using Basic Formulas

📸 Screenshot 5: Formula bar showing SUM or COUNTIF
Excel formulas help perform calculations automatically. Common formulas include:
SUM for adding values.
COUNT or COUNTIF if for counting entries
AVERAGE for calculating the mean.
Formulas makes analysis faster and reduce manual errors.
Click an empty cell

Enter a formula such as:

=COUNTIF(P:P,"Nairobi")

=COUNTIFS(P:P,"Nairobi",O:O,"Intern")

Using an Excel formula to analyze data

Creating Charts

📸 Screenshot 6: Bar or column chart created from data
Chart helps turn data into visual information. Excel allow users to create charts such as bar chart and pie charts.
charts makes it eaiser to compare values and understand trends.
Chart created in Excel to visually represent the data.

Formatting Data

📸 Screenshot 7: Formatted table with bold headers and colors
Formatting helps improve readability. This include bolding headers, adjusting column width, and applying number formatts.
Well-Formatted data is easier to understand and looks more professional.

Formatted dataset to improve readability and presentation.

Conclusion

📸 Optional Screenshot: Final dataset or chart
Microsoft excel is a useful tool for basic data analytics. It help organize data, Perfomr simple analysis, and present result clearl. For beginners, Excel is good strating point before learning advance data analyiss.

Understanding Git and GitHub: A Beginners Guide

Joseph Okwemba — Sun, 18 Jan 2026 06:06:06 +0000

What is Git?

Git is version control system that helps you keep track of changes in made to your code. Instead of saving many copies of the same project, Git record each changes and store it as part of the project history.
with git you can always go back to an earlier version of your code if something goes wrong. Git works locally on your computer and does not need internet to track changes.

Why version control is important

version control is important for several reasons

it helps you avoid loosing your work
You can see what changes were made and when
It allows Multiple people to work on the same project
You can easily fix mistakes by going back to previous version In real software project, Version is necessary because code change is always changing.

Git and GitHub Difference

Git and GitHub are not the same thing

Git is the tool that track and changes your computer
GitHub is an online platform where you store and share your Git project you use Git commands to send your code to GitHub or get code from GitHub

Seting up Git on your computer

Before using Git, you need to make sure it is installed and configured.

Check Git version

To confirm Git is installed:

git --version

Configure your Name and Email

Git uses this information to track who made changes.

git config --global user.name "joseph" 
git config --global user.email "joseph@gmail.com"

To confirm the configuration :

git config --global --list

Setting up HHS

SSH allow you to connect to GitHub securely without entering your password every time.

Generate HHS key

ssh--keygen -t ed25519 -C joseph@gmail.com"

Press enter to accept the default file location.

Start the HHS agent

eval "$(ssh-agent -s)"

Add SSH key

ssh-add ~/.ssh/id_ed25519

Copy the public key

cat ~/.ssh/id_ed25519.pub

Copy the output and add it to GitHub → setting → SHH → and GPG key.

How to push GitHub code

First, open your project in the terminal

Step one

Step 1: Initialise Git

git init

Step 2: Check file status

git status

Step 3: Add File

git add.

step 4: Commit changes

git commit -m "My first commit"

Step 5 : Connect to GitHub Repository

git remote add origin https://github.com/username/repository-name.git

Step 6: Push the code

git push -u origin main

your code will now appear on GitHub

How to pull code from GitHub

pulling means getting the latest version of the project from giHub

git pull origin main

this is usefully when working others or when switching devices

How to track changes using Git

To see changed files

git status

To see what exactly changed.

git diff

To view commit history

git log

This commands help you understand how your project has changed over time

conclusion

Git is very important tool for developers. It helps you manage your code, track changes, and work with others.

Luxdev Markdown Language Class

Joseph Okwemba — Tue, 13 Jan 2026 11:00:02 +0000

How to right a markdown language

this is the first markdown language the students have learned and they can now write an article on dev.to

The first things students learned

the student have learned how to write a heading

lastly but not leat

she/he tolld me that they love tech and i responded i enjoy tech too.
-Morning class
-Evening class

*name = "kaleb"

Here is where you wiil find me; visit my linkedIn account on