This tutorial will discuss about the two DAX Query Plans in Power BI.
Query plans affect the DAX’s performance in generating valuable data. They also help in optimizing DAX codes that make your report more compelling.
These are the two query plans that are generated by the formula engine:
- Logical Query Plan
- Physical Query Plan
Every DAX query triggers these two events.
Logical Query Plan In Power BI
This is the DAX query flow. For this part, focus on the third step which is the Simplify Logical Query Plan Tree.
This query resembles your DAX query. When you run a DAX query, it mostly matches up with what the Logical Query Plan is doing.
To illustrate, here’s a sample query.
If you run this, you’ll get a result of 29,138.
If you run the query in DAX Studio and turn on the Query Plan Events tab, you’ll come across the Query Plans. Among the two types, the Logical Query Plan shows up first.
Each indented line is a subtask built on each other. The first line is the final outcome of your DAX. The other lines sum and scan the FactSales’ Quantity column.
The first word before the colon is called the operator.
The words after the colon are called the operator types.
There are the two types of operator types:
- RelLogOp
- ScaLogOp
RelLogOp is a table output. DAX uses VertiPaq to scan a column to produce a table. On the other hand, ScaLogOp is a scalar output; this means it’s an aggregated number. If you look at the second and last line of the query plan, you’ll see that it sums and gets the quantity of the FactSales’ Quantity.
Logical Query Plans create a one-row table called Total Quantity which has the sum of Quantity from the FactSales table.
The query plan is easier to decode since it uses a simple DAX query. The more complex your DAX is, the more it is harder to decode.
A good thing to strive for is to see many “_VertiPaq” operators as possible. This means that it does all the work by itself to help optimize your DAX codes.
Physical Query Plan In Power BI
The Physical Query Plan shows up second among the two types. This is the fourth step in the DAX query flow. It has the same tree structure as the Logical Query Plan.
Once the Logical Query Plan is built, it is sent to the Physical Query Plan to be executed. If you run the same sample query, you’ll get the same answer.
Specifically, the fourth step in the DAX query flow is to Build the Physical Query Plan Tree.
It may look similar to the Logical Query Plan, but it’s different. Normally, the physical query plan is hard to match up with the DAX query in Power BI but since it’s a simple sample query, you can follow the process.
These are the three operator types in the Physical Query Plan:
- LookupPhyOp
- InterPhyOp
- SpoolPhyOp
LookupPhyOp gives scalar values in a given row context. The InterPhyOp iterates data through a table row-by-row. SpoolPhyOp receives results from a VertiPaq query, materializes it, and stores it into memory.
In the fourth line of the query plan, you can see a ProjectionSpool operator which means that a query is sent to the storage engine. It iterates the results of a query with the number of records.
The amount of records shown in the query plan should be the least amount of records required to complete the answer. The result of this simple query only has 1 row so it returned 1 record in the query plan.
The Records column, which corresponds to the ProjectionSpool’s SpoolPhyOp, shows how much data is sent from the storage engine to the formula engine. So, the more records you have, the slower your query will be.
If the DAX performance is slow, the Records column is a good place to start looking.
***** Related Links *****
DAX Calculations In Power BI: Best Practices
Optimize DAX Functions With This New Course
5 Strategies To Enhance Your Power BI DAX Skills
Conclusion
When you run a DAX Query, it goes through a series of steps before it gives you an answer. Two of those steps are the Logical and Physical Query Plans. They are responsible for the step-by-step process and execution of the formulas used in the calculation.
If you want to learn and understand more about these query plans, go through this tutorial and then try using the DAX Studio to see them first hand.
Nick
