In this tutorial, you’ll learn about VertiPaq compression and storage in Power BI. These two features are essential things that you need to understand in order to maximize the use of Vertipaq in your report. They will help you gain a smoother and faster performance for your DAX codes.
Column Storage Database For VertiPaq Compression
Data is stored in columns so that it can easily be retrieved without going through the whole table.
This is a basic table that’s based on rows.
It has Purchase Date, ProductName, Sales Person, Cost, and Quantity columns.
This is a table based on columns:
The columns have the same content but they are separated.
This is what VertiPaq does. It breaks up every column into its own entity and then applies different compression methods.
This decreases footprints inside RAM and increases the speed of the calculation.
If you want to SUM up the cost in the row storage database, you have to collect the cost from each row. But in the column storage database, you only need to go to the Cost column and calculate its sum.
Another example is if you want to know the sum of certain products in the row storage, it will go through every row to look for that data.
VertiPaq will only use the Product and Cost columns to give you the results which hasten the calculations’ performance.
VertiPaq Compression In Power BI
The goal of compressing data is to reduce the amount of memory needed to make queries run.
VertiPaq stores a compressed copy of the database in memory. Compressed columns make your code and query run faster compared to an uncompressed column.
There are three types of compression that you can use. They are generally called Encoding.
1. Value Encoding
Value Encoding is used on integer values especially since VertiPaq converts a lot of values in Power BI to integers.
2. Dictionary (Hash) Encoding
This type is used on text values.
3. Run-Length Encoding
Run-Length Encoding is used to look for repeating values. It codes a column that is value encoded or dictionary encoded.
***** Related Links *****
Optimize DAX Functions With This New Course
Measure In Power BI: Optimization Tips And Techniques
Advanced Tips To Optimize Your Power BI Table
Conclusion
VertiPaq in Power BI helps run your DAX codes in the best performance possible. It uses a different storage database and compresses data depending on your report’s data type.
I hope that this tutorial broadens your understanding of how the Storage Engine works and how it affects the whole performance of your DAX code. You also learned how it generates data and how it gives you your desired results in a quick and simple way.
Nick
