DATA MODELLING
Data modelling in Power BI Desktop is the process of organizing and structuring data from multiple sources so that it can be analyzed effectively. In Power BI this involves connecting tables, defining how they relate and making them good enough for reporting.
1. Joins in Power BI
Joins are used to combine data from different tables into one table based on a common column.
Types of Joins:
- Inner Join - This returns only matching records.
- Left Join - This returns all records from the left table together with matches.
- Right Join - It returns all records from the right table together with matches.
- Full Outer Join - It returns all records from all tables.
Example
If you have a Customer table and an Orders table, you can join them using Customer ID to see which customer made which order;
| CustomerID | Name |
|---|---|
| 1 | John |
| 2 | Mary |
| CustomerID | OrderAmount |
|---|---|
| 1 | 500 |
| 2 | 300 |
| 3 | 200 |
Result Table
CustomerID | Name | Amount
1 | John | 500
2 | Mary | 300
2. Relationships in Power BI
Relationships allow tables to remain separated but connected in the data model. They define how tables interact with each other inside Power BI's data model.
Types of relationships:
- One-to-Many(1:M) - This exists when one record in Table 1 is related to many records in Table 2.
Many-to-Many(M:M) - This occurs when multiple records in Table 1 relates to many records in Table 2.
One-to-One(1:1) - This relationship exists when each record in Table 1 matches exactly one record in Table 2.
3. Schemas in Power BI
A schema defines the overall structure of how tables are arranged and connected.
Types of schemas:
- Star schema This consists of one central fact table which is surrounded by multiple dimension tables.
- Snowflake schema This is an extension of the star schema where dimension tables are further split into smaller related tables.
Top comments (0)