This tutorial is about the best practices when using the VertiPaq Engine in Power BI. You’ll learn different techniques and approaches to make your DAX calculations perform smoother.
To be able to utilize your data models and relationships, you need to set them up in a way where you can understand the flow of your calculation.
The Data Model Setup
The best setup for VertiPaq is called Star Schema.
The fact table is located at the center of the model while the dimension tables surround it.
You can see that the filters from the dimension tables are transferred to the fact table. This shows the relationship between both tables.
The relationships between the dimensions and fact tables are based on the cardinality of the defining column.
Cardinality Of A Column
Cardinality is the unique values of a column.
If the DimSalesPeople table has 100 different sales people, the cardinality of that column is 100. It will feed into the FactSales table which has thousands of rows.
The cardinality of that column is equal to the number of sales people that exists in the FactSales table.
If you have a lot of unique values, performance issues may arise. Always remember that the cardinality between relationships is important.
Referential Integrity Violations In VertiPaq
Referential integrity violations can underutilize all of the available optimizations in your DAX. This happens when a value exists in a fact table but doesn’t exist in the dimension table.
VertiPaq Utilization
Here are the general best practices for utilizing VertiPaq:
1. Import Columns
Import columns that you only need to lessen the memory of the database.
2. Reduce Unique Values
Reduce the cardinality as much as possible. This will allow the encoding methods such as Value Encoding to work better. Split columns that have 2 values such as Date Time columns to Date and Time columns.
3. Reduce Precision
Another best practice to implement is to reduce the precision of your data. If your business requirement allows it, decrease the precision.
4. Convert To Integer Type
Converting text columns to integers makes the encoding faster. Value encoding is faster than a dictionary or hash encoding. So, convert text columns to integer.
Calculated Columns In Power Query
The best place to do calculated columns is at Power Query. This will allow for better compression and faster refresh.
This doesn’t mean that all calculated columns should be done at the source. You have to find the most suitable place to do them.
Don’t store partial or helper columns in a calculated column. It has the potential to cause a performance hit. It’s best to condense as many steps into one column.
***** Related Links *****
Optimize DAX Functions With This New Course
Measure In Power BI: Optimization Tips And Techniques
Simple Power BI Transformations For More Optimized Data
Conclusion
When it comes to optimizing your DAX in Power BI, VertiPaq is the answer. There are ways and practices that help improve the storage engine.
You can utilize this tutorial by understanding the methods. With these practices, a slow-performing DAX will no longer be a problem. You just have to maximize them to obtain your desired results.
Nick
