DEV Community: Philip Saidi

DATA MODELLING 101 IN POWERBI

Philip Saidi — Wed, 16 Sep 2026 03:58:48 +0000

WHAT IS DATA MODELLING IN POWERBI.

It's a process of setting up tables, relationships, calculations and access for data analysis scenarios.
Data modelling also define the data structure of single or many tables and there relationships.
A well defined model delivers fast performance, accurate results, simple DAX, and an intuitive experience for data analysts.
This topic will cover the essential principles of data modelling in PowerBI, with strong focus on recommended star schema, relationships and joins(both in PowerQuery and data model).

Why Data Modelling matters.

Good modelling delivers:

Faster query performance and lower memory consumption.
Simple and more reliable DAX.
Easier maintenance and scalability.
Better usage for business us

Model Schemas

We will focus on two types of schemas:

Star schema

The data model in this schema demonstrates the relationship between one fact table and many different dimension tables.
Figure below shows components of star schema.

Fact Table:
The fact table sits at the center of the schema and stores the measurable, quantitative data used for analysis. Examples include:

Sales amount
Units sold
Discount
Profit Each record in a fact table represents a business event (e.g sales transcation).

Dimension Tables:
Dimension tables surround the fact table and contain descriptive attributes that add context to the facts. Common dimensions include:

Product details
Customer details
Time attributes
Employee or store information These tables allow users to slice, dice, filter, and group the fact data for analysis (e.g., sales by region, by month, by product category).

Features of Star schema.

Fact table contains numeric measures.
Dimension table storing descriptive attributes.
High query performance.
Easy to understand, even for non-technical users.
Allows fast joins and simple queries.

Example of a star schema:(sample-chocolate-sales-data-all)

To demonstrate how a star schema works, consider a sample chocolate data where each shipments details is stored in a fact table and is analyzed through sorrounding dimension tables.

Shipments fact table contains numeric measures such as amount, boxes along with foreign keys to each dimension such as product id, geographical id and sales person id.
Sorrounding dimension tables provide descriptive attributes that allow the facts to be examined from different perspectives.
Locations dimensions describes the shipment location and product dimensions describes the product being shipped.
Calendar dimension shows the date in years, months the product was shipped.

Advantages of a Star schema

Easier to perform simple queries.
Simplified business reporting logically.

Disadvantages of a Star schema

Denormalized data can introduce duplicates.
Not as adaptable as normalized models when analysis needs change frequently.
Weak support for many to many relationships.

Snowflake Schema

Is data model technique where dimension tables are normalized into multiple related sub-tables.
The snowflake schema applies only to dimension tables, not the fact table.
Figure shows the snowflake schema.

Dimension tables are normalized into multiple related tables, creating a hierarchial structure.
Fact table is still located at the center of the schema, surrounded by dimension tables.
Each dimension table is further broken down into multiple related tables. Example: A customer dimension may contain a cityID that links to a separate city dimension tables storing city, state, country.

Features of a Snowflake Schema

Uses normalized tables to reduce redundancy and improve consistency.
Are built around a central fact table with connected dimension tables.
Dimensions can be split into multiple levels, allowing detailed analysis.
Requires more joins which can slow performance on large datasets. -Scales well for large data, but its complexity makes it harder to manage.

Example of Snowflake schema.

Employee diemnsion includes atrributes like employeeID, Name, DepartmentID, Region, and Territory.DepartmentID links to the department table, which holds department details like Nmae and location.

Advantages

Improves data integrity through normalization.
Reduces redundancy and storage usage.
Supports detailed hierarchical drill-down.

Disadvantages

Increased schema complexity.
More joins, leading to slower query performance.
Normalization may offer minimal storage savings compared to the entire warehouse.
Not recommended unless the hierarchy is essential and widely used in queries.

Difference Between Snowflake and Star Schema

Feature             Star schema      Snowflake schema
Dimension structure- Denormalized     - Normalized
Query performance -Faster(few joins) -Slower(more joins) 
Storage requirement- Higher          - Lower
Complexity         - Simple          - More complex
Use case - Simple analytics      - Complex hierarchies.

What are Table Relationships?

A relationship described between two or more tables via a common attribute is termed a table relationship. It is very crucial as it enables users to access data from two separate tables with ease.

Types of Table Relationships

There are four types of table relationships

One-to-One (1:1): One row in a table that can only link to one row in another table.

One-to-Many (1:*): One row matching to many rows in another table.

Many-to-One (*:1): Many rows in a table, matching one unique row in another table.

Many-to-Many (:): Multiple rows that can link into multiple rows in another table.

Cross filter direction

Cross filter considers the columns from both the tables that join them together and allows the user to tell the direction of filtering allowed.

Single: It represents a single direction filter. In this filtering choices in connected tables work on the table where values are being aggregated.
Both: It represents a bi-directional filter. In this, for filtering both tables connected are considered as one single table.

We need to identify key fields for Relationships

A table relationship works by matching key fields.

They can be fields with the same name in both fact and dimension tables.
Mostly we use the primary key of one table as a foreign key in another table and helps in establishing a relationship between two tables. Example: Table below shows primary keys like GeographicalID in the location table.

Impact of table relationships in powerBI

Incorrectly defined table relationships can lead to performance issues, such as slow query execution.
Complex relationships with large datasets may result in increased memory.

How to optimize table relationships

Avoid unnecessary relationships.
Keep the number of relationships to a minimum.
Use Bi-directional filtering only when necessary.
High cardinality relationships can impact performance.

Merged Tables in PowerQuery

This merges data from two or more tables into single table by using PowerQuery.
It's based on common columns between tables.

Importance of merging tables.

Enables user to perform analysis, reporting, and visualization on combined datasets without the need for complex joins/relationships between tables.

Merging Tables using Merge queries command

First load the data sets into powerbi as shown below:

Click on Transform data -> click on merge queries command

Select the left table for merge and next right table for merge.

Select type of join you want perform, the join operations are used to join the tables.

Types Of Joins

Left outer join:- Merges all the rows from the left table and matching rows from right table.
Right outer join:- Merges all the rows from the right table and matching rows from left table.
Full outer join:- Merges all the rows from the both tables.
Inner join:- Merges only same rows from the same tables.
Left anti:- Merges only rows from left table.
Right anti:- Merges only rows from right table.

Fuzzy Matching

Fuzzy matching is a technique used to identify and match similar strings or text values within a dataset by measuring their similarity based on various algorithms and criteria.

Impact of Merged tables on performance in powerbi

Tables can impact performance during data refresh.
Large merged tables can increase data model size and memory usage thus affecting performance.

How to make Merge queries work:

Limit the number of transformations applied to merged tables.
Apply filters and aggregations to reduce the number of rows and columns in merged tables before loading into data model.

CONCLUSION

For most business intelligence work, I would consider a star schema with one-to-many relationships, single-direction filtering, and a dedicated Date dimension.

Star schema is easier to perform simple queries in DAX and has simplified business reporting logically compared to a Snowflake schema which offers complex operations.
One to manyrelationships because it keeps filter propagation predictable: dimensions filter facts, not the other way around.
Date table which keeps time reporting possible for time series data analysis and reporting.

Jumia Product Performance Dashboard: Analyzing Pricing, Discounts, and Customer Reviews

Philip Saidi — Sun, 06 Sep 2026 13:35:06 +0000

Introduction

The project introduces a Jumia dataset in csv file.
Jumia is an ecommerce powerhouse operating accross Africa.
The goal of this project is to create an interactive Excel dashboard that provides insights into the
performance of products listed on Jumia

Overview

The dataset contains information about products listed on Jumia with the following columns:
• Product: Name of the product.
• Current Price: The current selling price of the product (in KSh).
• Old Price: The original price before discount (in KSh).
• Discount: The percentage discount offered on the product.
• Review: The number of customer reviews received by the product.
• Rating: The average customer rating of the product (out of 5).

Objectives of the objectives

Identify errors in the dataset
Perform data cleaning
Perform analysis for trends
Visualize your results using pivot charts
Use slicers to interact with the indicators
Create a dashboard with all the KPIs

Data Cleaning

I copied and named the cleaning sheet cleaned.
Autofitted column width to accomodate the long product names by
CTRL + A ->Home -> Format -> Autofit column width -> OK
Applied a filter for all the columns by Select First Header row -> Home -> Sort and Filter -> Filter-> Apply filter -> OK
Changed the header row background color Select First Header row -> Home -> Fill color -> Select the desired color -> OK to differenciate from the main data.

Data Errors Identified

Duplicates
The current and old price are in text format
Rating is mispelled and in text format
Review column is in negative number
Price in a row is in range of two numbers

Removing duplicates

Select the entire data -> Go to data tab -> Remove duplicates -> Choose all columns -> OK

Change current price and old price to currency format.

Select the current/old price column -> Use Find and replace -> First Replace Ksh to Blank -> OK -> Select price column -> Home -> Number -> Choose currency on the dropdown menu -> choose Ksh on the currency symbol -> select 1 decimal place -OK

Change Rating to number format

I used Find and replace
Select Rating column -> Home tab -> Find and select -> Replace or CTRL + H -> example 2.0 out 5 with 2, 2.5 out of 5 with 2.5
Changed the name by renaming the Ratingd to Ratings.

Change Review to a positive number

Select the Review column
CTRL + H for find and replace-> Replace - with blank-> OK

Price in a row is in range of two numbers

I calculated the average of the two numbers in range by
=AVERAGE(2200,3200)

Data Enrichment

The project required we create 3 columns

Price category

High price greater ksh 3000
Medium price greater or equal ksh 2000
Low price less than 1000 I used a nested IF statement in the inserted column and renamed it price category. =IF(B2>3000,"High Price",IF(B2>=2000,"Medium Price","Low Price"))

Discount Category

High discount greater than 40%
Medium discount less than or equal 40%
Low discount less than 20% I use a nested ID statement and renamed the column discount category =IF([@Discount]="","Missing",IF([@Discount]<20%,"Low Discount",IF([@Discount]<=40%,"Medium Discount","High Discount"))

Rating Category

Excellent greater than 5
Average less than or equal 4.5
Poor less than 3 I used a nested IF statement here too and renamed Rating category. =IF([@Ratings]="","Missing",IF([@Ratings]<3,"Poor",IF([@Ratings]<=4.5,"Average","Excellent")))

Discount Amount

I used subtraction from old price to current price.
Renamed the column Discount amount.
=[@[Old price]]-[@[Current price]]

DATA ANALYSIS USING PIVOT TABLES AND CHARTS.

I created pivot tables to help me with data analysis and data visualization.
From the data analysis

- Click anywhere in the data table
- Go to insert table 
- Choose pivot table
- Choose from existing table and a new worksheet
- OK

I created a pie chart and a bar chart showing discount by reviews, ratings category by average reviews, price and rating using the pivot tables created.

Excel Dashboard

I used these slicers to connect all the pivot tables/charts.
Created a pivot table -> Insert -> Slicer -> Choose columns (Rating category, Price category, Discount category) -> OK

From the Slicers and pivot tables/chart I created a Dashboard displaying all the charts, slicers for interaction and Key performance Indicators shown below

CONCLUSION

From the dataset given ratings,reviews,prices and large discounts do not guarantee for huge sales.Jumia should stock product that makes a different in quality this will attract customers.

Here is the link to the project files for collaboration:GITHUB

Getting Started with Excel for Data Analytics: From Basics to Data Cleaning.

Philip Saidi — Sat, 29 Aug 2026 20:13:33 +0000

Overview

What is data and data analysis

Data is information that helps people or business make decisions.

Types of data

Text (Abcde)
Numbers (12348.0)
Dates (12/3/1993, 12-5-2026)
Currency ($45.5, KES200.5)

Data analysis

The process of making data to a useful information using different tools such as Excel.
It entails processes like

Data formatting
Data validation
Data cleaning
Data analysis
Data visualization

What is Excel

Excel is a spreadsheet application used to create, view, edit , clean, analyze and visualize data.

Excel use cases

Create data or record data for reference.
To record data /data entry.
Data analysis and give insights.
Business intelligence on light weight.
Project management.
Cleaning data.
Create beautiful visual representation of data.

Creating data with Excel for Students grades

Open excel application in your pc
Open Blank workbook name it Students grades
Save by clicking File then Save as.
Choose a location to save and name the file
Click on cell A1 and name it FIRSTNAME
Press TAB to move to the next cell
Name it LASTNAME, next QUIZ 1, QUIZ 2, QUIZ 3, and AVERAGE as shown below
Autofit the columns by Clicking Home tab -> Format -> Autofit column -> OK.
Enter the data as shown in the screenshot and save.

Introduction to Excel Cleaning data.

The first step to data cleaning:

Sample data errors

Wrong date format
Incorrect data types
Blank cells/ missing values
Duplicates
Wrong format of data types
Missing values
Incorrect information

Formatting data

You can format numbers, texts, rows and columns

Number formatting

Numbers always align to the right of the cell.
Follow these steps to format numbers to desired data type

Select the column you want to format
Click Home tab/ribbon and find Number group
A drop down menu will occur, choose which number format you want and click OK.

Text Formatting

Text are a string or words like John, Lorry, HR.
Text are aligned to the left of the cell
Follow these steps to format texts

Select the text you want
Click Home tab
Choose to color or make it bold in the font section
You can change the background color and click OK.
Save your work.

Row and Column formatting.

Follow these steps

Select an entire row/column example ROW 1
Select Home tab and Click B to make it Bold.

Data Cleaning Using Excel

Data cleaning is the art of formatting and correcting data so that it can be correct and accurate for use in data analysis.

For this We will use a random data set from the internet called kenya-school-grades-dirty.
Start with removing duplicates

Select all data
Go to data tab
Click remove duplicates.
Select the column you want
Click OK.

In the data set names are not in proper case and have spacing.
We rectify this by:

Selecting the columns Student Name.
Right click and insert a new column
In the empty column write the formula =TRM(PROPER(B2:B221)) and Enter.
This removes any whitespace and capitalize the first letter.
Insert another cell and copy the corrected student name and delete the previous one then save.

In the gender it has more than one identifier that is M, f, Female, FEMALE, MALE.
We can correct this by removing the repeating gender by find and replace method.

Select the gender column.
Select Home tab
Find and select -> Replace
A dialogue box will pop up
Type what you what to find and replace all it with the correct gender.

Date format
In the last column the date format is inconsistent

Select column you want to format(Date recorded)
Go to Home tab
Then Click Number group
Scroll to date
Select the date format that is correct.

Data validation

Restricting users on what is allowed to be entered in a given column.
Follow these steps:

Select column you want to validate.
Click on data ribbon
Data validation group
Choose list
Type the data that you want to be allowed in that column
Click OK.

CONCLUSION

Excel is important in both data analysis, cleaning and visualization.

# MY FIRST GITHUB PROJECT: FROM A LOCAL FOLDER TO GITHUB USING GIT AND SSH.

Philip Saidi — Sat, 22 Aug 2026 08:37:21 +0000

What is git and github

Git is a command line tool used to create project folders and files that can be linked to your github account repositories.
Github is a website where you put all your projects folders and files to enable collaboration with you team or anyone who can contribute to that project.
Repository is a project folder that contains all of your project files.

Creating A Folder And Files

We first create a folder in your desktop using gitbash.
I followed these steps.

open gitbash
First know the current directory, if you in the Root directory tab change directory to Desktop.
Make directory with the name of the Folder.
Change the directory to the current Folder created.
Make a file in the folder you created, like app.py or README.md.
Write into the README.md and save.
Display the contents of the file.
Return to the previuos folder y_ou created. Use the code below for reference

pwd
cd Desktop
mkdir Folder name like Kenya-Health-Records
cd Kenya-Health-Records
touch File name with an extension like app.py or README.md
echo "# DATA CLEANING USING EXCEL" >README.md
cat README.md 
cd . .

Pushing Repo To Github Using Gitbash

While still in gitbash current folder I created.
I did this commands on the CLI

Run git init to initialize a git repo
Run git status to check I repo is ready 
Run git add . 
Run git commit -m "write your commit message"
Run git log --oneline to check the commit message in a oneline
Run git branch to confirm if you are in the main branch
Run git branch -M main to change master to main branch

Open github account, login and follow these steps.

Create a repository and give it a name and description.
Go to your quick set ip and click SSH.
Copy the url command
Open gitbash and make sure you are in the current folder you want to push to github._ Follow this commands below

Run git remote add origin "paste the url command from github"
Run git remote -v
Run git push - u origin main 
You will be prompted for your paraphrase you created while setting git
You will see a successful message that your folder successful pushed to github

Now go to your github account and refresh.You will see the files and folder you created in the repository you created on github.
Give yourself a clap