In this tutorial, you’ll learn valuable tips and best practices for data modelling in Power BI. You can watch the full video of this tutorial at the bottom of this blog.
This is one of the four pillars of Power BI report development and is a proven method to develop reports with ease. Honing in on your data modelling skills will greatly improve your reports and outputs.
Approaches To Data Modelling In Power BI
Setting up a data model starts with organizing your tables. There are two ways you can set them up:
1. Star Schema Approach
One of the best ways is by using the Star Schema approach:
- Value (or Fact) table at the center of the star
- LookUp (or Dimension) tables at the end points of the star
Here is an example:
You can see that the Fact Table, Sales, is at the center while the Dimension Tables are positioned to look like a star.
2. Waterfall Approach
The second approach and layout for data modelling in Power BI is called the Waterfall technique. You set up your LookUp Tables at the top and the Fact Tables at the bottom.
Here are some of the benefits of this approach:
- Arranges tables in rows (LookUp tables in the first row, Fact tables in the second row).
- Easy to visualize the dimensions “falling” to the Fact table.
- Displays all relationships and makes it easier to see errors.
When using this layout, you need to remember these details:
- Delete any relationships autogenerated by Power BI.
- Manually create all relationships.
- Use the “Manage Relationships” dialogue to maintain relationships.
With the Waterfall layout, you’ll always see the LookUp Tables at the top, Fact Tables in the middle, Measure Tables on the right, and Supporting Tables on the left.
Data Modelling Relationships
To fully understand how relationships work when it comes to data modelling in Power BI, you need to know the following:
Using The Manage Relationships Dialogue
When using the Manage Relationships dialogue, you’ll see the full list of relationships between each table and column.
This allows you to easily spot and correct the keys used to join tables. You’ll also see the state of each relationship that you can activate or inactivate.
Selecting the Manage Relationships dialogue in the model view will let you see all the relationships.
Take note that you can’t have two active relationships between the same tables.
In this case, you need to inactivate the Invoice State and activate the relationship for Order Date before you can close it.
Setting Up The Cardinality For Data Modelling In Power BI
You can view the cardinality and direction of each relationship using the Edit Relationship dialogue. Ideally, the relationships should have a one-to-many or many-to-one cardinality.
Power BI is excellent at setting the cardinality to default; it sets it according to your data.
If you go to the Edit Relationship dialogue, you’ll see the relationship between Sales and Channels.
Power BI picks up a channel key between the two tables.
You can choose your cardinality and make sure it’s in the right number. You also need to make sure that you edit the Cross Filter Direction depending on your data model.
Power BI often chooses Single for the Cross Filter Direction, but in some cases, it will choose the Both option.
If you see that Both is the default, confirm if the data in your data set is loaded and transformed as intended. Afterward, switch it back to Single or to your particular situation if possible.
Organizing Relationships In The Data Model
One-to-many relationships are denoted by a single arrowhead from one table to many tables.
You have to avoid bi-directional relationships which are denoted by double-directional arrowheads. Bi-directional relationships can lead to inconsistent results and often require a more complicated DAX code.
Using Active And Inactive Relationships
Next, you need to understand the active and inactive relationships between tables.
You can only have one active relationship between two related tables, but you can have as many inactive relationships as you want between them. You can also use an inactive relationship on-demand in DAX measures using USERELATIONSHIP.
Take note that you need to deactivate a relationship first before you can activate another relationship.
Measure Tables For Data Modelling In Power BI
You can add measure tables from the Home menu and then by clicking Enter Data.
For this example, the table is named Core Measures.
To add a measure, right-click the measure table and click New Measure.
When your model uses many measures, you can simplify your Fields pane by collapsing and expanding each measure.
Columns In Data Modelling
Another thing you should learn about Data Modelling is linking columns.
Another best practice is adding the suffix “Key” for any column that you’ll use for linking. When doing your data loading and data transformation work, always ensure that the fields used for linking have the suffix and correct data type.
Link only columns that have the same name, such as Customer Key. Avoid columns that end with an ID or code as they mean different things in different tables.
Another best practice is to use integers because it’s always the fastest method.
Column Visibility For Data Modelling In Power BI
The last thing in this tutorial is about Column Visibility.
Column Visibility is where you:
- Hide all linking columns
- Hide all columns that end-users don’t use in visuals, especially in Fact tables
- Create base measures for all SUMs and COUNTs
- Reduce errors by preventing users from selecting “naked” columns
This is a good thing if you plan to publish a report or data set for other people to use.
It helps users select the correct measures by hiding all the unnecessary columns in the model. This includes key sorts and columns that can only be used with a DAX code.
Hidden columns will appear grayed out:
If you expand the Sales Table, you can see the grayed out hidden columns. You can right click on the Field pane and choose your viewing options.
To hide a column, click the table of the column you want to hide. Then, right click on that column and choose Hide.
To view a hidden column, right click on the field pane and click View Hidden. You’ll then see a grayed out column among the list.
This tutorial is very helpful to those who are having difficulty with data modeling in Power BI.
These tips will help you to create more organized layouts which make it easier to understand the relationships in your data set. Once you’ve fully grasped how to set up data models, you can easily produce outstanding reports for end users in your organization.