DEV Community: WaithakaJoseph

Article on Modelling, Joins, Relationships and Different Schemas In Power BI

WaithakaJoseph — Tue, 30 Jun 2026 21:47:03 +0000

Data Modeling, Relationships, and Schemas in Data Analytics

In the fields of data analytics, data warehousing, and database management, modeling and schema design are the fundamental pillars used to organize and query information efficiently.

This article provides a comprehensive guide to these core concepts.

1. Data Modeling

Data modeling is the architectural process of designing how data is stored, interconnected, and accessed within a system.

Core Questions Addressed:

Storage: What specific data points need to be captured?
Structure: How should individual tables be organized?
Connectivity: How do these tables interact with one another?

Levels of Data Models:

Conceptual Model: A high-level business perspective focusing on entities and their relationships, devoid of technical specifications.
Logical Model: Defines specific attributes, keys, and relationships. It is independent of the Database Management System (DBMS).
Physical Model: The actual implementation within a database, including technical details like indexes, partitions, and storage requirements.

2. Relationships

Relationships define the logic of how data in one table corresponds to data in another.

One-to-One (1:1): A single record in Table A relates to exactly one record in Table B.
One-to-Many (1:M): The most common relationship; for example, one Customer can place many Orders.
Many-to-Many (M:M): Multiple records in one table relate to multiple records in another. This requires a Junction Table (Bridge Table) to function. Example: One Student can enroll in many Courses, and one Course contains many Students.

3. SQL Joins

Joins are used to combine rows from two or more tables based on a related column.

Join Type	Description
Inner Join	Returns only the records that have matching values in both tables.
Left Join	Returns all records from the left table and the matched records from the right.
Right Join	Returns all records from the right table and the matched records from the left.
Full Outer Join	Returns all records when there is a match in either the left or right table.
Cross Join	Returns the Cartesian product (every possible combination) of the two tables.
Self Join	A regular join in which a table is joined with itself.

4. Database Schemas

A schema is the logical configuration that defines how tables are organized and managed.

A. Star Schema

The standard for data warehousing and Power BI. It features a central Fact Table (quantitative data) surrounded by Dimension Tables (descriptive data).

Visual Representation:

          Dim_Customer
                |
Dim_Product -- Fact_Sales -- Dim_Date
                |
          Dim_Store

Pros: Simple to understand, high query performance, optimized for reporting.
Cons: Includes some data redundancy (denormalized).

B. Snowflake Schema

An extension of the star schema where dimension tables are normalized (split into additional related tables).

Visual Representation:

          Country_Table
               |
          Dim_Customer
               |
Dim_Product -- Fact_Sales -- Dim_Date

Pros: Reduced data redundancy and improved data integrity.
Cons: Requires more complex queries and more joins, which can impact performance.

C. Galaxy Schema (Fact Constellation)

A complex design where multiple fact tables share the same dimension tables.

Structure: Multiple Fact tables (e.g., Sales and Returns) linked to shared dimensions (e.g., Date and Product).
Pros: Supports complex business processes and enterprise-level analytics.
Cons: Highly difficult to design and maintain.

5. Integrity Keys

Keys are the unique identifiers that maintain the relationship between tables.

Primary Key (PK): A unique identifier for a specific row within its own table. It cannot contain null values.
Foreign Key (FK): A column in one table that points to the Primary Key of another table, creating a link between the two.

6. Normalization vs. Denormalization

Normalization: The process of organizing data to minimize redundancy (used in OLTP - transactional systems like banking apps).
Denormalization: The process of combining tables to speed up data retrieval (used in OLAP - analytical systems like Power BI or Tableau dashboards).

In Conclusion

Identify Entities: What are the "things" you are tracking? (People, Places, Transactions).
Define Keys: Every table needs a Primary Key (unique ID). Connect tables using Foreign Keys.
Choose your Schema: Use Star Schema for most reporting needs. Use Normalization if you are building an app that handles many live transactions.
Optimize Joins: Always join on indexed columns (like IDs) to ensure your queries don't slow down as your data grows.

How Excel is Used in Real-World Data Analysis: Excel Is More Than Just a Spreadsheet

WaithakaJoseph — Wed, 25 Mar 2026 18:28:24 +0000

In the world of data, tools like Python, Power BI and Tableau often steal the spotlight. However, there is one veteran tool that remains the undisputed backbone of business operations globally: Microsoft Excel.

While many view Excel as just a tool for making lists, it is actually a data analysis engine. In this article, I will explore how Excel is applied in the real world and share some key features that turn raw numbers into actionable intel.

What is Microsoft Excel?

At its core, Microsoft Excel is a spreadsheet application developed by Microsoft that uses a grid of cells to organize, manipulate and calculate data. Its power lies in its versatility. It can handle everything from a simple grocery list to complex financial models used by corporations worldwide.

Excel in the Real World: Practical Scenarios

In the professional world, data is rarely clean or easy to understand. Excel acts as the "Swiss Army Knife" for professionals across various industries:

Finance and Accounting: Finance & Accounting teams run on Excel. Analysts use it to forecast company growth, build budget models and calculate profit margins.
Marketing and Sales: Marketing teams use Excel to track campaign performance, calculate Return on Investment (ROI) and segment customers based on purchasing behavior.
Human Resources: HR departments use Excel to manage employee payroll, track attendance and analyze diversity and inclusion metrics within an organization.
Logistics and Supply Chain: Companies use it to track inventory levels, manage shipping schedules and optimize warehouse space.

Excel Power Features: Turning Data into Information

During my journey learning Excel, I discovered that the true magic happens when you stop entering data and start analyzing it. Here are a few features that are game-changers in real-world analysis:

1. Pivot Tables

Pivot Tables are perhaps the most powerful feature in Excel. They allow you to take thousands of rows of data and summarize them in seconds.

Application: If you have a year’s worth of sales data, a Pivot Table can instantly show you which product sold the most in the month of July without writing a single formula.

2. XLOOKUP (and VLOOKUP)

Data is often scattered across different sheets. The XLOOKUP formula allows you to merge this data seamlessly.

Application: Imagine you have a list of Product IDs in one sheet and a Price List in another. Using XLOOKUP, you can automatically "look up" the price for each ID, saving hours of manual searching.

3. IF Statements

Logic is central to data analysis. The IF function allows you to categorize data based on specific conditions.

Formula Example: =IF(B2>1000, "High Value", "Low Value")
Application: A manager can use this to instantly flag "High Value" orders that need special attention.

4. Conditional Formatting

Data analysis is as much about visualization as it is about math. Conditional formatting allows you to highlight cells based on their values.

Application: You can set a rule to turn all cells red if a project is over budget, allowing a stakeholder to spot a problem the moment they open the file.

Personal Reflection: A New Way of Seeing

Learning Excel has fundamentally changed the way I understand data. Before, I saw data as a static, intimidating mountain of numbers. I used to think that "analysis" was something only mathematicians and accountants did.

Now, I interpret data as a story waiting to be told. Excel has taught me that with the right formula and a clear structure, you can strip away the noise and find the "Why" behind the numbers. It has shifted my mindset from being a passive observer of information to an active problem-solver. Whether I’m looking at personal finances or professional KPIs, I now look for patterns, trends and outliers that I would have been blind to before.