Data Modeling is the process of analyzing and defining different data type that is collected or produced. It also shows relationship between those data in visual form as data is captured, stored ans used in different ways as per the owner/business. Without a well-structured data model, dashboard in power BI can produce misleading insights,Poor performance and confusion.
In this article we will cover SQL joins, Relationship in Power BI,Schemas and common modeling issues.
SQL Joins Type
Joins combines data from multiple tables based on related column. In Power BI it happens before data is loaded into the model.
Inner Join- Returns to rows where there is matching values in both tables. It can be used to match employees to departments or authors to books.
Left Join-Returns all rows from left table and matched rows from the right. It can be used to list all departments and their employees
Right Join- Returns all rows from the right table and matched rows from the left. It will show all departments including those without employees.
Full Outer Join- Returns all rows when there is a match in either table. It Links employees to their department including the unmatched employees.
Left Anti Join- Returns rows from the left table that have no match to the right. Can be used to know employees who have not attempted to attend mandatory training.
Right Anti Join- Returns rows from the right table that have no match in the left. It can be used to determine employees recently employeed and have not been enrolled to the enjoy benefits.
We create the above on Power BI: Home>Transform data>Power query editor. Use Merge queries to select join type.
Power BI relationship
Relationships are created in the model view to connect tables after they are loaded. They inform us on how tables relate, allowing for filtering without merging data. It's created by going to Model view>Manage relationship
The following are some of the key concept in relationship:
Cardinality-Defines the uniqueness of values in columns used in relationship.
One to One(1:M)- Each row in table A related to one row in table B
One to many (1:M)- Each row in table A relate to multiple row in table B
Many to many (M:M)- Multipe row in table A relates to multiple row in table B
Cross-filter direction
Single- Filters flow in one direction
Both- Filters flow both ways
The difference between Joins and Relationship is that Join combines data at power query level whereas relationship defines connection between tables in the model.
Fact Vs Dimension tables
Fact tables answers the question what. It's use for measurable quantitative data e.g Sales whereas dimension table asnwers the question who, where and when. It's used to give descriptive attributes like customer product and date. They contain the unique key.
Data Schemas
Star Schema
It's a central fact tables connected to multiple dimension tables and is used for faster queries and simpler relationships in Power BI. It also improves performance.Snowflake Schema
This is an extension of a star schema where dimension tables are normalized into multiple related sub-tables. It's used for managing complex hierarchical data and improving the data integrity of the same.Flat table (DLAT)
It's a single wide table containing all facts and dimensions without relationships to other tables. It's used in small datasets and for quicker analysis.
Role-playing dimension
This allow a single dimension table to be used in multiple roles. In a bakery store, a date can be used to show order date, delivery date and return date.
Common Modeling issue
- Creating many to many relationships can lead to ambiquity
- Measure on role-play dimension requires duplicate data tables
- Circular references between tables can cause errors and make the model unusable
Mastering data modeling in Power BI requires understanding SQL joins, relationships, schemas, and best practices. By carefully structuring fact and dimension tables, choosing the right schema, and managing relationships, you can build efficient, scalable, and insightful Power BI models.
Top comments (0)