DEV Community: Nyamatari Brian

# Understanding Data Modeling in PowerBI: Joins, Relationship and Schemas.

Nyamatari Brian — Sun, 05 Apr 2026 08:01:39 +0000

Introduction.

Data Modeling is a good backbone of making Data Science and Analytics in PowerBI very successful. We are going to look into:

Data modeling fundamentals.
SQL Joins.
PowerBI relationships.
Facts vs deimension tables.
Data schema.

The big Question; WHAT is data modeling.

Well Data modeling is the process of organizing and structuring data from multiple sources into a logical format for analysis.

Here it is all about:

The Table connection.
Defining relationships.
Structuring data into facts and dimension tables.
Optimizing performance.

Just imagine all this like making your data easy and smooth to read and interpret.

SQL joins.

This is basically where we use joins to make data jointed from multiple tables using a common column.

1. INNER JOIN.

This only returns matching records in both tables.

2. LEFT JOIN.

Here in the records are all returned from left side of the table and matching the records from the right.

3. RIGHT JOIN.

This is basically the vice versa of LEFT JOIN, the records from the right table and matching ones from the left.

4. FULL OUTER JOIN.

This returns all records from both tables whether matched or unmatched.

5. LEFT ANTI JOIN.

The returning record in the table that do not exist in the right.

6. RIGHT ANTI JOIN.

The returns records in the right table that do not exist in the left.

Steps in the creation of join in PowerBI.

Open PowerBi desktop.
Select the datasets you want to use.
Click on transform data.
Select a table.
Click on merge queries.
Choose the second table.
Select on matching columns.
Choose the join type of your choice.
Click OK and expand the columns

PowerBI Relationships.

The datasets can connects to each other without ever merging them.

Types of Relationships

1. One to many. (1:M)

Here is where a table from dataset A relates to many tables in dataset B. Example; A customer can make many orders.

2. One to One.(1:1)

In this relationship the table matches exactly one in another table.

3. Many to Many.(M:M)

Here both tables from datasets contains duplicate values.

Key concepts.

Cardinality.

This is where tables are defined by how they relate;

One-to-many.
Many-to-many.
One-to-one.

Cross filter direction.

In this we get to understand;

Both directions and allows two way filtering but may cause ambiguty.
Single direction.

Active and inactive relationships.

In this relationship the active is usually the default and indirect usually requires the DAX.(User relationship)

Facts vs Dimension Tables.

Fact Table.

This is where it contains data that can be measured.
Example: Sales Amount.

Dimension Table.

Here the data is descriptive.
Example: Name of a customer.

DATA MODELLING SCHEMAS.

They are;

1. Star Schema.

Structure:

One Central fact table.
Multiple dimension tables.

2. Snowflake Schema.

They usually normalize structures where dimensions are split.
It has an advantage of saving storage.

3. Flat Table.

In this schema all the data is stored in one table.
It is very simple but also has poor performance on large datasets.

CONCLUSION.

Data modelling in PowerBI is essential for building accurate and high-performing dashboards. By understanding joins, relationships, schemas and table structures you can create models that are both efficient and scalable.

How Excel is Used in Real-World Data Analysis.

Nyamatari Brian — Sun, 29 Mar 2026 03:51:15 +0000

How Excel is Used in Real-World Data Analysis

INTRODUCTION.

In the current word Data is becoming daily talk in every business and it is becoming today's world currency. One of the tools used in the data world is Microsoft Excel. It is widely used in business, hospitals and finacial for analytics. The data analytics are used to make the organizational decision making and strategic moves.

What is Excel.

Microsoft Excel is a tool used for organizing, analyzing, and visualizing data. In Excel they use a simple interface of rows and columns for storage, creating presentation dashboards and calculate the datasets.

Eventhough there are other tools for similar work like powerBi and python, Excel still remains to be the easiest hence more people use it more oftenly.

Real world scenarios where we use Excel.

Business Analysis.

In Businesses they collect data for example they can collectd how many items have been bought today or how many products are used oftenly. As a data scientist you can use this this to keep track of sales and also check for trends i your business.In the business they can track:

Keep daily, weekly and monthly sales.
The data can be used to compare which products are more used to save on cost.

This data will be evaluated by the business team which will help them make good decisions for the business.

Financial management.

Most financial analysis people use Excel for keeping, budgeting and creating financial reporting. In organizations they can:

Create and keep track of expenses.
Forecasting future income and cost.
Create budgets.

cial analysis people use Excel for keeping, budgeting and creating financial reporting. In organizations they can:

Create and keep track of expenses.
Forecasting future income and cost.
Create budgets.

Data cleansing.

Sometimes it can be human error to duplicate data. Here one needs to clean same duplicate data to ensure accurate analyzation. Excel can be used by:

Helps in removing the duplicates in the data set.
Helps in handling the values that are missing.
Helps in sorting out and filtration of the data set.

Excel data analyzing formulas.

We use various formulas in analyzing data properly they include:

1. Logical Formulas.

This are are used to compare values and return a result of either TRUE or FALSE.

They include:

Operator	Meaning	Example	Result
`=`	Equal to	`=A1 = B1`	TRUE if equal
`<>`	Not equal to	`=A1 <> B1`	TRUE if not equal
`>`	Greater than	`=A1 > B1`	TRUE if A1 is bigger
`<`	Less than	`=A1 < B1`	TRUE if A1 is smaller
`>=`	Greater than or equal to	`=A1 >= B1`	TRUE if A1 ≥ B1
`<=`	Less than or equal to	`=A1 <= B1`	TRUE if A1 ≤ B1

2. Arithmetic.

This are the day to day formula.
they include;

Additional.
Subtraction.
Multiplication.
Division.

3. Text.

This are used in words in Excel.
They include;

=CONCAT(Join text)
=LEFT (Get first characters)
=UPPER(Convert to capital letters)
=LOWER(Convert to small letters)

4. Statistical.

In Excel they used for analyzing data.
They include;

=AVERAGE
=COUNT
=MAX
=MIN

5. Lookup.

They used to find something in excel.
They include;

=VLOOKUP
=XLOOKUP

6. Date and time.

They are used for time and date in excel.
They include;

=TODAY()
=NOW()
=YEAR()

Conclusion.

Microsft Excel has an advantage since it makes easy use of combined and powerful featrures. It is so seemless thata beginner can very fast learn and understand the basic functions and progress to also understand pivotTables and dashboarding.

Learning Excel is a skill that is helpful in the daily world that helps in decision making in most areas. Learning how to use Excel effectively is an important step for anyone looking to work with data confidently.

Tools for data science and analytics installation process.

Nyamatari Brian — Tue, 17 Mar 2026 16:32:51 +0000

Introduction.

Before we begin with the tools we need to ask the bigger question who is a data analyst and a data scientist. First, Data analyst; they beg to answer the question WHAT HAPPENED. Second, Data Scientist; they beg to answer the question PREDICTION AND PRESCRIPTION. In the current world data is flowing all over us day by day hence.

How to install MYSQL and PostgreSQL.

Introduction.

MYSQL is just but an open-source database. It is a relational database system where one can manage, store and extract data that is structured. PostgreSQL is an advanced database system that is used for large and high performance applications which stores data.

Steps for Installation.

Step 1: Downloading MYSQL.

Go to your web browser and search https://www.mysql.com/downloads/
Click on download now and download according to your laptop operating system.

Step 2: Installation of MYSQL.

Open the downloaded file.
Once opened choose "my server only".
Set the port number to default.
Create a root password then finish the configuration.

Postgre.

Step 1: Download PostgreSql.

Go to your browser https://www.postgresql.org/download/
Choose your Operating system.
After downloading open it then choose components for installation then click on next to continue.

Step 2: Configuration of PostgreSQL.

One will be asked to configure some essential settings which are:
You will be required to set a password.
You will be asked to input aport number leave it at default which is '5432'.
Leave the installation at locale default settings then finish installation.

Installation of Excel and PowerBi.

Excel.

Step 1: Installation.

Go to Microsoft store on windows and look for excel then download it.
After finishing the download open it and ensure it is working properly.

PowerBi.

Step 1: Installation.

Go to Microsoft store on windows and look for PowerBi then download it.
After the download is over open it and ensure it is working properly.

What about for other users that is MacBook and Linux.

One can use a Virtual machine to access this tools.

### Step 1: VirtualBox.

Install VirtualBox and select for MacBook or Linux.
Follow the installation instructions and the VirtualBox will be fully installed into your machine.
Download windows ISO file and save in you laptop.

Step 2: Create a virtual machine.

Open the virtualbox and then click on new.
You will be required to fill;
Name of the virtual machine.
The Type.
Version of the windows.

Step 3: Allocate virtual machine some memory.

Choose how much RAM to give the virtual machine.

Step 4: Create the hard disk of the VM.

Click on create a virtual hard disk now then click next.
Choose Virtualbox Disk image.
Choose the dynamically allocated then set size to 50GB.

Step 5: Install windows.

Select the virtualmachine you created then click on start.
You shall be prompted then select the windows ISO file that we downloaded and folow the normal windows installation steps. 3.The windows will open inside the virtual machine.

Step 6: Installation Excell and PowerBI.

Inside the windowss viryual machine go to Microssoft Store and download both PowerBI and excel.

Step 7:Launch the application.

Inside the Windows virtual machine you can now open;
PoweBI.
Excel.