Forem: David

Connecting Power BI to a PostgreSQL Database (Local & Cloud-Aiven)

David — Wed, 18 Mar 2026 09:52:01 +0000

Introduction

Power BI is a powerful business intelligence (BI) tool developed by Microsoft that allows users to analyze data and build interactive dashboards. It is widely used by organizations to transform raw data into meaningful insights for decision-making.

Most companies store their data in SQL databases such as PostgreSQL because they are efficient, reliable, and scalable. These databases allow structured storage of data in tables and support complex queries for analysis.

By connecting Power BI to a SQL database, analysts can:

Access real-time data
Perform advanced analysis
Build automated dashboards
Eliminate manual data entry

Connecting Power BI to a Local PostgreSQL Database

Step 1: Open Power BI Desktop

Launch Power BI Desktop and click Get Data from the Home tab.

Step 2: Choose PostgreSQL Database

Select PostgreSQL database
Click Connect

Step 3: Enter Connection Details

Server: localhost
Database: assignment
Select Import mode

Click OK

Step 4: View Available Tables

Power BI will display all tables in the database.

Step 5: Load Tables

Select the following tables:

customers
products
sales
inventory

Click Load to import them.

Connecting Power BI to Aiven PostgreSQL (Cloud)

Step 1: Get Connection Details from Aiven

From your Aiven dashboard, obtain:

Host
Port
Database name
Username
Password

Step 2: Connect in Power BI

Enter the host instead of localhost
Provide credentials

Installing SSL Certificate (Required for Cloud Connection)

Step 3: Download and Open Certificate

Aiven provides an SSL certificate (.crt) to secure the connection.

Step 4: Install Certificate

Choose Local Machine
Click Next

Step 5: Choose Certificate Store

Select:
Trusted Root Certification Authorities

Step 6: Complete Installation

Click Finish to install the certificate.

Why SSL is Important

SSL certificates:

Encrypt data during transmission
Protect against unauthorized access
Ensure secure database connections

Creating Relationships in Power BI

After loading the tables, go to Model View in Power BI.

Relationships Created:

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

What is Data Modeling?

Data modeling is the process of defining relationships between tables so Power BI understands how data is connected.

Why Relationships Matter:

Enable accurate calculations
Allow filtering across tables
Prevent incorrect aggregations

Power BI Dashboard

Using the loaded data, a dashboard was created with the following visuals:

Sales Performance

Total Sales (Card)
Sales trends

Product Performance

Sales by product
Sales by category

Customer Insights

Sales per customer
Top customers

Inventory Insights

Stock levels per product
Inventory comparison

Conclusion: Why SQL is Important for Power BI

SQL is an essential skill for Power BI analysts because it allows them to:

Retrieve data using SELECT
Filter data using WHERE
Aggregate data using SUM, COUNT, AVG
Combine tables using JOIN

With SQL, analysts can prepare clean and efficient datasets before visualizing them in Power BI.

Final Thoughts

Connecting Power BI to PostgreSQL (both local and cloud) allows for powerful data analysis and real-time insights. By combining SQL knowledge with Power BI visualization capabilities, analysts can build meaningful dashboards that support better business decisions.

Joins and Window Functions

David — Mon, 02 Mar 2026 19:11:41 +0000

In the world of data analytics, SQL is the essential language for extracting insights from relational databases. While basic queries (SELECT, WHERE) are straightforward, the true power of SQL lies in manipulating and analyzing complex data relationships. This is where Joins and Window Functions become indispensable tools.

Understanding these two concepts separates beginners from advanced SQL users. Joins help you connect different datasets, while Window Functions allow you to analyze data within those datasets without losing detail.

1. SQL Joins: Combining Data Across Tables

Relational databases are structured into multiple, smaller tables to prevent data repetition (normalization). For example, a customers table and an orders table are kept separate. Joins enable you to combine these tables based on a related column, usually a Primary Key–Foreign Key relationship such as customers.customer_id = orders.customer_id.

Core Join Types

INNER JOIN
Returns only the rows where there is a match in both tables.
This is used for finding valid, connected data (for example, all orders that belong to a customer).

SELECT customers.name, orders.order_id, orders.amount
FROM orders
INNER JOIN customers
    ON orders.customer_id = customers.customer_id;

LEFT JOIN (LEFT OUTER JOIN)
Returns all rows from the left table, and matched rows from the right table.
If no match exists, the right side will contain NULL values.
Ideal for keeping your main list intact (for example, all customers, even those who have not placed an order).

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

RIGHT JOIN
The opposite of a LEFT JOIN; it keeps all rows from the right table.

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

FULL OUTER JOIN
Returns all rows when there is a match in either table.
It combines the results of both LEFT and RIGHT joins.

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

2. Window Functions: Context-Aware Analytics

Window functions perform calculations across a set of table rows that are related to the current row. Unlike GROUP BY, which collapses rows into a single summary row, window functions keep individual rows intact while adding a calculated column.

The Syntax: `OVER()`

The OVER() clause defines the "window" or subset of data that the function operates on.

PARTITION BY → Divides the rows into groups
Example: PARTITION BY customer_id
ORDER BY → Orders the rows within each partition
Example: ORDER BY amount DESC

Common Window Functions

Ranking Functions

ROW_NUMBER() → Assigns a unique number
RANK() → Assigns rank, skips numbers on ties
DENSE_RANK() → Assigns rank without skipping numbers

Example: Rank orders from highest to lowest amount.

SELECT
    order_id,
    customer_id,
    amount,
    RANK() OVER (ORDER BY amount DESC) AS amount_rank
FROM orders;

Aggregation Functions

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

Example: Running total of order amounts over time.

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

Value Functions

LAG() → Accesses data from a previous row
LEAD() → Accesses data from a following row

Example: Compare each order with the previous one.

SELECT
    order_id,
    amount,
    LAG(amount) OVER (ORDER BY order_id) AS previous_amount
FROM orders;

3. Combining Joins and Window Functions

In analytics, you often join tables first to get a complete picture, and then apply window functions to analyze trends.

Scenario: List all orders with customer names, and rank orders by amount within each customer.

SELECT
    customers.name,
    orders.order_id,
    orders.amount,
    RANK() OVER (
        PARTITION BY orders.customer_id
        ORDER BY orders.amount DESC
    ) AS order_rank
FROM orders
JOIN customers
    ON orders.customer_id = customers.customer_id;

Joins are used to connect two or more tables in a relational database, allowing you to bring related data together from different sources. Because joins combine datasets, they can change the number of rows in the result, either expanding or reducing them depending on the type of join used. They rely on keywords such as JOIN and ON to define how tables relate. Window functions, on the other hand, perform calculations over a set of rows within a single result set without collapsing those rows. Unlike joins, they preserve the original row count while adding computed values like running totals, rankings, or moving averages. They are defined using the OVER() clause, often with PARTITION BY to group related rows for analysis.

Conclusion

By mastering both Joins and Window Functions, one can transform scattered, raw data into meaningful, actionable insights, often without needing complex, multi-step subqueries. These tools are foundational for anyone working in data analytics, backend development, or database administration.

If the aim is to move from intermediate to advanced SQL, this is one of the most valuable skill combinations you can learn.

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

David — Mon, 09 Feb 2026 14:06:03 +0000

Introduction

In the world of business analytics, Power BI has become the bridge between overwhelming data chaos and decisive action.

As one who is still learning and spending some time learning Power BI, I've discovered that analytics is fundamentally about translation: converting raw data into the language of business decisions. This article explores how analysts use Power BI's technical capabilities data transformation, data modeling, and DAX to create dashboards that help inform business decision.

The Reality of Raw Data

The Messy Data Problem

When you first connect to a real world dataset one expects clean, structured information ready for analysis. Instead, you find dates formatted as text, customer names spelled inconsistently, empty cells scattered randomly, and mislabeled columns with zero documentation. This data isn't bad per say lets call it raw data, and handling it is the analyst's first critical responsibility.

According to industry research, data scientists spend approximately 80% of their time on data preparation and cleaning rather than actual analysis. Without proper data cleaning, every subsequent analysis,DAX calculation,and dashboard visualization becomes unreliable. pragmatic.institute

Why Technical Skills Must Translate to Business Context

The gap between technical capability and business impact is where many analytics projects fail.Power BI's strength lies in its ability to facilitate this translation process systematically through its workflow: clean data → structure relationships → define measures → communicate insights.

Data Transformation

Data transformation is where analysts systematically convert unreliable source data into analysis ready datasets. When a healthcare organization uses Power BI to consolidate data across verticals, proper data cleaning reduces the time to access critical information from days to less than 24 hours. This responsiveness directly translates to better patient care decisions and operational agility.

Also consider a retail scenario where if your dataset contains "Nairobi," "NBO," "nairobi," and "NRB" as separate categories, sales analysis will incorrectly show four different locations. This leads to confusion, incorrect inventory decisions, and lost revenue. Power BI's transformation capabilities solve these fundamental issues before they contaminate downstream analysis.

Data Modeling

The Architecture of Relationships

After cleaning, the next translation step involves data modeling which involves structuring how tables relate to each other so Power BI can filter and calculate correctly. This requires understanding two core concepts:

Fact tables: Stores measurable event/transaction data (numeric values) at a specific grain. These tables typically contain many rows representing individual events.

Dimension tables: Provide descriptive context around those events : customer details, product information, date hierarchies, or location data. These tables describe the "who, what, when, where" of your facts.

By establishing clear one-to-many relationships (one customer has many purchases, one product appears in many transactions), Power BI can respond correctly to user interactions and produce accurate aggregations.

Why Model Design Determines Dashboard Success

A well designed data model operates invisibly : users simply interact with slicers and filters, and everything works as expected. Poor modeling, however, creates obvious problems: slicers that don't affect all visuals, incorrect totals, or filters that produce confusing results.

Organizations that implement proper star or snowflake schema designs in Power BI see dramatic improvements in report performance and user satisfaction. The technical modeling work translates directly into business users' ability to explore data independently and make decisions without constantly requesting new reports.

DAX

DAX is a collection of functions, operators, and constants that can be used in a formula, or expression, to calculate and return one or more values. While it appears to be a technical formula language, DAX is fundamentally about defining meaning in business terms.

When creating a "Total Revenue" measure, the analyst must answer business questions:

Should this include or exclude returns?
How do we handle partial refunds?
Do we account for discounts before or after tax?
How should this number respond when users filter by region or time period?

These business logic questions that DAX translates into executable code.

The Operational Efficiency Impact

DAX measures enhance operational efficiency by enabling real-time data analysis and automating complex calculations. For example, a retail company using DAX measures can dynamically monitor inventory levels and adjust stock based on real-time sales trends. Key performance indicators defined through DAX such as monthly revenue growth, profit margins, customer acquisition costs, and inventory turnover provide immediate insights into business performance.

Organizations that effectively implement DAX measures report significant improvements in decision making speed and accuracy. The translation from raw data to actionable KPIs happens automatically as new data flows into the system, enables responding swiftly to market changes.

Dashboards

Only after data is cleaned, modeled, and measured do dashboards truly matter.
Best practices include highlighting key metrics prominently, showing trends rather than isolated values, enabling exploration through interactivity, and maintaining focus and clarity.

Real- World Dashboard Impact

When properly designed, Power BI dashboards deliver measurable ROI.
In retail organizations using Power BI dashboards for inventory management have reduced stockouts and overstock situations by accurately forecasting demand. Marketing teams tailor campaigns to specific customer segments identified through dashboard analytics, enhancing customer satisfaction and boosting sales.

Thinking Beyond Tools

Learning Power BI teaches a fundamental lesson: data analysis is not about tools ,it's about thinking. Power BI simply provides the environment where analysts translate messy data through transformation, structure it through modeling, define meaning using DAX, and communicate insights through dashboards.

Conclusion

The journey from messy data to business action requires systematic translation through Power BI's workflow. Data transformation converts chaotic source data into clean datasets. Data modeling establishes the logical relationships that enable correct filtering and aggregation. DAX measures define business metrics that respond dynamically to user questions. Dashboards communicate insights in visual forms that spark decisions and action.

For analysts, mastering Power BI means developing the ability to think simultaneously about the technical language of data structures, transformations, and calculations, and the business language of decisions, outcomes, and impact.

Schemas and Data Modelling in Power B.I

David — Mon, 02 Feb 2026 17:09:40 +0000

Data modeling

is the architectural backbone of every successful Power BI solution. Whether building an executive dashboard or a complex analytics engine, the structural integrity of the data determines the performance, flexibility, and reporting accuracy

Schemas fall into two primary categories: Star and Snowflake, following general practices of dimensional modeling used widely in analytics and BI. Each defines a different way to model facts and dimensions, balancing simplicity, performance, storage efficiency, and modeling flexibility.

The Star Schema is usually the recommended approach because it, prioritizes read performance over write efficiency.

Star schema

The star schema involves structuring your data into multiple tables such that you have:
Fact tables contain quantitative attributes that you want to measure.
Dimension tables that contain descriptive attributes that group and categorize data from fact tables.

Fact tables

A fact table is the main table that records events (transactions) that happened, so duplicates are normal because the same type of event can occur many times. It’s also the table that changes/updates often compared to dimensions.
eg FactTable -Visits (VisitID, PatientID, DoctorID, FacilityID, DateID, VisitCost, LengthOfStay)

Dimension table

A dimension table is a table that holds information about categorical fields in the fact table and you use to slice and group the facts.
It’s typically smaller than the fact table.
eg Dimension - Patient, Doctor, Facility, Date

Relationships and cardinality

In Power BI, relationships are typically built by matching one column from one table to one column in another table. That means linking each dimension key to the corresponding key in the visits fact table, e.g., Patient[PatientID] → FactVisits[PatientID], Doctor[DoctorID] → FactVisits[DoctorID], Facility[FacilityID] → FactVisits[FacilityID], and Date[DateID] → FactVisits[DateID].

If two tables need to match on more than one field (for example, if you had a scenario where uniqueness is defined by both PatientID and DateID together), you would create a combined key such as PatientDateKey = PatientID & "-" & DateID in both tables and then relate using that single key column.

Cardinality describes how many rows in one table can match rows in another: one-to-one, one-to-many, many-to-one, or many-to-many.If a many-to-many relationship is unavoidable, use a Bridge Table (also known as an Associative Entity) to convert the logic into two one-to-many relationships.

Snowflake Schema

A snowflake schema is a data model where dimension tables are normalized into multiple related tables.

Instead of keeping everything in one large dimension table (like a star schema), you split descriptive data into smaller tables to reduce duplication.
It’s called “snowflake” because dimensions branch out into sub-dimensions, creating a layered structure.

Structure

Fact Table: Stores measurable event/transaction data (numeric values) at a specific grain.
Dimension Tables: Split into multiple connected tables to represent hierarchies.

Example (healthcare):

Patient → Ward → Department → Hospital
Doctor → Specialty → Department

Fact Table: Visits

VisitID
PatientID
DoctorID
WardID
DateID
VisitCost, LengthOfStay

Dimension Tables (normalized)

Patient (PatientID, FullName, WardID)
Ward (WardID, WardName, DepartmentID)
Department (DepartmentID, DepartmentName, HospitalID)
Hospital (HospitalID, HospitalName)

Relationships

Still mainly one-to-many relationships.
The difference is that dimensions connect through multiple levels (Patient → Ward → Department → Hospital), so filter paths are longer than in a star schema.

Good data modelling is critical because it directly dictates the efficiency, accuracy, and usability of your reports. A well-structured model with clear relationships and reduced redundancy improves query performance, allowing dashboards to load faster even as data scales. Furthermore, by enforcing data integrity constraints and defining logical connections between entities, a strong model ensures that calculations, filters, and aggregations remain accurate across the entire system, preventing errors that could lead to misleading insights.

A Beginner’s Guide to Git: Version Control, Tracking Changes, and Pushing Code to GitHub

David — Sun, 18 Jan 2026 11:27:34 +0000

Understanding Push, Pull, and Commit

If you're new to programming, terms like Git, GitHub, push, pull, and commit might seem confusing.

This guide breaks down these concepts step by step, using real commands from a beginner’s workflow.

What You’ll Learn

What Git and version control are
How to configure Git for the first time
How to track file changes
How to push and pull code using GitHub

Understanding Version Control

Version control is a system that helps you:

Track changes made to files over time

Revert to previous versions when something breaks

Collaborate safely with others on the same project

Git is the most popular version control system, while GitHub is an online platform where Git repositories are stored and shared.

Setting Up Git

Before using Git, configure your identity. This information appears in your commit history and helps others know who made changes.

git config --global user.name "dmungai"
git config --global user.email "dmungai@gmail.com"

Verify your configuration worked:

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

If Git prints your username and email, you're all set

Git Repositories Explained

A Git repository is simply a folder that Git is tracking.

If you run Git commands outside a repository, you’ll encounter this error:

fatal: not a git repository (or any of the parent directories): .git

This means you're not inside a Git-tracked project folder.

Creating Your Working Environment

Start by creating a folder for your project:

mkdir testfolderforcredentials
cd testfolderforcredentials

This folder remains a normal directory until you introduce Git.

Cloning from GitHub

To work on an existing project, clone it from GitHub:

git clone https://github.com/dmungai97/dmungai.git
cd dmungai97

Cloning does three things:

Downloads the project
Creates a .git folder inside it
Automatically connects it to GitHub

Creating and Tracking Files

Create a new file and add some content:

vi testfile
cat testfile

Git doesn't automatically track new files. Check what Git sees:

git status

You'll see untracked files listed.

To start tracking them:

git add testfile
git add testfile.txt

Run git status again to see the files are now staged and ready to be committed.

Committing Your Changes

A commit creates a snapshot of your project at a specific point in time:

git commit -m "this is test file for credentials - 1st method"

Commits are saved locally

Good commit messages are clear and concise

Pushing to GitHub

Send your commits to GitHub so others can see them:

git push

Your files are now saved locally and visible on GitHub.

Pulling Updates

If changes were made on GitHub or by another team member, download them:

git pull

Always pull before starting new work to avoid conflicts.

Quick Reference

git clone – Download a repository from GitHub
git status – Check which files are tracked or modified
git add – Stage files for commit
git commit -m "message" – Save changes
git push – Upload commits to GitHub
git pull – Download latest changes

Forem: David

Connecting Power BI to a PostgreSQL Database (Local & Cloud-Aiven)

Introduction

Connecting Power BI to a Local PostgreSQL Database

Step 1: Open Power BI Desktop

Step 2: Choose PostgreSQL Database

Step 3: Enter Connection Details

Step 4: View Available Tables

Step 5: Load Tables

Connecting Power BI to Aiven PostgreSQL (Cloud)

Step 1: Get Connection Details from Aiven

Step 2: Connect in Power BI

Installing SSL Certificate (Required for Cloud Connection)

Step 3: Download and Open Certificate

Step 4: Install Certificate

Step 5: Choose Certificate Store

Step 6: Complete Installation

Why SSL is Important

Creating Relationships in Power BI

Relationships Created:

What is Data Modeling?

Why Relationships Matter:

Power BI Dashboard

Sales Performance

Product Performance

Customer Insights

Inventory Insights

Conclusion: Why SQL is Important for Power BI

Final Thoughts

Joins and Window Functions

1. SQL Joins: Combining Data Across Tables

Core Join Types

2. Window Functions: Context-Aware Analytics

The Syntax: OVER()

Common Window Functions

3. Combining Joins and Window Functions

Conclusion

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

Introduction

The Reality of Raw Data

The Messy Data Problem

Why Technical Skills Must Translate to Business Context

Data Transformation

Data Modeling

The Architecture of Relationships

Why Model Design Determines Dashboard Success

DAX

The Operational Efficiency Impact

Dashboards

Real- World Dashboard Impact

Thinking Beyond Tools

Conclusion

Schemas and Data Modelling in Power B.I

Data modeling

Star schema

Fact tables

Dimension table

Relationships and cardinality

Snowflake Schema

Structure

Fact Table: Visits

Dimension Tables (normalized)

Relationships

A Beginner’s Guide to Git: Version Control, Tracking Changes, and Pushing Code to GitHub

Understanding Push, Pull, and Commit

What You’ll Learn

Understanding Version Control

Setting Up Git

Git Repositories Explained

Creating Your Working Environment

Cloning from GitHub

Creating and Tracking Files

Committing Your Changes

Pushing to GitHub

Pulling Updates

Quick Reference

The Syntax: `OVER()`