DEV Community: Otuko Mauwa

Data Modelling, Relationships And Joins In Power BI

Otuko Mauwa — Mon, 05 Oct 2026 00:12:27 +0000

Introduction

This article takes through the ideas that decide whether a model works as intended

Schemas; how table are organized
Relationships; how the model connects tables
Joins; how tables are physically combined in power query

Each section on this article explains the concept, shows with an example and guidance on when to use each one of them.

Data Modelling

Data modelling in power BI is the process of connecting separate data tables together so that they can have a connections with each other and produce accurate answers for business questions and demands.

Importance of Well Designed Data Model

Analytics - The data model is flexible and fully integrated, numerical metrics can be grouped or filtered by categorical attribute(dimensions like date) without hitting broken relationships and rigid hierarchies. User interface is easier to navigate and data fields are logically organized and named so that business user can easily locate metrics and categories without strain.

DAX Calculation - When one has a well designed data model theres no need to write complex formulas and codes to tell measure on how to filter data.

Performance - Having a well designed data model impacts the performance by preventing the model from hitting memory limits and faster data refreshes and a quicker visualization on reports.

Scalability - As the data model get bigger a lean one allows facts, dimensions and data sources to seamlessly integrate into the model without requiring a full report or have a model redesign.

Maintainability - A well designed data model makes it easier to debug, update, and manage over time without breaking existing reports.

Comparisons between Flat Table, Star Schema And Snowflake Schema

1) Flat Table
A flat table stores everything in one wide table, each row is transaction and every descriptive attribute the transaction needs like customer name, product name and category is repeated on that row.

Advantages

Faster to build, is exactly what a CSV export or database view already looks like.
No relationships to configure
Easy to understand and analyze

Disadvantages

Redundancy, storage of exact piece of information or repeated attributes values across multiple tables inside the data model instead of being organized into one relatable table.
Mixed Grain, a data model or table is combined into different level of details.
Hard to extend, adding more facts is hard since there's no shared dimensions to related to.

Flat table are appropriate when dealing with small dataset that will not be reused or combined with other datasets.

Performance and Complexity on Power BI
This model is simple but is heavy to run. Many text columns inflate the memory and perform slow scans. Performing DAX becomes impossible and complicated because counting distinct text columns needs extra logic to escape repeated rows.

2) Star Schema
A star schema has one central fact table surround by dimension table that are connected to the fact table using a single key.

Advantages

Simple and Predictable filtering - Every filter is one hop from dimension to fact
Simple DAX Once a measure is written it against the fact it works with any dimension
Easy to read and extend: User can tell which table hold metrics and which one hold filters. When a new business process is added against the fact table it can be depended onto other dimension tables.

Disadvantages

More tables and relationships than flat table making it to take time to design.
Dimensions have some redundancy

Star schema is appropriate in the day to use for reporting and analytics project

Performance and Complexity on Power BI
Start schema's give the best balance in Power BI, relationship is traversal is minimal, also star schema use very minimal memory and the model is easy to go about

3) Snowflake Schema
A snowflake schema is a version of star schema but in which the dimension are further categorized into sub-dimensions.

Advantages

Less repetition inside the dimensions.
Easier data maintenance, modification can be made one the intended sub-dimensions without altering the data model.

Disadvantages

With more table and relationships it becomes complicated to navigate
Filter must travel through several table before reaching the fact table.

When Its appropriate, when dimensions are large wiith more complex hierarchies.

Performance and Complexity on Power BI
To filter a chart using snowflake schema, power bi must traverse multiple tables through a chain relationship making it not recommended.

Facts and Dimension Tables

A fact table is a central data model that stores numerical number, measurements or metrics of a business like customerID, productID, sales amount. Dimension tables is a supporting table in data model that stores descriptive attributes and background information about a business like product name, category and customer email.

Information Stored in fact tables

Foreign keys - connects to dimensions tables this includes (productID, customerID)
Numeric number - This are numeric columns that contain business transaction such as (Sales amount, Units sold, discount percentage)

Information stored in Dimension tables

Primary Key
Every dimension table consist of unique IDs that acts as master key connects descriptive information and details to the central fact table.
Descriptive text attributes
This are columns that contain descriptive that is used to slice, dice, filter and group the data inro charts, tables and slicers.
Hierarchies and Categories
Dimension tables hod structural grouping that allow data to be arranged into forms of hierarchies.
Product category-Category-subcategory-product name

Grain of a Fact Table
The grain of a fact table is the level of detail that is show in a single row in a table.

Relationships and Joins Between Tables

Relationship is a rule that indicates how two or more table are connected or linked.

*One to many *
One row matched many rows. Filters flows from the one side to the many by default.

One to One
Each key appears at once on both tables. Filter flow in both direction.

Many to Many
Multiple row in one table are clinked to a different table with multiple rows.

Filter Direction

Filter direction refers to how a filter travels across relationships line to filter in other tables.

1) Single directions filtering
Filtering moves in one direction from one side dimension table to the many fact table.

2) Both directions filtering
Filters flows between the two tables along a relationship line. In setting up the dimension table can filter the fact table and fact table can filter the dimension table.

Why Bi-directions is discouraged
1) Performance
It heavily degrades report performance on large datasets since the engine is constantly evaluating complex bi-directional filtering logic.
2) Ambiguous Filter paths
With more than one route to be used it cannot the determined which path to be used.

Joins in Power Query

A join combines two separate tables horizontally by matching columns in one or more key columns. In power query this is done with merge queries feature, pulling matching details from both the left table and the right table into structured columns that can expand.

Types of Joins
1) Left Outer Join
A left outer joins returns all rows are from the left on the first table and adds matching columns from the second table whenever theirs a match. Unmatched row gets to be highlighted as "null" from the columns on the right.

2) Right Outer Join
Similar to the left outer join it joins returns all rows from the right table and adds matching columns from the second table whenever theirs a match. Unmatched rows gets highlighted as "null" from the columns of the left.

3) Full Outer Join
Matches are combined from both tables and unmatched one get to be highlighted as null.

4) Inner Join
Inner joins only returns rows that have a match in both tables. Those rows that are not matched are dropped and do not return null or blanks.

5) Left Anti Join
Left anti joins returns rows from the left table haven't matched in the right table.

6)Right Anti Join
Right anti joins returns row from the right table that haven't matched in the left table.

Power Query Joins vs Power BI Relationships

The difference is that power query join are physical data transformations that combine columns into tables while power BI relationships are logical data model that creates virtual connections.

1) Does a Power Query merge physically combine columns/data from tables?
Merging in power query evaluates matching keys and copies columns from the secondary table to the primary table resulting into a singled merged table.

2) Does creating a relationship combine the tables?
Creating a relations does not result in a combined table since creating a relationship does not copy or physically move data. The table remain separate. A relationship simply is creates a path that allows power bi DAX to perform filters on one table to another.

3) At what stage of the Power BI workflow does each operation occur?
Power Queries happens before the data is loaded into the model.

Relations happens right after the model is loaded into the model at the data modelling stage.

4) When would I choose a merge instead of a relationship?
I would choose a merge over relationship when i want to convert snowflake schema into easy to use star schema.

5) How can excessive merging affect the structure of a data model?
Excessive merging turn a clean relations structure into a huge and flat table which then takes up a huge storage. Large tables bloats the model size, refresh rate become slower and degrades its report rendering performance.

6) Why might keeping fact and dimension tables separate be preferable in a BI model?
Keeping the fact and dimension tables separate helps write clear DAX measures becomes straightforward since filters flows in a clean 1 to many relationships. Improves performance making rapid computations.

Recommended Power BI Architecture

I would recommend the star schema since for business intelligence project, it separates data into central fact table which contains quantitative measurements and foreign keys that is surrounded by dimension tables that contains descriptive attributes the following are the justifications.

1) Query and report performance
Vertipaq is a columnar database engine that is engineer and optimized for a start schema that can scan and filter compact tables instantly.

2) DAX simplicity
Filter flow from 1 sided dimension table to many sided fact table, this makes DAX measures into simple and readable.

3) Filter Propagation & Model Complexity
In star schema it is recommended to use a one to many relationship that has a single directional filter

4) Scalability and Maintainability
When new data sources are introduced including tables along with existing table there's no need to rebuild the model. The existing dimension tables can link with the a new fact table.

5) Ease of Creating Reports and Model Readability
The start schema creates the best layout for clean and clear structure in that the dimension table hold all the descriptive attributes and the fact table holds the numerical metrics.

Conclusion

Understanding between power query operations and power bi modelling is the defining line. Choosing when to merge tables and when to have then isolated directly dictates the performance, scalability and the long term maintenance of the data model. By applying the data transformations early in power query and highlighting relationships efficiently in the data model this enable reports to be understandable.

Jumia Product Analysis WithExcel Dashboards

Otuko Mauwa — Sat, 19 Sep 2026 23:27:43 +0000

Introduction

Big E-commerce organisation like Jumia handle large datasets that are vital in the day-to-day operations. The data needs to be evaluated to make good and decisive business decisions that will improve overal business perfomance.
For this article I will take you through the process of building a comprehensive product analysis and dashboards that will guide smarter business decisions.

Objective

The objective of this project is to:

To produce excel dashboards that provide insights into the perfomance of products.
To analyze product pricing, discounts, customer reviews and ratings to understand product perfomance and identity trends.

Dataset Overview

Product - Name of the product
Current price - The current seeling price of products in (Ksh)
Old price - The original price before discount in (Ksh)
Discount - The percentage discounts offer on the products
Review - The number of customer reviews received on the products
Ratings - The average customer rating of the product out of 5

Data Cleaning

Before any analysis could be done on the dataset, I had to clean the data for the mistakes that were done during data entry. This are the steps that i did towards effective data cleaning

1) The first step i did was to copy the data to a new worksheet then renamed it "Cleaned" and renamed the other worksheet as "original"

2) The next step I added freezing panes to assist with easier navigation.

3) Used the proper function to capitalize the first letters of the titles

4) To change the numeric columns (Current price) and (Old price) to their correct data types of currencies I pressed ctrl and selected both columns then changed the data field to currencies then used the find and replace to change (ksh) into blank which then sets the data to the correct field type.

5) Removed duplicates on the dataset which will lead to an accurate analysis

Data Enhancement

1) Discounted Amount
To find the discounted amount I created a new column right after the Old price column then perfomed a calculation on the new column Discounted amount = Old price - Current price

2) Rating category
For the rating category Iwas expected to display the rating either excellent, average or poor i used the below formula
=IF(NOT(ISNUMBER(H3)),"Not Provided",IF(H3<3,"poor",IF(H3<=4,"average",IF(H3>4.5,"excellent","good"))))

Since in the rating column there was blank cells i replaced the blanks with "Not Provided". The IF(NOT(ISNUMBER function was used to check if the highlighted cell has a number or not and returns cells that is not numerical with "Not provided".

I added rating of "good rating" since there was some figures that were left out and they were above the average rating and slightly below the excellent rating.

3) Discount Category
The Dicount category was categorized into three high, medium and low. I inserted a new column next to the discount pecentage and used the following formula for groupings. =IF(E4<20%, "Low Discount", IF(E4<40%, "Medium Discount", IF(E4>41%, "High Discount")))

4) Price Category
I was required for this project to display if prices were high, medium or low, used the following formula to determine the out of the prices
=IF(B3<500, "Low Price", IF(B3<1500, "Medium Price", IF(B3>1501, "High Price")))

Data Analysis

For data analysis I created an new worksheet and the worksheet created a table that best describe the various questions raised for the project as show below

Then used the filter and sort to find out the top rated products and lowest rated products to further give insights of how the products are performing on the market

To find if there was any relationship between products and customers reviews then also find if there was any relationship between high discount and more reviews on the products I used the CORREL() function where a positive relationship results with a +1, with a -1 symbolizing a strong negative relationship. Close to 0 represents a no relationship of two products

Dashboard

For the dashboard I first started by creating pivot tables on then data then from the pivot i then created slicers that are instrumental on the creating of the dashboards used for visualization.

Business Insights And Recommendations

1) High Discounts do not translate to high customer engagements since most of the products that have high discounts have poor ratings from customers.

2) Highly rated products from the data most of the products tend to have low prices on them but that is not on all products since other products have a medium or high price making it difficult to ascertain if products that have high ratings are cheap in the market.

3) High quality assurance on low rated products to make sure the products do not have defects making it to have low rating after which it affects the prices from low prices to medium.

4) Improving on the review method of the products so that most of the products are reviewed by customer after purchase since from the data a lot of customer fail to have reviews of products making it hard to know the customer satisfaction.

Conclusion

This project have changed how i interact with data and has given me more insights and how to approach projects to answers business questions and to provide solutions that make a huge difference on the overall performance products and organization.

The link below to github for the workbook that contains Original raw data and cleaned data, data enrichment, analysis and dashboard

Github repository https://github.com/alfieotuko/Jumia-product-analysis

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

Otuko Mauwa — Mon, 31 Aug 2026 01:39:32 +0000

Introduction

Excel is spreadsheet software tool that is wildly and commonly used to organize, format, calculate and analyze data in row and columns. It is widespread due to it's user friendly interface and powerful inbuild functions and features that makes it easy for beginners and professional alike to do day-to-day data related tasks. This article takes you through a step by step guidelines on how to use and navigate excel from basic navigation to practical data cleaning methods that are vital for data analytics.

Excel Workbook

1) Workbook and Worksheets
An excel workbook is the main file that can contain one or many worksheets. A worksheet is the single grid that contains multiple rows and columns. The worksheets are mainly found on the bottom left of the workbook. The below image indicates the different worksheets and a worksheet can be added by clicking the +

2) Cell
A cell is an intersection of arow and column.

3) Ribbon
A ribbon is a toolbar at the top part of a worksheet that has buttons and icon. It organizes tools into groups for quick formatting and data management.

Data Cleaning

Data cleaning is the process of finding and fixing errors so that the data can be easy to work on for accurate information and analysis. Before starting on data cleaning i copied the original worksheet to another worksheet and renamed it to clean and the clean worksheet is where i will be working on cleaning the data.

1) Test Functions
Upper - Changes text characters to Uppercase
Lower - Changes text characters to lowercase
Trim - Removes spaces in characters before and after and also in between
Concat - Can be used to join characters of two cells like using the first name and last name to create an email address

2) Find and Replace
Find and replace is the initial step in data cleaning the dataset by merging similar data that were enter incorrectly such human resource and HR which basically mean the same thing but were not keyed to the dataset as one. Find and replace tool will be used to merge them into one.

3) Conditional Formatting
Conditional formatting is a tool that changes the appearence of cells, it let me use colors or databars to highlight high values or errors without having to look at rows or columns manually.

4) Sorting and Filtering
The first step to data cleaning is sort each column from the largest to smallest for numeric columns and from A to Z for text columns to be able to find cells that have wrong date keyed in them. As highlighted below the age column has thirty keyed in as a text instead of number.

To change it from text written 'thirty' and correcting it into a number format

5) Removing Duplicates.
The second part is to remove duplicates which tends to distort data. On the ribbon the data part has the tool for removing duplicate and it displays a dialog box which then i selected the primary column which is unique to make removing duplicates effective.

6) Blank and Missing values
On the columns that have blanks i changed them to '0' on the numeric columns and on the text columns i changed them to 'Unknown'. This is done to reduce inaccuracies.

7) Aggregate Functions
Power () - Raises the a number to a specified power
Product() - Multiplies numbers
Min() - Finds the smallest number in a row or column
Sum - Adds a range of numbers
Max() - Finds the largest number
Median() - Returns the middle number from a set of given numbers
Mode() -Returns the most occuring number in a set of numbers
Average() Adds a group of numbers together then divides the sum by count numbers

All the functions and formulas must start with an (=).

8) Statistical Functions
Count - Counts numerical cells only
Counta - Count the number of cells that are not blank
Countblank - Counts all the blanks in a columns or rows.

9)Conditional Aggregations
Countif - In the dataset I used countif to find the number of staff in IT department

Countifs - Used it to find the number of female staff that are above 30.

Sumif - Used it find out the total amount of salary for female staff.

Sumifs - Used it to calculate the total salary of male staff that are above 30

Averageif - Used it to calculate the average age of male staff.

Averageifs - Used to calculate the average age of male staff that are in the HR department

Conclusion

Excel serves as a vital software tool that is used in data analytics due to its reliability and easy to use features that makes complex tasks easy to manage. Data cleaning is the corner stone of analytics that determines whether can be trusted and used for making bold decisions that shape outcomes of a business.

Using the above techniques and guidelines assures you of more reliable way of data cleaning.

My First GitHub Project: From a Local Folder to GitHub Using Git and SSH.

Otuko Mauwa — Sun, 23 Aug 2026 01:20:14 +0000

Introduction

Git is local software that i installed on my machine to help me manage codes while github is an online platform that host git repositories.
This is a step by step guide of how i used git to publish my first project to github while a SSH key for a secure connection with github.

Tools Used

This task was done in the following environments

Windows Operating system
Git

git --version
git version 2.55.0.windows.4

Github account

Actions Taken to Link Git and Github

This are the step by step methods that i used to mlink git and github

1) Configuring Git

Configured Git with my github username and the email I used in setting up my github account

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

2) Generating the SSH key

The next action is to generate ssh key. The command will generaye two ssh keys, a private ssh key which should not be share and a public ssh key which is what will be used to link the git and github. The generated public ssh key will be copied to be then pasted on github.

ssh-keygen -t ed25519 -C "alfiemauwa7@gmail.com"
clip < "c/user/omolo/".ssh/id_ed25519.pub

3) Inserting the SSH to Github

The copied ssh key will be be added to github by going to the github settings under the SSH AND GPG keys and then clicking on the ssh key. To confirm if the added ssh key was successful;

ssh -T git@github.com

After which it will display a message that it was successful.

4) Creating Local Folder
Creating a folder in the local directory that will hold data of the project.

cd Desktop
mkdir kenya-hospital-records-analysis
cd kenya-hospital-record-analysis

5) Creating a File and a Folder

Creating a README.md file inside the kenya-hospital-record-analysis folder alongside the file also creating a folder named "data".

touch README.md
mkdir data

6) Creating a Git Repository

This is to initialize and turn the folder in a git repository and to check in which branch is the folder in

git init 
git status
On branch main

7) Stage and Commit

This is to stage and saving the project checkpoint history but still not saved in online cloud

git add .
git status 
On branch main
git commit -m "first commit for kenya-hospital-records-analysis"

8) Linking To Github

Creating a new and empty repository on github and copying the the URL of the ssh from github and pasting it on git there by saving it on the online platform.

git remote add origin git@github.com:alfieotuko/kenya-hospital-records-analysis

9) Pushing to Github

To save the repository to github

git push -u origin main

Adding URL Link

Here is the link to my github
alfieotukoGithub

Conclusion

The local folder was saved and hosted in a cloud based github repositories with the ssh providing a secure hosting.