What is Data Modelling?
Data modelling is nothing but Entity-relationship diagrams that are used for better understanding of databases. If the relationship between the tables is not correct then the interaction between the visuals in Power BI will be wrong. So one needs to ensure the data model is correct when building dashboards.
In PowerBI Desktop, the developer can find the model view icon to find the data models and also the corresponding diagrams if already created.
Create Data model in PowerBI
When the data is loading, PowerBI will try to auto predict the relationships based on the column name and the value in it. If column name matches and one value matches surely it will create relationship. Developers need to ensure the relationship is correct. If the column name in two tables vary then the relationship needs to be created manually.
Once the data loaded the relationship can be checked under model view.
Go to model view, user can find the icon named manage relationship under home menu ( Model View>Home>Manage relationship).
Create a new relationship by using the add menu.
Cardinality will be auto-selected by PowerBI based on the data loaded. User can change the cross filter based on the needs that will be explained below.
Cardinality and cross filter
Cardinality explains how the record in each table connects with the other records in the corresponding table.
The cardinality should not be changed during a scheduled refresh or manual refresh in powerbi service else the refresh will fail and the developer needs to change the cardinality by using PowerBI desktop.
Cross filter direction is very important for the slicer to interact with the visuals.
Cross filter and cardinality combination explained here.
*INNER – Column from both tables filters other table data.
*LEFT – Column from left table act as a filter for both tables, but column from the right table will not filter the left table.
*RIGHT – Column from right table act as a filter for both tables, but column from the left table will not filter right table.
Example:
Single(TABLE A filters TABLE B) –> ( Due dates filter txn)
Table A can filter both A and B table while B table can’t filter A.
Here the later diagram explains left join output where all the data from the left table and the filtered matched records from the right table, so this can act as left join also.
Optimize the data model
Data model optimization will improve the performance of the dashboard and the user can see the difference when the datasets are large.
- Hide unused columns.
- The data type used in managing relationships.
- Both Directional relationship will slow down the model, try to use other cross filter types.
- Make sure to remove inactive relationship.
Hide unused column
Imported data often contain columns that are not used in the dashboards and reports. So the developer can hide the unused columns as well as the intermediate columns used for DAX calculations.
Data type in manage relationship
In manage relationship, the columns used for joining two tables should have the same data type otherwise, the joining won’t work.
In general integer data type column used for joining works faster than the text column. So if possible use alternate column in the place of text column or developer can create an integer column similar to text column which can be used for joining will improve performance on the larger dataset.
Ex: Here borrower id and customer id have the integer data type.
Save as PDF