DEV Community: Boaz Wangai

Data Modelling, Relationships & Joins

Boaz Wangai — Thu, 17 Sep 2026 04:52:42 +0000

Data Modelling in Power BI

Extracting reliable insights from a dataset requires a structured framework designed to guarantee accuracy, optimize performance, and streamline reporting. Developing this framework comprises the core process of data modeling.

A structured data model serves as the foundation for comprehensive analysis, driving success in crucial areas such as:

1. Scalability and Maintainability

A scalable data model facilitates the seamless integration of new data without requiring a structural rebuild. Because a well-designed model is inherently modular, it easily adapts when reporting requirements expand. When new metrics or data sources are introduced, new data can easily be added in new tables and relationships established with existing tables.

By structuring data into separate, logical tables, new data can be added in new tables and relationships established seamlessly. These updates automatically flow through the rest of the model as defined by the relationships. Eg. The addition of a new customer will only require the addition of all the customer details in the table set aside for that while the customer ID will be used in the other tables to represent the entire information.

2. Performance

Proper data modeling minimizes table width, eliminates redundant text columns from high-cardinality fact tables, leading to faster visual rendering and quicker dashboard interactions

3. DAX Calculations

DAX relies heavily on active relationship paths and filter propagation. A suitable data model ensures a predictable filter context, making it far easier to write formulas because users can mentally visualize how filters flow through the data.

4. Analytics

Choosing the right data model, with properly configured relationships, granularity and cardinality, drastically reduces logic errors to ensure all derived metrics yield accurate insights.

Data Modelling Approches in Power BI

Flat Tables

Flat table combines all attributes and transactional data into a single, unnormalized table. This structure introduces heavy data redundancy, increases model size, and makes writing clean, efficient DAX calculations significantly harder.

To keep the data model organized, clean, and efficient, datasets are separated into distinct fact and dimension tables. The logic behind how and why this data is split relies on:

Entity-Relationship: Descriptive attributes belonging to a specific subject (e.g., combining Customer_Name and Customer_City) are grouped into a dedicated dimension table. This ensures the data model is logically organized and meaningful.
Data Granularity: Granularity defines exactly what a single row represents. This dictates the lowest level of detail the fact table will store before connecting out to the dimensions.
Column Cardinality: Cardinality serves as the primary performance metric for the split. Columns with low cardinality (highly repetitive data) are moved into dimension tables to eliminate redundancy, while high-cardinality unique event identifiers remain in the fact table.

Fact tables are the tables in a schema that store quantitative data, while dimension tables store the descriptive data that gives context to the data in the fact tables. The combination and relationship between these fact and dimension tables make up the core architecture of a schema.

Characteristics of tables in schemas

Fact Tables

They hold quantitative numerical data which can be computed using DAX fnctions.
They have the foreign keys which are used as the connectors to the dimension tables.
Defines the grain of the data, which is the level of detail of the dataset. Eg. Each row holds a transaction of a single product to a specific client.
They are normally updated frequntly because they hold the data for recurring events.

Dimension Tables

They hold descriptive data which give context to the dataset in the fact tables.
They hold the primary keys which are the unique identifiers used to connect to the foreign keys in the fact tables.
The descriptive data rarely changes so these tables are not updated frequently.

The arrangement and relationship pattern between the fact table, dimension tables, and sub-dimension tables determine the type of schema:

A. Star Schema

In a star schema, all dimension tables are connected directly to a central fact table.

In the illustration, the customers, store and product dimension tables are connected to the central sales table, which is a fact table.

Advantages of a star schema

Intuitive and Easy to Navigate: Because there are no complex sub-tables, the model is easy for users to visualize.
Simple formulas are required for computations.
DAX performs better because it has less relationships to navigate.

Disadvantages of a star schema

Storing all data in a single flat table leads to disorganization, as it creates overwhelming visual clutter, mixes different levels of data granularity, and increases the risk of data entry inconsistencies.
Data Redundancy: Text values that would otherwise appear only once in a normalized snowflake schema are repeated across multiple rows.

In conclusion, the star schema is suited for use cases where the requirements are fast computation and easy interpretation and visualization.

B. Snowflake Schema

In a snowflake schema, the dimension tables are connected to a central fact table and the dimension tables themselves have subdimensional tables connecting to them.

Advantages of a snowflake schema

Allows storage of higly comprehensive detail with no repetition. For example, data for different mugs and umbrellas can be normalized further to have a separate table hold the category details, rather than repeating the details for each type of mug and each type of umbrella on the same table.
Data model maintainability is improved because updates can be made directly within their respective tables, rather than editing thousands of duplicate rows.

Disadvantages of a snowflake schema

Error propagation: If for instance an error was made in one sub dimension table, the error will affect the data to a larger extent as it will be passed on to the related tables and troubleshooting would be complex for such a case.
Complexity: the visual layout is more complex and therefore more difficult to visualize, and more complex formulas are required for computations.
When performing computations, DAX is slower because it has to navigate through complex relationships between the tables.

In conclusion:
The snowflake schema is ideal for managing massively complex and rigid data hierarchies, as it normalizes the data to completely avoid the repetition of entries.

Relationships in Power BI

Relationships refer to logical connections between tables that share a column with common data. They serve as bridges to enable Power BI to understand how the tables in a model relate to one another.

Characteristics and properties of relationships

1: Relationships are formed by linking the primary key of in the dimension table to the foreign key in the fact table. For example, sales transaction have customers IDs to represent the customer involved. This can be used to link to the table which holds the data about customers.

2: Cardinality of relationships: This refers to the numerical interaction between primary and foreign keys.

One-to-many: A single instance of a primary key connects to multiple instances of a foreign key. The use case for this is when the primary key in the dimension table corresponds to multiple instances of the foreign key in the fact table.

A unique primary key in one table links to a non-repeating foreign key in another table. This occurs when two tables share the same primary key structure or when an aggregated fact table contains exactly one record per foreign key.
Many-to-Many: Multiple instances of a foreign key connect to multiple instances of another foreign key, where both foreign keys represent the same underlying entity or attribute across both tables.The use case for this is when bridging two tables that both contain non-unique, repeating values, such as relating a fact table directly to another fact table at a shared grain.

3: Crossfilter direction

Single: Filters flow in one direction. Filtering the dimension table propagates to the fact table, but the inverse is not true. Eg, for two related tables, product table and sales table, if the product table is filtered to just display a single product, the sales table will also filter to show one product.

Both (Bi-directional): Filters flow in both directions simultaneously. Filtering the dimension table propagates to the fact table, and the inverse is also true. Although necessary for certain complex data models, bi-directional filtering can slow down performance and introduce ambiguity into your DAX calculations.

4: Active and inactive relationships

Power BI enforces a strict limit of one active relationship path between any two tables at a time. This constraint prevents ambiguous, conflicting filter arguments that would otherwise produce unpredictable calculation results in DAX. Any additional relationships established between those same tables are set to inactive but can be invoked using DAX.

Joins in Power Query

Joins in Power Query combine two separate tables horizontally into a single table based on matching columns.
The types of join is based on the rows that are retained:

Left outer join keeps all rows from the left table and only matching rows from the right table.

In this example, the join returns all the sales with the details of the corresponding clients who made the sales. The sale C5 returns nulls because it has no corresponding customer on the customers table.

Right outer join Keeps every row from the right/second table and matching rows from the first table.

In the example (same left and right tables as above), the join returns all the customers with their corresponding sales. Customer 4 has nulls because they have no corresponding purchases.

Full outer join keeps all the rows from both tables, regardless of matches.

This returns all the records from both tables and fills out nulls where clients have no corresponding sales and sales have no corresponding clients.

Inner join keeps only rows that have exact key matches in both tables.

The inner join returns all records which have their customer IDs in both tables. The sales that have no corresponding clients are removed and the clients with no corresponding clients are also removed.

Right anti join keeps only rows from the right table that have no match in the left table.

This returns the client Diana because they have no corresponding entry on the sales table.

Left anti join keeps only rows from the left table that have no match in the right table.

This returns the sale 104 because it has no corresponding customer (The customer ID C5 is not assigned to any client)

Conclusion

For a typical business intelligence project, the recommendation is to use a Star Schema.

Balanced Normalization: In a typical business scenario, the number of data categories is manageable. There is rarely a need to over-normalize and break tables down into multiple, deep hierarchies. Keeping a single tier of dimension tables is highly efficient and reduces unnecessary complexity.
Visual Clarity and DAX Simplicity: The star structure is easy to visualize. This visual clarity makes it straightforward to establish relationships, trace exactly how filters propagate from dimensions to facts, and write accurate DAX functions without getting lost in a web of tables.
Maintainability and Scalability: As the business grows, appending new data is simple. Because the separation between descriptive attributes and transactional metrics is straighforward, it is obvious where new data belongs, making long-term maintenance effortless.
Model Readability: Because typical business data is not overwhelmingly complex, the Star Schema ensures the model remains highly readable. It is sufficient to avoid the clutter of over-segmented tables.

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

Boaz Wangai — Mon, 24 Aug 2026 22:13:06 +0000

The Journey Begins

Like every journey, we need a trail, a compass, a couple of checkpoints and milestones, and a general sense of direction, and we need to know how to weave all these elements to move from the start to the destination. My journey is much the same, from a folder on my desktop to a repository on my GitHub. Join me.

Tools used

GitHub
Git Bash
Windows PowerShell
File explorer
Excel
VS Code

Workflow

A general overview of the checkpoints on our journey

Create a folder
Create a GitHub account
Create an SSH key
Start the SSH agent and add your key
Add the key to GitHub
Create a repository on GitHub
Link your folder

Creating a folder

To get to this checkpoint, I have 2 alternative paths that we can use. The general sense of direction arriving at a new folder created in a path of your choice.

Using file explorer

Navigate to the desired location via the file explorer and create a new folder.

Using Git Bash

On Git Bash, use the cd command and a path to navigate to the desired location.
Use the mkdir command to create a folder in that location.

Creating required files and subfolders

Inside your main folder, you can create files and folders that you will need for your project.

You may need to create a README file to document the description pf your project. For example what you're trying to achieve and the tools used in the project. Use the touch command for this.
To create a subfolder, use the mkdir command while inside the main folder.

Creating a GitHub Account

Navigate to the GitHub website. You have the option of signing up with email or you can continue with Google.

Creating an SSH Key

Your SSH key authenticates you to GitHub to ensure that you are the only one allowed to make pushes, clone, fetch, etc.

Open PowerShell and run as administrator. Check the version of Git installed or install if necessary.
Open Git Bash and generate SSH key and SSH passphrase. Use the command ssh-keygen -t ed25519 -C "your_email@example.com"
On running the command, set a location to store it or accept the default if it is your first key, and the set a passphrase.

Adding the SSH Key to the SSH Client

Run Windows PowerShell as administrator.
Run Get-Service -Name ssh-agent | Set-Service -StartupType Manual
Then Start-Service ssh-agent\ to start the agent.
Close the administrator PowerShell and open a normal PowerShell window.
Run the command ssh-add c:/Users/YOUR_USERNAME/.ssh/id_ed25519. Replace YOUR_USERNAME with your actual Windows username.

To finalize adding your SSH key to your GitHub

Copy the public key by running below command on Git Bash cat ~/.ssh/id_ed25519.pub | clip
Or display it by running cat ~/.ssh/id_ed25519.pub and then manually copy it.

Adding the SSH Key to GitHub

Navigate to New SSH Key or Add SSH Key on your GitHub profile.
In the SSH Key form, give it a title, set the Key type to Authentication Key and paste the key copied above, and click Add SSH Key

Creating a Repository on GitHub

Log into GitHub and click on **Add New Repository **
Fill in the name, description, and set it to either public or private
Create repository
Copy the SSH URL. This will be used to connect your local folder.

Linking Local Folder to GitHub

Using Git Bash

Navigate to the project folder using cd command (main folder).
Run git init to initialize Git. This means that GitHub will be tracking the changes in the folder.
Run git remote add origin [SSH_URL] to connect the local folder to the Git repository. The SSH URL is the link to the repository created in GitHub.
Run git add . to stage. The dot indicates that staging should be done in the specific directory we're in.
Run git commit -m[commit message] to set your commit message.
Run git branch -M main to rename the current branch as main.
Run git push -u origin main to push to GitHub.

Conclusion

There are different methods to achieve the same thing for different hurdles, much like taking different routes between successive check points. The important thing is to consider what you want to achieve, the tools to use, and the commands to run.