Data Modelling
Data modelling involves creating a visual diagram of data to show how it can be organized, structered and analysed within a database. In Power BI, data modelling enables ease in report creation, accuracy in calculations and efficient performance.
SQL JOINS
SQL JOINS is used to combine data in multiple tables based on the related columns between them. They are important in relational database since it enables us to retrieve data with unified view that is mostly spread across multiple tables.
INNER JOIN
INNER JOIN is an SQL operation that enables combining rows from multiple tables with respect to a related column.
LEFT JOIN
LEFT JOIN retains rows from the left side of the table, and combines rows that are matching from the right side of the table.
RIGHT JOIN
RIGHT JOIN merges two tables by retaining data from the right ide of the table and only matching data from the left side of the table.
FULL OUTER JOIN
FULL OUTER JOIN merges two tables by retaining all the data from both tables, regardless of whether they are matching.
LEFT ANTI JOIN
LEFT ANTI JOIN returns rows from the left side of the table that do not have a match on the right side of the table.
RIGHT ANTI JOIN
RIGHT ANTI JOIN returns rows from the right side of the table that do not have a match on the left side of the table.
How to access JOINS in a Power BI query
To access JOINS, go to HOME then click transform data > open power query editor > choose second table > select matching columns, then select the join type and lastly click ok.
Relationships in Power BI
Relationships connects tables, without merging them physically, enabling records from multiple places to be used jointly in reports. There are four types of relationships namely;
One-to-many
One-to-many connects a lookup table that contains values that are unique , to a table, where the values repeat.
Many-to-many
Many-to-many normally occur in a relationship when both tables contain duplicate values , enabling multiple data in one table to match multiple data in another.
One-to-one
One-to-one connects two different tables whereby two unique values from the different tables are matched.
Cardinality
Cardinality explains the kind of the relationship between table A and table B, describing how unique values in one column relate to values in another column.
Cross-filter Direction
Cross-filter Direction determines the flow of filters in related tables, with options for single direction or both direction.
Active vs Inactive Relationships
Active relationships are used in visuals by default while Inactive relationships has to be activated using DAX.
Creating Relationships in Power BI
Go to Model View
Drag a column from one table to the next
Lastly set to Cardinality or Cross-filter direction
Joins vs Relationships
In Joins, data is combined in one table, hence increasing the size of dataset, while in relationship, the performance is fast and efficient due to the schemas.
Fact and Dimension Tables
Fact tables has data that is quantitative and forms the star schema, while dimension tables hold descriptive data.
Data Modeling Schemas
A Schema is a framework and organization of data within in a model, which is significant for efficient data analysis.There are two types of schemas namely Star schema and Snowflake schema.
Star schema
Star schema organizes data into a central fact table and is surrounded by dimension tables.
Snowflake schema
Snowflake schema is an approach where dimension tables are normalized and broken into numerous tables that are related.
Flat Table
Flat Table is a table that contains all data, including descriptive and transactional facts.
Conclusion
Power BI seemed something complicated to me until I engaged in hands on learning that has made it easier for me to understand. Data medelling is a very important to learn since it is fundamental in analysing and reporting.
Top comments (0)