In this tutorial, you’ll learn about the VertiPaq Engine feature in Power BI which will help boost the performance of your DAX code. This post is an introduction to the Storage Engine and what it does with your DAX.
DAX Query Structure For VertiPaq Engine
Firstly, you need to understand the query structure that happens after you write your DAX equation.
When you write your DAX code, processes occur behind every function in the code to give you a result.
You need to understand these processes in order to find performance issues and optimize them.
This is the process behind the query structure:
After the DAX query is made, it will go through the SQL Server Analysis Services Tabular Model to be analyzed and tested. Next, it will pass through the first engine called Formula Engine.
The Formula Engine can’t retrieve the needed data. So, it requests help from the Storage Engine or the VertiPaq.
The VertiPaq then gets data from the data cache or the data source in a compressed method.
The compressed method is important for making the file size smaller and making the queries run faster.
VertiPaq Engine In Power BI
VertiPaq is based on columns that are different from a basic SQL server.
A basic SQL server operates everything on rows which makes the overall performance slower.
It has the ability to compress data in multiple ways and store that data into memory. This makes the performance of your DAX code faster.
If you work with a direct query, you can’t use the VertiPaq Storage Engine; the VertiPaq Storage Engine can only be used for imported models using the import function.
Here are the different characteristics of a VertiPaq Engine:
1. Scans And Retrieves Data
VertiPaq scans and retrieves data from the data source or wherever you have imported it from.
And then, it provides the queries that the Formula Engine needs to answer the DAX question.
2. Uses Multiple Cores
It’s faster than the Formula Engine because it is multi-threaded and can use multiple cores.
3. Completes Simple Expressions
The storage engine can complete simple expressions like SUM, AVERAGE, and even DISTINCTCOUNT.
The DISTINCTCOUNT function can cause problems at a SQL server type setting, but VertiPaq handles it efficiently.
4. Expresses Queries In A SQL Language
The queries generated by the storage engine are expressed internally with a pseudo SQL language called xmSQL.
It’s a visual representation of what the computer is doing in the background. It allows people to have an idea of what is happening behind the process.
***** 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
Optimizing DAX will improve the performance of your code. This is done through the use of the VertiPaq or the Storage Engine. This will help reduce the processes behind the code’s calculation and gives results more efficiently.
Nick
