You come across a very neatly designed Power BI report and immediately get excited at how easy it is to understand the data presented. What you may not see, however, are the various relationships that exist in the background, making it possible to present data clearly. This underscores the importance of establishing accurate relationships, especially when working with multiple tables. It is therefore important to understand data modelling, relationships and joins in order to present data accurately.
Data Modelling in Power BI
Data modelling in Power BI refers to the process of organising raw data, which is in the form of tables, to ensure that it is well structured and can be easily used for reporting. A well-designed data model is key to presenting data well and, therefore, to supporting insight generation. It ensures that reports are generated faster and more accurately, and that new data can be added easily without affecting reports that have already been developed.
Data Modelling Approaches
In modelling, there are two main types of tables used to present data: the fact table and the dimension table. A fact table stores quantitative information about a business event, while a dimension table provides descriptive or qualitative information about the numerical data.
Fig 1.0. An illustration of a fact table containing the details of transactions from patient visits to a hospital
Fig 2.0. An illustration of dimension tables (circled in red) showing details of the transactions in the fact table (facts_visit).
Various data modelling approaches can be used when working with Power BI. These are the flat table, the star schema and the snowflake schema.
Flat Table Schema. The flat table schema is a single table that contains both the transactions and the descriptive information. It contains all the columns, so no relationships are required. The main advantage of a flat table schema is that there is no relationship management, since all the data is within the same table. Secondly, it can load faster because it is the only table. However, a flat table performs poorly as the number of rows grows, since data loads more slowly. Another disadvantage is that writing DAX expressions can become difficult and error-prone. Given these considerations, the flat table schema is appropriate when working with smaller datasets.
Fig 3.0. Flat table showing all the details related to hospital visits.
Star Schema. A star schema consists of one fact table surrounded by related dimension tables. The fact table has its own primary key and various foreign keys, which are the primary keys in the dimension tables; it therefore has a one-to-many relationship with each of them. The fact table contains the details of transactions, while the dimension tables describe these transactions in detail.
Fig 4.0. An illustration showing a star schema with a central fact table named Fact Visits and dimension tables having a one-to-many relationship with it.
In this case, the facts_visit table contains the details of transactions related to hospital visits, while dimension tables such as dim_diagnosis, dim_wards, dim_patients, dim_doctors and dim_procedures provide further descriptions of the details relating to the hospital visit transactions.
Snowflake Schema: A snowflake schema is a dimensional model made up of dimension tables and related sub-tables, which creates a hierarchical structure. One advantage of this design is that it reduces data redundancy and improves storage efficiency. It also provides a hierarchical representation that shows different levels of granularity. The primary disadvantage of a snowflake schema is its complexity, which can lead to slower query performance.
Fig 5.0. An illustration showing a snowflake schema, in which the fact table is described by dimension tables, which are further described by sub-dimension tables in a hierarchy.
Relationships in Power BI
Creating relationships in Power BI is critical because it ensures that data is analysed accurately and that proper insights are generated. A relationship in Power BI refers to the connection between two tables using a shared column. Creating relationships between tables links them, which makes it possible to pull combined data from various categories across the tables. Relationships also reduce data redundancy, as information in one table does not have to be repeated in the other, thus saving storage space.
Power BI supports the following relationship cardinalities:
i. One-to-many (1: )
ii. One-to-one (1:1)
iii. Many-to-many (: *)
One-to-Many (1: *) relationship exists between a dimension table and a fact table, where one row in the dimension table is matched to many rows in the fact table. A practical example is a customer ID in the customer table that matches many transactions in a sales table. This relationship should be used only where the column in the fact table is a foreign key that corresponds to the primary key in the dimension table.
Fig 6.0: This illustration shows the one-to-many relationship, where dim_diagnosis and dim_patients each have a primary key that matches several records in the facts_visit table, in which it appears as a foreign key.
One-to-One (1:1) relationship occurs when two tables have matching unique values, such as a customer ID, which can be assigned to only one customer and appears only once in each table. For example, two tables, customer location and customer preference, could share the same unique values (the customer ID), which serve as the key when linking the two tables.
Fig 7.0: One to many relationship showing one record that appears once in the fact table and many times in the dimension table.
_*Many-to-Many (: ) *_relationship occurs when many records in one table match several records in another table. A many-to-many relationship does not require a primary key, as records can appear more than once in both tables. For example, a customer’s region may appear several times in both the customer table and the sales transaction table.
Fig 8.0. An illustration showing a many-to-many relationship where a record appears more than once in both tables.
Filter Direction
Filtering is another important practice when working with data models in Power BI. It is critical to ensure that the filter direction is well defined when developing a data model. Filter direction defines how filters flow between related tables. There are two forms of filter direction that can be used in a data model:
i. Single-direction filtering
ii. Both-direction filtering
Single-direction filtering means that the filter flows in only one direction: from the dimension table to the fact table. For example, if a product category such as beverages is filtered in the dimension table, the matching records are retrieved from the fact table using the product key.
_Both-direction filterin_g means that filters flow both ways, so filtering either table affects the other. This is most commonly needed in many-to-many relationships between tables. Because of this, both-direction filtering should be used carefully, as it can add complexity and affect the performance of the report being developed.
Joins in Power Query
Joins in Power Query combine two tables using matching values. In Power BI, joins are usually done using the Merge function in Power Query, where one can select Merge Queries or Merge Queries as New. The following types of joins can be performed in Power Query:
Left Outer Join keeps all the rows from the left (first) table and includes the matching rows from the right (second) table. For example, a fact table such as facts_visit, which contains the details of hospital visits and transactions, can be matched with a dimension table such as the doctor table. The left outer join retains all the rows of the fact table and only adds the matching details from the doctor table to show which doctor treated a particular patient.
Right Outer Join keeps all the rows from the second (right) table, together with the matching rows from the first (left) table.
Full Outer Join joins two tables and returns all the rows from both, whether they match or not.
Inner Join returns only the rows that match in both tables.
Left anti join returns all the records from the first (left) table that have no matching record in the second (right) table.
Right anti join returns only the records from the second (right) table that have no match in the first (left) table.
Power Query Joins vs Power BI Relationships
Joins in Power Query can easily be confused with relationships in Power BI, which can make data analysis difficult. A Power Query join physically combines two tables into one, whereas a relationship only creates a connection between two tables using a shared column that serves as the primary key in the dimension table and the foreign key in the fact table. These two distinct processes happen during the data modelling stage, when data has been loaded and cleaned and is now being prepared for analysis to generate insights that support business decisions. Merge is used to join tables physically into one table. However, merging should be done carefully to avoid excessive merging, which can increase the complexity of the data and lead to data redundancy, poor performance and reduced granularity. When working with a Power BI model, it is important to keep the fact and dimension tables separate, because the fact table provides the details of a transaction while the dimension table provides descriptive information about it, thereby improving understanding of the data.
Conclusion
Power BI plays an essential role in data analysis and reporting, and at the centre of it all are the relationships between tables. It is advisable to use the star schema when building relationships in Power BI, as it creates clear and efficient relationships between tables. The star schema ensures that there is a central fact table containing the details of the transactions. Surrounding it are dimension tables, which provide qualitative information about the transactions. Relationships should then be created between the dimension tables and the fact table, whereby the primary keys of the dimension tables are linked to the foreign keys in the fact table, as this improves model readability and makes it easier to create reports. These should be one-to-many relationships guided by a single-direction filter approach.
Top comments (0)