Modelling, Joins, Relationships and different Schemas

Data Modelling is the invisible architecture that structures your data so it is fast accurate and scalable.

It is the process of identifying, organizing and defining the types of data a business collects and the relationship between them.

Joins

1.Left OuterJoin

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

If there is no match,it returns null for the right table's column.

2.Right Outer Join

It returns all the rows from the right table that are specified in the right outer join clause, not just the rows in which the columns match.

3.Inner Join

Also known as a simple join, returns rows from joined tables that have matching rows.

4.Full Outer Join

It returns all joined rows from both tables, plus one row for each unmatched left row(extended with nulls on the right), plus one row for each unmatched right row(extended with nulls on the left).

5.Cross Join

A cross join returns all possible combinations of rows of two tables(also called a Cartesian product).

Relationships

1.Cardinality

• One-to-Many
  
  On the one-side of the relationship the column must have unique values; on the many-side the value can(and usually does) contain duplicates.




• One-to-One
  
  On both sides of the relationship the columns need to have unique values. A more accurate name would be "zero-or-one"-to-"zero-or-one" relationship because the presence of a row in one table does not imply the presence of a corresponding row in the other table.




• Many-to-Many
  
  On both sides of the relationship the columns can have duplicates.
  
  These occur when neither table has unique values, or when you are trying to directly connect two fact tables.

2.Active vs Inactive relationships

Active: Represented by a solid line; used automatically by Power BI visuals.

Inactive: Represented by a dashed line ; ignored by default unless explicitly invoked.

Different Schemas

1. 
   Star Schema
   It is a straightforward and commonly used data modeling approach in Power BI.
   It consists of a central fact table connected to multiple dimension tables, forming a star-like structure.
   Advantages are:

• Simplicity


• Query Performance


• User-Friendly

2.Snowflake Schema

It is an extension of the star schema, where dimension tables are further normalised into multiple related tables, forming a snowflake-like structure.

Advantages are:

• Reduced Redundancy


• Scalability


• Detailed data organization.