Forem: Mohamed

How to Connect Power BI to a PostgreSQL Database (Localhost and Aiven Cloud)

Mohamed — Sat, 14 Mar 2026 15:59:42 +0000

Power BI is a powerful business intelligence (BI) and data visualization tool
developed by Microsoft. It allows organizations to analyze data, build
interactive dashboards, and generate reports that help decision-makers understand
trends, patterns, and business performance.

In modern organizations, large volumes of operational and analytical data are
stored in databases. Instead of manually exporting this data into spreadsheets,
companies connect Power BI directly to databases, so reports can be updated
automatically. This connection enables analysts to build dashboards that always
reflect the latest data.

One of the most widely used database systems for storing analytical data is
PostgreSQL. PostgreSQL is an open-source relational database management system
known for its reliability, scalability, and strong SQL support. Companies use SQL
databases like PostgreSQL to store structured data such as sales, transactions,
customer records, and inventory information. When Power BI connects to these
databases, analysts can query and visualize the data efficiently.

Connecting Power BI To A Local PostgreSQL Database

To begin analyzing data stored in PostgreSQL, you first connect Power BI Desktop
to the database.

1. Open Power BI Desktop

Start by opening Power BI Desktop on your computer. The Home ribbon provides
access to data connection tools.

2. Click Get Data

On the Home ribbon, click Get Data. Power BI supports many data sources including
Excel, CSV, cloud services, and databases. If PostgreSQL is not immediately
visible, click More to see the full list of connectors. Type PostgreSQL in the search bar and Power BI will immediately filter the results.

3. Choose PostgreSQL Database

Select PostgreSQL database from the available connectors.

Power BI will open a connection dialog where you enter database details

In the dialog box enter:

Server: localhost:5432
Database: database name (e.g., company )
Data connectivity mode:
- Import (load data into Power BI)
- DirectQuery (queries the database live)

Then click OK.

4. Provide Credentials

Power BI will ask for authentication details.

Enter:

Username: PostgreSQL user
Password: database password
Authentication type: database

After entering credentials, click Connect.

Select Tables to Load

The Navigator window appears showing all tables and views in the PostgreSQL
database.

For example you might select:

customers
products
sales
inventory You can preview each table and then click Load to import the data into Power BI.

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

Many companies store their databases in the cloud. One example is Aiven, which
provides managed PostgreSQL services.

Connecting Power BI to a cloud PostgreSQL instance is similar to connecting
locally, but it requires additional connection details.

1. Obtain Connection Details from Aiven

From the Aiven dashboard you will find:

Host (database server address)
Port (usually 19534)
Database name
Username
Password

2. Download and Install the SSL Certificate

Aiven requires SSL encryption for all PostgreSQL connections. Because of this,
you must download the CA Certificate and install it on your machine before
connecting Power BI to the database.

Download the CA Certificate

Open your Aiven service dashboard.
Navigate to the connection information section.
Download the CA Certificate file.

The certificate will usually download as a file named: ca.pem.
Windows expects to use the .crt extension. To make the certificate compatible,
rename the downloaded ca.pem file to ca.crt.

Install the Certificate in Windows

Double click the ca.crt.
The Certificate window will open.
Click Install Certificate.
Choose Local Machine.
Select Place all certificate in the following store.
Choose Trusted Root Certification Authorities.
Finish the installation.

3. Connect Using Power BI

Open Power BI Desktop.
Click Get Data from Home tab.
Use the search bar to search for PostgreSQL database.
Select it and click Connect.

In the connection window, enter the following details from the Aiven dashboard:

Server: pg_12345-user.aivencloud.com:19534
Database name: company Click OK.

Power BI will then prompt you for authentication.

Enter:

Username
Password

and select database authentication, then click Connect.

Because the CA Certificate was installed earlier, Windows already trusts the
Aiven server, allowing Power BI to establish a secure SSL connection
automatically.

Once the connection succeeds, the Navigator window will appear showing the
available tables in your database, ready for you to select and load.

Understanding SQL Joins and Window Functions

Mohamed — Sat, 07 Mar 2026 19:06:51 +0000

You'll often find that the data you need is spread across multiple tables or
requires complex calculations that simple GROUP BY queries can't handle. Two
of the most powerful tools in your SQL toolbox for solving these challenges are
JOINS and Window Functions.
While Joins allow us to stitch together data from multiple tables, Window
Functions allow us to perform complex calculations across rows without losing
granularity of our data.

This article explains:

What Joins are
Types of Joins with examples
What Window Functions are
Why Window Functions are powerful
Sample queries

SQL JOIN

What is a Join?

A Join combines rows from two or more tables using a related column between
them. In a normalized database, data is split into multiple tables to avoid
redundancy. For example, you might have a weather table with city names and a
cities table with detailed city locations. To get a complete picture, you need
to join these tables.

Why do we need Joins?

In relational databases, data is usually split across multiple tables to reduce
duplication and maintain consistency. This process is known as normalization

Types Of Joins

1. Inner Join

This join returns only matching rows from both tables.
If a row in one table has no corresponding row in the other, it is excluded
from the results.

Syntax

SELECT
    columns
FROM
    table1
    INNER JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.name,
    o.amount
FROM
    customers c
    INNER JOIN orders o ON c.customer_id = o.customer_id

2. Left Join

This join returns all rows from the left table, plus matching rows from the
right table. If no match exists, you get NULL values from the right table.
Syntax

SELECT
    columns
FROM
    table1 LEFT
    JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    LEFT JOIN orders o ON c.customer_id = o.customer_id

3. Right Join

This join returns all rows from the right table, plus matching rows from the
left table. This is the mirrors image of a LEFT JOIN.
Syntax

SELECT
    columns
FROM
    table1
    RIGHT JOIN table2 ON table1.column = table2.column

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    RIGHT JOIN orders o ON c.customer_id = o.customer_id

4. Full Join

Full Join returns all rows from both tables.
If rows from the two tables match based on the join condition they are combined
into a single row. If no match found, the missing columns are filled with NULL
values.
Syntax

SELECT
    columns
FROM
    table1
    FULL JOIN table2 ON table1.column = table2.column

Example


SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    FULL JOIN orders o ON c.customer_id = o.customer_id

5. Cross Join

A CROSS JOIN creates a Cartesian product. Every row from the first table paired
with every row from the second table. No condition needed.
Syntax

SELECT
    columns
FROM
    table1
    CROSS JOIN table2

Example

SELECT
    c.customer_id,
    c.name,
    o.customer_id
FROM
    customers c
    CROSS JOIN orders o

WINDOW FUNCTIONS

Window functions allow computations across related rows while preserving
individual rows.
A key difference between GROUP BY and Window Functions is how results are
returned.
GROUP BY collapses multiple rows into a single summary row.
Window Functions performs a calculation across group of rows while returning
each individual row.

Why Window Function Are Powerful

Window functions are widely used in analytics queries because they allow you to
perform complex calculations without losing row-level detail.

General Syntax

window_function([arguments]) OVER (
    [PARTITION BY partition_expression, ...]
    [ORDER BY sort_expression [ASC | DESC], ...]
    [frame_clause]
)

Types of Window Functions

Window functions are generally divided into three main categories.

Aggregate Window Functions
Ranking Window Functions
Value Window Functions

1. Aggregate Window Functions

These functions perform aggregate across a window of rows.
Common aggregate window functions:

SUM()
AVG()
COUNT()
MIN()
MAX()

Example

SELECT
    o.customer_id,
    o.order_date,
    c.first_name,
    c.last_name,
    o.quantity,
    SUM(o.quantity) OVER (PARTITION BY o.customer_id) AS customer_total
FROM
    orders o
    JOIN customers c ON o.customer_id = c.customer_id;

2. Ranking Window Functions

Ranking functions assign a rank or position to rows based on ordering.

Common ranking functions:

i. ROW_NUMBER() - assign a rank or position to rows based on ordering.

SELECT
    employee_name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

ii. RANK() - assigns the same rank for ties but skips numbers.

SELECT
    employee_name,
    salary,
    RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

iii. DENSE_RANK() - similar to RANK(), it assigns the same for ties but
does not skip numbers.

SELECT
    employee_name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

iv. NTILE() - divides rows into equal buckets.

SELECT
    employee_name,
    salary,
    NTILE(4) OVER (ORDER BY salary DESC) AS salary_group
FROM employees;

3. Value (Navigation) Window Functions

These functions allow you to access values from other rows within the window.

Common navigation functions:

i. LAG() - Returns the value from the previous row.

SELECT
    order_id,
    quantity,
    LAG(quantity) OVER (ORDER BY order_id) AS previous_quantity
FROM orders;

ii. LEAD() - Returns the value from the next row.

SELECT
    order_id,
    quantity,
    LEAD(quantity) OVER (ORDER BY order_id) AS next_quantity
FROM orders;

Translate Messy Data, DAX, and Dashboards into Action Using Power BI

Mohamed — Mon, 09 Feb 2026 06:43:01 +0000

Introduction

Most data does not arrive in a neat, analysis-ready format. Analyst often work with spreadsheet full of missing values, inconsistent labels, duplicate records, and confusing date or currency format. Left untreated, this kind of messy data leads to inaccurate reports and poor business decisions. The real value of an analyst's work begins not with charts, but with making sense of this chaos.Power BI enables analyst to clean, model, and analyze imperfect data,transforming it into clear dashboards that support confident, data-driven decisions.

This article explores how analysts use Power BI to translate messy data, DAX calculations, and dashboard into actionable insights.

Power Query

Power Query Editor is the first tool in the analyst arsenal. Before any analysis
begins, data must first be loaded into Power Bi from various resources. These sources may include Excel files, CSVs, databases, cloud services, or enterprise systems. Power Bi connects to these sources and brings the raw data into Power Query, where preparation begins.

Once the data is loaded, Power Query is used for data profiling cleaning, and transformation, such as removing errors, replacing null values, and reshaping tables.
This step is critical. If the data foundation is wrong, every dashboard and decisions built on top of it will be misleading.

Using Power Query, analyst:

Remove duplicate and errors
Standardize values (e.g "Ke","Kenya")
Handle missing data logically
Change data types for dates, numbers, and currencies

Modeling Data

Data Modeling is the process of structuring data into tables and defining relationships between them in a way that support efficient analysis.
Once data is cleaned, analyst structure it into a star schema.
This structure allows meaningful relationships and accurate calculations across time period or region.
A well-designed model ensures that DAX measures behave correctly and that insight can be trusted.

Example

FactSales table: contain transaction-level revenue, profit, discount, and quantity.
DimProduct: product detail and categories
DimDate: for time intelligence
DimRegion: geographic hierachy

Using DAX to Build Actionable Metric

Raw data alone doesn't tell a story. DAX formulas turn it into measures that answer business questions.
DAX allows decision-makers to explore scenerios and evaluate performance.

Example:

Total Revenue

Total Revenue = Sum(FactSales[Revenue])

Gross Profit

Gross Profit = Sum(FactSales[Revenue] - FactSales[Cost])

Margin

Margin % = DIVIDE([Gross Profit], [Total Revenue],0)

Designing Dashboards

A dashboard is more than charts. It's a storyboard for action.
It's visual command center. It turns a clean data and smart calculations into a clear, interactive story that anyone can understand.

Effective Power BI Dashboard:

Highlights KPI first (Revenue, Profit, Margin)
Use trends to show performance over time
Rank top and bottom performers
Include slicer/filters so users can explore by region, product , or salesperson.
Avoid clutter and focus attention on what needs action

Turning Insights into Decisions

The final step is action. Power BI insights influence decisions such as:

Adjusting discount policies when margin drop
Identifying under performing regions or salespeople
Improving delivery processes based on on-time metrics
Planning scenarios under rising costs or changing demands

Power BI is powerful not because it creates charts, but because it helps analysts translate complexity into clarity. By cleaning messy data , modeling it correctly, applying DAX logic, and designing purposeful dashboard, analysts turn raw numbers into real business actions.

Schema And Data Modelling in Power BI: A Comprehensive Guide

Mohamed — Mon, 02 Feb 2026 05:07:14 +0000

Introduction

Power Bi is a powerful business intelligence tool used to analyze data and
create interactive reports and dashboards. However, the quality, performance,
and accuracy of Power Bi reports depend heavily on how the data is modeled.

What is Data Modelling

Data modelling is the process of structuring data into tables and defining relationships between them in a way that supports efficient analysis.

What is a Schema

Schema refers to the organizational structure of your data model. Its the specific arrangement or pattern you choose to organize your data tables and their relationships.

Define the Analysis Goal

Data modelling begins by identifying what needs to be analyzed.

Typical questions include:

What is being measured?
How will result be compared

Clear Goal guide the structure of the model

Create the Fact Table

A fact table stores measurable data from business activities.

A table is a fact table if it:

Contains numeric values
Records transactions or events
Grows continuously

Example:

Sales transactions
Orders
Payments

Common fact table columns:

SalesAccount
Quantity
OrderID
ProductKey

Create Dimension Tables

Dimension tables provides meaning to the facts.

They describe:

Who performed the action
What is involved
When it happened
Where it occurred

Example:

Customer
Product
Date
Location

Dimension allows data to be filtered, grouped, and summarized.

Define Relationships

Relationships link dimension tables to the fact tables.

Key Rules for Defining Relationships

Relationships are usually one-to-many
Dimension tables are on the one side
Fact tables are on the many side
Filter flows from dimension to fact
Single-direction filtering is preferred

Correct relationships ensure accurate results.

Schema in Power Bi

A schema refers to the way table are structured and related within a data model.Schema define how fact tables and dimension tables are organized to support analysis and reporting.

Power Bi mainly uses two schema designs

Star Schema
Snowflake schema

Star Schema

A star schema is a data modelling design where a central fact table is directly connected to multiple dimension tables. The structure resembles a star.

Advantages of Star Schema

Easy to understand
Faster query performance
Simplifies DAX (Data Analysis Expression) calculation
Reduces ambiguity in relationships
Recommended by Microsoft for Power Bi

Disadvantages of Star Schema

Uses more storage space
Some data redundancy in dimension tables
Less suitable for highly normalized data

Example:

FactSales
DimCustomer
DimProduct
DimDate
DimRegion

Each Dimension connects directly to the fact table.

Snowflake Schema

A snowflake schema is a more complex version of a star schema where dimension tables are normalized into multiple related tables.

Advantages of Snowflake Schema

Reduces data redundancy
Uses less storage space
Suitable for very large dimension tables
Better normalization of data

Disadvantages of Snowflake Schema

More complex due to many joins
Slower performance due to many joins
Harder to write and maintain DAX (Data Analysis Expression)
More difficult to users to understand
Not ideal for most Power Bi reports

Data Modelling is the foundation of effective Power Bi reporting. Understanding schema, fact and dimension tables and relationships allows analyst to build models that are fast, accurate, easy to maintain.

Introduction to Microsoft Excel for Data Analytics: A Beginner-Friendly Guide

Mohamed — Sun, 25 Jan 2026 15:17:51 +0000

Data is everywhere, but making sense of it is what truly matters. That’s where
Microsoft Excel comes in. It is one of the most accessible and powerful tools
for anyone starting a journey in data analytics.

What is Microsoft Excel?

Microsoft Excel is a spreadsheet application that allows you to:

Store data in rows and columns
Perform calculations
Clean and organize data
Analyze patterns and trends
Create charts and summaries

Understanding the Excel Interface

Before analyzing data, you need to understand the basic layout of Excel.

Workbook and Worksheet

A Workbook is the Excel file
A Worksheet (Sheet) is the individual page inside the workbook

A workbook can contain multiple sheets, which is useful for separating raw data,
cleaned data, and analysis.

Rows, Columns, and Cells

Columns - Vertical (A, B, C ...)
Rows - Horizontal (1, 2, 3, ...)
Cell - The intersection of a row and a column (e.g., A1)

Types of Data in Excel

Common data types include:

Text - names, categories (e.g., "Mohamed", "Sales")
Numbers - Quantities, prices, salaries
Date & Time - Transactions, timestamps
Boolean - TRUE or FALSE

Entering and Formatting Data

Clean data is the foundation of good analysis.

Basic best practices:

Use one column per variable (e.g., Date, Product, Revenue)
Avoid merged cells
Keep headers clear and descriptive
Remove empty rows within the dataset

Formatting tips:

Use bold for headers
Apply number formats (currency, date, etc.)

Opening an Existing Excel File

If your data is already in Excel format (.xlsx), you can open it directly:

File -> Open -> Select .xlsx File

This is the quickest option when working with previously saved datasets.

Import Data into Excel

Data can be entered manually or imported from external sources such as CSV files.

To import data:
Data -> Get Data -> From Text/CSV

This ensures that large datasets are loaded accurately.

Step 1: Data Cleaning - Prepare Your Dataset

Most real-world data is messy. Before analysis, the data must be cleaned.

Removing Duplicate Records

Duplicate rows can distort results.

Data -> Remove Duplicates

This removes repeated entries based on selected columns.

Cleaning Text with Functions

TRIM() removes extra spaces
UPPER() and LOWER() standardize text formatting

Step 2: Sorting and Filtering Data

Sorting

Sorting arranges data in ascending or descending order.

Data -> Sort A to Z

Filtering

Filtering allows you to focus on specific values.

Data -> Filter
This is useful when exploring a subset of large datasets.

Step 3: Essential Excel Formulas for Analysis

All Excel formulas are used in the same way. Follow these steps whenever you
want to apply a formula:

Click on the cell where you want the result to appear
Type = followed by the formula name (for example: SUM, AVERAGE, COUNTIF)
Open a bracket (
Select the range of cells or enter the required values
Close the bracket )
Press Enter

SUM and AVERAGE

Used to calculate total and mean values:

=SUM(B2:B100)
=AVERAGE(B2:B100)

COUNTIF

Used to count how many cells meet a specific condition.

=COUNTIF(C2:C50, "Completed")

IF

Used to make decisions based on a condition.

=IF(D2>100, "High", "Low")

This allows you to categorize data based on conditions.

XLOOKUP

Used to find information in one column and return a related value from another
column.

=XLOOKUP(E2, A2:A100, B2:B100)

Step 4: PivotTables

PivotTables allow you to summarize large datasets without writing formulas.

Steps:

Select the dataset
Go to Insert -> PivotTable
Drag fields into Rows, Columns, and Values

PivotTables are commonly used to calculate totals, averages, and counts by
category.

Step 5: Data Visualization with Charts

Charts help communicate insights clearly.

Steps:

Select your data
Go to Insert -> Charts
Choose a suitable chart type (Column, Line, or Bar)
Add titles and labels for clarity

Microsoft Excel is a powerful starting point for anyone interested in data analytics.
With features such as data cleaning tools, formulas, PivotTables, and charts,
Excel enables users to transform raw data into meaningful insights.

#Beginner’s Guide to Git & GitHub Using SSH Keys

Mohamed — Sat, 17 Jan 2026 08:21:22 +0000

Learn Git basics for Beginners

If you're new to coding, you've probably heard about Git and GitHub. They're essential tools for developers, but they can seem confusing at first. This guide will walk you through everything step by step.

What is Git

Git is a version control system. Think of Git as a history recorder for your project.

It helps you:

Track changes in your files
Go back to previous versions
Work with others without overwriting their code

What is GitHub

GitHub is an online platform where you store Git repositories.

It allows you to:

Back up your code online
Collaborate with other developers
Share your project publicly or privately

Step 1: Installing Git Bash

For Windows Users:

Visit Git Bash
Download the Windows installer
Run the installer with these recommended settings:
- Select "Use Git from Git Bash only "
- Choose "Use the OpenSSL library"
- Select "Checkout Windows-style, commit Unix-style line endings"
- Choose "Use MinTTY"
- Leave other options as default

For Mac Users:

# Option 1: using Homebrew
brew install git

# Option 2: Download from <git-scm.com>

For Linux Users:


# Ubuntu/Debian
sudo apt-get install git

#Fedora
sudo dnf5 install git

Step 2: Connecting Git to Your GitHub Account

1. Configure Your Identity

git config --global user.name "your name"
git config --global user.email "your email"

2. Generate an SSH Key

# Generate a new SSH key
ssh-keygen -t ed25519 -C "your email"

# Press Enter to accept default location

# Copy the key to your clipboard

# For Windows
C:\Users\YOUR_USERNAME\.ssh\id_ed25519.pub

# For Mac:
~/.ssh/id_ed25519.pub

# For Linux:
~/.ssh/id_ed25519.pub

Add SSH Key to GitHub

Go to your GitHub account
Go to settings then click SSH and GPG keys
Click "New SSH key"
Paste your key and give it a descriptive name
Click "Add SSH key"

Test Your Connection

ssh -T git@github.com

You should see: "Hi username! You've successfully authenticated...."

Step 3: Git Commands

Initialize a new repository

# Create project folder
mkdir my-first-project

# Navigate to your project folder
cd my-first-project

# Initialize git tracking
git init

# list all files and folders in the project directory
ls -la  # you will find .git folder

Git creates a hidden .git folder to store history.

Open Project in VS Code

Open VS Code
File -> Open Folder
Select my-first-project
Click "Open"
Click "New File" icon
Name it "hello.py"
Add this content

# print Hello World in Python
print("Hello World!")

Check your repository status

git status

Output shows untracked/modified files

Stage Files

# Stage specific file
git add hello.py

# Stage multiple files
git add hello.py README.md

# Stage all changes
git add .

# Check what's staged
git status

Commit Changes

# commit with descriptive message
git commit -m "Add initial Python script and documentation"

Step 4: Connect to GitHub and Push

Create Repository on GitHub

Go to your GitHub account
Click + -> New repository
Name: my-first-project
Description: "Learning Git basics"
Click "Create repository"

Connect Local to Remote

# Copy the SSH URL from GitHub
# Example
git remote add origin git@github.com:yourusername/my-first-project.git

# verify connection
git remote -v

Push to GitHub

# First push (sets upstream)
git push -u origin main

# Subsequent pushes
git push

What happens when you push

Your local commits are uploaded to GitHub
Others can now see and access your code
Your work is backed up in the cloud

Step 5: Pulling Code from GitHub

When to pull:

When starting work on a project
When teammates have made changes
Before making your own changes

# basic pull
# Go to the project directory
git pull

# pull from specific branch
git pull origin feature-branch

Everyone makes mistakes with git at first. That's normal! The key is to start with simple workflows and gradually learn more advanced features.

Git is a powerful tool that becomes intuitive with practice.