DEV Community: asma Salah

Data Modelling, Relationships & Joins

asma Salah — Sun, 13 Sep 2026 12:58:16 +0000

Data Modelling in Power BI

Coming into this, I assumed "data modelling" just meant loading a table into Power BI and building charts on top of it. What it actually means is deciding how your tables relate to each other before you ever touch a visual because that structure is what every DAX calculation, filter, and report performance number depends on downstream. A messy model doesn't just look ugly in the background, it makes calculations slower, filters behave unpredictably, and reports harder to maintain as the business grows.

There are three common approaches to structuring this:

flat tables
star schemas
snowflake schemas.

Flat table is the simplest structure, every piece of data lives in one wide table. A sales record might repeat the customer's name, region, and product category on every single row, because nothing is broken out separately.

Advantages:

No relationships to configure
Dead simple to build for tiny datasets
Easy for a beginner to understand at a glance

Disadvantages:

Massive redundancy: the same customer name gets typed thousands of times
Slower performance as row count grows
DAX measures get harder to write cleanly, since everything is tangled into one table

When it's appropriate:

Very small, one-off analyses
A dataset that will never grow or be reused
Quick exploratory checks, not a real report

Performance impact:

Power BI's engine (VertiPaq) compresses data column by column, so repeated values do compress individually
But forcing one column to carry mixed responsibilities hurts both compression and calculation clarity as the model scales

SaleFlat
Sale ID Category
CustomerName Quantity
ProductName Revenue
Region OrderDate

Star Schema

A star schema separates data into one central fact table (the measurable events: sales, orders) surrounded by dimension tables (descriptive context: customers, products, dates, locations), each connected directly to the fact table.

Advantages:

Minimal data redundancy
Fast performance: Power BI's engine is optimized specifically for this shape
Simple DAX measures
Easy to understand and extend as the model grows

Disadvantages:

Requires upfront design effort, you have to decide what's a "fact" vs a "dimension"
Slightly more setup than just dumping everything into one table

When it's appropriate:

Almost always, for any real Power BI report
It's the industry recommended default schema

Performance impact:

This is the exact shape Power BI is built around
Filters propagate cleanly across one relationship hop
DAX measures like SUM(FactSales[Revenue]) stay simple and fast

Snowflake Schema

A snowflake schema takes a star schema further by normalizing dimensions, splitting a dimension into multiple related tables. For example, DimProduct might split into DimProduct and DimCategory, so category names aren't repeated across every product row.

Advantages:

Even less data redundancy than a star schema
Useful when a dimension has a lot of repeated sub-attributes

Disadvantages:

More tables means more relationships to manage
Adds complexity to the model
Extra joins during query execution can slow performance

When it's appropriate:

Large enterprise models where a dimension is genuinely large and repetitive enough that normalizing it saves meaningful storage or performance
Otherwise it's often unnecessary complexity for a student or small-business project

Performance impact:

Generally slightly slower than a pure star schema
Power BI has to traverse an extra relationship hop to get from the fact table to the outer dimension table

Fact Tables and Dimension Tables

Once I understood the star shape, the next question was, how do I actually decide which table is the "fact" and which ones are "dimensions"?. Facts are things that happened, dimensions are things that describe what happened.

Fact Tables

A fact table stores the measurable events of the business, the numbers you actually want to add up, average, or count.

What's normally stored in a fact table:

Numeric measures (Quantity, Revenue, Profit)
Foreign keys linking to each related dimension table
A timestamp or date key for when the event occurred

Measures vs. descriptive attributes:

Measures are numbers meant to be aggregated, summed, averaged, counted (e.g. Revenue, UnitsSold)
Descriptive attributes are labels that explain the measure but aren't themselves calculated (e.g. CustomerName, ProductCategory),these belong in dimension tables, not the fact table

Grain / granularity:

Grain means what one single row in the fact table actually represents
Example: if each row is one line item on an order, the grain is "one product per order." If each row is one full order regardless of how many products it contains, the grain is "one order"
Getting the grain wrong early causes major rework later, since every measure and relationship is built assuming a specific row-level meaning

Examples of fact tables:

FactSales: one row per sale
FactOrders: one row per order
FactTransactions: one row per financial transaction

Dimension Tables

A dimension table stores the descriptive context that explains who, what, where, and when around a fact.

What's normally stored in a dimension table:

Descriptive attributes (names, categories, addresses)
A unique key that identifies each row (e.g. CustomerID)
Attributes used for filtering and grouping reports, not for calculation

Examples of dimension tables:

DimCustomer: customer name, region, contact info
DimProduct: product name, category, brand
DimDate: day, month, quarter, year
DimLocation: city, country, store branch

Practical Example: Connecting Fact to Dimensions

Using the diagram we used earlier, a central FactSales table records every sale, quantity, revenue, and foreign keys pointing to a customer, a product, a date, and a location. It doesn't store the customer's name or the product's category directly; it just stores the keys pointing to those details.

DimCustomer connects to FactSales so every sale can be traced - back to who bought it
DimProduct connects to FactSales so every sale can be traced back to what was sold

Relationships in Power BI

A relationship is what tells Power BI how two tables connect specifically, which column in one table matches which column in another. Without relationships, every table would sit isolated, and Power BI would have no way to combine a sale in FactSales with the customer who made it in DimCustomer. Relationships are what let a star schema behave like one connected model instead of a pile of unrelated spreadsheets.

One-to-Many (1:*)

How it works:

One row on the "1" side can match many rows on the "many" side
This is the default and most common relationship type in Power BI

Example:

One customer in DimCustomer can appear in many rows of FactSales (a customer can make multiple purchases).

When to use it:

Anytime a dimension table describes many events in a fact table, this is the backbone of every star schema

When not to use it:

Don't force a 1:* relationship between two tables that don't actually have that structure; it will silently produce wrong aggregations (e.g., inflated totals) if the "1" side isn't actually unique

One-to-One (1:1)

How it works:

Exactly one row in Table A matches exactly one row in Table B, with no repeats on either side

Example:

Employee and EmployeeBadge: Each employee has exactly one badge, and each badge belongs to exactly one employee

When to use it:

Rare in practice, mostly used when splitting one table into two for organizational or security reasons (e.g., separating sensitive HR data into its own table)

When not to use it:

Don't use 1:1 just because two tables happen to be the same size, confirm the actual relationship is genuinely one-to-one, not coincidentally matching row counts.

Many-to-Many (:)

How it works:

Multiple rows in Table A can match multiple rows in Table B, with no single "unique" side.

Example:

Students and Courses: One student takes many courses, and one course has many students enrolled

When to use it:

Only when the real-world relationship genuinely has no unique side. Power BI supports this, but it should be a deliberate choice, not a shortcut

When not to use it:

Avoid it as a workaround for poor table design; many-to-many relationships make filter behavior harder to predict and can silently produce incorrect totals if not modeled carefully Keys, Uniqueness, and Referential Integrity

Primary Keys:

A column that uniquely identifies each row in a table (e.g. CustomerID in DimCustomer)
Every value in this column must be unique, no duplicates allowed

Foreign Keys:

A column in another table that references a primary key elsewhere (e.g., CustomerID in FactSales)
These values are allowed to repeat, since one customer can have many sales

Why CustomerID is unique in DimCustomer but repeats in FactSales:

In DimCustomer, CustomerID is the primary key, each customer appears exactly once
In FactSales, CustomerID is a foreign key, it appears once per sale, so the same customer's ID shows up on every row representing their purchases

Referential integrity:

Every foreign key value should have a matching primary key somewhere. A CustomerID in FactSales should always correspond to a real customer in DimCustomer
When this breaks (a foreign key with no match), Power BI treats those rows as "unknown" in visuals, which can silently distort a report if left unnoticed.

Active vs. inactive relationships:

Power BI only allows one active relationship between two tables at a time; this is the one used automatically in calculations.
Additional relationships between the same two tables can exist but must be marked inactive and are only used when explicitly called with USERELATIONSHIP() in a DAX formula
A common example: a FactSales table might have both an OrderDate and a ShipDate, both relating to ``, but only one can be active by default, so the other stays inactive until a specific measure needs it.

Filter Direction

Once tables are related, the next question is: when I click on something in one table, which other tables actually respond to that click? That's what filter direction controls, it determines which way a filter is allowed to "travel" across a relationship.

Single-Direction Filtering

How it works:

A filter only flows from the "1" side of a relationship to the "many" side
This is Power BI's default behavior for relationships in a star schema

Example:

Selecting a product in DimProduct filters FactSales down to only the sales rows for that product
But selecting or filtering something in FactSales does not filter DimProduct back, the dimension table stays fully visible

Why this is the safe default:

It matches how a star schema is meant to work, dimensions describe facts, not the other way around
It keeps filter behavior predictable and easy to reason about Bidirectional Filtering

How it works:

The filter flows both ways, selecting something in the fact table can also filter the dimension table, and vice versa

Example:

Useful in a many-to-many scenario, like filtering a Students table based on a selection in a Courses table, where neither table is a clear "dimension" or "fact"

Why it should be used carefully:

Ambiguous filter paths: if a model has multiple bidirectional relationships forming loops between tables, Power BI can no longer determine a single clear path for a filter to follow, which can cause errors or force it to pick an unintended path
Unnecessary model complexity: turning on bidirectional filtering everywhere "just in case" makes the model harder to debug, since every filter interaction now has more possible directions to trace
Performance cost: filters traveling in both directions across many relationships require more computation than a clean single-direction star schema

Practical rule of thumb: only turn on bidirectional filtering when you have a specific, deliberate reason (like a genuine many-to-many relationship), never as a default setting across your whole model.

Joins in Power Query

A join is how you combine two tables based on a matching column, using Merge Queries in Power Query. Unlike a Power BI relationship, a merge actually pulls columns from one table into another during the data-loading stage, before the model is even built.

Using my Customers table (6 customers, two of whom, Grace and Mary, have never placed an order) and Orders table (7 orders, one of which references a CustomerID that doesn't exist in Customers), here's what each join type produces:

Inner Join

Definition: keeps only rows where the matching column exists in both tables
Retained: customers 1–4 and their matching orders
Result: 6 rows Grace, Mary, and the orphaned order (106) are all dropped

Left Outer Join

Definition: keeps every row from the left (first) table, plus matches from the right table where they exist
Retained: all 6 customers, with order details attached where available, blank/null where not
Result: 8 rows. Grace and Mary appear once each with null order fields

Right Outer Join

Definition: keeps every row from the right (second) table, plus matches from the left table where they exist
Retained: all 7 orders, with customer details attached where available
Result: 7 rows; Order 106 appears with null customer fields

Full Outer Join

Definition: keeps every row from both tables, matched where possible, null where not
Retained: everything, matched rows, Grace, Mary, and Order 106
Result: 9 rows total

Left Anti-Join

Definition: keeps only rows from the left table that have no match in the right table
Retained: customers with zero orders
Result: 2 rows, just Grace and Mary

Right Anti-Join

Definition: keeps only rows from the right table that have no match in the left table
Retained: orders with no valid customer
Result: 1 row, Order 106

Power Query Joins vs. Power BI Relationships

A merge and a relationship both "connect" two tables, but they do fundamentally different things, at different stages of the workflow.

Does a Power Query merge physically combine data?

Yes. A merge creates a genuinely new, combined table, the columns from both tables physically sit together in one result, as I saw when merging Customers and Orders
This happens during data preparation, before anything is loaded into the model.

Does creating a relationship combine the tables?

No. A relationship leaves both tables completely separate, DimCustomer and FactSales remain two distinct tables in the model
The relationship just tells Power BI, "These two tables are linked by this key," so it can look up matching rows on demand when a report needs it.

At what stage does each happen?

Merge in Power Query before the data is loaded into the model
Relationship to the Data Model, after the data has already been loaded

When would you choose a merge instead of a relationship?

When you genuinely need one flat, combined table for a specific export or a tool that can't handle relationships
When a calculation is much simpler to express against one combined table than across a relationship
Generally: rarely, for a proper BI model, most reporting scenarios are better served by relationships

How can excessive merging affect the model?

Every merge creates a new flattened table, which reintroduces the same redundancy problem a star schema was designed to avoid, the same customer name or product category gets repeated across every row again
A model full of merged tables starts looking like several disconnected flat tables instead of one clean star schema, making it harder to maintain and slower to query as data grows

Why keep fact and dimension tables separate instead of merging them?

Smaller table sizes: DimCustomer stores each customer once, not once per sale
Cleaner DAX: measures can reference the fact table's numbers and let relationships handle the descriptive context
Easier maintenance: updating a customer's region means changing one row in DimCustomer, not thousands of rows in a merged mega-table

Practical example, side by side:

If I merged Customers into Orders, I'd get one wide table where "Alice Wambui, Nairobi" is repeated on both her orders (101, 104), creating exactly the redundancy problem from flat table explanation.
If I instead related the two tables, "Alice Wambui, Nairobi" is stored once in Customers, and both her orders in the fact table simply reference her CustomerID, no repetition, and Power BI resolves the connection only when a report actually needs it

Recommended Power BI Model

After working through data modelling, relationships, filter direction, and joins hands-on, my recommendation for a typical business intelligence project is a star schema, with single direction one-to-many relationships as the default. Here's the reasoning, broken down by the factors that actually matter in practice:

Query and report performance:

Power BI's engine (VertiPaq) is specifically optimized around the star schema shape.
Fewer relationship hops mean filters resolve faster, especially as data volume grows

####DAX simplicity:

Measures like SUM(FactSales[Revenue]) stay simple when the fact table holds only numbers and keys
Snowflake or flat structures force DAX to work around extra joins or repeated columns, adding unnecessary complexity to formulas

Model readability:

A star schema is visually intuitive in Model View, one glance shows what's a fact and what's a dimension
A flat table hides this structure entirely; a snowflake schema adds enough branching that it takes longer to read at a glance

Scalability:

Adding a new dimension (e.g. DimSalesRep) to a star schema is a single new table and one new relationship
A flat table would require adding and repeating a new column across every existing row instead

Data redundancy:

A star schema keeps each customer, product, or date stored once, referenced by key, this is the single biggest advantage over a flat table
Snowflake reduces redundancy even further, but for most business reporting needs, the star schema's redundancy is already low enough that the extra normalization isn't worth the added complexity

Maintainability:

Updating a customer's region means editing one row in DimCustomer, not hunting through thousands of repeated rows in a flat table
Fewer tables and simpler relationships (star) are easier to hand off to another analyst than a deeply normalized snowflake model

Ease of creating reports:

Star schema's clean dimension-to-fact structure means report builders can drag fields into a visual and get correct, predictable aggregations without needing to understand complex relationship paths

Filter propagation:

Single-direction filtering (dimension to fact) is predictable and matches how most business questions are asked, "show me sales by region," not "show me regions by sales."
I would only enable bidirectional filtering in a specific, deliberate case, like a genuine many-to-many relationship, never as a blanket default.

Model complexity:

Star schema hits the sweet spot between "too simple" (flat table, all redundancy) and "too complex" (snowflake, extra hops and relationships) for the vast majority of business reporting needs.

My default relationship design: one-to-many, single-direction, active relationships from each dimension table to the central fact table. I'd only introduce a snowflake normalization if a specific dimension were large and genuinely repetitive enough to justify it, and I'd only use bidirectional filtering or many-to-many relationships when a real business question required it never as a starting default.

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

asma Salah — Mon, 31 Aug 2026 16:26:27 +0000

When people hear "Excel," they usually picture something simple; rows, columns, maybe a few totals. What I didn't expect going into this week was how much of actual data analytics happens before any analysis even starts. Most of the work is just getting the data into a state where it can be trusted. This week was about learning that groundwork: how to move around a spreadsheet efficiently, how to keep data organized as it grows, and how to clean up the small inconsistencies that quietly break formulas and calculations later.

To make this practical instead of theoretical, I worked with a sample sales dataset, 40 order records with customer names, products, quantities, prices, dates, regions, and order status. It wasn't a clean dataset on purpose. Names were inconsistently capitalized, some had extra spaces, some prices were stored as text with currency labels attached, a few cells were empty, and two rows were accidental duplicates. In other words, it looked like real data, because real data is almost never clean the first time you see it.

Excel Basics

Before cleaning anything, I had to get comfortable with how Excel actually organizes information. A spreadsheet isn't just a table, it's a grid of individually addressable cells, where every cell has a coordinate (like B7), and that coordinate is what formulas and functions actually reference. That single idea is the foundation for everything else: sorting, filtering, formulas, formatting, all of it works because Excel knows exactly where each piece of data lives.

Rows are records, columns are fields. Each row in my dataset was one order; each column was one attribute of that order (Product, Quantity, Price, etc.). Keeping that structure consistent is what makes a spreadsheet usable for analysis instead of just a place to dump numbers.
Cell references let formulas move with your data. Referencing F2 in a formula, then dragging it down to F40, automatically adjusts to F3, F4, and so on, Excel handles the relative positioning for you.
Formatting. How a number is formatted (currency, date, plain number) affects how Excel treats it, not just how it looks.

Freezing Panes

With 40 rows of data, it doesn't take long before your headers scroll out of view, and once that happens, it's easy to misread which column is which. Freezing panes solves this by locking the header row and the first column in place while the rest of the sheet scrolls underneath it.

I did this through View → Freeze Panes → Freeze Top Row, and it immediately made the sheet easier to navigate — I could scroll through all 40 orders and still see exactly which column I was looking at.

Sorting and Filtering

Once the headers were locked in place, the next step was organizing them in a way that reveals patterns..

Sorting rearranges the entire dataset based on one or more columns. I sorted my sales data by Region first, using Data → Sort, which grouped all orders from the same region together instead of leaving them scattered in the order they were entered.

Filtering, on the other hand, doesn't rearrange anything,it temporarily hides rows that don't match a condition you set, so you can focus on a subset without losing the rest of the data. I applied a filter using Ctrl + Shift + L or HOME -> filter & sort, which added dropdown arrows to each header. From there, I filtered the Status column to show only "Cancelled" orders, which instantly narrowed 40 rows down to a handful, useful if I wanted to investigate cancellations specifically without deleting or reorganizing anything else.

Cleaning Text

Scrolling through the Customer Name column also exposed a different problem that the values weren't consistent. Some names were fully uppercase (ALICE WAMBUI), some had random casing (grace Njeri), and a few had extra spaces hiding inside them.

Two functions fixed this:

TRIM() removes extra spaces, leading, trailing, and repeated spaces between words. Leaving only single spaces between words. =TRIM(B2) cleaned up entries like " Samuel Mwangi".
PROPER() capitalizes the first letter of each word and lowercases the rest, standardizing casing. =PROPER(B2) turned "ALICE wambui" into "Alice Wambui".

I combined both in a single formula

=PROPER(TRIM(B2))

After applying this down the column, I copied the results and used Paste Special → Values to replace the original messy column with the cleaned, static text, otherwise the formulas would keep referencing the original (still-messy) column.

Removing Duplicate Rows

Two orders, 1006 and 1013, appeared twice each, with completely identical values across every column. Duplicate rows like this are especially misleading in a sales dataset because they don't just clutter the sheet, they distort any totals or counts calculated from it. A SUM() of the Total column, or a count of orders per region, would both come out inflated if duplicates aren't caught first.

Excel has a built-in tool for this rather than requiring a manual scan: Data → Remove Duplicates. Selecting the full dataset and running this tool opens a dialog where you choose which columns to check for duplication.

Basic Calculations, Formulas, and AutoSum

The Total column had been left empty on purpose, meant to be calculated as Quantity × Unit Price for each order. Rather than typing that out manually 40 times, I used a formula referencing the two relevant cells:

=E2*F2

I then dragged this formula down the entire column, and Excel automatically adjusted the cell references for each row (E3*F3, E4*F4, and so on), calculating every order's total in seconds.

For a grand total across all orders, I used AutoSum, one of Excel's most basic but genuinely time-saving tools. Selecting the cell directly below the last Total value and pressing Alt + = automatically inserted a SUM() formula covering the entire column, without me needing to type the range manually:

=SUM(G2:G40)

Basic arithmetic operators worked exactly as expected once the data was clean: + for addition, - for subtraction, * for multiplication, / for division.

Understanding the Git Workflow: Working Directory, Staging, Commit and Push

asma Salah — Thu, 27 Aug 2026 09:38:57 +0000

Working directory

This is the local project folder; you can create it in the Git terminal by using the following:

mkdir folderName command, for example, mkdir luxDev

switching to that folder, we use

cd folderName command.
To create a file in the folder, we use type nul > fileName

Then in the event of making changes, we use Git to track the changes.

git status command. This command will help us find the untracked changes in files.

Staging

Staging lets you choose what exactly goes into your next commit.

git add . is used to stage all changes while,
git add fileName if you want to save a single file or 2 to 3 files

Commit

This is where git records your staged changes to be permanent point in project history.

git commit -m "add folder setup and structured the first comment"
A commit doesn't affect your working directory or your staging area; rather, it takes a snapshot of what's currently staged and locks it into history

Git also gives you tools to actually see what's happening at each stage, which helped me trust the process instead of just following steps blindly.

git log shows the history of commits

This prints each commit's unique ID, author, date, and message

git diff shows exactly what changed in a file, line by line, before you commit it. This is what finally made the working directory.

git diff— shows changes in your working directory that are not yet staged
git diff --staged — shows changes that are staged, waiting to be committed

Push

Everything above happens locally, on your own machine. Push is the step where your commit history gets sent to a remote repository (like GitHub), so it's backed up and shareable.

git push origin main

This is the step that made Git "real" for me because before pushing, all my work exists only on my laptop. After pushing, it's on GitHub, visible to anyone I share the repo with, and safe even if my laptop dies tomorrow.

Each stage answer a different question

Working directory: What am I currently changing?
Staging: What do I want to include in my next save point?
Commit: What's now permanently recorded in history?
Push: What's now shared with others?