DEV Community: Benjamin Ochieng

How I Connected a Cloud PostgreSQL Database to DBeaver and Power BI — And What Nobody Tells You About the Process

Benjamin Ochieng — Sun, 05 Jul 2026 12:19:01 +0000

A step-by-step account of connecting Aiven cloud to a local database tool and into Power BI — with the frustrations, the certificates, and the small victories included.

The Problem Nobody Warns You About

You have data. It lives somewhere in the cloud — a PostgreSQL service, spinning quietly on a server you have never physically touched. You want to query it, clean it, visualise it. Simple enough in theory.

Then you open your laptop.

Within ten minutes you are staring at connection errors, downloading files called things like ca.pem, and wondering why a database tutorial never mentioned certificates once. This article is my attempt to fix that. Here is the full road — every step, every reason, and every thing that tripped me up — from creating a PostgreSQL service on Aiven to pulling live data into Power BI.

What You Will Need

Before starting, get these ready:

An Aiven account (free tier works fine to follow along)
DBeaver Community Edition installed on your computer
Power BI Desktop installed
Basic familiarity with what a database is — you do not need to know SQL yet

Step 1 — Creating a PostgreSQL Service on Aiven

Aiven is a managed cloud database platform. Think of it as renting a database that lives on a server somewhere — you do not manage the server, you just use the database. For data analysts who want to work with live, cloud-hosted data without setting up their own server infrastructure, it is a reasonable starting point.

When you log into Aiven for the first time, you land on a dashboard that asks what kind of service you want to create. Select PostgreSQL. You will be prompted to:

Choose a cloud provider — Google Cloud, AWS, or Azure all work. For a free-tier test, the region does not matter much, but pick one geographically close to you for better connection speed.
Select a plan — the free tier gives you a single-node PostgreSQL instance, which is enough for learning and small datasets.
Name your service — pick something meaningful. I used jcars to match the dataset I was working with.

After clicking Create Service, Aiven starts building your database. This takes between one and five minutes. The status indicator on your dashboard will show Rebuilding, then Running. Do not try to connect during the Rebuilding phase — the service is not ready and the connection will fail.

Wait until it says Running before moving to the next step.

One thing worth knowing: Aiven generates connection credentials automatically when the service is created. You will need the host, port, database name, username, and password from the service overview page. Keep that page open — you will be copying from it several times.

Step 2 — Connecting the Aiven Service to DBeaver

DBeaver is a free database management tool. It lets you connect to databases, run SQL queries, view table structures, and import data — all from a desktop interface. If you have never used it before, think of it as the Excel of database tools: not the most powerful option available, but accessible and genuinely useful.

To connect DBeaver to your Aiven PostgreSQL service:

Open DBeaver and click the plug icon in the top left (or go to Database → New Database Connection)
Search for PostgreSQL and select it
Click Next

You will see a connection form asking for several fields. Fill them in directly from your Aiven service overview page:

Field	Where to find it in Aiven
Host	Listed as "Host" in the Connection Information panel
Port	Listed next to the host, typically `20025` for Aiven PostgreSQL
Database	Usually `defaultdb` on Aiven free tier
Username	Listed as "User" in the Connection Information panel
Password	Click "Show password" in Aiven to reveal it

After filling these in, click Test Connection before clicking Finish. If the test succeeds, you will see a green confirmation message. If it fails, the most common cause at this stage is a firewall or SSL issue — which brings us to the certificate step.

Do not skip the connection test. A failed test here is much easier to debug than a failed connection three steps later when other things have changed.

Step 3 — Importing a Table into DBeaver

Once connected, your Aiven database appears in the DBeaver sidebar as a tree structure. You will see your database name, then schemas, then tables. On a fresh Aiven service, the public schema exists but contains no tables.

To import data from your computer into DBeaver:

Right-click on the public schema in the sidebar
Select Import Data
Choose your source format — CSV is the most common
Browse to your file and select it
DBeaver will preview the data and attempt to map columns to data types automatically

Pay attention to the data type mapping step. DBeaver will make educated guesses — a column of dates might be read as text, or a numeric column with occasional blanks might be flagged as incompatible. Correct any obvious errors before proceeding.

Click Import and wait. For larger files this can take a minute or two. When finished, right-click the public schema and select Refresh — your new table should appear in the tree.

Step 4 — Confirming the Import Worked

To confirm everything landed correctly, right-click your table and select View Data. DBeaver runs a SELECT * query against the table and shows you the results in a spreadsheet-style grid. Scroll through the first few rows and check:

Column names match what you expected
Row count looks right
Data types are sensible — dates look like dates, numbers look like numbers

If something looks wrong here, it is better to re-import with corrected settings than to fix it downstream in Power BI or Power Query. Catching data problems close to the source saves time.

Step 5 — Downloading the SSL Certificate from Aiven

Here is where most tutorials go quiet. They tell you to connect Power BI to PostgreSQL, show a screenshot of the connection dialog, and move on. What they skip is the certificate.

Aiven requires SSL connections. That is not optional — it is a security requirement of the platform. Power BI's PostgreSQL connector supports SSL, but it needs the certificate to verify the connection is legitimate. Without it, the connection either fails outright or connects in an insecure mode that Aiven blocks.

To get the certificate:

Open your Aiven service overview page
Scroll down to the Connection Information section
Find CA Certificate and click Download

You will receive a file called ca.pem. Save it somewhere you can find it — a dedicated folder for your project is a sensible choice. You will need to point Power BI to this file during setup.

Step 6 — Importing the Certificate into Your System

Downloading the certificate is one thing. Making your system trust it is another.

From your system's certificate manager — on Windows, search for Manage user certificates in the Start menu — navigate to Trusted Root Certification Authorities, right-click, and select Import.

Walk through the import wizard and point it to your ca.pem file. Accept the default options unless you have a specific reason to change them.

Why does this matter? When Power BI connects to Aiven, it performs an SSL handshake — a verification process where your computer and the Aiven server confirm they trust each other. If your system does not have the Aiven CA certificate in its trusted store, that handshake fails, and the connection is refused before it even starts.

I am not fully certain whether this step is strictly required on all operating systems and Power BI versions — behaviour may vary. If your connection works without it in Step 7, you can skip this step. If the connection fails with an SSL error, come back here first.

Step 7 — Connecting Power BI to Your Aiven PostgreSQL Database

With the certificate sorted, Power BI is the last piece.

Open Power BI Desktop and go to Home → Get Data → More. Search for PostgreSQL database and select it. You will see a connection dialog asking for:

Server — paste your Aiven host and port together, formatted as hostname:port. For example: pg-xxxx.aivencloud.com:20025
Database — type defaultdb (or whatever your Aiven database is named)

Click OK. Power BI will ask for your username and password — use the same credentials from your Aiven service overview.

If the connection is successful, Power BI shows a Navigator panel listing all tables in your database. Select the tables you want to load, click Load (to bring data straight in) or Transform Data (to open Power Query for cleaning first).

That is it. Your cloud database is now live inside Power BI.

What This Setup Actually Gives You

A PostgreSQL database on Aiven connected to Power BI is not just a technical exercise. It is the foundation of a proper data workflow.

Your data lives in one place — the cloud database. Any tool that can connect to PostgreSQL (DBeaver, Power BI, Python with psycopg2, or any SQL client) can read from it. When the data updates, your Power BI reports update on refresh without you having to manually replace CSV files or re-import spreadsheets.

For a data analyst building a portfolio, this kind of setup also demonstrates something that a cleaned Excel file cannot: you understand how production data environments work. Data in the real world lives in databases. It is queried, not downloaded. Being comfortable with that distinction is worth more than it might seem on a CV.

Quick Reference — Connection Details at a Glance

Tool	Key Setting	Where to Find It
DBeaver	Host, Port, Database, Username, Password	Aiven service overview → Connection Information
DBeaver	SSL mode	Set to require in the SSL tab
Power BI	Server field	`hostname:port` from Aiven (e.g. `pg-xxxx.aivencloud.com:20025`)
Power BI	Database	`defaultdb` or your named database
Certificate	CA file	Download from Aiven → Connection Information → CA Certificate

The Invisible Architecture Behind Every Great Power BI Report

Benjamin Ochieng — Mon, 29 Jun 2026 06:44:54 +0000

A practical guide to database schemas, joins, and data modelling — and why they matter for every dashboard you will ever build

Imagine you walk into a brand new supermarket. The shelves are fully stocked — thousands of products — but nothing is organised. Beverages are next to shoe polish. Rice is next to televisions. There are no labels, no sections, no logic. You could spend hours searching for a single item.

That is exactly what happens when you load messy, unstructured data into Power BI. The tool is powerful. The data is there. But without structure, the dashboard you build will be unreliable at best and misleading at worst.

This article is about the invisible architecture that sits beneath every great Power BI report — database schemas, joins, and data modelling. These are not the flashiest topics. But understanding them is what separates an analyst who builds dashboards from one who builds dashboards that are actually trusted.

First — What is Power BI?

Power BI is Microsoft's business intelligence platform. It connects to virtually any data source — Excel files, SQL databases, cloud services, APIs — and transforms that data into interactive reports and dashboards that anyone in an organisation can use to make decisions.

A sales manager in Nairobi can open a Power BI report on her phone and instantly see which product categories are underperforming. A CFO in London can filter a revenue dashboard by quarter, region, or salesperson in seconds. None of that is possible, though, if the data feeding those visuals is disorganised.

That is where schemas, joins, and data modelling come in.

The Blueprint: Database Schemas

Before any data enters Power BI, it lives somewhere — in a database, a spreadsheet, or a cloud system. A database schema is the blueprint of how that data is organised. It defines the tables, the columns within those tables, the rules that govern the data, and how different tables relate to each other.

Think of it like the architectural drawing of a building. You do not start constructing walls before you have a plan. Similarly, you should not start building dashboards before understanding how your data is structured.

There are three levels of schema that every analyst should know:

Conceptual — the big picture. What are the main entities in this business? Customers, Products, Orders, Salespeople. This is the level where you ask business questions, not technical ones.

Logical — the structure. How do those entities relate? A Customer places many Orders. Each Order contains multiple Products. This level defines tables, columns, and relationships — independent of any specific software.

Physical — the implementation. This is the actual database: the SQL tables, the primary keys, the foreign keys, the constraints. This is what Power BI connects to.

When you open Power BI and click "Get Data," you are connecting to the physical layer. But the quality of your analysis depends entirely on how well the conceptual and logical layers were designed.

The Connector: Joins

Data in the real world is never stored in a single table. A business database might have a Customers table, an Orders table, a Products table, and a Salespeople table — all separate, all connected by shared keys.

A join is how you combine those tables to answer a real business question.

Consider this scenario from a Kenyan electronics distributor: the Orders table holds transaction records, but it only stores a CustomerID — not the customer's name or location. The Customers table holds names and locations but not what they purchased. To answer the question "Which region generates the most revenue?" — you need both tables talking to each other.

That is a join.

There are several types, and each serves a different purpose:

Inner Join — returns only rows where a match exists in both tables. Use this when you want clean, complete records. If a customer has never placed an order, they will not appear in the result. In Power BI, this is the most common join used in data modelling.

Left Outer Join — returns all rows from the left table, and matched rows from the right. If no match exists, the right-side columns return NULL. Use this when you want to see all records from your primary table, even if related data is missing. For example: all products, including those with no sales yet.

Right Outer Join — the mirror of the left join. All rows from the right table, matched rows from the left.

Full Outer Join — returns everything from both tables, matched or not. Useful when auditing data completeness — finding customers with no orders, or orders with no matching customer record.

In Power BI, these relationships are managed visually in the Model View. You drag a line between two tables, Power BI asks you which columns to join on, and it handles the rest. But you need to understand what type of join makes sense for your analysis — the tool does not make that decision for you.

The Foundation: Data Modelling

Data modelling is the process of deciding how all your tables connect and how your data is structured for analysis. It is the most critical step between raw data and a reliable dashboard.

There are three stages:

Conceptual modelling is where you define what exists. What are your entities — the main things your business cares about? For a retailer: Products, Customers, Orders, Regions. At this stage you are drawing boxes and arrows, not writing SQL.

Logical modelling is where you define how entities relate. A Customer places one or many Orders. An Order contains one or many Products. This stage produces a schema — the plan that developers and database administrators use to build the actual database.

Physical modelling is where you implement it. Tables get created, primary keys are assigned, foreign keys are set, and constraints are defined to protect data integrity.

Choosing the Right Design: Six Database Schema Models

Not all databases are structured the same way. The design you choose depends on your data's complexity, how it will be used, and what tool will consume it. Here are the six main schema designs — what they are, how they work, and when to use each one.

1. Flat Model

What it is: The simplest possible structure — a single two-dimensional table, like a spreadsheet. Every row is a record, every column is a field.

Real-world example: A shopkeeper in Gikomba market tracking daily sales in a single Excel sheet — product name, quantity, price, date — all in one place.

When to use it: Small datasets, simple applications, quick personal tracking. It breaks down the moment your data gets complex or relationships between different types of data need to be tracked.

Ease of use: ⭐⭐⭐⭐⭐ — the easiest. No technical knowledge required.

Power BI relevance: You will often receive data in this format. The job of the analyst is to recognise its limitations and restructure it before building a model.

2. Hierarchical Model

What it is: Data organised in a tree structure — one parent, many children. Each record has exactly one parent, but can have multiple children beneath it.

Real-world example: An organisation chart. A CEO sits at the top. Below are Directors. Below each Director are Managers. Below each Manager are staff. Each person has only one direct boss — that is a hierarchical relationship.

When to use it: When your data has a clear, strict parent-child structure — organisational hierarchies, file systems, product category trees (Electronics → Phones → Smartphones).

Limitation: It only works for one-to-many relationships. The moment one child needs two parents — say, a project that belongs to both the Finance and Operations departments — the hierarchical model breaks.

Ease of use: ⭐⭐⭐ — straightforward to understand, but rigid in practice.

Power BI relevance: Power BI supports hierarchies natively — you can drill down from Year → Quarter → Month → Day in a chart. That drill-down functionality is a hierarchical model in action.

3. Network Model

What it is: Like the hierarchical model but with the rigidity removed. Instead of a strict tree, the network model allows many-to-many relationships — a child can have multiple parents, and a parent can connect to many children in multiple directions. Think of it as a web rather than a tree.

Real-world example: A university database where a Student can enrol in many Courses, and each Course can have many Students. No single parent-child rule applies — the relationships go in all directions.

When to use it: Complex systems where entities genuinely have many connections — supply chains, telecommunications networks, social networks.

Limitation: The flexibility that makes it powerful also makes it difficult to design and maintain. It is rarely used in modern business analytics.

Ease of use: ⭐⭐ — complex to design and navigate.

Power BI relevance: Low direct relevance. Power BI's model view works best with simpler, cleaner relationships. If your data looks like a network model, it usually needs restructuring before it is brought into Power BI.

4. Relational Model

What it is: The dominant model in modern databases. Data is stored in multiple separate tables, and tables are linked to each other through shared keys — a primary key in one table matches a foreign key in another. This is the foundation of SQL databases like MySQL, PostgreSQL, and SQL Server.

Real-world example: M-Pesa transaction records. One table stores Customer details (CustomerID, Name, Phone). Another stores Transactions (TransactionID, CustomerID, Amount, Date). The CustomerID field connects the two — that link is a foreign key relationship, and it is the heart of the relational model.

When to use it: Almost always, for business data. The relational model handles complex data efficiently, avoids duplication, and scales well. It is what most organisations use for their operational databases.

Ease of use: ⭐⭐⭐⭐ — requires understanding of keys and relationships, but tools like Power BI make it very manageable.

Power BI relevance: This is Power BI's native language. When you connect Power BI to a SQL database, you are connecting to a relational model. The Model View in Power BI is essentially a visual representation of relational table relationships.

5. Star Schema

What it is: A specialised design built specifically for analytics and reporting — not for storing live operational data, but for analysing historical data at speed. It has one central fact table containing measurable business events (sales, transactions, clicks), surrounded by multiple dimension tables containing descriptive information (who, what, where, when).

Real-world example: A supermarket analytics database. The fact table holds every sales transaction — date, product, store, quantity, price. The dimension tables describe: which product? (Product table), which store? (Store table), which customer? (Customer table), when? (Date table). The fact table sits in the middle like the centre of a star, with dimension tables radiating outward.

When to use it: Whenever you are building dashboards or analytical reports. It is fast, simple to query, and easy to understand. Most Power BI data models follow the star schema pattern.

Ease of use: ⭐⭐⭐⭐⭐ for analysts — this is the most intuitive structure for building reports.

Power BI relevance: This is the recommended structure for Power BI. Microsoft's own documentation advises building star schemas before loading data into Power BI. The reason is performance — Power BI's calculation engine (called DAX) runs significantly faster on a well-built star schema than on flat or unstructured data.

6. Snowflake Schema

What it is: A variation of the star schema. The difference is that in a snowflake schema, the dimension tables are further broken down into sub-tables — normalised, in database terminology. Instead of one flat Product dimension table, you might have a Product table linking to a Category table, which links to a Department table.

Real-world example: Continuing the supermarket example — in a star schema, the Product dimension might have columns for ProductName, Category, and Department all in one table. In a snowflake schema, Category and Department would each be separate tables, linked back to Product. The shape this creates looks like a snowflake — many branching connections rather than simple spokes.

When to use it: When storage efficiency matters and your dimension tables contain significant redundancy. Normalising reduces data duplication. It is more common in large enterprise data warehouses where storage costs are significant.

Trade-off: Snowflake schemas are more complex to query. Joining multiple tables to get a single answer takes more processing. For Power BI specifically, this extra complexity can slow report performance.

Ease of use: ⭐⭐⭐ — more complex than star schema. Requires more joins to retrieve the same information.

Power BI relevance: Power BI can work with snowflake schemas, but the general advice is to flatten them back toward a star schema before building your model. The performance gain from simplicity usually outweighs the storage savings from normalisation.

Which Schema Should You Use and When?

Here is a simple decision guide:

Situation	Recommended Schema
Quick personal tracking, small dataset	Flat Model
Strict parent-child data (org charts, categories)	Hierarchical Model
Complex many-to-many relationships	Network Model
Operational business database (live transactions)	Relational Model
Analytics, dashboards, Power BI reports	Star Schema
Large enterprise data warehouse, storage-sensitive	Snowflake Schema

For the vast majority of analysts building Power BI dashboards — start with the star schema. It is the easiest to build, the fastest to query, and the most natural fit for how Power BI thinks about data.

If your data arrives as a flat model (a single Excel sheet), your job is to identify the entities and split them into a fact table and dimension tables. If it arrives as a relational model from a SQL database, restructure it into a star schema before building your Power BI model. If it is a snowflake, consider flattening it.

Why This All Matters for Every Dashboard You Build

Here is the real-world connection.

During my own analysis of an electronics sales dataset — 632 transactions across 13 countries — the data came from multiple sources. Revenue figures lived in one table. Salesperson information in another. Product categories in a third. Without understanding joins and schemas, I could not have combined those sources to answer questions like:

Which salesperson generates the highest revenue per order?
Do high-discount products attract more customer reviews?
Which region is growing fastest month on month? Every single one of those questions required data from at least two tables. Every answer depended on a correctly defined relationship. The final Power BI model followed a star schema — a central Sales fact table surrounded by dimension tables for Products, Salespeople, Regions, and Dates. Every slicer, every chart, every KPI card on the dashboard was powered by that structure.

Power BI gives you the tools to build beautiful, interactive dashboards. But schemas give your data structure. Joins give your data breadth. Data modelling gives your analysis integrity. And choosing the right schema design gives your reports the speed and reliability that decision-makers depend on.

Get those right, and Power BI becomes genuinely powerful. Skip them, and you are building on sand.

How Excel is Used in Real-World Data Analysis

Benjamin Ochieng — Sat, 06 Jun 2026 17:29:40 +0000

Prior to taking this course, I believed that Excel was simply another table. A common place where people left numbers when they were not sure what to do with them. I can honestly say after a few weeks, that was an embarrassing mistake.
Excel is one of the applications developed by Microsoft that is used for spread sheets. The fundamental idea is that it enables you to collect, organize, calculate and visualize data, without writing a single line of code. It's ubiquitous, so much so, that one might find it in finance teams, HR departments, hospitals, construction companies and small businesses across Kenya and the world.
Financial Reporting & Budget Tracking
A common application of Excel in the real world is financial reporting. Organizations rely on it to monitor revenues and expenditure, create budget projections, and monitor performance relative to a specific goal. This is easily managed with Excel because it can hold a lot of many to many transactional data, and has the ability to auto calculate differences, percentages, and totals. When a company performs a comparison of revenue for this quarter against the same quarter last year, it's pretty sure doing it in EXCEL.
HR and Workforce Data
HR teams use Excel to keep records of employees, from salaries to departments, hire dates, employee performance scores, etc. This is the type of data I used in our data cleansing that I used to work with. I saw blank salary fields, children aged 4 years old, and hire dates such as 31st April (which is not a valid date) was a clear indicator of the value of clean data. Bad inputs create bad outputs, and Excel can help you catch and correct the issues before they get to the people who have to make the decisions.
Project Tracking and Timeline Management
In construction and project-based industries (which I am familiar with working as a site engineer) Excel is used to track milestones, resource allocation and working days are calculated. One of the functions that I found interesting this week is NETWORKDAYS. It works out the business day count between two dates, automatically excluding weekends and public holidays. For project managers creating delivery schedules, that's a time-saving function for counting by hand hours.
What Changing My Mind Felt Like is about.
My transition was more to the learning of formulas than anything else. The realization that Excel is where raw data becomes useful. When I used AVERAGE and MEDIAN to fill in missing values, or MODE to determine the most frequently occurring value in a column, I was not simply performing calculations, but making decisions about what the data should be telling me. That's a responsibility that alters the way you think about numbers.
A person's cleaning of the data determines the trustworthiness of the data. That cleaning occurs in the pages of Excel — and now I see why it's important to get it right.