Data Modelling,Relationships and Joins.

Introduction

Data modelling is the process of defining tables and establishing their relationships.The benefits of data modelling is that it simplifies data analysis expressions,speeds up query perfomance and reduces complexicity.
Flat table is a single table containing all dimensions and facts. Combines all of your data into one place containing all attributes

It is suitable for a small,simple dataset where data has few rows.
Advantages of a flat table
It is easy to understand
Appropriate for small databases
Easy to import and analyze.
Requires few or no relationships
Disadvantages include
Creates data redudancy-where the same customer,product or category information may be repeated many times.
The table may become very wide and difficult to maintain as the dataset grows.

Star schema - centralized fact table surrounded by single dimension tables.
It organizes data into two distinct table types to form a star like structure.

An example of a star schema above,we can see the fact table being sales table and the rest being dimensional tables.
Advantages
Fast performance
Simple DAX formulas and clear pathways.
_Disadvantages
_
Redudancy within dimensions since dimension tables are denormalized
Requires light upfront preparation since raw data does not come arranged as a star schema.
Intergrating data measured at different detail into a star schema requires creating separate fact tables or careful model design.

A star schema is suitable for systems with large volumes of transactional data where performance and file size matter.
I recommend a star schema with one to many cardinalities and single direction filtering.

Snowflake schema-normalized extension of a star schema where dimension tables is normalized and connects to other dimension tables.
Advantages :
Easier to update attributes in one place.
Saves storage in relational database
Reduces duplicates dimensional values.
Disadvantages : increase DAX complexicity as it requires multiple joins steps.
Degrades power bi performance due to chain filtering and relationships.

The difference between star and snowflake schema is that Snowflake schema data is split into sub-dimensional tables to eliminate duplicate data therefore expanding hierarchical branches while the star schema each dimension is a single,flat table connected directly to the fact table.

Fact tables and dimension tables
Fact tables -contains mostly keys,dates and numerical metrics eg patients_id,doctors_id,procedure_id

Dimension tables- contains descriptive attributes used to slice,dice,filter and group facts.It mostly describes and gives more of the context around the facts

Granularity-The exact level of detail representation by a singe row in afact table eg individual item line vs daily total transactional summary

Key entities:
Facts:fact sales,fact orders,fact inventory
Dimensions:dimcustomer,dimproduct,dim location

Cardinalities in data modelling/creating relationships-this refers to the raw data in one table in relation to data in another table based on numerical count of matching rows.

Many to one(:1) this relationship occurs mostly when connecting from the fact table to the dimension table. The foreign key in the fact table connecting with the primary key in the dimension table.
One to many(1:)Primary key in the dimension table connects to foreign key in fact table.
One to one(1 : 1)connected using one primary key in the fact table to another primary key in the dimensional table or vice versa.
Many to many(* : *)used when bridge tables or direct relationships handle non unique keys on both sides.eg a foreign key and another foreign key.

Primary Key-is the main unique column identifier for each row in a table,it is exactly one per table eg customer id in dimcustomerexample C100)
Unique keys-ensures values in a secondary column are unique across all rows eg emailaddress,dimcustomers.
Foreign key non unique identifier column in a fact table linking back to a dimension.
Referential integrity-ensuring foreign key values in the fact table exist in the referenced dimension table.
Active vs inactive-only active filter path can exist between two tables at a time.

We activate relationships on the home tab>manage relationships>status>active

Inactive paths are activated using DAX functions like USERLATIONSHIP()and are dotted lines.

Filter direction
Single direction-filter flows strictly from the one side dimensionn to the many side(fact).This is the default.

Bi-directional(Both)-filters flow in both directions across tables.

Some disadvantages of bi-directional filters are that they may cause perfomance drops,ambigous filtering paths and unexpected visual results.

Joins in power query

select the join kind and click ok

Left outer-Keeps all rows from left table or table 1 and matching rows from table two.
Right Outer -keeps all rows from the right table or table two and the matching rows from table one.
Full outer-keeps all rows from both tables,combining matches and leaving nulls for non matches.
Inner-keeps only rows where the join keys match in both tables
Left anti-Keeps rows from table 1 that have no match in table two.Great to use for finding missing records.
Right anti-Keeps rows from table two and those that have no match in table one.

MERGE AND APPEND IN POWER QUERY.
Merge-this is basically physically combining two physical tables into a single wide table.

When to merge?we merge when combining staging lookup tables before loading eg merging product subcategory into product category

Append-We do this by stacking rows of tables on top of each other.This makes the table longer.The condition is that the table must have same exact number of columns and be the same type of columns.

append command below merge queries is used to stack or combine rows together,rows with exact number columns.

Conclusion

Understanding data modelling,relationships and joins is the foundation of an effective data analysis.
Understanding the flat tables and relationships simplifies my DAX functions and power query which speeds up the query perfomance. Understanding the right join type to use and establishing a clear cardinality makes it easier to model my data.