Today’s post will talk about how the storage engine in Power BI can do mathematical operations. This tutorial is helpful if you want to get combined figures without a column or a table. When dealing with easy measures or codes, the best practice is to send them into the VertiPaq storage engine.
Unlike the formula engine, the storage engine can only work on simple DAX queries. It can’t solve complex queries because this is the responsibility of the formula engine. It can only work on simple queries to provide insightful data and results.
Storage Engine And xmSQL Statements In Power BI
This is a sample query that has a SUMX statement. It’s a simple measure that asks for an aggregated figure of the Total Sales by multiplying the Sales Data’s Current Price and Sales Data’s Quantity.
If you run the measure in DAX Studio, you’ll see the xmSQL statement and the result.
You’ll see that it uses new terms such as WITH, $Expr0, and PFCAST. $Expr0 is used to define the measure, and then it is called in the SELECT statement. The storage engine or VertiPaq sometimes automatically adds a cast operator called PFCAST.
The product of the Current Price and the Quantity is pushed down to the storage engine. The appearance of the WITH statement in VertiPaq means that it is also sent to the storage engine.
If you look at the Physical Query Plan, you’ll see a SpoolLookup operator that has a Sum_Vertipaq. This implies that it scans one row to get the sum of the current price multiplied by the quantity.
In the Logical Query Plan, you’ll see the Scan_Vertipaq. This shows that the query is done in the storage engine.
So even if you’re iterating a fact table, the storage engine is the one completing the task. Since VertiPaq is doing the work, the execution is done really fast. And if you add in some optimization techniques in the data model or format your measure or codes properly, your DAX’s performance will be faster and better.
As much as possible, properly set up your data model and measures so that you can have a smooth experience in obtaining valuable data.
***** Related Links *****
VertiPaq Storage Engine On Multi-Threaded Environment
VertiPaq Compression And Storage In Power BI
What Is DAX Studio In Power BI Desktop
Conclusion
The storage engine is the best place to make your queries work. Since it can only do simple queries, it takes less execution time and optimizes the overall performance of your DAX codes. It’s considered as best practice to make VertiPaq do all the work.
Experience how the VertiPaq storage engine works by practicing your queries in DAX Studio and VertiPaq. You may check out our previous blog posts about understanding the VertiPaq storage engine.
Nick
