Data Modelling, Joins, Relationships & Types of Schemas in Power Bi.

Introduction

In todays data-driven business environment, organizations generate and store huge amounts of data from various sources. To transform this raw data into meaningful insights, it is important to structure it effectively.
Data modelling provides a framework for representing data, defining relationships between data elements and ensuring consistency and accuracy within analytical systems.
In Power Bi, data modelling plays a critical role in enabling efficient data analysis and reporting.

Data Modelling

What is data modelling?

Data modelling is the process of organizing and connecting data tables together for analysis, visualization and reporting.
It involves creating relationships between tables, creating measures and calculated columns and structuring data in a way that makes analysis and reporting accurate.
In power Bi, a good model typically:

• Uses dimension tables

• Enables fast filtering and aggregation

• Minimizes redundancy for perfomance and accuracy

• Separates data into fact tables

A strong data model is the foundation for accurate dashboards and scalable analytics.

Joins

Joins define how two tables are combined based on a related column.

Types of joins:

1. Inner Join -It returns only the matching rows that exist in both the right and left tables. If a value doesn't exist in both tables, the row is thus dropped.
   It is used when you only want records that are common in both tables.

2. Left Join - Returns all rows from the left table and matching rows from the rigt. Missing matches become null.

3. Right Join- Returns all rows from the right table and matches from left.

4. Full Outer Join - Returns all rows from both tables, matched where possible.

Relationships in Power Bi

Relationships in power Bi are the vital links that connect two or more data tables based on common columns (keys)
They dictate how filters propagate across the data model allowing you to slice and aggregate information from multiple tables into a single cohesive report without needing to merge them.

Types of relationships in Power Bi

1. One-to-Many(1:*)- Found when a primary table links to a secondary, larger dataset.
   -one customer:many orders

2. Many-to-One(*:1)- It connects a dimension table with unique values to a facts table containing many instances of the same value.
   -Its the most common and recommended relationship.

3. Many-to Many(:)- Used when both tables have repeating values.
   -Requires carefull handling as it can cause ambiquity.

4. One-to-One(1:1)- Used when a value in either table appears exactly once. It is relatively rare.

   Types of Schemas

   What is a schema in power Bi?
   A schema refers to the logical structure and organization of tables in a data model.
   Star Schema-
   A star schema is a foundational data modelling technique that organizes data into a central fact table sorrounded by dimension tables.
   Key components

• Fact table- its the central table in a data model. It contains numerical values that you want to anlyze and usually links to dimension tables through keys.

• Dimension tables - They are tables that contain descriptive information about the business entities in the tables.
  They provide contexts to the numbers stored in fact tables and are used for filtering, grouping and labeling data in reports.

Advantages

• One central fact table

• Multiple dimension tables

• Fast query perfomance

Snowflake Schema
It is a data modelling structure where dimension tables are normalized and split into multiple, related sub-tables to save data storage space and reduce redundancy.
Advantages

• Better data integrity

• Reduced storage

Disadvantages

• Slightly slower queries

• Requires more joins