data modelling
It is the process of analyzing and defining different data types and the relationships between them
SQL joins
we shall assume Table A(left) and Table B(right) as data sets given to us
Inner Join: This returns only rows with matches from both table A and B.
Left Join: Returns all rows from the left table(A) and matching rows from the right table(B). Nulls from table B will be returned if no matches.
Right Join: Returns all rows from the right table(B) and matching rows from the left table(A). No matches will appear as nulls.
Full Outer Join: Returns all rows from both tables.
Left Anti: Returns rows from the left table (A) that have no match on the right table (B).
Right Anti: Returns all rows from the right table(A) that have no matches on the left table(B).
Illustrations of Joins
Power BI relationships
Relationships connect tables using keys
1. One-to-Many(1:M): Commonest that links one table to many
Example: One patient that has many visits.
2. Many-to-Many(M:M): Links both tables as they both have duplicates.
Example: Patients and diseases where one patient can have many diseases but one disease affects many patients.
3. One-to-One (1:1): There is only one match from one table to another.
Example: Patients and their NIN
Cardinality defines the relationship type between two tables for example: 1:M, 1:1 and M:M
Fact vs Dimension tables
Fact tables contain numbers and measurable data for example visits, sales and cases
dimensional tables contains descriptive information for example patient details, location and time.
schemas
This is how tables are organized
- Star Schema: one central fact table has surrounding dimensions.
- Snowflake Schema: Dimensions are split into smaller tables for example location, country, region and district.
- Flat table (DLAT): Everything is in one table where everything is merged.
Role playing Dimensions is where one table is used multiple times in different roles.
Top comments (0)