Forem: Elizabeth Njihia

How to Connect Power BI to PostgreSQL (Local & Cloud) — Complete Beginner to Analyst Guide

Elizabeth Njihia — Mon, 30 Mar 2026 14:23:11 +0000

Introduction.

Every company today collects huge amounts of data — customer purchases, product inventory, website activity, and financial transactions.
But data sitting inside a database doesn't create value on its own.
It becomes powerful only when analysts transform it into insights.
This is where Power BI and SQL databases work together.
In this guide, I’ll walk through how to connect Power BI to PostgreSQL databases, both locally and in the cloud.
Objectives
• What Power BI is and why businesses use it
• Why SQL databases are the backbone of analytics
• How to connect Power BI to a local PostgreSQL database
• How to connect Power BI to Aiven PostgreSQL in the cloud
• How to create relationships and data models in Power BI
• Why SQL is one of the most important skills for BI analysts

What is Power BI?

Power BI is a business intelligence and data visualization tool developed by Microsoft.
The power of Power BI lies in its ability to pull data from
many sources, clean it, analyze it, and then create visuals that are easy to understand and actionable.
It helps analysts convert raw data into:
• interactive dashboards
• business reports
• data visualizations
• performance metrics

Instead of manually analyzing spreadsheets, Power BI allows companies to connect directly to data sources like:

SQL databases
Excel
APIs
cloud services
data warehouses

Once connected, Power BI can automatically transform the data into charts, dashboards, and insights that update in real time.

How Data Flows in a Typical BI System
Here is a simple diagram showing how data moves from a database into Power BI dashboards.

Businesses rely on this pipeline to turn raw operational data into strategic decisions.

Why SQL Databases Are Essential for Analytics?

Most companies store their critical data in relational databases, and SQL (Structured Query Language) is the standardized and efficient language used to access, manage, and analyze this data. Its capabilities far exceed those of general-purpose tools like spreadsheets when dealing with large, complex datasets.
SQL databases such as PostgreSQL, MySQL, Microsoft SQL Server and Oraclestore information in structured tables.

Example of tables in a retail system:

Table	Description
customers	customer information
products	product catalog
sales	transaction records
inventory	stock levels

These tables are connected using keys and relationships, which makes them perfect for analytics.
Example of a Database Structure

This type of structure is exactly what Power BI connects to.

Connecting Power BI to a Local PostgreSQL Database.

Before connecting Power BI to PostgreSQL, you must install the PostgreSQL connector (Npgsql driver).
Once installed, the process is simple.

Step 1 — Open Power BI Desktop
Launch Power BI Desktop.
You will see the main interface where reports and dashboards are created.

Step 2 — Click "Get Data"
From the top ribbon:
Home → Get Data
This opens the list of available data sources.
Get Data window showing multiple connectors

Step 3 — Select PostgreSQL Database
In the connector list:

Search → PostgreSQL Database
Select → Connect
PostgreSQL connector selection window

Step 4 — Enter Database Details
Now you must enter the connection details.
Example:
Server: localhost:5432
Database: sales_db
Explanation:

Field	Meaning
localhost	database running on your computer
5432	default PostgreSQL port
sales_db	name of database

Power BI will then ask for authentication:
Authentication Type: Database
Username: postgres
Password: ********
After entering the credentials, click Connect.

Step 5 — Select Tables
Power BI will open the Navigator Window.
You can now choose the tables you want to load.
Example of tables:

customers
products
sales
inventory Click Load to import them into Power BI.

Connecting Power BI to a Cloud PostgreSQL Database (Aiven)

Many modern companies store their databases in the cloud. A popular service is Aiven PostgreSQL, which provides fully managed databases. Connecting to cloud databases requires secure encrypted connections.

step 1: Prep your Aiven Credentials
Before opening Power BI, you need your "Connection Information" from the Aiven Console.

Log in to Aiven.io and select your PostgreSQL service.
On the Overview tab, look for the Connection information section.
Download the CA Certificate: Click the download button for ca.pem. (You will need this to avoid the "Remote certificate is invalid" error.)
Keep this tab open; you’ll need the Host, Port, User, and Password. Example:

Host: pg-39c2286-kaburanjihia445-cb8d.f.aivencloud.com
Port: 19050
Database: defaultdb
User: avnadmin
Password: ********

step 2: Install the Npgsql Connector
Power BI requires a specific driver to talk to PostgreSQL.
Download the Npgsql GAC Installer (e.g., version 4.0.10 is widely stable for PBI).
During installation, crucial step: Ensure you check the box for "Install to GAC".
Restart your computer after installation.

step 3: Trust the Aiven Certificate (Windows)
Because Aiven uses a self-signed CA, Windows needs to trust it.
Find your downloaded ca.pem file. Rename it to aiven.crt if Windows doesn't recognize it.
Double-click the file -> Install Certificate -> Local Machine.
Place it in the following store: Trusted Root Certification Authorities.

step 4: Connect in Power BI Desktop
Open Power BI Desktop and click Get Data > PostgreSQL database.
In the Server:

Enter the Host and Port from Aiven in this format: your-host.aivencloud.com:12345
Database: Usually defaultdb.
Advanced Options: In the "Additional connection string parameters," type: sslmode=verify-ca;
Click OK. When prompted for credentials, select the Database tab (not Windows) and enter your Aiven Username and Password.

step 5: Handling the Cloud (Power BI Service)
If you publish this report to the web, the connection might fail because Aiven’s firewall blocks the Power BI Service.
Install a Standard Power BI Gateway on a VM or local machine. This acts as the secure "tunnel" between Aiven and the cloud.

Loading Tables into Power BI
Once connected, Power BI loads the tables into the data model.

Example tables:

Table	Purpose
customers	customer data
products	product catalog
sales	transactions
inventory	stock levels

Consequently ,Power BI automatically detects relationships between tables. For Example :

customers.customer_id → sales.customer_id
products.product_id → sales.product_id
products.product_id → inventory.product_id

Here is a Power BI Data Model Diagram;

These relationships allow Power BI to correctly calculate metrics such as:

total revenue
product sales
customer purchases
stock levels

Once the data model is ready, analysts can create visuals such as:
• Total Sales
• Revenue by Product
• Top Customers
• Inventory Levels

Why SQL Skills Are Critical for Power BI Analysts
Even though Power BI is visual, SQL is still one of the most valuable skills for analysts. SQL helps analysts retrieve and prepare data efficiently. Example of SQL Queries;
Retrieve data

SELECT *
FROM sales;

Filter records

SELECT *
FROM sales
WHERE sale_date >= '2025-01-01';

Aggregations

SELECT product_id, SUM(amount) AS total_sales
FROM sales
GROUP BY product_id;

Join tables

SELECT c.name, s.amount
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id;

Using SQL before loading data into Power BI helps:
• reduce dataset size
• clean messy data
• improve dashboard performance

Conclusion.

By connecting Power BI to PostgreSQL databases — both locally and in the cloud — analysts can:

• access structured data
• build accurate data models
• create interactive dashboards
• support data-driven decision making

✨ Data stored in databases becomes valuable only when someone transforms it into insights.

Mastering Joins and Window Functions in SQL

Elizabeth Njihia — Mon, 02 Mar 2026 16:29:28 +0000

Introduction.

If you’re working with data — especially in tools like Power BI, Excel, or databases like MySQL or PostgreSQL — understanding Joins and Window Functions is a game changer. These two concepts help you combine data and analyze it in powerful ways without losing detail. Here is an article to guide you through joins and window functions.

🔗What Are Joins?

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

Importance of joins.
Most databases are structured into multiple tables to avoid duplication. To analyze the data meaningfully, you need to bring those tables together.

Types of Joins
1️⃣ INNER JOIN
Returns only the matching records in both tables.

Example;

SELECT customers.name, orders.order_date
FROM customers
INNER JOIN orders
ON customers.customer_id = orders.customer_id;`

Meaning: Only customers who have placed orders will appear.

2️⃣ LEFT JOIN (LEFT OUTER JOIN)
Returns all records from the left table and matching records from the right table.
Example;

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.customer_id = orders.customer_id;

Meaning: All customers appear — even those who never placed orders (their order columns will be NULL).

3️⃣ RIGHT JOIN
Returns all records from the right table and matching records from the left.
Example;

SELECT customers.name, orders.order_date
FROM customers
RIGHT JOIN orders
ON customers.customer_id = orders.customer_id;

4️⃣ FULL OUTER JOIN
Returns all records when there is a match in either table.
Example;

SELECT customers.name, orders.order_date
FROM customers
FULL OUTER JOIN orders
ON customers.customer_id = orders.customer_id;

🧠 Summary
INNER JOIN → Only matching rows

LEFT JOIN → Everything on the left + matches

RIGHT JOIN → Everything on the right + matches

FULL JOIN→ Everything from both

🪟 What Are Window Functions?

Window functions perform calculations across a set of rows related to the current row — without collapsing the result into a single row like GROUP BY does.

Basic Structure of a Window Function

SELECT 
    column,
    window_function() OVER (
        PARTITION BY column
        ORDER BY column
    )

FROM table;
Key Parts:

OVER() → defines the window

PARTITION BY → divides data into groups

ORDER BY → defines order within the group

Common Window Functions
1️⃣ ROW_NUMBER()
Assigns a unique number to each row.

SELECT 
    name,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rank_position
FROM employees;

Use case: Ranking employees by salary.

2️⃣ RANK()
Similar to ROW_NUMBER but allows ties.

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

3️⃣ DENSE_RANK()
Like RANK but doesn’t skip numbers after ties.

SELECT 
    name,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rank
FROM employees;

4️⃣ SUM() OVER()
Calculates running totals or grouped totals without grouping the table.

SELECT 
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department) AS dept_total_salary
FROM employees;

This keeps every row but adds department totals.

5️⃣ Running Total

SELECT 
    order_date,
    amount,
    SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

🧠 summary

They calculate across related rows.
They do not reduce rows.
They add analytical power without losing detail.
Perfect for ranking, running totals, comparisons.

Window functions increase insight, not width or height.

🗣️ Insights
Think of Joins as connecting puzzle pieces. Each table is a piece of information. When you join them, you build the full picture.
Think of Window Functions as giving each row awareness of its neighbors. Instead of summarizing the table, you let each row “see” the other rows around it.

Joins = relationship builders

Window functions = insight enhancers

Together, they form the backbone of advanced SQL analytics.

Visualization;

Where Data Meets Decisions

Elizabeth Njihia — Sat, 07 Feb 2026 22:00:25 +0000

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

In theory, data should be clean, complete, and ready for analysis. In reality, business data is messy. It comes from multiple sources, contains missing values, inconsistent formats, and unclear definitions.
This is where Power BI analysts create value.
Their role is not just to build dashboards, but to translate messy data into insights that drive real business decisions. This article explains how analysts use Power Query, data modelling, DAX, and dashboards in Power BI to move from raw data to action.

1. Use of power of power query.

Before any dashboard or calculation is built, analysts start by cleaning and preparing data using Power Query.

Common issues in a data set;

Missing values such as "Not provided" or blanks
Numbers stored as text
Duplicate records
Inconsistent date and currency formats

Example: Cleaning a Reviews Column

Imagine a product reviews dataset where some rows contain text like "Not provided" instead of numbers.

In Power Query, an analyst would:

Replace "Not provided" with null
Change the column data type to Whole Number

📌 Business impact:
Clean data ensures leaders trust the dashboard. Decisions based on inaccurate data can lead to lost revenue, poor forecasting, or wrong strategy.

2.Data modelling.

After cleaning the data, analysts design a data model that mirrors how the business operates.

Most Power BI reports use a star schema, which includes:

Fact tables (Sales, Orders, Reviews)
Dimension tables (Products, Customers, Date)

Example: Sales Star Schema

Fact Sales table contains Order ID, Product ID, Quantity, Revenue
Dim Product contains Product Name, Category, Brand
Dim Date contains Date, Month, Quarter, Year

Relationships are created from dimension tables to the fact table using keys.

📌 Business impact: A well-designed model improves report performance and ensures KPIs calculate correctly across visuals.

3. DAX application.

Raw data answers what happened. DAX helps analysts explain why it happened.

Analysts use DAX to create:

KPIs and metrics
Time-based analysis
Categories and performance bands

Example: Key DAX Measures

Total Revenue

Total Revenue = SUM(FactSales[Revenue])

Average Rating (ignoring missing values)

Average Rating = AVERAGE(Reviews[Rating])

Discount Category

Discount Category =
SWITCH(
    TRUE(),
    [Discount %] < 0.2, "Low Discount",
    [Discount %] < 0.4, "Medium Discount",
    "High Discount"

📌 Business impact: Leaders can segment products, track trends, and evaluate performance using logic that matches business rules.

4.Dashboard creation.

A Power BI dashboard should answer questions instantly, not just display numbers.

Effective dashboards:

Highlight KPIs at the top
Use the right visual for each question
Allow filtering using slicers
Tell a clear story

Example: Product Performance Dashboard

Visuals used:

KPI cards for Total Revenue, Average Rating, Total Reviews
Bar chart showing Revenue by Product Category
Scatter plot showing Discount % vs Reviews
Line chart showing Rating trends over time

Insights generated:

Products with high discounts but low reviews may have quality issues
Declining rating trends signal customer satisfaction problems
Top-performing categories guide inventory and marketing decisions

5. Evaluation of insights.

The true value of Power BI lies in how insights influence decisions.

Marketing teams adjust campaigns based on customer behavior
Sales teams focus on high-performing products
Management monitors KPIs in real time

Power BI connects technical analysis directly to business strategy.

Conclusion

Power BI is more than charts and visuals. Analysts use it to:

Clean and structure messy data
Build models that reflect real business processes
Apply business logic using DAX
Deliver dashboards that support confident decision-making

Note:Power BI helps you listen to data, make sense of it, and move smart.

Data Modelling and schemas in power BI.

Elizabeth Njihia — Mon, 02 Feb 2026 13:58:14 +0000

Introduction.

Mastering data modeling is the "secret sauce" that separates a slow, confusing Power BI report from one that is lightning-fast and dead-accurate. If your visuals are lagging or your numbers don't seem to add up, the problem usually isn't the visual it’s the model. This article explains schemas and data modelling in Power BI using simple understandable language.

What Is Data Modelling in Power BI?

Data modelling is the process of Organizing data into tables and defining relationships between those tables.

A good data model helps:

Reports run faster
Calculations give correct results
Dashboards remain easy to maintain

Fact tables vs Dimension tables

Fact Tables: These store the numbers. Think of them as a list of events.

Note: They are usually very long (millions of rows) and contain Foreign Keys to connect to other tables.

Dimension Tables: These provide the context. They answer the "Who, What, Where, and When."
Examples: Product names, Customer details, Store locations...

Note: They are usually wider (many descriptive columns) but shorter than fact tables.

Star Schema

The star schema is the most recommended data model in Power BI.

Structure:

One central fact table
Multiple dimension tables
All dimensions connect directly to the fact table

It looks like a star ⭐.

Why Star Schema Is Best

Easy to understand
Faster report performance
Works best with DAX
Fewer relationship error

Snowflake Schema

A snowflake schema is a more complex version of the star schema.

Structure:

Dimension tables are split into multiple related tables
Dimensions connect to other dimensions

Snowflake Schema Example:
Instead of one Product table:

Product → Subcategory → Category

This reduces data duplication but increases complexity.

Downsides of Snowflake Schema

More relationships
Slower performance
Harder to debug errors
More complex DAX formulas

In Power BI, star schema is usually preferred over snowflake schema.

Relationships in Power BI

Relationships define how tables are connected.

Common Relationship Types;

One-to-Many (most common)
Many-to-One
Many-to-Many

One-to-Many Relationship
Example: One Product → Many Sales records

Product ID appears once in the Product table but many times in the Sales table.

Many-to-Many Relationship
Occurs when: Both tables contain duplicate keys

This type can cause:

Incorrect totals
Confusing result

Direction: Always aim for Single Direction filtering (from the Dimension to the Fact table). Bi-directional filtering can cause "ambiguity," where Power BI gets confused about how to apply filters, leading to incorrect numbers.

Why data modelling is critical.

Performance: Power BI’s engine (VertiPaq) is designed to compress and scan columns in a Star Schema. Large flat tables eat up memory and make slicers feel sluggish.
Accuracy: Proper modeling prevents "double counting." If your relationships are messy, a simple SUM might return a number way higher than reality.
DAX Simplicity: Writing formulas (DAX) is much easier when your model is clean. A Star Schema makes time-intelligence functions (like "Sales Year-over-Year") work instantly.

Common Data Modelling Mistakes.

Using too many fact tables
Mixing fact and dimension data in one table
Creating unnecessary many-to-many relationships
Using snowflake schemas when star schemas work better

Conclusion

Data modelling is the foundation of every Power BI report.
By using fact and dimension tables, applying a star schema, and creating proper relationships, you ensure your reports are fast, accurate, and scalable.

Great visuals start with great models — design your data right, and Power BI will do the rest 📊✨

Introduction to MS Excel: Simple guidelines to understand excel.

Elizabeth Njihia — Sun, 25 Jan 2026 16:14:11 +0000

Introduction.

Microsoft Excel is a popular spreadsheet tool by Microsoft that helps users store, organize, analyze, and display data using rows and columns. You don’t need programming skills to start analyzing data with Excel. With simple features like tables, formulas, sorting, and charts, Excel helps you understand data and make decisions based on it.

This article introduces how MS Excel can be used for basic data analytics, using simple explanations and practical examples.

What is Data Analytics.

The process of examining raw data to uncover hidden patterns, trends, and insights, which are then used to make informed, data-driven decisions. It involves collecting, cleaning, and organizing data to transform it into actionable information for solving problems or forecasting future trends.

what find in excel.

Rows (horizontal – labeled with numbers)
Columns (vertical – labeled with letters)
Cells (where rows and columns meet, e.g., A1)

Each cell can store text, numbers, or formulas.

Example of a dataset:

Turning Data into a Table

Tables ensure ease in analysis.

Steps to follow;

Select your data
Press Ctrl + T
Click OK

Benefits of tables:

-Automatic filters
-Better formatting
-Easier analysis

Data sorting.

This is the process of arranging rows of data in a specific, meaningful order. Example: Sort scores from highest to lowest to see top performers.
Steps to follow;
1.Click anywhere in the Score column
2.Go to Data → Sort Z to A

Data Filtering.

Filtering helps you view specific data only.
Example: View only students who scored above 70.
Steps:
1.Click the filter arrow in the Score column
2.Choose Number Filters → Greater Than
3.Enter 70

FORMULAS and FUNCTIONS.

Formulas allow Excel to calculate values automatically.
Arithmetic/Basics:
=A1+B1, =A1-B1, =A1*B1, =A1/B1 (Basic operators)
=SUM(A1:A10): Adds a range of cells.
=AVERAGE(A1:A10): Calculates the mean of a range.
=PRODUCT(A1:A10): Multiplies cells.
=COUNT(A1:A10): Counts cells containing numbers.
=ROUND(A1, 2): Rounds a number to a specific digit.
Logical & Conditional:
=IF(condition, value_if_true, value_if_false): Checks a condition.
=SUMIF(range, criteria, [sum_range]): Sums cells based on a condition.
=COUNTIF(range, criteria): Counts cells based on a condition.
Lookup & Reference:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]): Finds data in a table.

Conditional formatting.

This highlights important data visually.
Example: Highlight scores below 70 in red.

Steps:

Select the Score column
Go to Home → Conditional Formatting
Choose Highlight Cell Rules → Less Than
Enter 70

Use of Pivot Table

A Pivot Table is used to summarize and analyze large amounts of data quickly. It helps you see patterns, totals and averages without writing formulas.
Steps in creating a pivot table;
1.Select your data
2.Go to Insert → PivotTable
3.Click OK
Excel creates a new sheet with Pivot Table fields.

Example Use Case;
Using the student scores data, a Pivot Table can:

Show average score per subject
Show total scores per subject
Count how many students took each subject

How to do it:
1.Drag Subject to Rows
2.Drag Score to Values
3.Choose Average or Sum

Importance of pivot tables.

No formulas needed
Fast data summarization
Easy to update
Ideal for reports and analysis

Creating Charts.

Charts help you visualize data, making it easier to understand trends.
Example: Column Chart for Scores.

Steps:
1.Select the data
2.Go to Insert → Column Chart
3.Choose a chart style

Why Excel Is Good for Beginners in Data Analytics:

Easy to learn
No coding required
Widely used in schools and workplaces
Great for small datasets
Strong visualization tools

Conclusion.

Microsoft Excel is a powerful and beginner-friendly tool for data analytics. With basic features like tables, formulas, sorting, filtering, and charts, you can analyze data and gain useful insights without technical complexity. If you are starting your journey in data analytics, Excel is a perfect place to begin.

Good luck in your journey of analytics.

Mastering Git: A Beginner-friendly guide to Version Control.

Elizabeth Njihia — Sun, 18 Jan 2026 07:47:20 +0000

Introduction.

Git is a version control system used to track changes in files over time. It is commonly used in software development to manage code, keep a history of changes, and work safely on projects.

This article is written from a beginner’s perspective and is intended for readers who are new to coding and Git. It explains what Git is, why it is useful, and introduces basic Git commands with simple explanations.

What is git.

It is an open source for version control system used to track changes in a code during software development.

Why git is useful.

Some of the uses are;

Saving ones progress
Push the code to GitHub
Pull updates from a repository

Repository is where Git stores your project and its history.

Common git commands in summary.

git init

Initializes a new Git repository.
Tracks your project

git status

Shows the current state of your project
Helps you know what files are changed or ready to commit

git add

Stages changes for commit

git add .
git add <filename>

git commit

Records changes in Git history

Importance of commit messages

Maintains a clear and collaborative software development process.
Serve as a permanent historical record.

Example of a simple commit message

git commit -m "Update ReadMe File"

git pull

Fetches and updates code from a remote repository

git push

Uploads your commits to a remote repository

How it helps keep your work backed up

In details:

git init

The git init command is used to start version control in a project. It tells Git to begin tracking changes inside a folder. When this command is run, Git creates a hidden .git directory where it stores all version history and configuration.

You usually use git init once, at the beginning of a project.
git init
git status

The git status command shows the current state of the project. It tells you which files have been changed, which files are staged for commit, and whether your local branch is up to date.

This command is useful because it helps you understand what Git is doing at any moment.
git status
git add

The git add command is used to stage changes. Staging allows you to choose which file changes should be included in the next commit.

Git does not automatically save every change you make. You must explicitly tell Git what to track using git add.
git add .
git commit

The git commit command saves staged changes into Git’s history. Each commit represents a snapshot of the project at a specific point in time.

A commit includes a message that explains what changes were made. Clear commit messages make it easier to understand the project history later.
git commit -m "Update homepage layout"
git pull

The git pull command retrieves the latest changes from a remote repository and merges them into your local project. It ensures your local copy stays up to date.

This command is especially important when working with others or when using GitHub.
git pull
git push

The git push command sends your local commits to a remote repository. This makes your changes available to others and acts as a backup of your work.

You typically push after committing your changes.
git push

Lessons learnt.

Always check the git status
Commit often
Pull before pushing
Git helps avoid losing code

Conclusion.

Practice makes perfect therefore if you are new like myself let's build up confidence by practicing daily. If experienced refine your work flow.

Enjoy coding!🥳