Forem: Beverline Otiende

Connecting PowerBI to a PostgreSQL database

Beverline Otiende — Sun, 29 Mar 2026 03:15:44 +0000

PowerBI is a business intelligence (BI) tool developed by Microsoft, designed to help users visualize data, create interactive dashboards and make informed decisions.
It is widely used across industries because it simplifies complex data and presents it in a visually compelling way.
PowerBI enables data analysts to :

Connect to multiple data sources
Transform and clean data
Do Data modelling
Build interactive dashboards and reports
Share insights across teams and organizations

One of the strengths of PowerBI is its ability to connect to multiple data sources, particularly SQL databases.

SQL (Structured Query Language) databases are the backbone of modern data storage systems.
SQL databases are very essential because they:

Store large volumes of structured data
Ensure data integrity and consistency
Allow efficient querying and data retrieval
Support complex analytical operations

By connecting PowerBI to SQL databases, companies can unlock real-time insights, improve data accuracy and streamline their analytics workflow.

In this article, we will walk through how to connect Power BI to both a local PostgreSQL database and a cloud-hosted PostgreSQL database (Aiven), and how to model your data effectively inside Power BI.

Connecting PowerBI to a local PostgreSQL database
Let us first see how to connect PowerBI to a local PostgreSQL database.

Step 1: Open Power BI Desktop Application
Launch Power BI Desktop on your computer if you have it installed. If not you can download it from the Microsoft store here PowerBI Desktop download link

Step 2: Click “Get Data”
On the Home ribbon, click Get Data to view all available data sources.

Step 3: Select PostgreSQL Database
Choose the database category, then select PostgreSQL Database

After selecting the PostgreSQL, press Connect.

Step 4: Enter Connection Details
After pressing Connect, you will be prompted to enter your postgreSQL connection details.

You will have to check your local postgreSQL connection to check on the connection details i.e; right click on your database then select Edit connection, it will bring you to this page;

From this page you are able to get the server name(host), database, port and other information.
Server: localhost:5432 (you put your server name:then port)
Database: your database name.

After putting your server name and database name, click OK.

Step 5: Provide Credentials
Then Enter your Username and password from your local postgreSQL connection.
It should look like this;

Then Click Connect.

Step 6: Load Data
After Clicking Connect, Power BI will display a Navigator window showing available tables on the left.

Select the tables you need by ticking their respective checkboxes.
For me I'm choosing the tables;

customers
products
sales
inventory

My selected tables have "assignment." before their names because they are in a schema called assignment.

After selecting your tables, you can choose to load
or Transform Data or Cancel depending on what you want to with the data first.
For my case I'm selecting Load.
After Selecting Load, the tables then get loaded to your PowerBI and you can be able to perform data cleaning, data validation, data modelling, data analysis and visualization.

You can navigate from the right side to select your tables and work on them.

Connecting PowerBI to a cloud database-Aiven PostgreSQL database
Cloud databases are increasingly popular today because they allow us to have remote access to our databases, ensure scalability and ensure high availability of our databases.
Aiven provides managed PostgreSQL services in the cloud.

Step 1: Go to Aiven and check for your PostgreSQL database connection if you have one. If you don't have one then create a new database connection.

If you have a connection, open it, if you don't have one then Create service from the top right corner.
Open your SQL connection from Aiven by double clicking it. Then go to Connection information.

From the Connection Information, check for:

Host (e.g., your-db.aivencloud.com)
Port
Database name
Username
Password

Step 2: Download SSL Certificate
Aiven enforces secure connections using SSL (Secure Sockets layer).
Download the CA Certificate (usually a .pem file) from your Aiven service dashboard.

After clicking the CA certificate download, it will be downloaded and you have to save it in a secure and accessible location on your computer.
SSL is Important because it ensures:

Data encryption during transmission
Protection against unauthorized access
Secure communication between Power BI and the cloud database. Without SSL, your data could be exposed to security risks.

Step 3: Install PostgreSQL ODBC Driver
To be able to connect PowerBI to an aiven PostgreSQL database, you need a PostgreSQL ODBC Driver.
You can download it using the link Download PostgreSQL ODBC Driver here
After installing in, open the ODBC Data Sources from your computer.

Step 4: Create an ODBC connection
After opening ODBC Data Sources
Go to System DSN then click Add
Select PostgreSQL Unicode(x64)

Click Finish then it will open up the window below.
Input your connection details from your Aiven PostgreSQL database as shown below;

Step 5: Configure SSL
Enable SSL mode by Setting SSL Mode to require
Provide the certificate file path if required

Click Test to see if your connection is Okay. If Connection is okay, proceed and save. If not recheck your connection details

Step 6: Launch PowerBI and Get data from your ODBC
After saving, close it the go and open your PowerBI desktop.
Go to Get Data and search for ODBC

Select ODBC and click Connect
Select your Data source Name and click okay

Then input your username and password from your Aiven connection then press Connect.
Step 6: Select your tables from the Navigator
A navigator window will open up. Then choose the schema where your tables are and then select your tables. For me my schema is assignment and my tables are Customers, inventory, products and sales.
Click Load to load your tables.

Loading tables in PowerBI and creating relationships
After loading the tables, PowerBI automatically detects the relationships between the tables based on shared columns.
However it is necessary to verify these relationships before working on the data to ensure accuracy.

For my case, I have four tables;

Customers table: stores customer information
Products table: contains products details
Sales table: records sales transactions
Inventory table: tracks stocks levels

To create relationship between tables in PowerBI;
Launch your PowerBI desktop if you haven’t.
Go to the Modelling tab in your PowerBI and select Manage Relationships.

Check the relationships if they are okay and select the relationship you want to edit if any then click edit.

Edit the relationship appropriately then Save.

Proper relationships in PowerBI are important because they allow analysts to:

Join tables correctly.
Aggregate data accurately.
Enable filtering across visuals.
Build meaningful dashboards.

Importance of SQL skills for PowerBI analysts
While PowerBI is a powerful visualization tool, SQL remains a critical skill for any data analysts.
SQL helps analysts to:

Retrieve data efficiently from databases
Filter datasets using conditions
Perform aggregations
Join multiple tables
Prepare clean datasets before importing into PowerBI

In real world scenarios, analysts rarely use raw data directly. Instead they Use SQL to extract and shape data, Load the data into PowerBI then they build dashboards and generate insights from the data.

Conclusion
PowerBI and SQL databases together form a powerful combination for data analysis and business intelligence.
SQL databases provide a reliable and structured way to store and manage data whereas PowerBI transforms that data into clear and interactive insights that support data driven decision making.
Being able to connect Power BI and SQL together gives analysts a competitive advantage and organizations that leverage on this integration make smarter, faster and more informed decisions, which translate to better outcomes and sustained growth.

Advanced SQL for Data Analytics: Advanced Techniques Every Data Analyst Should Know

Beverline Otiende — Sun, 22 Mar 2026 12:29:08 +0000

In today’s data-driven world, many organizations heavily rely on data to help them make informed decisions, to optimize their operations and to help them gain a competitive advantage over their competitors.
At the heart of this data reliance and transformation lies SQL (Structured Query Language), which is the fundamental tool used to interact with relational databases.
SQL is the backbone of data analytics because most business data is usually stored in relational databases such as PostgreSQL, MySQL or Microsoft SQL Server.

Data analysts use SQL to:
•Retrieve data for reporting and dashboards
•Clean and transform raw data
•Perform aggregations and calculations
•Analyze trends and patterns
•Support decision-making processes

While basic SQL skills such as creating databases and tables, inserting data into tables, updating tables and deleting from tables are essential, they are not enough to handle the complexity of real world data problems.
Advanced SQL techniques are required to help data analysts to extract deeper insights, handle large datasets efficiently and solve complex business challenges.
These advanced SQL techniques go beyond simple queries and include concepts such as

Joins
Window functions
Common Table Expressions (CTEs)
Subqueries
Stored Procedures

In this article, I explore more about these advanced SQL techniques and how they are applied in real-world data analytics scenarios.
This article is designed to help beginners understand not just the syntax, but also the practical application of these techniques in business environments.

JOINS

Joins in SQL allow analysts to combine data from different tables into one result set based on a related column.

Let us use these two tables below (Products table and Purchases table) to understand more about the different types of SQL Joins.

Below is the products table:

products Table
| # | Product ID | Product Name | Price  |
| - | ---------- | ------------ | ------ |
| 1 | 1          | Laptop       | 55,000 |
| 2 | 2          | Phone        | 10,000 |
| 3 | 3          | Charger      | 2,000  |
| 4 | 4          | Mouse        | 500    |

Below is the purchases table:

purchases table
| # | sale_id  |product_id| quantity | total_purchase |       |
| - | --------| -------- | --------- | -------------- |
| 1 | 1       | 1        | 2         | 110,000        |
| 2 | 2       | 2        | 3         | 30,000         |
| 3 | 3       | 3        | 2         | 4,000          |
| 4 | 4       | [NULL]   | 1         | 3,500          |
| 5 | 5       | [NULL]   | 4         | 25,000         |
| 6 | 6       | [NULL]   | 5         | 70,000         |

Types of SQL Joins
a) INNER JOIN
Inner Join only returns rows that have matching values in both tables being joined.

E.g. we can check for rows with matching values from our products and purchases tables using the sql query below.

 SELECT * 
 FROM products p
 INNER JOIN purchases s
    on p.product_id = s.product_id;

From this INNER Join query above, you get:

| # | product_id | product_name | price  | sale_id | product_id | quantity | total_purchase |
| - | ---------- | ------------ | ------ | ------- | ---------- | -------- | -------------- |
| 1 | 1          | Laptop       | 55,000 | 1       | 1          | 2        | 110,000        |
| 2 | 2          | Phone        | 10,000 | 2       | 2          | 3        | 30,000         |
| 3 | 3          | Charger      | 2,000  | 3       | 3          | 2        | 4,000          |

b) LEFT JOIN (LEFT OUTER JOIN)
Left Joins returns all the rows from the left table (first table selected) and matching rows from the right table (second table).
If there is no match from the right table, it fills with NULL.

For example lets check for all the rows and columns from the products table and only the corresponding matching values from the purchases table. we perform a Left Join using the query below.

SELECT * 
FROM products p
LEFT JOIN purchases s
   on p.product_id = s.product_id;

From this Left Join we get;

| # | product_id | product_name | price  | sale_id | product_id | quantity | total_purchase |
| - | ---------- | ------------ | ------ | ------- | ---------- | -------- | -------------- |
| 1 | 1          | Laptop       | 55,000 | 1       | 1          | 2        | 110,000        |
| 2 | 2          | Phone        | 10,000 | 2       | 2          | 3        | 30,000         |
| 3 | 3          | Charger      | 2,000  | 3       | 3          | 2        | 4,000          |
| 4 | 4          | Mouse        | 500    | [NULL]  | [NULL]     | [NULL]   | [NULL]         |

c) RIGHT JOIN (RIGHT OUTER JOIN)
Right Joins returns all the rows from the right table (second table selected) and matching rows from the left table (first table).
If there is no match from the left table, it fills with NULL.

For example we can do a Right Join to get the corresponding rows from the products table and all the rows from the purchases table using the query below.

SELECT * 
FROM products p
RIGHT JOIN purchases s
   on p.product_id = s.product_id;

From this right join, we get;

| # | product_id | product_name | price  | sale_id | product_id | quantity | total_purchase |
| - | ---------- | ------------ | ------ | ------- | ---------- | -------- | -------------- |
| 1 | 1          | Laptop       | 55,000 | 1       | 1          | 2        | 110,000        |
| 2 | 2          | Phone        | 10,000 | 2       | 2          | 3        | 30,000         |
| 3 | 3          | Charger      | 2,000  | 3       | 3          | 2        | 4,000          |
| 4 | [NULL]     | [NULL]       | [NULL] | 4       | [NULL]     | 1        | 3,500          |
| 5 | [NULL]     | [NULL]       | [NULL] | 5       | [NULL]     | 4        | 25,000         |
| 6 | [NULL]     | [NULL]       | [NULL] | 6       | [NULL]     | 5        | 70,000         |

d) FULL JOIN (FULL OUTER JOIN)
Full Joins returns all rows from both tables. It is basically a combination of Left Join and Right Join.

For example lets do a Full Join to find all the rows from both the tables using the syntax below.

SELECT * 
FROM products p
FULL JOIN purchases s
   on p.product_id = s.product_id;

From this Full Join we get;

| # | product_id | product_name | price  | sale_id | product_id | quantity | total_purchase |
| - | ---------- | ------------ | ------ | ------- | ---------- | -------- | -------------- |
| 1 | 1          | Laptop       | 55,000 | 1       | 1          | 2        | 110,000        |
| 2 | 2          | Phone        | 10,000 | 2       | 2          | 3        | 30,000         |
| 3 | 3          | Charger      | 2,000  | 3       | 3          | 2        | 4,000          |
| 4 | [NULL]     | [NULL]       | [NULL] | 4       | [NULL]     | 1        | 3,500          |
| 5 | [NULL]     | [NULL]       | [NULL] | 5       | [NULL]     | 4        | 25,000         |
| 6 | [NULL]     | [NULL]       | [NULL] | 6       | [NULL]     | 5        | 70,000         |
| 7 | 4          | Mouse        | 500    | [NULL]  | [NULL]     | [NULL]   | [NULL]         |

e) CROSS JOIN
This returns every combination of rows from both tables and the number of rows multiply.

SELECT * 
FROM products p
CROSS JOIN purchases s;

f) SELF JOIN
Self Join is when you join a table to itself. You need to use aliases to refer to the same table in two roles

Lets explore the rest of the advanced sql functions using these 2 tables below.** clients table and sales table**
Here is the clients table

clients table
| # | client_id | client_name   | city         |
| - | --------- | ------------- | ------------ |
| 1 | 1         | Alice Johnson | New York     |
| 2 | 2         | Bob Smith     | Los Angeles  |
| 3 | 3         | Charlie Brown | Chicago      |
| 4 | 4         | Diana Prince  | Houston      |
| 5 | 5         | Ethan Hunt    | Phoenix      |
| 6 | 6         | Fiona Clark   | Philadelphia |

Below is the sales table

sales table
| # | sale_id | client_id | product_name | quantity | sale_amount |
| - | ------- | --------- | ------------ | -------- | ----------- |
| 1 | 1       | 1         | Laptop       | 3        | 75,000      |
| 2 | 2       | 2         | Phone        | 2        | 25,000      |
| 3 | 3       | 3         | Tablet       | 10       | 25,000      |
| 4 | 4       | 4         | Monitor      | 1        | 35,000      |
| 5 | 5       | 5         | Printer      | 7        | 35,000      |
| 6 | 6       | 6         | Keyboard     | 2        | 1,000       |
| 7 | 7       | [NULL]    | Laptop       | 3        | 85,000      |

WINDOW FUNCTIONS

Window functions in SQL allow you to perform calculations across a set of rows that are somehow related to the current row without grouping them into a single result.
You can use Window functions to rank rows, calculate cumulative totals or to find the difference between consecutive rows in a dataset.
Unlike like aggregate functions like SUM(), AVG(), COUNT(), MIN(), MAX() which return a single result for a group of rows, Window functions return a value for each row while still providing information from the related rows.

The basic syntax of Window function include;

PARTITION BY: This splits the data into groups.
ORDER BY: defines calculation order
OVER(): defines the window

Common window functions include:
a)ROW_NUMBER(): This assigns a unique number to each row in the table.
For example lets assign unique row numbers to our sales table and order by sale_amount in descending order. We use the sql query below;

 SELECT *, 
    ROW_NUMBER() OVER (ORDER BY sale_amount desc) AS row_number
FROM sales;

From this query, we get unique row numbers assigned to our table as shown below. Even if more than one rows have the same value, they get different row numbers.
We get the assigned row_numbers added as an extra column with numbers 1,2,3,4,5,6,7 as;

| sale_id | client_id | product_name | quantity | sale_amount | row_number |
| ------- | --------- | ------------ | -------- | ----------- | ---------- |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 1          |
| 1       | 1         | Laptop       | 3        | 75,000      | 2          |
| 4       | 4         | Monitor      | 1        | 35,000      | 3          |
| 5       | 5         | Printer      | 7        | 35,000      | 4          |
| 2       | 2         | Phone        | 2        | 25,000      | 5          |
| 3       | 3         | Tablet       | 10       | 25,000      | 6          |
| 6       | 6         | Keyboard     | 2        | 1,000       | 7          |

b)RANK(): This assigns ranks to each rows with ties getting the same rank but leaving gaps in subsequent ranks.
Lets use our sales table to rank our rows and order by sale_amount in descending order.
We use the sql query;

 SELECT *, 
    RANK() OVER (ORDER BY sale_amount desc) AS rank
FROM sales;

From this query we get a new column rank, and rows with the same sales amount get the same rank value and then there are gaps to subsequent ranks. i.e we get 1, 2, 3, 3, 5,5,7. there is a gap between 3 and 5 because we have two rank 3s. same as for the two rank 5s then rank 7.

| sale_id | client_id | product_name | quantity | sale_amount | rank  |
| ------- | --------- | ------------ | -------- | ----------- | ----- |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 1        |
| 1       | 1         | Laptop       | 3        | 75,000      | 2          |
| 4       | 4         | Monitor      | 1        | 35,000      | 3          |
| 5       | 5         | Printer      | 7        | 35,000      | 3          |
| 2       | 2         | Phone        | 2        | 25,000      | 5          |
| 3       | 3         | Tablet       | 10       | 25,000      | 5          |
| 6       | 6         | Keyboard     | 2        | 1,000       | 7          |

c)DENSE_RANK() : This assigns ranks to each rows with ties getting the same rank but does not leave gaps in subsequent ranks.
Lets use our sales table to dense_rank our rows and order by sale_amount in descending order.
We use the sql query;

 SELECT *, 
    DENSE_RANK() OVER (ORDER BY sale_amount desc) AS dense_rank
FROM sales;

From this query we get a new column dense_rank, and rows with the same sales amount get the same dense_rank value and there are no gaps to subsequent ranks. i.e we get 1, 2, 3, 3, 4, 4, 5.
From this DENSE_RANK query, we get;

| sale_id | client_id | product_name | quantity | sale_amount | dense_rank |
| ------- | --------- | ------------ | -------- | ----------- | ---------- |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 1          |
| 1       | 1         | Laptop       | 3        | 75,000      | 2          |
| 4       | 4         | Monitor      | 1        | 35,000      | 3          |
| 5       | 5         | Printer      | 7        | 35,000      | 3          |
| 2       | 2         | Phone        | 2        | 25,000      | 4          |
| 3       | 3         | Tablet       | 10       | 25,000      | 4          |
| 6       | 6         | Keyboard     | 2        | 1,000       | 5          |

d)LEAD(): Allows you to compare the value of the current row with the value of the next row.
To demonstrate LEAD, lets do a Right join on the clients and sales table. Then calculate the lead sale amount and order by sale amount in descending order.
We use the query below;

 SELECT s.sale_id, c.client_name, s.product_name, s.sale_amount, 
    LEAD(s.sale_amount) OVER (ORDER BY s.sale_amount DESC) AS next_sale_amount
FROM clients c
RIGHT JOIN sales s
    on c.client_id = s.client_id;

From this query we get a new column next_sale_amount and it helps us compare the current sale_amount to the next sale_amount in descending order as shown below;

| sale_id | client_name   | product_name | sale_amount |next_sale_amount   |
| ------- | ------------- | ------------ | ----------- | ------ |
| 7       | [NULL]        | Laptop       | 85,000      | 75,000 |
| 1       | Alice Johnson | Laptop       | 75,000      | 35,000 |
| 4       | Diana Prince  | Monitor      | 35,000      | 35,000 |
| 5       | Ethan Hunt    | Printer      | 35,000      | 25,000 |
| 2       | Bob Smith     | Phone        | 25,000      | 25,000 |
| 3       | Charlie Brown | Tablet       | 25,000      | 1,000  |
| 6       | Fiona Clark   | Keyboard     | 1,000       | [NULL] |

e)LAG(): Allows you to compare the value of the current row with the value of the previous row.
To demonstrate LAG, lets do a Right join on the clients and sales table. Then calculate the LAG sale_amount and order by sale_amount in descending order.
We use the query below;

SELECT s.sale_id, c.client_name, s.product_name, s.sale_amount, 
      LAG(s.sale_amount) OVER (ORDER BY s.sale_amount DESC) as prev_sale_amount
FROM clients c
RIGHT JOIN sales s
    on c.client_id = s.client_id;

From this query we get a new column prev_sale_amount and it helps us compare the current sale_amount to the previous sale_amount in descending order as shown below;

| sale_id | client_name   | product_name | sale_amount | prev_sale_amount |
| ------- | ------------- | ------------ | ----------- | ---------------- |
| 7       | [NULL]        | Laptop       | 85,000      | [NULL]           |
| 1       | Alice Johnson | Laptop       | 75,000      | 85,000           |
| 4       | Diana Prince  | Monitor      | 35,000      | 75,000           |
| 5       | Ethan Hunt    | Printer      | 35,000      | 35,000           |
| 2       | Bob Smith     | Phone        | 25,000      | 35,000           |
| 3       | Charlie Brown | Tablet       | 25,000      | 25,000           |
| 6       | Fiona Clark   | Keyboard     | 1,000       | 25,000           |

f)NTILE(): This is used to divide rows into quartiles based on the total.
e.g. NTILE(4) – Divides the total rows into 4 equals parts
NTILE(10) – Divides total rows into 10 equal parts
Lets divide the sales table into 3 groups and order by quantity,(NTILE(3)).
We use the query;

SELECT *, 
    NTILE(3) OVER (ORDER BY quantity desc) as sale_amount_ntile
FROM sales;

From the query we get our sales table divided into 3 quartiles;

| sale_id | client_id | product_name | quantity | sale_amount | sale_amount_ntile |
| ------- | --------- | ------------ | -------- | ----------- | ----------------- |
| 3       | 3         | Tablet       | 10       | 25,000      | 1                 |
| 5       | 5         | Printer      | 7        | 35,000      | 1                 |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 1                 |
| 1       | 1         | Laptop       | 3        | 75,000      | 2                 |
| 2       | 2         | Phone        | 2        | 25,000      | 2                 |
| 6       | 6         | Keyboard     | 2        | 1,000       | 3                 |
| 4       | 4         | Monitor      | 1        | 35,000      | 3                 |

g)SUM() OVER() : is used to calculate the cumulative sum or partitioned sum of a column across a set of rows while preserving each individual row in a result set.
It allows you to compute running totals or group total without losing the detail of each row.

For example lets compute the running totals of the sale_amount using the query below;

SELECT *, 
    SUM(sale_amount) OVER (ORDER BY sale_amount desc) as     rounding_total
FROM sales;

From this query you get a rounding totals column;

| sale_id | client_id | product_name | quantity | sale_amount | rounding_total |
| ------- | --------- | ------------ | -------- | ----------- | -------------- |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 85,000         |
| 1       | 1         | Laptop       | 3        | 75,000      | 160,000        |
| 4       | 4         | Monitor      | 1        | 35,000      | 230,000        |
| 5       | 5         | Printer      | 7        | 35,000      | 230,000        |
| 2       | 2         | Phone        | 2        | 25,000      | 280,000        |
| 3       | 3         | Tablet       | 10       | 25,000      | 280,000        |
| 6       | 6         | Keyboard     | 2        | 1,000       | 281,000        |

h)PARTITION BY(): This divides the result set into partitions and the window functions work independently within each partition.

For example lets get the Average of the sale_amount and partition by quantity.

SELECT *, 
    AVG(sale_amount) OVER (PARTITION BY quantity) as avg_sale_amount
FROM sales;

This query calculates the average for ever quantity as shown below;

| sale_id | client_id | product_name | quantity | sale_amount | total_quantity |
| ------- | --------- | ------------ | -------- | ----------- | -------------- |
| 4       | 4         | Monitor      | 1        | 35,000      | 35,000         |
| 2       | 2         | Phone        | 2        | 25,000      | 13,000         |
| 6       | 6         | Keyboard     | 2        | 1,000       | 13,000         |
| 1       | 1         | Laptop       | 3        | 75,000      | 80,000         |
| 7       | [NULL]    | Laptop       | 3        | 85,000      | 80,000         |
| 5       | 5         | Printer      | 7        | 35,000      | 35,000         |
| 3       | 3         | Tablet       | 10       | 25,000      | 25,000         |

SUBQUERIES (Nested queries)

A Subquery is a SQL query written inside another query (a query within a query).
Subqueries allow you to perform an operation that depends on the result of another query.
We mainly use subqueries in order to simplify complex logic, to calculate intermediate results and to filter data dynamically.

Subqueries can appear in the:

a)The SELECT clause: These subqueries add an extra computed column to the result set. Each subquery executes once for every row of the outer query.
Here is an example of a query in the SELECT clause;

SELECT c.client_id, c.client_name, c.city ,(
    SELECT SUM(s.quantity) AS total_quantity
    FROM sales s
    WHERE c.client_id =s.client_id
    )
FROM clients c;

From this query we get

| client_id | client_name   | city         | total_quantity |
| --------- | ------------- | ------------ | -------------- |
| 1         | Alice Johnson | New York     | 3              |
| 2         | Bob Smith     | Los Angeles  | 2              |
| 3         | Charlie Brown | Chicago      | 10             |
| 4         | Diana Prince  | Houston      | 1              |
| 5         | Ethan Hunt    | Phoenix      | 7              |
| 6         | Fiona Clark   | Philadelphia | 2              |

b)The FROM clause: These subqueries create a temporary or derived table. The outer table can then select or filter data from it.

Below is an example of a subquery in the FROM clause;

SELECT * 
FROM (
    SELECT product_name, quantity, SUM(sale_amount) AS total_sales
    FROM sales
    GROUP BY product_name,quantity
    )
WHERE total_sales > 25000;

From this query we get;

| product_name | quantity | total_sales |
| ------------ | -------- | ----------- |
| Monitor      | 1        | 35,000      |
| Printer      | 7        | 35,000      |
| Laptop       | 3        | 160,000     |

c)The WHERE clause: These allow the outer query to filter results based on another query’s output.

Here is an example of a subquery in the WHERE clause;

SELECT c.client_name, c.city 
FROM clients c
WHERE client_id IN (
    SELECT s. client_id
    FROM sales s
    WHERE sale_amount > 25000
    );

From this query we get;

| client_name   | city     |
| ------------- | -------- |
| Alice Johnson | New York |
| Diana Prince  | Houston  |
| Ethan Hunt    | Phoenix  |

Subqueries can either be Correlated subqueries or Non-correlated queries.
Correlated subqueries: These subqueries depend on the outer query. They are evaluated once per row of the outer query.

Non-Correlated subqueries: These subqueries do not depend on the outer query. They can be executed on their own.

COMMON TABLE EXPRESSIONS (CTEs)

A CTE in SQL is basically creating a temporary table or result set and then querying that temporary table. The temporary table created only exists during the execution of a single SQL query.
A CTE helps make your SQL query easier to read and organize especially when your query becomes long or has multiple subqueries.
The Syntax of a CTE is;

WITH cte_name AS (
--SQL query that defines the temporary result
SELECT…
FROM
)
SELECT *
FROM cte_name;

Let us now explore different ways to write CTEs:

a)Basic CTEs: These are direct and straight forward CTEs.
For example, let us create a CTE sales_summary with columns product_name, quantity and total_sale_amount. Then we query this CTE.

 WITH sales_summary AS (
    SELECT product_name, quantity, SUM(sale_amount)
    FROM sales
    GROUP BY product_name, quantity
    )
 SELECT *
 FROM sales_summary;

From this query, we get;

| product_name | quantity | total_amount|
| ------------ | -------- | -------     |
| Monitor      | 1        | 35,000      |
| Printer      | 7        | 35,000      |
| Keyboard     | 2        | 1,000       |
| Tablet       | 10       | 25,000      |
| Laptop       | 3        | 160,000     |
| Phone        | 2        | 25,000      |

b)CTEs with Joins: This is where Joins are incorporated into the CTEs.

Here is an example of a sql query of a CTE with a join.

WITH client_purchases AS (
    SELECT s.client_id, c.client_name, s.product_name, SUM(s.quantity) AS total_quantity
    FROM sales s
    JOIN clients c
        ON s.client_id = c.client_id
    GROUP BY s.client_id, c.client_name, product_name
    )
 SELECT *
 FROM client_purchases
 ORDER BY total_quantity desc;

From this query we get;

| client_id | client_name   | product_name | total_quantity |
| --------- | ------------- | ------------ | -------------- |
| 3         | Charlie Brown | Tablet       | 10             |
| 5         | Ethan Hunt    | Printer      | 7              |
| 1         | Alice Johnson | Laptop       | 3              |
| 6         | Fiona Clark   | Keyboard     | 2              |
| 2         | Bob Smith     | Phone        | 2              |
| 4         | Diana Prince  | Monitor      | 1              |

c)Multiple CTEs in one query: This involves defining more than one CTE separated by commas.

Here is a sql query with multiple CTEs;

with client_purchases as (
    select s.client_id, c.client_name, s.product_name
    from sales s
    join clients c
        on s.client_id = c.client_id
    group by s.client_id, c.client_name, product_name
    ),
total_sales as(
    select s.client_id, SUM(s.sale_amount) as total_sale_amount
    from sales s
    group by s.client_id
    )
 select cp.client_id, cp.client_name, cp.product_name, ts.total_sale_amount
 from client_purchases cp
 join total_sales ts
    on cp.client_id = ts.client_id

From this query, we get;

| client_id | client_name   | product_name | total_sale_amount |
| --------- | ------------- | ------------ | ----------------- |
| 1         | Alice Johnson | Laptop       | 75,000            |
| 4         | Diana Prince  | Monitor      | 35,000            |
| 5         | Ethan Hunt    | Printer      | 35,000            |
| 2         | Bob Smith     | Phone        | 25,000            |
| 3         | Charlie Brown | Tablet       | 25,000            |
| 6         | Fiona Clark   | Keyboard     | 1,000             |

d)Recursive CTEs: used when you need to deal with hierarchial data such as managers and employees reporting chains.

     **<u>STORED PROCEDURES</u>**

A stored procedure is a named block of sql logic stored in a database and can be executed by calling.
Stored procedures are mainly used to automate repetitive tasks, for validation and to ensure consistency.

The syntax for Stored procedures is;

CREATE OR REPLACE PROCEDURE procedure_name 
LANGUAGE plpgsql 
AS $$ 
BEGIN 
   -- SQL statements 
END; 
$$;

Explanation of the stored procedure syntax:

CREATE → creates the procedure
OR REPLACE PROCEDURE → updates the procedure if it already exists
procedure_name → name of your procedure
LANGUAGE plpgsql(procedure language for postgresql) → enables procedural features
AS $$ ... $$ → defines the body of the procedure
BEGIN ... start of procedure logic
END; → start/end of procedure logic

After creating the procedure, you can call it using the syntax:

CALL procedure_name(...)

Let us use our clients table below to demonstrate on stored procedures.

clients table
| # | client_id | client_name   | city         |
| - | --------- | ------------- | ------------ |
| 1 | 1         | Alice Johnson | New York     |
| 2 | 2         | Bob Smith     | Los Angeles  |
| 3 | 3         | Charlie Brown | Chicago      |
| 4 | 4         | Diana Prince  | Houston      |
| 5 | 5         | Ethan Hunt    | Phoenix      |
| 6 | 6         | Fiona Clark   | Philadelphia |

For example let us create a stored procedure to add clients to our clients table using the syntax below;

CREATE OR REPLACE PROCEDURE add_clients(
c_client_name varchar,
c_city varchar)
LANGUAGE plpgsql
AS $$
BEGIN 
    INSERT INTO clients(client_name, city)
    VALUES(c_client_name, c_city);
END;
$$;

This query above creates a stored procedure named add_clients

To be able to call this stored procedure 'add_clients', we use the query below;

CALL add_clients('Sylvia Otiende', 'Liverpool');

This adds another client Sylvia Otiende in Liverpool to our clients list as shown below;

| client_id | client_name    | city         |
| --------- | -------------- | ------------ |
| 1         | Alice Johnson  | New York     |
| 2         | Bob Smith      | Los Angeles  |
| 3         | Charlie Brown  | Chicago      |
| 4         | Diana Prince   | Houston      |
| 5         | Ethan Hunt     | Phoenix      |
| 6         | Fiona Clark    | Philadelphia |
| 7         | Sylvia Otiende | Liverpool    |

Let us update our clients table and update the city of client_id 2 (Bob Smith) to Nairobi.
To create the update_clients procedure, we use the syntax below;

CREATE OR REPLACE PROCEDURE update_clients(
c_client_id int,
c_city varchar)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE clients
    SET city = c_city
    WHERE client_id = c_client_id;
END;
$$;

To call and update client_id 2 city to Nairobi, we use the query;

CALL update_clients(2, 'Nairobi');

From calling , we get client_id 2 city updated from Los Angeles to Nairobi.

| client_id | client_name    | city         |
| --------- | -------------- | ------------ |
| 1         | Alice Johnson  | New York     |
| 3         | Charlie Brown  | Chicago      |
| 4         | Diana Prince   | Houston      |
| 5         | Ethan Hunt     | Phoenix      |
| 6         | Fiona Clark    | Philadelphia |
| 7         | Sylvia Otiende | Liverpool    |
| 2         | Bob Smith      | Nairobi      |

You can use stored procedures to;

Insert data into a table
update data in a table
Delete data
For validation

Stored procedures are mainly used in;

Banking to record transactions safely
E-commerce to create orders, update stock, apply discounts
HR to update staff records, compute payroll actions
Education to enroll students, assign classes, update results

Conclusion
In conclusion, advanced SQL techniques play a critical role in transforming raw data into meaningful insights.
By mastering concepts such as joins, window functions, CTEs, subqueries and stored procedures, data analysts are able to handle complex datasets efficiently while maintaining accuracy and clarity.
As organizations continue to rely on data for strategic growth, the ability to apply advanced SQL effectively remains an essential skill for every data analyst aiming to deliver impactful and data-driven solutions.

EXPLORING SQL JOINS AND WINDOWS

Beverline Otiende — Tue, 03 Mar 2026 09:13:51 +0000

In real world analytics, data rarely exists in isolation, rather it is usually stored in separate but related tables. Extracting meaningful insights from these related tables usually require more advanced SQL concepts like;

Joins: which allow you to combine data from multiple tables.
Window functions: allow you to perform calculations across rows without collapsing your dataset.

Mastering these tools transforms SQL from a querying language into a powerful analytical engine. Together they unlock powerful analysis.

In this article we explore more about JOINS and WINDOWS and how we can use them in our analysis.

SQL JOINS
A SQL join combines rows from two or more tables based on a related column. Think of it like merging two Excel sheets sing a common column.

We use joins when;

You have a Normalized Database
Information is split across multiple tables
You need enriched reporting datasets

Types of Joins
Lets use these two tables to understand more about the types of joins.

The first table is the employees table

employees table
| Employee ID | Name    | Department ID | Manager ID | Salary |
| ----------- | ------- | ------------- | ---------- | ------ |
| 1           | Alice   | 1             | NULL       | 50,000 |
| 2           | Bob     | 2             | 1          | 45,000 |
| 3           | Charlie | 1             | 1          | 47,000 |
| 4           | Diana   | 3             | NULL       | 60,000 |
| 5           | Eve     | NULL          | NULL       | 40,000 |

Below is the second table, departments table

 departments table
+----+---------------+-----------------+
| #  | department_id | department_name |
+----+---------------+-----------------+
| 1  | 1             | Engineering     |
| 2  | 2             | Sales           |
| 3  | 3             | Marketing       |
| 4  | 4             | Finance         |
+----+---------------+-----------------+

1.Inner Join
An Inner Join in SQL returns only the rows that have matching values in all the tables involved in the join, excluding any non-matching data.

From our tables employee table and departments table with the common column department_id, If you want to find the rows with matching values on both the tables you do an Inner Join using the SQL query below;

SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments 
ON employees.department_id = departments.department_id;

The result of this Inner Join query is;

+----+---------+-----------------+
| #  | name    | department_name |
+----+---------+-----------------+
| 1  | Alice   | Engineering     |
| 2  | Bob     | Sales           |
| 3  | Charlie | Engineering     |
| 4  | Diana   | Marketing       |
+----+---------+-----------------+

2.Left Join(Left Outer Join)
A Left Join returns all records from the left table and the matched records from the right table. If no match exists Null values are returned for the right table's columns. It is essential for retaining all data from the primary table while appending related data.

From our table 'employees' and 'departments' with a shared column 'department_id', we can do a LEFT JOIN to find all employees and their respective departments.

SELECT employees.name, departments.department_name
FROM employees
LEFT JOIN departments 
ON employees.department_id = departments.department_id;

The result of this Inner Join query is;

+----+---------+-----------------+
| #  | name    | department_name |
+----+---------+-----------------+
| 1  | Alice   | Engineering     |
| 2  | Bob     | Sales           |
| 3  | Charlie | Engineering     |
| 4  | Diana   | Marketing       |
| 5  | Eve     | NULL            |
+----+---------+-----------------+

3.Right Join(Right Outer Join)
A Right Join in SQL returns all records from the right table and the matching records from the left table.

If a row in the right table has no corresponding match in the left table, the columns from the left table will contain Null values in the result set.

From our table 'Employees' and 'Departments' with a shared column 'department_id', we can do a RIGHT JOIN to find all departments and their respective employees.

SELECT employees.name, departments.department_name
FROM employees
RIGHT JOIN departments 
ON employees.department_id = departments.department_id;

This Right Join query returns;

+----+---------+-----------------+
| #  | name    | department_name |
+----+---------+-----------------+
| 1  | Alice   | Engineering     |
| 2  | Bob     | Sales           |
| 3  | Charlie | Engineering     |
| 4  | Diana   | Marketing       |
| 5  | NULL    | Finance         |
+----+---------+-----------------+

4.Full Join (Full Outer Join)
A Full Join is a SQL operation that returns all rows from both the left and right tables, combining matching rows and including non-matching rows from either table with Null values for the columns of the table that lacks a match.

It can be thought of as a combination of a Left Join and a Right Join.

From our table 'Employees' and 'Departments' with a shared column 'department_id', we can do a FULL JOIN to find all employees and all the departments.

SELECT employees.name, departments.department_name
FROM employees
FULL JOIN departments 
ON employees.department_id = departments.department_id;

The result of this full join is;

+----+---------+-----------------+
| #  | name    | department_name |
+----+---------+-----------------+
| 1  | Alice   | Engineering     |
| 2  | Bob     | Sales           |
| 3  | Charlie | Engineering     |
| 4  | Diana   | Marketing       |
| 5  | Eve     | NULL            |
| 6  | NULL    | Finance         |
+----+---------+-----------------+

WINDOW FUNCTIONS
SQL window functions perform calculations across a set of related rows (a "window") and return a single value for each original row, without collapsing the rows. They contrast with standard aggregate functions that return a single value for an entire group of rows.

They are commonly used for tasks like Aggregates, rankings and running totals.

Basic Windows syntax
OVER() - defines the window
PARTITION BY - splits data into groups
ORER BY - defines calculation order

Types of Window Functions
The two types of Window functions are;

1. Aggregate Window functions
2. Ranking Window Functions

Let us use this Staff table to explore Window functions;

Staff table
+---------+--------+-----+-----------------+---------+
| staff_id| name   | age | department_name | salary  |
+---------+--------+-----+-----------------+---------+
| 1       | Tonny  | 26  | Engineering     | 80,000  |
| 2       | Phenny | 16  | Marketing       | 65,000  |
| 3       | Steve  | 30  | Engineering     | 75,000  |
| 4       | Juliet | 40  | Sales           | 30,000  |
| 5       | Nelly  | 22  | Marketing       | 27,000  |
| 6       | Joshua | 20  | Finance         | 55,000  |
+---------+--------+-----+-----------------+---------+

Aggregate Window Functions
These calculate aggregates over a window of rows while retaining the individual rows.

They include;

SUM() - Adds together numbers within a window.

Lets find the Sum of the Salaries from the Staff table. We use the query

SELECT name, age, department_name, salary,
SUM(salary) over (partition by Department_name) as Sum_of_Salary
FROM Staff;

From this query, we get the output;

+----+--------+-----+-----------------+---------+---------------+
| #  | name   | age | department_name | salary  | sum_of_salary |
+----+--------+-----+-----------------+---------+---------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 155,000       |
| 2  | Steve  | 30  | Engineering     | 75,000  | 155,000       |
| 3  | Joshua | 20  | Finance         | 55,000  | 55,000        |
| 4  | Phenny | 16  | Marketing       | 65,000  | 92,000        |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 92,000        |
| 6  | Juliet | 40  | Sales           | 30,000  | 30,000        |

AVG() - calculates the average within a window

Eg Finding the average from our Staff table, we use the SQL query;

SELECT name, age, department_name, salary,
AVG(salary) over (partition by Department_name) as AVG_Salary
FROM Staff;

From this query we get;

+----+--------+-----+-----------------+---------+-----------+
| #  | name   | age | department_name | salary  | avg_salary|
+----+--------+-----+-----------------+---------+-----------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 77,500    |
| 2  | Steve  | 30  | Engineering     | 75,000  | 77,500    |
| 3  | Joshua | 20  | Finance         | 55,000  | 55,000    |
| 4  | Phenny | 16  | Marketing       | 65,000  | 46,000    |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 46,000    |
| 6  | Juliet | 40  | Sales           | 30,000  | 30,000    |

MAX() - returns maximum value in the window

For example lets find the Maximum salary in the Staff table and partition by department_name. we use the sql query;

SELECT name, age, department_name, salary,
MAX(salary) over (partition by Department_name) as max_Salary
FROM Staff;

We get the output as;

+----+--------+-----+-----------------+---------+------------+
| #  | name   | age | department_name | salary  | max_salary |
+----+--------+-----+-----------------+---------+------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 80,000     |
| 2  | Steve  | 30  | Engineering     | 75,000  | 80,000     |
| 3  | Joshua | 20  | Finance         | 55,000  | 55,000     |
| 4  | Phenny | 16  | Marketing       | 65,000  | 65,000     |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 65,000     |
| 6  | Juliet | 40  | Sales           | 30,000  | 30,000     |

MIN() - returns minimum value in the window.

For example lets find the Minimum salary in the Staff table and partition by department_name. we use the SQL query;

SELECT name, age, department_name, salary,
MIN(salary) over (partition by Department_name) as min_Salary
FROM Staff;

From the query we get;

+----+--------+-----+-----------------+---------+------------+
| #  | name   | age | department_name | salary  | min_salary |
+----+--------+-----+-----------------+---------+------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 75,000     |
| 2  | Steve  | 30  | Engineering     | 75,000  | 75,000     |
| 3  | Joshua | 20  | Finance         | 55,000  | 55,000     |
| 4  | Phenny | 16  | Marketing       | 65,000  | 27,000     |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 27,000     |
| 6  | Juliet | 40  | Sales           | 30,000  | 30,000     |

COUNT() - counts the rows within a window.

For example lets find the count of names in the Staff table and partition by department_name. we use the SQL query;

SELECT name, age, department_name, salary,
COUNT(salary) over (partition by Department_name) as count_of_names
FROM Staff;

From the query we get;

+----+--------+-----+-----------------+---------+----------------+
| #  | name   | age | department_name | salary  | count_of_names |
+----+--------+-----+-----------------+---------+----------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 2              |
| 2  | Steve  | 30  | Engineering     | 75,000  | 2              |
| 3  | Joshua | 20  | Finance         | 55,000  | 1              |
| 4  | Phenny | 16  | Marketing       | 65,000  | 2              |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 2              |
| 6  | Juliet | 40  | Sales           | 30,000  | 1              |

Ranking Window Functions
These return rankings of rows within a partition based on a specific criteria.

They include;

RANK() - Assigns ranks to rows(it skips ranks for duplicates) within a window.

Lets use RANK() to rank our staff salaries in Descending order and partition by department_name. We use the query;

SELECT name, age, department_name, salary,
RANK() over (partition by Department_name order by Salary DESC) as salary_rank
FROM Staff;

The query returns;

+----+--------+-----+-----------------+---------+------------+
| #  | name   | age | department_name | salary  | salary_rank|
+----+--------+-----+-----------------+---------+------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 1          |
| 2  | Steve  | 30  | Engineering     | 75,000  | 2          |
| 3  | Joshua | 20  | Finance         | 55,000  | 1          |
| 4  | Phenny | 16  | Marketing       | 65,000  | 1          |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 2          |
| 6  | Juliet | 40  | Sales           | 30,000  | 1          |

ROW_NUMBER() - assigns unique numbers to rows.

Lets get the row numbers from our staff table based on Salary DESC and partition by department_name;

SELECT name, age, department_name, salary,
ROW_NUMBER() OVER (PARTITION BY Department_name order by Salary DESC) as staff_row_number
FROM Staff;

This query returns;

+----+--------+-----+-----------------+---------+----------------+
| #  | name   | age | department_name | salary  | staff_row_number|
+----+--------+-----+-----------------+---------+----------------+
| 1  | Tonny  | 26  | Engineering     | 80,000  | 1              |
| 2  | Steve  | 30  | Engineering     | 75,000  | 2              |
| 3  | Joshua | 20  | Finance         | 55,000  | 1              |
| 4  | Phenny | 16  | Marketing       | 65,000  | 1              |
| 5  | Nelly  | 22  | Marketing       | 27,000  | 2              |
| 6  | Juliet | 40  | Sales           | 30,000  | 1              |

DENSE_RANK()- assigns ranks to rows without skipping rank numbers for duplicates.
PERCENT_RANK() - shows the relative rank of a row as a percentage between 0 and 1.

When working with Window functions, you have to ;

Partition carefully because without Partition by the whole table is treated as one group.
Order by where necessary

In Conclusion, SQL Joins and Windows are fundamental to effective data analysis.

Joins allow us to combine related tables into meaningful datasets while window functions enable advanced calculations such as rankings and aggregate operations while keeping the rows intact.

Together, these tools transform SQL from a simple querying language into a powerful analytical resource.

How Analysts Translate Messy Data, DAX and Dashboards into Action using PowerBI

Beverline Otiende — Mon, 09 Feb 2026 07:23:47 +0000

As an analyst, you will always begin your analysis with raw data that is often messy, inconsistent, incomplete and scattered across multiple systems. To be able to create value from this raw data, you will have to first clean, model, standardize and finally transform your raw data into insights that guide strategic data driven decision making.
PowerBI is one of the most powerful business intelligence tools analysts use to bridge the gap between messy data and actionable business intelligence.

This article explores how we can use PowerBI to do data cleaning, data modelling, DAX (Data Analysis Expressions) and in creating dashboard designs.

Data Cleaning and Transforming Messy Data in PowerBI
Data cleaning is the process of identifying and correcting errors, inconsistencies and inaccuracies in data sets.
Data cleaning is very crucial because it enables us to work with clean, structured and relevant data in our analysis unlike using uncleaned data that is poor quality data which translates to incorrect analysis and decisions.
Power Query in PowerBI allows analysts to clean and transform raw data through;

Removing duplicates
Handling missing values and errors
Fixing datatypes
Standardizing formats
Splitting or merging columns
Filtering irrelevant records

Structuring data with proper modelling
After data cleaning, it is important to build relationships between tables using proper data modelling techniques.
Data modelling is basically setting up tables, relationships, calculations and access for analysis scenarios
Good data modelling in PowerBI involves incorporating a Star Schema which improves performance, the accuracy of calculations, flexibility of reporting and scalability for future analysis.
Proper Modelling often involves:

Creating fact tables
Creating Dimension tables
Designing a Star Schema
Defining relationships between the tables.

Turning Data into Insights with DAX (Data Analysis Expression)
DAX is the formula language used in PowerBI to create custom calculations. We use DAX to transform structured data into meaningful business insights.
DAX enables us to calculate New measures and New columns to compute key metrics such as Total Revenue, Profit margins, growth rates among others.
For example, DAX allow you to create a New Measure for Total Revenue as shown below;

Total Revenue = SUM(Kenya_Crops_Cleaned_Final[Revenue (KES)])

We can also create a New Column as shown below;

Expected Selling Price = Kenya_Crops_Cleaned_Final[Market Price (KES/Kg)]*Kenya_Crops_Cleaned_Final[Yield (Kg)]

We use New Measure if you expect a single output while we use New Column if the expected outpu is multiple rows corresponding to the data.

Designing Dashboards in PowerBI
A dashboard is not just a collection of charts but a clear visual representation of our analysis that supports effective decision making.
An effective PowerBI dashboard must:

Highlight Key Performance Indicators(KPIs)
Use appropriate graphs to draw attention to critical metrics
Present insights clearly and concisely
Enable filtering

Here is an example of a PowerBI dashboard with KPIs, Slicers and Charts

Conclusion
PowerBI is more than just a visualization tool. It is a platform that enables us as analysts to transform messy data into reliable insights that drive real business decisions.
We use Power Query to clean the data, Data modelling to organize the data , DAX enables us perform dynamic calculations and the dashboard for visualizations and insights.
We are able to use PowerBI to find out what happened, why it happened and what will happen through data cleaning, data modelling, DAX calculations and interactive dashboards.

Schemas and Data Modelling in PowerBI

Beverline Otiende — Mon, 02 Feb 2026 10:29:18 +0000

Introduction to Data Modelling in PowerBI
Data Modelling is the process of structuring raw data into a logical format by setting up tables, connecting multiple data sources using relationships and calculations in order to make analysis fast, accurate and easy to understand.
Data modelling in PowerBI greatly affects the quality of insights you get from your data.
A good data model must define:

How tables are organized
The relationship between the tables
The Calculations

Schema in Data Modelling
A Schema is the overall structure of how data tables are arranged and connected in a model.
In PowerBI, Schemas mainly describe how fact tables connect to dimension tables and the flow of filters between tables.
A fact table is the center table of a data model and contains measurable, quantitative data used for analysis
A dimension table is a table that explains a certain aspect or dimension in the fact table. It provides a descriptive context for facts
The two most common Schemas used are:

Star Schema
Snowflakes Schema

1. Star Schema
A Star Schema is a data model where you have one central fact table and multiple dimension tables all connected.
It looks like a Star when visualized.

Structure of Star Schema

A fact table in the middle
Dimension tables around it
No relationship between dimension tables

Advantages of a Star Schema include:

They are simple and intuitive
Best performance in PowerBI
They have fewer relationships
Easier DAX calculations

Star Schema is the recommended modelling approach in PowerBI.

2.Snowflake Schema
A snowflake Schema is a more complex version of a star schema where dimensions are normalized and dimensions connect to other dimensions.
They are more common in databases but not ideal for PowerBI unless unavoidable.

Structure of a Snowflake Schema
Has a fact table that connects to a dimension table
That dimension connects to another dimension

Advantages of a Star schema include:

There is reduced data redundancy
Smaller dimension tables

Disadvantages of Snowflakes schemas

They are harder to understand
Slower performance
More complex DAX

Importance of Good Modelling in PowerBI

It improves overall performance: Good modelling ensures we have fewer tables and relationships which translates to faster reports. Star schema reduces query complexity and ensures efficient memory usage.
Ensures accuracy in Calculations and reporting
Simplicity and usability: Good modelling simplifies DAX calculations and ensures easier debugging and maintenance.
Scalability: Good modelling makes it easy to add new dimensions and easier to create new measures.
Reduces Data redundancy and storage size

In conclusion, effective modelling is the foundation of successful PowerBI reporting. Data modelling comes first since good visuals cannot fix a bad data model.
Using a Star Schema, clearly separating the fact and dimension tables and defining proper relationships ensures effective modelling and thus accurate calculations and reliable reports.

Evaluating Microsoft Excel as a Data Analytics tool in Modern Business Decision Making

Beverline Otiende — Sun, 25 Jan 2026 14:35:18 +0000

Microsoft Excel remains one of the most commonly used tools for Data Analytics in businesses and organizations. This is mainly because it is very easy to use, it is easily accessible and powerful enough to handle many data analysis tasks.
This article explores ways in what Excel is and how it is used in data analysis.

WHAT IS MICROSOFT EXCEL
Excel is a spreadsheet program that can be used to organize data, to perform calculation, clean data, create visualizations and analyze data.
Data Analytics on the other hand involves examining raw data in order to identify patterns and draw data-driven insights that support decision making in organizations and businesses.
Excel as a tool.

EXCEL TEXT FUNCTIONS
Text Functions in Excel are used as part of data cleaning to get a section of data or to extract relevant information from text fields.
They include;
LEFT Function: extracts text from the left side of a cell. Its syntax is,

=LEFT(text, number_of_characters_to_extract)

RIGHT Function: extracts text from the right side of a cell. Syntax is,

=RIGHT(text, number_of_characters_to_extract)

MID Function: extracts text from the middle of a cell. Syntax is

=MID(text, start_position, number_of_characters)

LEN Function: counts the number of characters in a text. syntax is,

=LEN(text)

TRIM Function: removes extra spaces from a text. Syntax is,

=TRIM(text)

CONCATENATE Function: joins texts from different cells. Syntax is,

=CONCATENATE(text1, " ", text2)

DATA VALIDATION
This is done to restrict what is entered in a cell during data entry to avoid errors.

REMOVING BLANKS
Removing blanks is a common aspect in data cleaning.
To remove blanks;

Choose column you want to remove blanks from
Go to Home tab and then to Find and Select
Go to Special then Remove Blanks.

PERFORMING BASIC ARITHMETIC OPERATIONS IN EXCEL
With Excel you can do Basic Addition, Subtraction, Division, Multiplication, Power and many more.
All you have to do is put "=" before you write what you want to add or Multiply or Divide;
e.g
= 2+2 gives you 4 for Addition
= 10/2 gives 5 for Division
= 5* 3 gives 15 for Multiplication
=17-9 gives 8 for Subtraction
= 2^3 gives 8 for Powers

AGGREGATE FUNCTIONS
We have special functions we use when performing arithmetic operations like Sum, difference, Average just to mention but a few.
Assume you have a column of data from range E2 TO E16 you want to calculate different parameters.

Adding the numbers together

= SUM(E2:E16)

Finding the Average
= AVERAGE(E2:E16)

Finding Maximum Value
=MAX(E2:E16)

Finding Minimum value
=MIN(E2:E16)

Finding the Count
=COUNT(E2:E16)

LOGICAL FUNCTIONS
Logical functions in Excel help you make decisions in your data.
They check whether a condition is TRUE or FALSE and then return a result based on your condition.
They include;

IF Function: Checks a condition and returns a value if the condition is True and another value if it is False. Its syntax is shown below;

=IF(condition, value_if_true, value_if_false)

AND Function: Returns TRUE only if all conditions are met. Its syntax is shown below;

=AND(condition 1, condition 2)

OR Function: Returns True if at least one of the conditions is True. Its syntax is shown below;

=OR(condition 1, condition 2)

NOT Function: It reverses the logical result. Its syntax is shown below;

=NOT(condition)

PIVOT TABLES
A Pivot Table is a powerful Excel tool that helps us summarize, analyze and explore large amounts of data without writing formulas.
To insert a Pivot table;

Select your entire table
Go to the Insert tab and Select Pivot Table(on the extreme left)
Select New Worksheet then press Okay
Then drag and drop your columns on the Row, Value and Column section on the Right depending on the summary you want. On the Values section, you can specify if you want Sum, Average, Count or anything else. To do that, you select under the 'Values' section, Select your Value, the Value field settings, select the summary you want eg Sum, count. then press Okay

SLICERS
Slicers provide Visual Filters for Pivot tables and pivot charts.
To insert a slicer for a Pivot Table;

Select anywhere on the Pivot Table
Go to PivotTable Analyze Tab and Select Slicer
Select the Slicers you want to have and click Okay. Based on your selection on your slicer, your data values change.

PIVOT CHARTS
Pivot Charts are used for visualizations and are made from Pivot tables
To insert a Pivot chart;

Select anywhere on your Pivot Chart
Go to the PivotTable Analyze Tab the select Pivot Chart
Select the chart you want to input either Column chart, Bar chart, Pie Chart among others depending on your data and analysis.
Modify the chart according to your preferences.

Pivot charts show a graphical representation of Pivot tables and you can use them depending on your data and analysis.
Commonly used charts include:

Line charts: To show trends over time
Column charts: Compares values across categories using vertical bars.
Bar charts: Compares values across categories using horizontal bars.
Pie/Doughnut Chats: Shows proportions of a whole.
Combo charts: Combines two chart types.
Histogram: Shows distribution of numeric data.

BUILDING A DASHBOARD
An Excel Dashboard is a visual summary of key metrics and trends, usually on one worksheet and is designed for quick understanding by managers and stakeholders.
Key considerations for a Dashboard include;
• One screen only (no scrolling)
• Focus on key KPIs, not raw data
• Consistent colors and fonts
• Clear titles and labels
• Interactive but simple
When building a dashboard, open a new Excel worksheet, then copy and paste the Pivot Charts you would like to have in there plus the slicer. Ensure your Dashboard is neat and organized.
Also use dashboard colors according to the theme of the company you are presenting to.

A beginners guide to Git: Version control, Tracking changes, Pushing and Pulling Code.

Beverline Otiende — Sun, 18 Jan 2026 04:13:09 +0000

If you are serious about Tech, then Git is one of the non-negotiables. You've probably heard about the word Git and Github thrown around by 'Techies' and it may sound intimidating to you as a beginner.
In this article, we are going to breakdown and understand what Git is and how we can use it.
This article aims to explain:

What version control is
What git is and how it matters
How Git tracks changes
How to push and pull code

Understanding Version control
Version control is a system that tracks and records changes made to our code over time in a special database called repository.
A repository in simple terms is a project folder that Git is tracking.
Think of version control as a time machine + a collaboration tool for your code that helps us work together and track changes.
It also helps us know what changes were made to our code and by who, and if you screw something up you can easily revert your project back to an earlier state.
There are different version control system but Git is the most common one.

What is Git and why it matters
Git is the most popular version control system in the world. This is mainly because it is free, open source, super fast and scalable.
The main difference between Git and Github is that Git is a tool installed in your computer and Github is an online platform that stores Git repositories.
To be able to use Git you have to install it in your computer using the link; google for windows and for mac and Linux install via the official site.

To check the version of git installed in you computer yo run the code;

git --version

Git runs locally on your computer and;

Tracks every change you make to your files
Knows who made each change and when
Allows you to save snapshots of your project

How Git tracks changes

Git generally has three main states for its files;

Working directory: this is where you edit files
Staging area: this is where you tell Git the changes you want to save
Repository(History): this is where Git permanently stores snapshots(commits).

A commit is a snapshot of your project at a specific time and
Each commit normally has a;
-Unique ID
-Author
-Date
-Message
The commit history acts as the Git time machine and we can view it using the code;

git log

Pushing code to GitHub(Git push)
Git push means sending your local commits to the remote repository(Github).
To push code to Github, we use the code;

git push -u origin name

When you enter this code on your Git, your code appears on Github and is visible to other people.

Pulling code from Github(Git pull)
This is done to get the latest changes from the remote repository(Github).
The code for pulling code is;

git pull origin main

Git pull is very important especially when working on multiple machine, collaborating with others and when updating your local copy.

Why Git is essential for your career
If you are serious about Tech, then Git is a non-negotiable for your career.
Git mainly helps you to;

Collaborate with teams worldwide
Work safely without fear
Show your work to employers
Contribute to open source projects

When working with Git as a beginner, it is very important to;

Always commit often in small chunks
Be specific and clear with your commit messages
Always Git pull before starting work

In conclusion, Git may feel confusing at first, but it becomes second nature with practice. Just start small, make mistakes, learn and keep going.