DEV Community: Erickson mugendi

Connecting Power BI to SQL Databases: From Local Servers to Cloud Platforms

Erickson mugendi — Tue, 14 Jul 2026 03:12:35 +0000

Introduction

When doing doing armature database creation and data storage for in local machine the type of connection mostly required is quite simple.
Large organizations and large files storage require bigger and sometimes very large storage, this is where the term Cloud comes in.
Companies like Microsoft, oracle, Aws, Digital ocean etc provides this services.

local Database connection

Depending on the type of database a user is using, one can choose what Database Management System to use.
Several DBMS platforms exist but one Dbeaver is a universal and is what we will focus on.
To connect to your local database on dbeaver you will need:

1.PostgreSQL Installed

Ensure PostgreSQL is installed on your computer.

PostgreSQL Server

PostgreSQL service running

2.Database Details
You'll need these connection details:

Field Example
Host localhost or 127.0.0.1
Port 5432 (default)
Database postgres or your database name (e.g., sales_db)
Username postgres
Password The password you set during PostgreS installation

3.DBeaver

Download and install DBeaver Community Edition if you haven't already.

4.PostgreSQL JDBC Driver

The first time you create a PostgreSQL connection in DBeaver, it will ask to download the PostgreSQL JDBC driver.

Click Download. DBeaver installs it automatically.

How to conect

Step 1

Open DBeaver.

Step 2

Click:

Database
→ New Database Connection

or click the New Connection icon.

Step 3

Choose

PostgreSQL

Click Next.

Step 4

Enter your connection details.

Example:

Host:
localhost

Port:
5432

Database:
postgres

Username:
postgres

Password:

Step 5

Click

Test Connection

If successful, you'll see:

Connected

Click Finish.

Connecting to a cloud Database

Today organizations increasingly store their data in online cloud-hosted databases rather than on local servers. These databases provide secure access over the internet, making it easy for teams in different locations to work with the same data.

How the Connection Works
Online Database
(AWS, Azure, Aiven, GCP)
│
│ Internet
▼
Power BI Desktop
│
▼
Power Query
│
▼
Reports & Dashboards

lets connect power bi to Aiven SQl database

Step 1: Install the MySQL Connector

Power BI uses the MySQL Connector/NET driver to communicate with MySQL databases.

If it isn't already installed:

Download MySQL Connector/NET from Oracle.
Install it.
Restart Power BI.

Step 2: Connect from Power BI

Open Power BI Desktop.

Go to:

Home
→ Get Data
→ MySQL Database

Step 4: Enter the Server Information

In the Server field, enter:

mysql-demo.aivencloud.com:15432

Replace the hostname and port with the values from your Aiven dashboard.

Step 4: Authenticate

Choose Database Authentication.

Enter:

Username:
avnadmin

Password:

Click Connect.

Step 5: SSL Configuration

Aiven requires encrypted connections.

Aiven mysql doesnt really need SSl certificate as it uses
the database driver (MySQL Connector/NET or Npgsql).

Step 8: Select Tables

After authentication, the Navigator window appears.

Example:
products

sales

employees

Select the tables you need.

Choose:

Load – Import directly into Power BI.
Transform Data – Open Power Query to clean and shape the data before loading.

Postgre Database

connection to postgre service requires ssl certificate

i) click your Windows taskbar search tool, type "Manage computer certificates" (or press Win + R, type certlm.msc, and press Enter) to open the Certificate Manager.
ii) On the left side panel, locate and expand the folder named Trusted Root Certification Authorities.
iii) Right-click the underlying Certificates sub-folder -> Go to All Tasks -> Click Import....
iv) Step into the wizard and click Next. When the file browser opens, change the extension dropdown in the bottom-right corner from "X.509" to "All Files (.)" otherwise, your ca.pem file will stay invisible
v) Select your ca.pem file, click Next through the default locations, and click Finish. Click Yes on the dramatic Windows security warning pop-up.

then move to certificate and right click to display

##click browse and navigate to where you stored the ca.certificate

Click next till Finish to install.

##Click Finish to complete installation

This point you install and return to powerbi and follow the process above for authentication. You will be connected and ready to transform your data

Data modelling relationships, joins and schemas in power bi

Erickson mugendi — Tue, 30 Jun 2026 07:17:04 +0000

Data modelling is bringing all the tables, laying them out and connecting relationships between. A well-designed model makes your dashboards faster, easier to maintain, and more scalable.

A fact table stores measurable business events, such as sales, orders, or transactions. It contains numeric values and foreign keys to related dimension tables.

A dimension table stores descriptive information such as customer names, product categories, or dates that is used to filter and group facts.

Types of Relationships

One-to-Many This means one table can be connected to other many tables. eg. A landlord can have many rental houses.
Many-to-Many eg. many students can have many different courses they are undertaking. Many-to-many relationships are supported but should be used carefully because they can introduce ambiguity.
One-One Each row matches exactly to one row
Many-One This is simply the same relationship viewed from the opposite table.

JOINS

Used when you want to combine multiple tables physically before loading them into the data model. You do this using the Merge feature.
Inner Join: Returns only rows where matching keys exist in both tables.
Left Outer Join: Returns all rows from the first table and matches from the second.
Right Outer Join: Returns all rows from the second table and matches from the first.
Full Outer: Returns all rows from both tables.

Schemas

A schema is how you structure your data model
Types include:

Star schema
There is one central fact table.
There are several surrounding dimension tables.
Snowflake Schema
A snowflake schema is like a star schema, but dimension tables are further broken down into smaller related tables.

PRIMARY KEYS A primary key is a column that uniquely identifies each row in a table.
FOREIGN KEY A foreign key is a column in one table that refers to the primary key in another table. Foreign keys are used to connect tables.

Fact table is a stores events, transactions, or measurable business activities.
Dimension Table is a describes the items in the fact table.

Data Science and Analytics

Erickson mugendi — Sat, 06 Jun 2026 13:26:20 +0000

##Data science and analytics
This has my dream all through and finally am here. I am super excited to start my new journey, week one has been all about data cleaning in excel which excited me as its a field have been heavily involved in career wise, I am diving deep to make data more insightful.

How Excel is Used in Real-World Data Analysis

Cleaning data in excel really helps to bring out certain data driven trends clearly.
Forecasting is very useful to predict market trends. Data can be classified into quarters and clearly show how sale trends appear.
Visualization is definitely a feature admired in boardrooms to show case business progress.
ISBLANK, IFERROR are helpful in big datasets to check for errors and blank cells in cleaning.
Turning to Power query is a game changer in connecting to different databases.