DEV Community: Joy Kipkogei

Power BI Technical Article : Data Modelling, Relationships & Joins

Joy Kipkogei — Wed, 30 Sep 2026 12:33:56 +0000

Introduction

In today's data-driven business environment, organizations collect large amounts of information from sales systems, customer databases, financial systems, spreadsheets, and operational applications. Having lots of data is not enough, though. Organizations need to turn it into meaningful information that supports better decisions.

Power BI is a business intelligence and data visualization platform developed by Microsoft. It lets you connect to different data sources, clean and transform data, build data models, create calculations, and present insights through interactive reports and dashboards.
Power BI can connect to Excel workbooks, CSV files, databases, cloud services, and other business applications.

Power Query is then used to clean and transform the data before it is loaded into the Power BI data model.

Transforming data and building visuals are only part of the process. How the data is structured in the model is just as important.

Why Is Data Modelling Important?

Data modelling is the process of organizing tables, defining relationships between them, and creating a structure that allows data to be analyzed correctly. A good model matters because of:

Reporting: users can easily slice and filter data using dimensions.
DAX calculations: measures become easier to write and understand.
Performance: a good model reduces unnecessary duplication and supports efficient storage and querying.
Scalability: the model can accommodate growing transaction volumes and additional dimensions.
Maintainability: other developers and analysts can understand the model more easily.

1. Flat Tables, Star Schemas and Snowflake Schemas

1.1 Flat Tables

A flat table stores information from different business entities together in a single table. Instead of separate tables for customers, products, dates, and sales, all the relevant information is stored as columns in one table.

Structure of a flat table

Advantages and disadvantages of a flat table

Advantages	Disadvantages
Simple to understand and build	Repeats descriptive values on every row
No relationships to manage	Wide tables with many high-cardinality columns
Fast to prototype	Slower refresh and harder maintenance as data grows
Fine for small datasets	Hard to share dimensions across multiple fact tables

Appropriate use:

Small, simple datasets
One-off or ad hoc analysis
Datasets with no repeating relationships
Prototypes or personal dashboards
Data that already arrives flat

1.2 Star Schema

A star schema is a modelling pattern that organizes data into one central fact table (the transactions: sales, orders, events) surrounded by several dimension tables (the descriptive attributes: who, what, where, when). It is the recommended pattern for Power BI models.

Fact table (center): contains quantitative data.
Dimension tables (points): contain descriptive attributes.

Structure of a star schema

Advantages and disadvantages of a star schema

Advantages	Disadvantages
Simple, intuitive structure	Requires upfront design effort
Fast, predictable queries	Needs data preparation to split source data into facts and dimensions
Easy to write DAX against	Some denormalization inside dimensions
Shared dimensions across reports	Not needed for tiny, one-off datasets

Appropriate use of a star schema:

BI analysts and report developers building Power BI, Tableau, or other dashboards on recurring, growing datasets.
Data warehouse and data engineering teams designing the backend structure that feeds reporting tools.
Organizations with multiple related datasets (sales, inventory, finance) that want shared, consistent dimensions across reports.

1.3 Snowflake Schema

A snowflake schema takes the star schema one step further. Instead of stopping at a single table per dimension, the dimensions themselves are normalized into sub-tables (for example, Product → Subcategory → Category).

Structure of a snowflake schema

Advantages and disadvantages of a snowflake schema

2. Fact Tables and Dimension Tables

Fact table: contains quantitative, measurable data (for example, revenue and quantity sold) alongside foreign keys that link to the dimension tables.
Dimension tables: contain descriptive attributes or context about the facts (for example, customer names, product categories, store locations).

Numeric columns vs. descriptive attributes

Numeric fact columns such as Quantity and Sales Amount are additive: they can be summed, averaged, or compared over time. In Power BI you aggregate them with DAX measures such as Total Sales = SUM(Sales[Sales Amount]).

Descriptive attributes (Customer Name, Product Name, Category, Location, Date) are not aggregated. They are used to filter, group, or label the numbers. This is why numeric values live in the fact table and descriptive attributes live in dimension tables.

Grain

Grain defines exactly what one row in the fact table represents. For example, "one row per order line" or "one row per product per store per day." Decide the grain first, because every column in the fact table must be true at that level of detail.

3. Relationships in Power BI

A relationship is established by matching the primary key in a dimension table to the corresponding foreign key in the fact table. Power BI stores data in separate tables, so without a relationship those tables have no way of knowing how they relate.

Key terms

Cardinality: describes how rows in one table relate to rows in another, based on the count of matching rows.
Primary key: a column that uniquely identifies each row in a table.
Foreign key: a column that references the primary key of another table.

Relationship cardinalities

One-to-many (1:*): a single row in the "one" table matches many rows in the "many" table. Example: one Customer → many Sales rows.
One-to-one (1:1): each row in Table A links to exactly one row in Table B, and vice versa. Both sides need unique keys.
Many-to-many (*:*): rows in Table A can match multiple rows in Table B and vice versa, because neither side has unique values in the key column.

4. Filter Direction

Single-direction filtering

Filters flow only from the "one" side of the relationship (usually the dimension table) to the "many" side (usually the fact table). Filters do not flow in the opposite direction.

Example: selecting a Category in a slicer filters the Sales table, but selecting a Sales row does not filter the Category table.

Both (bidirectional) filtering

The filter direction is set to Both, so filters travel from the dimension to the fact table and from the fact table back to the dimension.

Advantages

More flexible filtering across related tables.
Useful in some complex reporting scenarios.

Considerations

Can create ambiguous filter paths.
May cause unintended cross-filtering between tables.
Can make the model more complex and hurt performance.
For many-to-many scenarios, a bridge table with single-direction relationships is usually a safer solution than bidirectional filtering.

5. Joins in Power Query

A join combines two tables by matching values in key columns. In the Power Query interface this operation is called Merge Queries. The join type decides which rows are kept.

Inner Join

Keeps only records that have matching values in both tables.

Left Outer Join

Keeps all rows from the left (first) table and only the matching rows from the right (second) table. Unmatched left rows get nulls in the columns from the right table.

Right Outer Join

Keeps all rows from the right (second) table and only the matching rows from the left (first) table.

Full Outer Join

Keeps all records from both tables, whether or not a match exists.

Left Anti Join

Keeps only the rows from the left table that have no match in the right table.

Right Anti Join

Keeps only the rows from the right table that have no match in the left table.

Both Power Query joins and Power BI relationships connect data from multiple tables, but they serve different purposes and happen at different stages of the workflow.

6. Power Query Joins vs Power BI Relationships

What is a Power Query merge?

A merge combines data from two or more tables during the data preparation stage, before the data is loaded into the model. Power Query physically brings columns from one table into another based on matching values.

Sales table

SalesID	ProductID	Quantity
1001	P01	5
1002	P02	3

Products table

ProductID	ProductName
P01	Laptop
P02	Mouse

After a Left Outer Join:

SalesID	ProductID	Quantity	ProductName
1001	P01	5	Laptop
1002	P02	3	Mouse

Does a merge physically combine data? Yes. It copies columns from one table into another.

What is a Power BI relationship?

A relationship connects tables within the model without physically combining them. The tables remain separate, and Power BI uses the relationship to let filters and calculations flow between them during analysis.

Does creating a relationship combine tables? No. Relationships do not copy, append, or merge data. They only define how tables interact.

Where each one happens in the workflow

Data Sources
     ↓
Power Query        ← merges happen here
     ↓
Load Data
     ↓
Data Model         ← relationships are created here
     ↓
Reports

When to use a merge

You need additional columns in a single table.
You are cleansing or enriching data.
You are working with small datasets.
You are preparing staging tables before modelling.

When to use a relationship

You are building a star schema.
You are working with large datasets.
You are creating scalable BI solutions.
Multiple fact tables need to share common dimensions.

How excessive merging affects a data model

Excessive merging tends to produce one large flat table full of repeated values such as Customer Name, Product Name, Region, and Category. This can lead to:

Increased model size and memory consumption
Wider tables with many high-cardinality columns
Slower refresh times
Harder maintenance
Ambiguity when several fact tables need the same dimensions

Why keep fact and dimension tables separate?

Reduced redundancy: dimension information is stored once instead of thousands of times.
Better performance: smaller dimension tables filter faster and keep the model lean.
Easier DAX: measures work against clean fact columns, and slicers use clean dimension columns.
Improved scalability: you can add new fact and dimension tables without redesigning the model.
Better readability: developers can clearly see what is being measured (facts) and how it is categorized (dimensions).

Practical business example

Merge approach: a company merges Sales, Customers, Products, and Regions into a single table of one million rows. Every row repeats customer, product, and region details.

Result: larger model, more redundancy, lower performance.

Relationship approach: separate tables are kept and connected by relationships.

Fact Sales

Customer Key	Product Key	Region Key	Date Key	Sales Amount
1	10	100	20260101	250

Dim Customer

Customer Key	Customer Name
1	Amina Otieno

Dim Product

Product Key	Product Name
10	Laptop

Dim Region

Region Key	Region
100	Nairobi

Dim Date

Date Key	Date
20260101	2026-01-01

Result: smaller model, better performance, easier maintenance, greater scalability.

7. Recommended Power BI Model

For most business intelligence projects, I recommend a star schema with one-to-many relationships and single-direction filtering, based on performance, maintainability, scalability, and ease of report development.

Why not a flat table?

Flat tables are easy to understand, but they become hard to maintain as datasets grow:

High data redundancy
Larger model sizes
Slower refresh
Poor scalability
Difficult maintenance

They suit small prototypes but are generally not ideal for enterprise BI solutions.

Why not a snowflake schema?

Snowflaking saves a little storage but adds more tables, more relationships, and longer filter paths. In Power BI, dimension tables are usually small, so the storage saving is minor. The cost is a model that is harder to navigate and slower to query. Unless a source system forces it, flatten sub-dimensions into a single dimension table (the Product table carries Subcategory and Category).

Best practices to go with the star schema

Use a dedicated Date table and mark it as a date table.
Hide foreign keys in the fact table so report authors use dimension columns.
Use role-playing dimensions when one dimension plays several roles (for example, Order Date and Ship Date both link to Dim Date).
Understand active vs inactive relationships: only one relationship between two tables can be active. Use USERELATIONSHIP() in DAX to activate an inactive one.
Prefer single-direction filtering and avoid bidirectional filters unless truly necessary.

Conclusion and Key Takeaways

Data modelling is as important as data transformation and visualization.
Flat tables are fine for quick prototypes but do not scale.
Star schemas are the recommended pattern for Power BI: fast, simple, and easy to maintain.
Snowflake schemas add complexity that rarely pays off in Power BI.
Use Power Query merges for data preparation and relationships for modelling.
Keep facts and dimensions separate, define the grain, and use one-to-many relationships with single-direction filters.

A well-built model makes every report, measure, and refresh easier for you and for the next person who inherits it.

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

Joy Kipkogei — Sun, 23 Aug 2026 05:10:16 +0000

As someone who works with reports and data, with completely zero experience in data analytics and data science, Git and GitHub initially felt unfamiliar to me. I was used to working with documents, spreadsheets, reports, and data, but I had never used version control or the command line before.

As I started learning Git and GitHub, I realized that these tools can help me organize, track, and manage data-related projects. I decided to put what I learned into practice by creating a simple personal project and taking it from a local folder on my computer to GitHub using Git and SSH.

Rather than just documenting the commands, this article shares what I actually did, what each step means, the challenges I encountered, and what I learned along the way.

Introduction to Git and Git Hub
Git - A version control system that tracks changes to files and keeps a history of those changes as projects develop.
GitHub- A cloud-based platform that hosts Git repositories and allows people to store, manage, share, and collaborate on projects.
_Comparison of the two : _
Git is the tool that tracks changes in your project, while GitHub is the online platform where you can store and share that project

Step 1: Installing the Required Tools and Applications
Before starting my project, I first installed and prepared the tools required to work with Git and GitHub.
The main tools I needed were Git, Git Bash, Visual Studio Code (VS Code), and a GitHub account
Step 2: Setting Up Git and Creating an SSH Key
After installing Git, I opened Git Bash, which provides a command-line interface for interacting with Git and navigating files on my computer.
I then configured my Git identity using my name and email address.
After configuring Git, the next step was to connect my local computer to GitHub. I generated an SSH key using Git Bash and then added my public key to my GitHub account. This allowed me to establish a secure connection between my local Git environment and GitHub.
Step 3: Creating a project folder
I created a folder by running this commands:
Run pwd- to confirm location
Run ls - to see folders available
Run cd "Desktop"- the chosen location to create the project folder
Run ls - to check content of the Desktop folder
Run mkdir my-data-project - to create folder
Step 4: Create files inside the project folder
I created three subfolders to organize my project: Data, Scripst and notebooks
run mkdir data
run mkdir scripts
run mkdir notebook
run touch README.md
Step 5: Add content to the file folders
For README file
run code README.md
This opened the file in VS Code, where I added information about the project, its purpose, and the project structure.
For data file
I copied the data received in class and pasted in the folder
then run ls of the data folder to see the data file uploaded
Step 6: Add and Commit Files
Check Git status:
run git status
Stage all project files:
rungit add .
Create the first commit:
run git commit -m "Initial project setup"
Verify:
run git status
Step 7: Creating a a GitHub Repository
Open GitHub
Create a newrepository and name it Kenya-Hospital-Records-2
Copy the ssh code generated and run it on Gitbash
Check the connection by running git remote -v
Run git branch -M main - to rename branch to main
Run git push -u origin main to push the project to GitHub
The git push command uploads my local commits to the remote repository. origin refers to my GitHub repository, while main is the branch I am pushing.
The project was added successfuly:

Challenges I encountered as a first time using Git and Gitbash
• Navigating folders using Git Bash.
• Understanding Git commands.
• Setting up SSH authentication.
• Connecting the local repository to GitHub.
_ What I learned _
This practical exercise helped me understand how Git and GitHub work together. I learned how to create a local project, organize files, initialize a Git repository, stage and commit changes, create an SSH connection, connect my local repository to GitHub, and push my project online.
More importantly, I learned that Git is not just about memorizing commands. Understanding what each command does makes it easier to troubleshoot errors and manage projects effectively. As I continue learning data analytics and data science, I can see how Git and GitHub will help me organize my projects and track my work over time.