Data modelling relationships, joins and schemas in power bi

Data modelling is bringing all the tables, laying them out and connecting relationships between. A well-designed model makes your dashboards faster, easier to maintain, and more scalable.

A fact table stores measurable business events, such as sales, orders, or transactions. It contains numeric values and foreign keys to related dimension tables.

A dimension table stores descriptive information such as customer names, product categories, or dates that is used to filter and group facts.

Types of Relationships

1. One-to-Many This means one table can be connected to other many tables. eg. A landlord can have many rental houses.
2. Many-to-Many eg. many students can have many different courses they are undertaking. Many-to-many relationships are supported but should be used carefully because they can introduce ambiguity.
3. One-One Each row matches exactly to one row
4. Many-One This is simply the same relationship viewed from the opposite table.

JOINS

Used when you want to combine multiple tables physically before loading them into the data model. You do this using the Merge feature.
Inner Join: Returns only rows where matching keys exist in both tables.
Left Outer Join: Returns all rows from the first table and matches from the second.
Right Outer Join: Returns all rows from the second table and matches from the first.
Full Outer: Returns all rows from both tables.

Schemas

A schema is how you structure your data model
Types include:

• Star schema
  There is one central fact table.
  There are several surrounding dimension tables.

• Snowflake Schema
  A snowflake schema is like a star schema, but dimension tables are further broken down into smaller related tables.

PRIMARY KEYS A primary key is a column that uniquely identifies each row in a table.
FOREIGN KEY A foreign key is a column in one table that refers to the primary key in another table. Foreign keys are used to connect tables.

Fact table is a stores events, transactions, or measurable business activities.
Dimension Table is a describes the items in the fact table.