DEV Community: Okall Omondi

Connecting Power BI to SQL Databases: Local SQL Server and Aiven PostgreSQL (SSL) Using DBeaver

Okall Omondi — Sun, 12 Jul 2026 08:55:35 +0000

If you're learning Power BI, one of the first practical skills you need is connecting it to a database. Most tutorials stop after showing a simple SQL Server connection, but real projects are rarely that simple. Sometimes the database is running on your own machine. Other times it lives in the cloud and requires SSL certificates before Power BI will even talk to it.

In this guide, I'll walk through both scenarios:

Connecting Power BI to a local SQL Server.
Connecting Power BI to an Aiven PostgreSQL database using SSL, with DBeaver helping us verify the connection first.

The goal is not just to make the connection work, but to understand why each step matters so you can troubleshoot issues later.

Before You Start

You should have:

Power BI Desktop installed.
PostreSQL Server installed locally.
DBeaver installed.

An Aiven PostgreSQL service powered on and running.

The SSL certificate file downloaded from Aiven, usually named ca.pem.

DBeaver is useful because it lets you confirm that the database itself is reachable before involving Power BI. If DBeaver cannot connect, Power BI almost certainly won't either.

Part 1: Connecting Power BI to a Local SQL Server

Let's start with the easier case.

Step 1: Find Your SQL Server Name

Open SQL Server Management Studio (SSMS) if you have it installed, or check the SQL Server services list.

Common server names look like:

localhost
localhost\SQLEXPRESS
DESKTOP-ABC123\SQLEXPRESS

If you're using SQL Server Express, the \SQLEXPRESS part is often required.

Step 2: Open Power BI

Launch Power BI Desktop.
Select Get Data → SQL Server.

Step 3: Enter Connection Details

Fill in:

Field	Example
Server	127.0.0.1:0000 (localhost IP Adress:port_number)
Database	postres in my case or leave blank to see all databases, or enter a specific database name.

Choose Import mode for beginners. It loads data into Power BI and is easier to work with than DirectQuery.

Click OK.

Step 4: Authenticate

Power BI will ask how to log in.

For a local development machine, Windows authentication is usually the simplest option because it uses your current Windows account.

If your SQL Server uses SQL logins instead, choose Database and enter the username and password.

Step 5: Select Tables

After connecting, Power BI shows the available tables and views.

Select the tables you need and click Load.

At this point, your local SQL Server is connected and ready for reporting.

Part 2: Verify Aiven PostgreSQL in DBeaver First

Cloud databases introduce one extra requirement: SSL encryption.

Aiven provides PostgreSQL over an encrypted connection, which means Power BI needs the same certificate information that DBeaver uses.

Step 1: Create the Connection in DBeaver

Open DBeaver.
Click New Database Connection.
Choose PostgreSQL.

Enter the connection details from your Aiven dashboard:

Field	Example
Host	pg-12345.aivencloud.com
Port	12345
Database	defaultdb
User	Your Aiven username
Password	Your Aiven password

Step 2: Configure SSL

From the Aiven dashboard, download the CA certificate

On your windows PC launch Microsoft Management Console by typing mmc in the search console, and add the certificate snap-in by clicking on File --> Add/Remove Snap-in.

Restart Power BI if you had it open to ensure the SSL certificate is loaded.

Part 3: Connect Power BI to Aiven PostgreSQL with SSL

Step 1: Install the PostgreSQL Connector if Needed

Recent versions of Power BI already include it. If Power BI prompts you to install the PostgreSQL driver, follow the prompt and restart Power BI.

Step 2: Start the Connection

Select Get Data → PostgreSQL database.

Enter the server as host:port from the aiven connection information section.

Example

pg-12345.aivencloud.com:12345

Enter the database name, such as defaultdb.

Step 3: Configure SSL in Power BI

When the authentication window appears:

Choose Database authentication.
Enter your Aiven username and password.
Expand Advanced options if available.
Set SSL Mode to verify-full.
Browse to the same ca.pem certificate used in DBeaver.

Click Connect.

Step 4: Load the Data

Power BI will display the PostgreSQL schemas and tables.

Select the tables you need and click Load.

The first connection may take a little longer because Power BI is validating the SSL certificate.

Why Use DBeaver Before Power BI?

Beginners often try to troubleshoot everything inside Power BI, which can be frustrating because the error messages are not always specific.

DBeaver gives clearer feedback:

If the host or port is wrong, DBeaver tells you immediately.
If the password is incorrect, you'll see an authentication error.
If the SSL certificate is missing or invalid, DBeaver points directly to the SSL problem.

Once DBeaver connects successfully, Power BI usually becomes a straightforward configuration task.

Common Mistakes Beginners Run Into

Using the wrong SQL Server instance name

localhost and localhost\SQLEXPRESS are not the same thing. If one fails, check which instance is actually running. If localhost is not working, use the localhost IP address 127.0.0.1

Forgetting the port number for PostgreSQL

Aiven does not always use the default PostgreSQL port 5432. Use the exact port shown in the Aiven dashboard.

Skipping SSL configuration

Aiven requires encrypted connections. If you omit the certificate or use the wrong SSL mode, Power BI will refuse the connection.

Mixing up authentication methods

Local SQL Server often uses Windows authentication, while Aiven PostgreSQL uses database username and password authentication. Choosing the wrong method leads to login failures even when the credentials are correct.

Key Takeaways

By the end of this setup, you should be able to connect Power BI to two very different database environments:

Local SQL Server using Windows or SQL authentication.
Aiven PostgreSQL using SSL certificates and database credentials.

The local SQL Server connection is mostly about identifying the correct server instance. The Aiven PostgreSQL connection is mostly about getting SSL configured correctly. DBeaver acts as a useful checkpoint before you bring Power BI into the picture.

Once both connections are working, the experience inside Power BI is surprisingly similar. You choose tables, load the data, and start building reports. The difficult part is usually the first successful connection. After that, adding new tables or refreshing data becomes much easier because the connection settings are already saved.

If you're practicing, I recommend testing the local SQL Server connection first, then moving to Aiven PostgreSQL. It builds confidence because you learn the Power BI workflow before dealing with SSL and cloud networking details.

Understanding Data Modeling, Schemas, Relationships, and Joins: A Beginner's Guide

Okall Omondi — Sat, 11 Jul 2026 03:33:53 +0000

When people first start learning SQL, they usually focus on writing queries. You learn SELECT, WHERE, GROUP BY, and maybe even a few aggregate functions. Everything seems manageable until you come across multiple tables. Suddenly, you're asking questions like:

Why is the customer information in a different table? How are these tables connected? And what exactly is a JOIN?

If you've been wondering the same thing, you're not alone.

Before you can write meaningful SQL queries, you need to understand how data is organized. That's where data modeling, schemas, relationships, and joins come in. These concepts work together, and once they click, databases become much easier to understand.

Let's go through them one step at a time.

What Is Data Modeling?

Think about building a house.

Before anyone starts laying bricks, an architect creates a blueprint. The blueprint shows where the rooms go, how they're connected, and where everything belongs.

Data modeling works the same way.

A data model is the blueprint of a database. It defines what information needs to be stored, how it should be organized, and how different pieces of information relate to one another.

Instead of rooms and doors, a data model deals with things like:

Customers
Products
Orders
Employees
Payments

Rather than storing everything in one giant table, the data model separates information into logical groups.

For example, imagine you're designing a database for an online store.

Instead of creating one huge table containing customer details, product information, payment records, and shipping addresses, you would divide the information into smaller tables.

You might have:

Customers
Products
Orders
Order Items
Payments

Each table has a clear purpose. This makes the database easier to maintain and reduces duplicate data.

What Is a Schema?

Once you've designed your data model, the next step is creating the actual structure inside the database.

This structure is called a schema.

A schema describes how the database is organized. It contains the tables, columns, data types, constraints, and relationships.

Think of it as the database's floor plan.

For example, a Customers table might look like this:

CustomerID	Name	Email	Phone
101	Jane Doe	[jane@email.com]	555-1234

The schema defines that:

CustomerID is an integer.
Name is text.
Email stores email addresses.
Phone stores phone numbers.

It also specifies rules, such as requiring every customer to have a unique CustomerID.

Without a schema, a database would have no structure. It would simply be a collection of unorganized data.

Understanding Primary Keys

If every customer has a name, why can't we just use names to identify them?

Because names aren't always unique.

You might have two customers named John Kamau. Which one are you referring to?

This is why databases use primary keys.

A primary key is a column whose value uniquely identifies every row in a table.

For example:

CustomerID	Name
101	John Kamau
102	John Kamau

The names are identical, but the CustomerIDs are different.

This guarantees that every customer can always be identified correctly.

A good primary key should:

Never be empty
Never contain duplicate values
Stay stable over time

Almost every table in a relational database has one.

Understanding Relationships Between Tables

Databases become powerful because tables don't exist in isolation.

They're connected through relationships.

A relationship tells the database how information in one table is connected to information in another.

Imagine these two tables.

Customers

CustomerID	Name
101	Musyoka
102	Kiilu

Orders

OrderID	CustomerID	Total
5001	101	75
5002	101	120
5003	102	45

Notice something?

The Orders table contains CustomerID.

That's not a coincidence.

It links every order to the customer who placed it.

CustomerID inside the Orders table is called a foreign key.

A foreign key points to the primary key in another table.

This is how databases connect information without storing the same customer details repeatedly.

Types of Relationships

Most beginner databases use three types of relationships.

One-to-One

One record matches exactly one record.

For example:

One employee has one company ID card.

These relationships are less common.

One-to-Many

This is by far the most common relationship.

For example:

One customer places many orders.
One teacher teaches many students.
One department has many employees.

Notice that one side has a single record, while the other side can have many.

Many-to-Many

Sometimes both sides can have multiple records.

For example:

Students enroll in many courses.
Courses contain many students.

Databases usually solve this by introducing a third table called a junction table.

For example:

Students
Courses
StudentCourses

The StudentCourses table stores which student is enrolled in which course.

What Are Joins?

Now comes the question most beginners ask.

If customer information is stored in one table and order information is stored in another, how do we view them together?

The answer is JOINs.

A JOIN combines rows from two or more tables based on a related column.

Let's use the earlier example.

Customers

CustomerID	Name
101	Musyoka
102	Kiilu

Orders

OrderID	CustomerID	Total
5001	101	75

If you write a JOIN, the result becomes:

OrderID	Name	Total
5001	Musyoka	75

The database matches CustomerID in both tables and combines the related information.

This is why relationships are so important. Without them, JOINs wouldn't know how the tables are connected.

Common Types of SQL Joins

You'll encounter several types of joins as you learn SQL.

INNER JOIN

Returns only matching records.

If a customer has no orders, they won't appear in the results.

This is the join you'll probably use most often.

LEFT JOIN

Returns every record from the left table, even if there's no matching record in the right table.

This is useful when you want to find customers who haven't placed any orders yet.

RIGHT JOIN

Works like a LEFT JOIN but starts from the right table instead.

Many SQL developers rarely use it because a LEFT JOIN can usually achieve the same result by switching the table order.

FULL OUTER JOIN

Returns all records from both tables, matching where possible.

If there's no match, the missing values are shown as NULL.

Not every database system supports this join directly.

How These Concepts Fit Together

It's easy to see these as separate topics, but they're really parts of the same picture.

Here's the flow:

You create a data model to plan your database.
You implement that design using a schema.
Tables are connected through relationships using primary and foreign keys.
SQL JOINs use those relationships to retrieve data from multiple tables.

Once you understand this sequence, writing SQL queries starts to make much more sense.

Common Beginner Mistakes

Everyone makes mistakes while learning databases. Here are a few that come up often.

Putting everything into one table

It may seem simpler at first, but it quickly leads to duplicated data and maintenance headaches.

Ignoring primary keys

Without unique identifiers, it's difficult to connect tables correctly or guarantee data accuracy.

Joining on the wrong columns

Always check that you're joining a foreign key to its corresponding primary key. Joining unrelated columns can produce incorrect or misleading results.

Memorizing joins without understanding relationships

Many beginners try to memorize SQL syntax first. It's usually more effective to understand how the tables relate to one another. Once the relationships are clear, choosing the correct join becomes much easier.

Practical Tips for Learning

If you're just getting started, don't rush into complex database designs.

Instead:

Draw your tables on paper before creating them.
Label the primary and foreign keys.
Practice identifying one-to-one, one-to-many, and many-to-many relationships.
Build small databases, such as a library, school, or online store.
Write simple JOIN queries and predict the results before running them.

This approach helps you understand the logic behind the database rather than simply memorizing SQL syntax.

Key Takeaways

A data model is the blueprint that plans how data should be organized.
A schema defines the structure of the database, including tables and columns.
A primary key uniquely identifies each record in a table.
A foreign key connects one table to another.
Relationships describe how tables are linked.
JOINs combine data from related tables to answer real-world questions.

Final Thoughts

Data modeling, schemas, relationships, and joins can seem like separate topics when you're first learning databases. In reality, they're different pieces of the same foundation.

Once you understand how data is organized and why tables are connected the way they are, SQL becomes far less intimidating. Queries stop feeling like random commands and start feeling like conversations with the database.

If you're learning SQL, spend time understanding the structure before chasing more advanced syntax. A solid grasp of these fundamentals will make every future topic—from writing reports to designing databases—much easier to learn.

How Excel is Used in Real-World Data Analysis

Okall Omondi — Sat, 06 Jun 2026 06:35:07 +0000

What is Excel?

Well, the simplest answer? Excel is a tool.
You might ask, which tool?
Think of it as a bookshelf, or the wardrobe in your house.
What does the bookshelf and the wardrobe have in common? Partitions.
Most bookshelves have identifiers to help you locate the exact partition where the book is stored. The last library I visited had letters and numbers. The letters were written horizontally (from left to right) across the bookshelf and the numbers were written vertically (from top to bottom). I could easily identify the exact partition by using the letter and number combination, for example the first partition in the bookshelf was A1. This is the exact way Excel is organized with letters and numbers.

The partition A1 in the bookshelf represents a cell in Excel. The horizontal letters (from left to right) of the bookshelf represent a row in an Excel, and the vertical numbers from top to bottom in a bookshelf represent a column in an Excel. A single bookshelf represents a worksheet in Excel and several bookshelves represent a workbook in Excel. Simple right?

How Excel is used in real world Data Analysis.

You have probably heard of the phrase “data is the new oil” right? What makes it the new oil? Apart from it being the new “money printer”, data is the “know it all”, it is the real story teller. However, before data can tell you stories and turn into your cash machine, you must first make sense of it, to understand what happened and why it happened. The process of understanding what happened and why it happened is called Data Analysis. By understanding what happened and why it happened, you can easily figure out what will happen (Data Science). Excel is fundamental in the understanding phase, and in the figuring out phase, because it acts as the story for the data to be understood and figured out.

Which areas do we then use Excel in the real world? Imagine this scenario, you are running a small refurbished laptops resale shop, every laptop you sell with a small profit on top of the purchase price, and making profits in your head. However, at the end of the month you find out you are short of cash to pay bills forcing you to borrow money from friends, yet, you did not sell any laptop at a loss. What do you do? This is where Excel comes in. Excel can help you to store your sales data, organize the data and clean the data. Excel also gives you formulas such as SUM, AVERAGE, MIN, MAX, COUNT and functions such as IF, SUMIF, AVERAGEIFS, VLOOKUP which you can use to do calculations and create logics to see how your business is doing. Excel also has pivot table functionality which is helpful in summarizing data quickly. You can also draw insights from the data using the various visualization tools in Excel such as charts and graphs to help you see where you are losing money and make informed decisions.

How has learning Excel changed how I see data?

Learning Excel and understanding its capabilities has been an eye opener, and I now view every instance of my life as a data point to be tracked and understood. This past week I have started tracking simple things in my life like the time I go to bed, the time I wake up, how long I take on a task, and so much more. I will be using this data to improve and be a better person.

Diving into Bug Bounty Hunting: My Journey Begins with Resources and Tips for Beginners

Okall Omondi — Mon, 20 Jan 2025 07:31:54 +0000

After taking the much needed break and rest in the festive seasons, a period in which I had much reflection on my career and life goals, I dove into cybersecurity with a focus on bug bounty hunting. I have been contemplating on this for a while, ever since I came across the 0 to 100K in Bug Bounty Year One thread on X by @Rhynorater. I used ChatGPT to create a bug bounty hunting road map and begun on a resource finding mission. I will write weekly posts documenting my journey and milestones into becoming a bug bounty hunter. This first article will document some of the useful resources that those aspiring to become bounty hunters can use. The resources range from ChatGPT, YouTube Channels, websites, GitHub Accounts, X Accounts, and SubReddits.

Remember

When starting out, always be aware of the shiny object syndrome and analysis paralysis. Shiny object syndrome (SOS) is the tendency to be distracted by new ideas (courses) that are often at the expense of your current goal, which is becoming a bug bounty hunter in this case. Analysis paralysis is that point when you cannot decide because you are overwhelmed with courses/ materials and you are not sure of which one to use.

Another thing I will recommend while starting is developing a good note taking system. There are various Note Taking Apps you can use, such as Notion, Evernote, Microsoft OneNote, Joplin, Obsidian and many others. When choosing a note app, there are factors you have to put into consideration. Some features I look for in a note taking app are cost, ease of use, organizational features, and cross device access. My choice is Obsidian. Having a good note taking system is like having a second brain. Good notes always come in handy along your career trajectory, and they are what will catapult you to the next level.

Resources

Now, back to resources. I will focus on the free resources here, as I believe most of the required knowledge is free if you have the patience and time to go after it.

ChatGPT: The first and key resource I found helpful is ChatGPT. You are not restricted to this. You can use GPTs of your choice like copilot, Gemini or any other you may prefer. I used ChatGPT to brainstorm and be sure that I really wanted to go into bug bounty hunting. After being sure I wanted this, I prompted it to come up with a learning path and a study plan for bug bounty hunting. There are unlimited ways you can use GPTs from generating explanations, examples and analogies, motivation, role-plays, questions, mind maps, mental associations and much more. You can also use them to engineer effective prompts which you can then re-use to get exact answers you want.
YouTube Channels: Some of the YouTube channels I have found to be amazing are:
NahamSec - Has detailed bug bounty tutorials and live hacking sessions.
STÖK - Offers practical bug hunting techniques and methodologies.
InsiderPhD - Insider PHD is great for beginners, provides structured learning content. This is the channel I have been using, mostly. I highly recommend it if you are starting out.
Jason Haddix - Industry veteran sharing advanced techniques and methodologies
TCM Security - Comprehensive ethical hacking and penetration testing tutorials
HackerSploit - Detailed tutorials on various security tools and techniques
LiveOverflow - In-depth technical content about security research and exploitation
PortSwigger - Official channel for Burp Suite with web security tutorials
Learning Websites:
PortSwigger Web Security Academy - Free, comprehensive web security training. I recommend PortSwigger Academy if you are starting out.
Bugcrowd University - Free educational resources for bug bounty hunters. Bugcrowd also provides a platform for the Vulnerability Disclosure Program (VDP) and Bug Bounty Programs (BBP). It is a good place to start your bug bounty hunting by creating an account on their platform.
TryHackMe - Interactive cybersecurity training platform with guided learning paths. Try Hack Me is fully free, but it has some amazing labs that provide hands-on experience.
HackTheBox - Platform offering realistic penetration testing labs and challenges
HackerOne Hacktivity - Public bug reports to learn from real-world examples. HackerOne also provides a platform for the Vulnerability Disclosure Program (VDP) and Bug Bounty Programs (BBP).
PentesterLab - Hands-on exercises for web penetration testing
VulnHub - Provides materials to gain practical cybersecurity experience
OWASP - Comprehensive resource for web application security knowledge
GitHub Accounts:
BugBounty: This GitHub account has most of the resources you need in becoming a bounty hunter.
X Accounts: There is an amazing Bug Bounty Community on X with lots of accounts dedicated to sharing information on Bug Bounty Hunting. I will not recommend any account in particular but if you are on X, search for a bug bounty and you’ll find lots of accounts with posts that will inspire you.
SubReddits: Reddit is another Social Media platform I like. There is an amazing community here with amazing people who are always ready to jump in and assist with any query you might be having. Just be sure to search first if the question was asked and answered before. Also, be sure to ask on the right subreddit. The subreddit for bug bounty hunters which is active, and I’d recommend you join is r/bugbounty/.