This tutorial will discuss the Relationships tab in the VertiPaq Analyzer in DAX Studio. You’ll learn how useful it is in identifying referential integrity violations which slow down your DAX codes. It helps you determine which table or column needs to be optimized and improved.
Relationships Tab Tutorial In The VertiPaq Analyzer
The Relationships tab shows all of the relationships that are in your data model. To illustrate, this is a sample file which will be used in this tutorial. You can see that it has four relationship sets.
This is the diagram view of the data model.
It has four dimension tables that relate to the fact table. Going back to the Relationships tab, you can see the Max From Cardinality column.
This column defines the cost of the relationship which is the amount of time DAX needs to transfer the filters from the dimensions table to the fact table.
Other important columns in the tab are the Missing Keys, Invalid Rows, and Sample violations; they show Referential Integrity issues in your model.
Referential Integrity Violations
Referential Integrity Violations occur when there’s value on the many side of a one-to-many relationship that doesn’t exist on the one side.
These violations slow down the performance of your DAX and sometimes lead to inaccurate calculations.
To locate these violations, you can either use the Relationships tab or run a DAX query. It’s a best practice to always fix the issues to have a smoother performance.
If you go back to the Relationships tab, you can see that there are violations that refer to the relationships of the dimensions table to the fact table.
The fact table has 781 missing CustomerKeys and 1 missing SalesPersonKey that doesn’t exist in the DimCustomer and DimSalesPerson table.
The Sample Violations column shows what the missing keys are. In the example, it showed only 5 keys from the Customer Key table.
If you want to change the number of keys shown, go to File and click Option. Next, click Advanced and then change the number of the violations. In this example, 20 missing keys are used.
Once you go back to the tab, rerun the DAX by clicking View Metrics. You can now see more of the missing keys.
Now, you need to figure out why these keys are missing and what they are. Create a DAX query to give you the missing keys from the Customer Key table.
First, use the EVALUATE function and then the EXCEPT function. For the left table, use DISTINCT function for the FactSales table’s CustomerKey.
And for the right table, use DISTINCT function for the DimCustomer table’s CustomerKey. Once you press F5, you’ll see all of the 781 missing keys.
VertiPaq Analyzer Tutorial: Fixing RI Violations
To fix the violations, go back to the sample file and click Transform Data. Next, go to the DimCustomer table, get the full list, and then click Apply.
Run the DAX query again. You can now see that it doesn’t have missing keys.
For the violation from the SalesPersonKey table, do the same thing. Go to the DimSalesPerson table, get the missing key, and then click Apply.
There are now no referential integrity violations.
***** Related Links *****
Optimize DAX Functions With This New Course
Simple Power BI Transformations For More Optimized Data
Optimize Power BI Formulas Using Advanced DAX
Conclusion
If you don’t know how and where to start optimizing your DAX, use the Relationships tab in the VertiPaq Analyzer Metrics. It locates tables with referential integrity violations and helps you get rid of them for a faster DAX calculation.
Performance issues always occur but once you master the use of the Relationships tab, you’ll get error-free calculations.
Nick
