DEV Community: Dorsila Otieno

Data Modelling, Relationships And Joins In Power BI

Dorsila Otieno — Sun, 13 Sep 2026 12:08:02 +0000

Introduction

Power BI is a business intelligence and data visualization platform used to transform raw data into meaningful information for decision making. However, creating effective reports does not only depend on attractive charts and dashboards. The underlying data model is equally important. A well designed data model determines how tables interact, how filters are applied, how DAX calculations behave, and how efficiently Power BI processes queries.

Data modelling in Power BI involves organizing tables, defining relationships between them, and establishing how data should interact within a report. Common modelling approaches include the flat table, star schema, and snowflake schema. In addition, Power BI provides relationships in the data model and joins in Power Query for connecting data.
Understanding these concepts is essential because poor modelling can result in incorrect calculations, duplicated information, slow reports, ambiguous filter paths, and difficult-to-maintain solutions. This article discusses data modelling, fact and dimension tables, relationships, filter direction, Power Query joins, and the differences between Power Query merges and Power BI relationships.

Data modelling in power BI

Data modelling in Power BI is the process of organizing data from one or more sources into a structured model that allows users to analyze and visualize information effectively. It involves deciding which tables should be created, how they should be related, which columns should be keys, and how filters should move between tables. For example, a retail business may have information about customers, products, dates, locations, and sales. Instead of keeping all this information in one large table, the data can be separated into logical tables and connected using relationships.

A good data model is important because it Makes DAX calculations easier to write and understand, Improves report and query performance, Reduces unnecessary data duplication, Makes the model easier to understand, allows reports to scale as data increases, Makes troubleshooting easier, Supports accurate filtering and aggregation and Improves long-term maintainability while a poor data model is a disadvantage because it can produce incorrect totals, unnecessarily large datasets, complicated DAX formulas and ambiguous relationships.

Flat table

A flat table is a single, large data table that combines all your information such as sales transactions, product names, and customer details into one wide row structure, for example

customer ID │ Date │ Customer │ Product │ Sales │ Category│ Location │ Quantity │

1001 | 01/09/2026 | Alice | Laptop | Electronics | Nairobi| 1 | 80,000
1002| 01/09/2026 |John| Phone |Electronics| Kisumu| 2| 60,000|

A flat table can be suitable for a small, simple dataset where the data has relatively few rows. However, for larger business intelligence solutions, a dimensional model is generally more appropriate
A flat table is Simple to understand, easy to import and start analyzing, requires few or no relationships, appropriate for small dataset, and convenient for simple dashboards. A flat table also has limitations in that it creates data redundancy ,the same customer, product, or category information may be repeated many times the table can become very wide, changes to descriptive information may need to be repeated across many records, it becomes difficult to maintain as the dataset grows, and it may increase model size and reduce the advantages of dimensional modelling.

Star schema

A star schema is a data modeling technique in Power BI where a central fact table connects directly to multiple surrounding dimension tables, forming a star like structure. It consists of a central fact table surrounded by dimension tables, the structure resembles a star because the fact table is positioned at the center while dimensions surround it. Dimension tables support filtering and grouping, while fact tables support summarization. Star schema is highly suitable for business intelligence projects involving sales, finance, inventory, human resources, healthcare, marketing, and other analytical workloads.

It is generally the preferred approach for Power BI models because it separates descriptive information from measurable business events

for example:

Fact Sales: customer ID, Date Key ,Customer Key ,Product Key ,Location Key
Quantity, Sales Amount,

Dim Customer: Customer Key, Customer Name, Gender, Age group

Dim Product: Product Key, Product Name, Category, Brand
Dim Date: Date Key, Date, Month, Quarter, Year
Dim Location :Location Key, County, Region

Advantages of a star schema

Provides a clear and understandable model.
Reduces unnecessary duplication.
Makes DAX measures easier to create.
Provides efficient filter propagation.
Supports scalable reporting.
Makes the Model View easier to understand.
Works well with Power BI's analytical engine.
The disadvantages of a star schema are as follows:
Requires more planning than a flat table.
May require Power Query transformations.
Poorly designed keys can cause relationship problems.
Users need to understand fact and dimension concepts.

Snowflake schema

A snowflake schema is a data modeling structure where dimension tables are split into multiple related sub dimension tables forming a pattern that resembles a snowflake .It is an extension of the star schema where dimension tables are further divided into related tables. Snowflake schemas may be appropriate when dimensions have complex hierarchical structures or when maintaining normalized dimension data is important. However, Microsoft notes that Power BI models generally benefit from star-schema principles, and snowflake dimensions may sometimes not be normalized to produce a simpler model table.

Advantages

Reduces duplication within dimensions.
Can represent complex hierarchical structures.
Can be useful when dimensions contain multiple levels.
May be appropriate when the source system is already highly normalized.

Disadvantages

Creates more tables and relationships.
Makes the model more complicated.
Can make report development less intuitive.
Can create longer filter paths.
May require more complex DAA

Fact tables and Dimension tables

Fact tables

A fact table stores measurable business events. It normally contains numeric values that can be aggregated together with foreign keys that identify the related dimensions. It stores quantitative, numerical data and measurable business events like sales, orders, or transactions. Fact tables anwser questions like how many, how much, how often and what was the total

Dimension tables

Dimension tables contain descriptive information used to filter, group, and analyze facts. Examples include: Dim Customer, Dim Product, Dim Date, Dim Location. Dimensions anwser questions like who, where , what, when and which category

Grain or granularity of a fact table

The grain describes exactly what one row in a fact table represents.For example, the grain of Fact Sales might be, One row represents one product sold in one order. This distinction is important because all measures and relationships depend on the level of detail stored in the fact table. A consistent grain prevents incorrect aggregations and helps ensure that DAX calculations produce reliable results. Microsoft specifically recommends that fact tables load data at a consistent grain.

Relationships in power BI

Relationships in a Power BI data model link two separate tables together using a common column or key, allowing filters to flow and data to combine correctly in your reports. They are important business information is often distributed across several tables.

One to One

A one-to-one relationship occurs when each record in one table corresponds to exactly one record in another table. It may be useful when information has been separated into two tables for security, organization, or specific modelling requirements.

Customer ID 1 ---------- 1 Customer ID

One to many

A one-to-many relationship occurs when one record in one table can be related to many records in another table. For example One customer can make many purchases. This is the most common relationship in a star schema. It is normally used between a dimension table and a fact table. It should not be used if the supposed one side contains duplicate values the key on the one side must be unique

Dim Customer                 Fact Sales

Customer ID  1  ----------    Customer ID

For example:

Dim Customer
C001 - Mary

Fact Sales
C001 - Order 101
C001 - Order 102
C001 - Order 103

Many to Many

A many-to-many relationship occurs when multiple records in one table can correspond to multiple records in another table. For example, one student may register for many courses, while one course may have many students. Many to many relationships are useful when the business situation genuinely contains many-to-many relationships. They should not be used to solve duplicate key problems. Where possible, a bridge table and a clearer star schema should be considered. Primary key uniquely identifies each record in a table while a foreign key is a column that refers to a key in another table.
Student ID -------- Course ID

Referential Integrity

Referential integrity means that relationships between tables are logically consistent. For example, a Customer ID appearing in Fact Sales should normally correspond to a valid Customer ID in Dim Customer. If a fact record contains a Customer ID that does not exist in the customer dimension, it may result in unmatched or unexpected data during analysis. Good data preparation should therefore identify missing keys, duplicates, and invalid references before building the final model.

Active and inactive relationships

An active relationship is the relationship Power BI automatically uses when applying filters between tables while a inactive relationship exists in the model but is not automatically used for filtering. It can be activated within a DAX calculation when required. A common example involves dates, Fact Sales table might contain: Order Date, Ship Date, Delivery Date. A single Date dimension may therefore have multiple relationships with Fact Sales, but normally only one can be active for automatic filtering at a time. Inactive relationships are useful when a report needs to analyze the same fact table using different date roles.

Filter direction

Filter direction determines how filters move between related tables. With single-direction filtering, filters normally move from the dimension table toward the fact table while bidirectional filtering allows filters to move in both directions. single direction filtering is normally prefered in star schema because it provides predictable filter propagation. Possible problems associated with bidirectional filtering are Ambiguous filter paths, Unexpected filtering behaviour. More complicated models, Difficulty troubleshooting calculations and Potential performance issues, Therefore single direction filtering is the most prefered.

Joins in power query

A join combines information from two tables based on one or more matching columns. In Power Query, this is performed using Merge Queries.

A Left Outer Join keeps all records from the left table and matching records from the right table.
Right Outer Join keeps all records from the right table and matching records from the left table.
Full Outer Join keeps all records from both tables, whether or not they have a match.
An Inner Join keeps only records that have a match in both tables.
Left Anti Join returns records that exist in the left table but do not have a match in the right table.
A Right Anti Join returns records that exist in the right table but do not have a match in the left table.

| Join Type | Records Retained | Example |
| Left Outer | All Customers + matching Orders | Find all customers, including those without orders |
| Right Outer | All Orders + matching Customers | Find all orders, including unmatched orders |
| Full Outer | All Customers and Orders | Find all matched and unmatched records |
| Inner | Only matching records | Find customers who placed orders |
| Left Anti | Customers without Orders | Find customers who never placed an order |
| Right Anti | Orders without Customers | Find orders without matching customer |

Power query joins vs power BI relationships

Power Query merge combines information during the data preparation stage. For example, Customers and Orders can be merged using Customer ID so that customer information becomes available in the resulting query.

Customers + Orders
↓
Power Query Merge
↓
Combined Query

The merge can physically bring columns from one table into another.

A relationship is created during the data modelling stage.

Dim Customer
|
| 1:*
|
Fact Sales

The tables remain separate.

A relationship does not physically combine their columns. Instead, it tells Power BI how the tables are connected and how filters should move between them.
Their differences include: Power query merge is a data preparation stage, Combines columns/data ,Produces a transformed query, Uses join types, Can increase table size, Useful for enriching data while a power BI relationship is a Data modelling stage, Connects separate tables, Produces a model relationship, Uses cardinality and filter direction, Keeps tables separate, Useful for analysis and filtering
A merge can be appropriate when: A column is required directly in another table, Data sources need to be cleaned or consolidated, The information logically belongs in the same table while a relationship is suitable when relationship is preferable when tables represent different business entities or roles.

Reccommended power BI model

For a typical business intelligence project, I would recommend a star schema. The recommended model would contain one or more central fact tables supported by descriptive dimension tables. I would also recommend to use one-to-many relationships between dimensions and fact tables.

For example:

Dim Customer 1 → * Fact Sales
Dim Product 1 → * Fact Sales
Dim Date 1 → * Fact Sales
Dim Location 1 → * Fact Sales
I would also use single-direction filtering from dimensions to facts.

Dim Product
|
↓
Fact Sales

This provides predictable filter propagation and reduces the possibility of ambiguous relationships. Bidirectional filtering would only be introduced when a genuine business requirement makes it necessary.
The following are the reasons why I would recommend a star schema:

1.DAX simplicity Measures can be created against the fact table while dimensions provide the filtering context.
For example: Total Sales = SUM(Fact Sales[Sales Amount])
A user can then analyze this measure by Product, Customer, Date, or Location.

2.Scalability New products, customers, locations, and transactions can be added without redesigning the entire model.

3.Reduced Redundancy Customer and product information does not need to be repeated for every transaction.

4.Maintainability Changes to dimension information can be made in one location rather than across thousands or millions of fact records.

5.Filter Propagation The dimension-to-fact relationship provides a predictable filtering structure.

6.Performance Separating descriptive dimensions from transactional facts can make the model efficient and reduce unnecessary duplication.

Conclusion

In conclusion, effective data modelling, relationships, and joins are essential for building reliable Power BI reports. A star schema with clear fact and dimension tables, appropriate relationships, and mainly single-direction filtering provides better performance, simpler DAX, and easier maintenance. Proper use of Power Query joins also helps prepare and combine data efficiently for analysis.

Building An Interactive Excel Dashboard For E-commerce Product Analysis :A case study of Jumia products

Dorsila Otieno — Sat, 12 Sep 2026 07:43:00 +0000

Introduction

Jumia is one of Africa’s leading online shopping platforms, offering a wide range of products at different prices and discount levels. With many products competing for customers’ attention, understanding pricing, discounts, customer reviews, and ratings is important for making better business decisions. This project analyzes data from 116 products listed on Jumia to identify patterns in pricing, discounts, and customer feedback.
The analysis uses Microsoft Excel to clean, organize, and analyze the product data and present the findings through an interactive dashboard. The dashboard provides insights into product prices, discount levels, customer reviews, and ratings, helping sellers and Jumia understand product performance and make informed decisions about pricing, promotions, and customer engagement.

Dataset description

The dataset consist consists of the following variables:

Product: The name and description listed on jumia
Current price: The current selling price in kenyan shillings
Old price: The original price of the product before any discount was applied
Discount: The percentage deduction offered on the the original price
Review: The number of customer reviews received by each product
Rating: The average customer rating given to the product
Rating category:
Discount category:
Current price category:

Data cleaning and preparation process

The collected product dataset was cleaned and prepared in Microsoft Excel to ensure that the data was accurate, consistent, and suitable for analysis. The following steps were carried out:

Removal of Duplicate Products: Duplicate product records were identified and removed to ensure that each product appeared only once in the dataset. This helped prevent duplication from affecting the analysis and calculations.
Standardization of Currency Values: The Find and Replace function was used to remove the “KSh” text from price values. The values were then converted into a numerical format and formatted to KES currency.
Freezing Panes: Panes were frozen to keep the column headings visible while scrolling through the dataset. This improved navigation and made it easier to identify variables when working with the large set of data.
Creation of Discount Categories
Additional columns were created to categorize products according to their discount levels. The categories used were High Discount, Medium Discount, and Low Discount.
Categorization of Product Ratings
Product ratings were grouped into three categories: Poor, Average, and Excellent. A nested IF function was used to automatically assign each product to the appropriate rating category,e.g(=IF(G2>40%,"High discount"IF(G2>20%,"Medium discount"IFG2<20%,"Low discount"
Calculation of discount amount was done using the function =C2-B2
Filtering of the Dataset Sort and filter was used to view and examine specific products or categories based on selected criteria.
Conversion into an Excel Table
The cleaned dataset was converted into an Excel table. This made the data easier to manage and automatically extended formulas and formatting when new data was added. he table features were used to calculate important summary statistics, including the count of products, number of reviews, ratings, averages, and total sums of numerical variables. These calculations provided a foundation for the analysis and development of the product performance dashboard.

Dashboard creation process

The cleaned data was organized into an Excel table, PivotTables were then created to summarize products, ratings, reviews, prices, and discounts, charts were then created to visualize the data, KPI cards were added to display key performance indicators, and slicers and filters were added to make the dashboard interactive. The charts, tables, and KPIs were arranged and formatted for easy interpretation.

key insights and business recommendations

Building An Interactive Excel Dashboard For E-commerce Product Analysis :A case study of Jumia products

Dorsila Otieno — Sat, 05 Sep 2026 11:35:08 +0000

Introduction

Jumia is one of Africa’s leading online shopping platforms, offering a wide range of products at different prices and discount levels. With many products competing for customers’ attention, understanding pricing, discounts, customer reviews, and ratings is important for making better business decisions. This project analyzes data from 112 products listed on Jumia to identify patterns in pricing, discounts, and customer feedback.
The analysis uses Microsoft Excel to clean, organize, and analyze the product data and present the findings through an interactive dashboard. The dashboard provides insights into product prices, discount levels, customer reviews, and ratings, helping sellers and Jumia understand product performance and make informed decisions about pricing, promotions, and customer engagement.

Dataset description

The dataset consist consists of the following variables:

Product: The name and description listed on jumia
Current price: The current selling price in kenyan shillings
Old price: The original price of the product before any discount was applied
Discount: The percentage deduction offered on the the original price
Review: The number of customer reviews received by each product
Rating: The average customer rating given to the product
Rating category:
Discount category:
Current price category:

Data cleaning and preparation process

The collected product dataset was cleaned and prepared in Microsoft Excel to ensure that the data was accurate, consistent, and suitable for anaysis. The following steps were carried out:

Removal of Duplicate Products: Duplicate product records were identified and removed to ensure that each product appeared only once in the dataset. This helped prevent duplication from affecting the analysis and calculations.
Standardization of Currency Values: The Find and Replace function was used to remove the “KSh” text from price values. The values were then converted into a numerical format and formatted to KES currency.
Freezing Panes: Panes were frozen to keep the column headings visible while scrolling through the dataset. This improved navigation and made it easier to identify variables when working with the large set of data.
Creation of Discount Categories
Additional columns were created to categorize products according to their discount levels. The categories used were High Discount, Medium Discount, and Low Discount.
Categorization of Product Ratings
Product ratings were grouped into three categories: Poor, Average, and Excellent. A nested IF function was used to automatically assign each product to the appropriate rating category,e.g(=IF(G2>40%,"High discount"IF(G2>20%,"Medium discount"IFG2<20%,"Low discount"
Calculation of discount amount was done using the function =C2-B2
Filtering of the Dataset Sort and filter was used to view and examine specific products or categories based on selected criteria.
Conversion into an Excel Table
The cleaned dataset was converted into an Excel table. This made the data easier to manage and automatically extended formulas and formatting when new data was added. he table features were used to calculate important summary statistics, including the count of products, number of reviews, ratings, averages, and total sums of numerical variables. These calculations provided a foundation for the analysis and development of the product performance dashboard.

Dashboard creation process

key insights and business recommendations

Insights

High discounts (40%+ off) drive significantly higher review counts and engagement compared to low-discount items. However, steep discounts on low-rated items fail to generate sustained volume, indicating that discounts attract initial attention of customers

Medium prices generates the highest overall volume of positive reviews. Extremely low-priced products frequently suffer from lower average ratings due to perceived or actual quality issues.
Products rated above 4.0 receive higher visibility and sales volume. Items below a 3.0 rating show a sharp drop in customer reviews, signaling buyer hesitation and poor search rank performance.

Recommendations

Shift focus from high pricing to "Medium Discounts" (20–40%) on high-rated products (4.0+ stars) to maximize profit margins while maintaining high conversion rates
Encourage post-purchase reviews for average items using follow-up prompts or small loyalty incentives, as higher review directly correlate with better search placement on Jumia.
Pair high-performing products with newer or lower-visibility items in discount to boost engagement without relying heavily on price drops.

Link

https://github.com/otienodorsila90-cell/MS-Excel-dashboard-for-E-commerce-product-analysis-A-case-study-for-Jumia-products

Getting Started With Excel.

Dorsila Otieno — Sat, 29 Aug 2026 19:05:18 +0000

Introduction

Microsoft Excel is a spreadsheet program developed by Microsoft that allows users to organize, calculate, format, and analyze data using grids of rows and columns. Rows are the horizontal lines in the Excel worksheet while columns are the vertical bars in the worksheet. Excel worksheet contains cells and ranges, A cell in a Microsoft excel worksheet is made by the intersection of rows and columns while a range is made up of two or a group of cells. Microsoft Excel also has a workbook that is made up by a group of worksheets. The rows are ordered in alphabets while the columns are ordered in numerical form.

Basics and cleaning in Microsoft Excel

Microsoft Excel contains a formula bar that helps display or edit contents in an excel worksheet. keying in data is done by clicking a cell and typing data that can be inform of texts, Numbers, and dates. To move to next cell press enter. Tabs i.e File, Home, Insert, Draw , Page layout etc.

Home tab

The Home tab can be used to expand the width of all columns for visibility of data in the cells by clicking Format and choosing Auto column width. Conditional formatting can be used to view values between a range ,greater than or less than depending on the information you want, or check on text that contains specific alphabets of your choice. You can also sort and filter your data which will enable you to standardize your data.

Data tab

this tab can be used to design scatter charts that looks at two numeric variables and also design a pivot table .Data validation is also possible in this tab to avoid inserting wrong data in your worksheet. In this section you can also remove duplicates to ensure standardization of data.

Other tabs

Insert is used to add tables and columns etc, Custom is used to pick color and font size. Filtering hides rows temporarily and without deleting them. Wrap texting is used to make long texts appear on multiple lines in the same cell.

Functions used in Excel

Arithmetic operators

The following signs are used in excel for Arithmetic analysis in Microsoft Excel; + is used for addition, - is used for subtraction, * is used for multiplication, / is used for division, ^ is used for exponent.

Statistical functions

These functions are used to summarize ,analyze and interpret data. They include :=COUNT which is used to count non-blank numeric cells, =COUNTA which is used to count all non-blank cells, =COUNTBLANK Which is used to count all blank cells.
Conditional aggregation functions are also used in Microsoft excel for various functions ,They include =COUNTIF which Counts cells that match a single rule or criteria while=COUNTIFS can check multiple conditions at the same time, =SUMIF add values based on a single condition, while =SUMIFS add values based on multiple conditions .=AVERAGEIF is used to calculate the average of a group of cells in a specific range.

Dispersion and spread functions.

These measures how far apart numbers are from average. They include;=POWER, =SQRT used to find square root, =PRODUCT used for multiplication purposes, =MIN used to find the minimum value in a range, =MAX used to find the maximum value in a range, =MODE which is used to find mode ,=MEDIAN ,=SUM,=AVERAGE.

Non-numerical functions

These functions are used in text columns. They include;

=LEN used to check on the length of texts
=RIGHT used to check on the letters on the right part of a text
=LEFT used to check on letters on the left part of a text
=TRIM used to trim texts
=MID used to check on the middle letters of a text
=FIND used to find specific letters on a text
=CONCATused to join words from different cells
=LOWER used to replace texts to lower case
=UPPER used to replace texts to upper case
=PROPER used to replace texts to proper case

Other non-numerical functions are also in relation to date , month and year. They include;

=Today it shows the current date
=Now it shows the current time
=YEAR it shows the current year
=MONTH it shows the current month
=DAY
To find difference between dates ,months and year ,the following functions can be used;
=Datedif shows the difference between two dates in different cells
=Monthdif shows difference in months
=Yeardif shows the difference between years e.g difference of a hire year and the current year
=Networkdays

Conclusion

In conclusion, mastering the basics of Microsoft Excel provides a foundational skillset for efficiently managing and understanding data. By learning how to navigate the grid, perform simple calculations, and use core functions like SUM or AVERAGE, users can instantly transform messy, manual tasks into organized, automated workflows. Ultimately, these basic features serve as the critical building blocks for anyone looking to build data literacy, save time, and unlock more advanced analytical tools in the future.

Understanding Gitworkflow

Dorsila Otieno — Sat, 22 Aug 2026 15:11:45 +0000

Git Workflow

Git is a local version control system that tracks code changes, while GitHub is a cloud-based platform used to host those changes and collaborate with others. Together, they form the backbone of modern software development by allowing multiple developers to work on the same codebase simultaneously without overwriting each others work

Working directory of git

This is the actual, physical folder on your computer's filesystem where you view, create, edit, and delete your project files.
It can either contain :

Tracked files: files that Git actively monitors and includes in version control history
Untracked files: are any files in your working directory that have not yet been added to your Git repository's snapshots or staging area.

Staging

Staging is the process of preparing specific file changes to be included in your next commit.

Reasons for staging

Atomic Commits: It allows you to group related changes together. If you fix a bug and work on a new feature at the same time, you can stage and commit the bug fix separately from the incomplete feature.
Review Mechanism: It provides a safe buffer zone to double-check exactly what lines of code are moving forward.
Work Checkpointing: You can stage a file at a certain point of success, continue experimenting on that file in your working directory, and still preserve your staged checkpoint.

Staging commands

git add "filename" Stages a specific file.
git init Manages project.
git status To see what files are currently sitting in staging vs your working directory.
git diff Shows differences between your working directory and your staging area.
git diff --staged Shows differences between your staging area and your last commit
git restore --staged "filename" Removes Changes from Staging

Commit and push

To save your local changes and upload them to git you need to stage your changes, commit them locally, and push them to the server.

Commands used in commit and push

The block of code below is used in the given order to commit and push;

git add
git commit -m "commit message"
git log
git log --oneline
git branch

The next step is to create a respiratory in your github account that is linked to your gitbash then copy the link on the SSH key. Get back to gitbash and follow the steps in the block of code below;

git remote add origin <paste>
git remote -v
git push -u origin main

Other commands used in Git

cd used to change directory
cd .. used to get backwards with one step
mkdir used to make a new folder
ls used to list folders
ls -la used to list all files
touch used to make a new file
echo " heading" > README.md
Nano README.md used to write long texts
cat README.md Used to read texts in README.md

Conclusion

In conclusion, Git is the industry-standard, open-source distributed version control system (DVCS) that acts as the backbone of modern software engineering. By utilizing a unique three-stage architecture (Working Directory, Staging Area, and Local Repository), Git optimizes how changes are tracked, stored, and managed over time. It bridges the gap between individual, experimental coding and large-scale, enterprise collaboration.