DATA MODELLING
This is a detailed process that involves creating a visual representation of data and its relationships.
TYPES OF TABLES
FACT TABLE
It contains quantitative data for analysis.
DIMENSIONS TABLE
Provides context to fact table data.
STAR SCHEMA
It contains a single fact table in the center that connects to multiple other dimensiontables.
SNOW FLAKE
Is an extension of a star schema where dimension tables are broken down into subdimensions.
FLAT TABLE
A table that displays data in a simple, two-dimensional format without any relationships to other tables.
SQL JOINS IN POWER BI
- INNER JOIN: Returns rows present in both tables if there is a match.
- LEFT JOIN: Returns all rows present in the left table and matching rows from the right table.
- RIGHT JOIN: Returns matching rows from the left table and all rows present in the SQL right table.
- FULL OUTER: Returns all rows present in both right and left tables.
- LEFT ANTI: Returns rows from the left table that don't have matches in the right table.
- RIGHT ANTI:Returns rows from the right table that don't have matches in the left table.
POWER BI RELATIONSHIPS
One-to-Many (1:M)
One row in table A matches many in table B. For example,
Many-to Many (M:M)
Here one is recommended to use a bridge table.
0ne-to-One (1:1)
One row in table A matches one in table B. For example,
Many-to-One (M:M)
Many rows in table A match one in table B. For example,
Active and Inactive relationships
The active relationship is used by default in reports and calculations, while the inactive relationship is not used unless specified. It is also useful for alternate paths such as multiple dates.
DIFFERENCE BETWEEN JOINS AND RELATIONSHIPS
Joins; combine tables based on a condition, result in new tables with combined columns and are used in power query steps. Relationships; define connections between tables in a data model and are used for; filtering, calculations and visuals across tables.
Top comments (0)