Transcription

DAX Query PlansIntroduction to performance analysis and DAX optimizations using query plansAuthor: Alberto FerrariPublished: Version 1.0 Revision 2 – July 17, 2012Contact:–Summary: This paper is an introduction to query optimization of DAX code through the usage of the DAXquery plans. It uses the Contoso database, which you can download from here: http://sdrv.ms/131eTUK andthe Tabular version of AdventureWorks, available on CodePlex.Acknowledgments: I would like to thank the peer reviewers that helped me improving this document: MarcoRusso, Chris Webb, Greg Galloway, Ashvini Sharma, Owen Graupman and all our ssas-insiders friends.I would also like to give a special thanks to T.K. Anand, Marius Dumitru, Cristian Petculescu, Jeffrey Wang,Ashvini Sharma, and Akshai Mirchandani who constantly answer to all of our fancy questions about SSAS.

BI professionals always face the need to produce fast queries and measures. In order to obtain the bestperformance, a correct data model is needed but, once the model is in place, to further proceed withimprovements, DAX optimization is the next step.Optimizing DAX requires some knowledge of the xVelocity engine internals and the ability to correctly readand interpret a DAX query plan. In this paper we focus on very basic optimizations and we will guide youthrough the following topics: How to find the DAX query plan The difference between the logical and physical query plan A brief description of the difference between formula engine and storage engine Some first insights into the query plan operatorsThe goal of the paper is not that of showing complex optimization techniques. Rather, we focus on how toread different formulations of the same query understanding why they behave differently, by means ofreading their query plans.

Understanding DAX query plans is a long process. We start with very simple queries and only when thesebasic concepts are clear enough, we will dive into the complexity of DAX expressions. Our first query isamazingly simple and it runs on the Contoso database:EVALUATEROW ("Sales",SUM ( OnlineSales[SalesAmount] ))This query returns the sum of sales for the entire table OnlineSales and, to check it, you can simply run itinside an MDX query window in SSMS on the Contoso demo database. Let’s use it to start learning how toread a query plan.In order to catch the query plan, you need to use the SQL Server Profiler, run a new trace and configure it tograb the interesting events for a DAX query, like in the following picture:You need to capture four events: Query End: this event is fired at the end of a query. You can take the Query Begin event too but Iprefer to use the Query End, which includes the execution time. DAX Query Plan: this event is fired when the query engine has finished computing the query planand contains a textual representation of the query plan. As you will learn, there are two differentquery plans, so you will always see two instances of this event for any DAX query. MDX queries, on

the other hand, might generate many plans for a single query and, in this case, you will see manyDAX query plan for a single MDX query. VertiPaq SE Query Cache Match: this event occurs when a VertiPaq query is resolved by looking atthe VertiPaq cache and it is very useful to see how much of your query performs a real computationand how much just does cache lookups. VertiPaq SE Query End: as with the Query End event, we prefer to grab the end event of the queriesexecuted by the VertiPaq Storage Engine.You will learn more about these events in the process of reading the profiler log of the query. Now, it is timeto run the trace, execute the query and look at the result:Even for such a simple query, SSAS logged five different events: One DAX VertiPaq Logical Plan event, which is the logical query plan. It represents the execution treeof the query and is later converted into a physical query plan that shows the actual query executionalgorithm. Two VertiPaq scan events, i.e. queries executed by the VertiPaq engine to retrieve the result of yourquery. One DAX VertiPaq Physical Plan event. It represents the real execution plan carried on by the engineto compute the result. It is very different from the logical query plan and it makes use of differentoperators. From the optimization point of view, it is the most important part of the trace to read andunderstand and, as you will see, it is probably the most complex of all events. A final Query End event, which returns the CPU time and query duration of the complete query. All of the events show both CPU time and duration, expressed in milliseconds. CPU time isthe amount of CPU time used to answer the query, whereas duration is the time the userwaited for getting the result. Using many cores, duration is usually lower than CPU time,because xVelocity used CPU time from many cores to reduce the duration.Let us look at the various events in more detail. Looking at the event text, you will notice that they are nearlyunreadable because all of the table names are shown with a numeric identifier appended to them. This isbecause the query plan uses the table ID and not the table name. For example, the first event looks like this:

AddColumns: RelLogOp DependOnCols()() 0-0 RequiredCols(0)(''[Sales])Sum Vertipaq: ScaLogOp DependOnCols()() Currency DominantValue BLANKTable 'OnlineSales 936cc562-4bb8-46e0-8d5b-7cc9c9e8ce49' -BlankRow Aggregations(Sum)Scan Vertipaq: RelLogOp DependOnCols()() 0-142RequiredCols(134)('OnlineSales'[SalesAmount]) Table 'OnlineSales 936cc562-4bb8-46e0-8d5b7cc9c9e8ce49' –BlankRow'OnlineSales'[SalesAmount]: t]) Currency DominantValue NONEFor the sake of clarity, we will use a shortened version of the plans (which we edited manually):AddColumns: RelLogOpSum Vertipaq: ScaLogOpScan Vertipaq: RelLogOp'OnlineSales'[SalesAmount]: ScaLogOpQuery plans are represented as simple lines of text. Each line is an operator and the following lines, indented,represent the parameters of the operator. In the previous example, you can see that the outermost operatoris AddColumns, which creates the one-row table with the Sales column. The Sales column is the sum of allsales and, in fact, its operator is a Sum VertiPaq one. Sum VertiPaq scans the OnlineSales table and sumsthe OnlineSales[SalesAmount] column.The logical query plan shows what SSAS plans to do in order to compute the measure. Not surprisingly, it willscan the OnlineSales table summarizing the SalesAmount column using SUM. Clearly, more complex queryplans will be harder to decode.After the logical query plan, there are two VertiPaq queries that contain many numbers after each tablename. We removed them, for clarity. This is the original query:SET DC KIND "DENSE";SELECTSUM([OnlineSales ]), COUNT()FROM [OnlineSales 936cc562-4bb8-46e0-8d5b-7cc9c9e8ce49];While this is the cleaned version:SET DC KIND "DENSE";SELECTSUM ( [OnlineSales].[SalesAmount] ),COUNT()FROM[OnlineSales];And this is the second VertiPaq query:SET DC KIND "AUTO";SELECTSUM ( [OnlineSales].[SalesAmount])FROM[OnlineSales];

The two queries are almost identical and they differ for the Event subclass. Event subclass 0, i.e. VertiPaqScan, is the query as the SSAS engine originally requested it; event subclass 10, i.e. VertiPaq Scan Internal, isthe same query, rewritten by the VertiPaq engine for optimization. The two query are – in reality – a singleVertiPaq operation for which two different events are logged. The two queries are always identical, apartfrom a few (very rare) cases where the VertiPaq engine rewrites the query in a slightly different way.VertiPaq queries are shown using a pseudo-SQL code that makes them easy to understand. In fact, by readingthem it is clear that they compute the sum of the SalesAmount column from the OnlineSales table.After these two queries, there is another query plan:AddColumns: IterPhyOpSingletonTable: IterPhyOpSpool: LookupPhyOpAggregationSpool Cache : SpoolPhyOpVertipaqResult: IterPhyOpThe physical query plan has a similar format as the logical one: each line is an operator and its parametersare in subsequent lines, properly indented with one tab. Apart from this aesthetic similarity, the two queryplans use completely different operators.The first operator, AddColumns, builds the result table. Its first parameter is a SingletonTable, i.e. an operatorthat returns a single row table, generated by the ROW function. The second parameter Spool searches for avalue in the data cached by previous queries. This is the most intricate part of DAX query plans. In fact, thephysical query plan shows that it uses some data previously spooled by other queries, but it misses to showfrom which one.As human beings, we can easily understand that the spooled value is the sum of SalesAmount previouslycomputed by a VertiPaq query. Therefore, we are able to mentally generate the complete plan: first a queryis executed to gather the sum of sales amount, its result is put in a temporary area from where it is grabbedby the physical query plan and assembled in a one-row table, which is the final result of the query.Unluckily, in plans that are more complex this association tend to be much harder and it will result in acomplex process, which you need to complete to get a sense out of the plan. Both the logical and physical query plan are useful to grab the algorithm beneath a DAXexpression. For simple expressions, the physical plan is more informative. On the otherhand, when the expression becomes complex, looking at the logical query plan gives aquick idea of the algorithm and will guide you through a better understanding of thephysical plan.The final event visible in the profiler is the Query End event, which is basically useful to look at the totalnumber of milliseconds needed to run the query (i.e. the duration). In our example, the sum was computedin 16 milliseconds.If, at this point, you run the query once again, your profiler is still catching data and it will look like this:

In the figure you can see both queries, one after the other. The second one took 0 milliseconds to executeand this is because the first VertiPaq query has been found in the cache. In fact, instead of a VertiPaq Scaninternal, you see a VertiPaq Cache exact match, meaning that the query has not been executed: its result wasin the VertiPaq cache and no computation has been necessary.Whenever you optimize DAX, you always need to clear the database cache before executing a query.Otherwise all the timings will take the cache into account and your optimization will follow incorrectmeasurements.In order to clear the cache you can use this XMLA command, either in an XMLA query window in SSMS or inan MDX query window, as we shown below: Batch xmlns 3/engine" ClearCache Object DatabaseID Contoso /DatabaseID /Object /ClearCache /Batch You can conveniently put the clear cache command right before your query, separating them with a GOstatement, like in the following example: Batch xmlns 3/engine" ClearCache Object DatabaseID Contoso /DatabaseID /Object /ClearCache /Batch GOEVALUATEROW ("Sales",SUM ( OnlineSales[SalesAmount] ))When you will be a guru of optimizations, it will probably be useful to run the queries with and without thecache, in order to understand what usage your DAX code is doing of the cache. As of now, it is better to focuson cold cache optimizations, which require less attention.

Before we move on with more complex query plans, it is useful to look at the same query expressed with aniterator. Even if you probably learned that iterators do what their name suggest, i.e. they iterate the resultof a table, in reality the optimizer makes a great work in trying to remove the iteration from the query andtake advantage of a more optimized plan.Let us profile, as an example, this query:EVALUATEROW ("Sales",SUMX ( OnlineSales, OnlineSales[SalesAmount] ))Looking at the query plan, you will discover that it is identical to the one expressed by SUM. The optimizerdetected that the VertiPaq engine can execute directly the operation inside the iteration, so it run the queryusing the same plan as of standard SUM.This kind of optimization not only happens when you use SUMX to aggregate a column, as in this case, butalso in many cases when the expression can be safely computed using a pseudo-SQL query. For example,simple multiplications and most math expressions are resolved in VertiPaq queries. Look at this query plan:EVALUATEROW ("Sales",SUMX ( OnlineSales, OnlineSales[UnitPrice] * OnlineSales[SalesQuantity]))SSAS solves it with this VertiPaq query:SET DC KIND "AUTO";SELECTSUM ( (PFCAST( [OnlineSales].[UnitPrice] AS INT ) * PFCAST ( [OnlineSales].[SalesQuantity] AS INT)))FROM [OnlineSales];The query runs at the same speed as a regular SUM, because the engine executes the calculation during thetable scanning of the OnlineSales table.

In the first section, we introduced how to grab and read a DAX query plan. Before diving into more complextopics, it is now time to introduce the two engines that work inside DAX: the formula engine (FE) and thestorage engine (SE). Whenever a query needs to be resolved, the two engines work together in order tocompute the result. Formula Engine is able to compute complex expressions (virtually any DAX function) but, because ofits power, has one strong limitation: it is single threaded. Storage Engine is much simpler: it is able to perform simple mathematical operations on numbers,follow relationships for joins and retrieve data from memory while applying filters. Because of itssimplicity, it is a highly efficient multi-threaded engine that is able to scale over many cores.When tuning performance of a DAX expression, one of the main goals, if not the primary one, is to write thecode to maximize the usage of SE and consequently reduce the amount of work taken by FE.Roughly speaking, VertiPaq queries are executed by the SE, whereas the DAX query plan is the part of thequery that is executed by the FE. If you look again at the queries of previous sections, most of thecomputation effort was undertaken by SE. In fact, the sum of sales amount was entirely computed by aVertipaq SE Query (by the way, now you know why it is called VertiPaq SE Query), whereas all what FE hadto do was gathering the final result of the query and assemble it in a single row table. In fact, the query planof those queries was a perfect one.In order to better understand the interaction between formula engine and storage engine, now we use amore complex query, where the formula engine needs to carry on more work.EVALUATEADDCOLUMNS (VALUES ( Product[ColorName] ),"Sales",CALCULATE ( SUM ( OnlineSales[SalesAmount] ) ))This query is resolved by following two VertiPaq SE queries and query plan.The first VertiPaq query retrieves the product color and the sum of sales from the OnlineSales table:SELECTProduct.ColorName,SUM ( OnlineSales.SalesAmount ),COUNT ()FROMOnlineSalesLEFT OUTER JOIN Product ON OnlineSales.ProductKey Product.ProductKeyThe second VertiPaq query returns the list of colors from the Product table. It is useful to note that the twolists of colors can be different, in case a color exists for some products that were never sold online.

SELECTProduct.ColorName,COUNT ()FROMProductFinally, you see the query plan that, as we already know at this point, relies on temporary results returnedby the previous queries:AddColumns: IterPhyOp IterCols(0, 1)('Product'[ColorName], ''[Sales])Spool Iterator Spool : IterPhyOp IterCols(0)('Product'[ColorName]) #Records 16AggregationSpool Cache : SpoolPhyOp #Records 16VertipaqResult: IterPhyOpSpool: LookupPhyOp LookupCols(0)('Product'[ColorName]) Currency #Records 16AggregationSpool Cache : SpoolPhyOp #Records 16VertipaqResult: IterPhyOpThis query plan scans a table containing 16 color names (first Spool Iterator). Then it adds a column comingfrom the lookup of the color name in another table, which contains 16 rows composed by a color name anda currency value. Your task is to understand which of the VertiPaq queries returned those tables, so to giveto the query plan its complete shape.The first table is the result of the second VertiPaq query, i.e. the list of colors retrieved from the Producttable, whereas the second table used for the lookup is the result of the first VertiPaq query, which returnedcolor names and total of sales for each color. It is the FE and not the SE that performs the final join betweenthe two queries.This is a very good plan, because the FE is only working on small tables (16 rows each) and, of course, it isgoing to be very fast, even if single-threaded. The vast majority of the work (scanning the fact table andgrouping by product color, following the relationship) is in charge of SE.When evaluating what SE and FE execute, remember that the two engines work in a different way regardingcache usage. SSAS caches only the results of VertiPaq queries, not the result of DAX calculations. Any taskexecuted by SE goes in cache and produce faster results in following identical queries, whereas any jobexecuted by FE will repeat the computation again.If your query has a relevant portion executed by FE, this part is executed repeatedly, every time you querythe measure and, if the time spent on FE is predominant, you do not benefit too much from the VertiPaqcache. It is worth to note that MDX queries still have a calculation cache available to them. Theresult of an MDX calculation is stored in cache, whereas the result of a DAX FE calculationis not. Thus, regarding cache usage, MDX queries behave slightly better than DAX ones.Nevertheless, generally speaking, using DAX you have a better control over the algorithmused to resolve the query.

The storage engine executes simple calculations directly and the formula engine executes the more complexones, like complex joins and iterations. SE scans tables and either returns a result or spools the resulting tablefor further execution, whereas FE executes iteration over the data returned by SE.There is also a mixed scenario, which DAX often uses when the SE has to execute some non-trivial calculationsduring a table scan, but SE cannot handle them because of their complexity. In such a case, SE has the optionto call back the FE in order to compute complex expressions during the table scan.A special SE operator called CallBackDataID performs this interaction between FE and SE. Consider thefollowing query:EVALUATEADDCOLUMNS (VALUES ( Product[ColorName] ),"Sales",CALCULATE (SUMX (OnlineSales,IF (OnlineSales[SalesAmount] 10,OnlineSales[SalesAmount]))))This query is very similar to the one you used at the beginning of this section. The main difference is that,instead of summing all of the sales from OnlineSales, you only sum the ones that are greater than 10 USD. Inorder to do that, you use SUMX iterating over all the rows and getting rid of the unwanted ones.The IF inside SUMX is an issue, because SE is not able to evaluate IF conditions. In such a scenario, DAX hastwo options: It scans the OnlineSales[SalesAmount] column using a VertiPaq query and then processes the IFinside FE. This requires the spooling of the VertiPaq query result and, as such, requires memory. It scans the OnlineSales[SalesAmount] column inside SE and, during the iteration, SE asks FE toevaluate the IF on a row-by-row basis. SE invokes FE for each row, but the query memoryrequirements is much lower.If you look at the query plan, you will see this VertiPaq query:

SELECTProduct.ColorName,SUM ([CallbackDataID (IF (OnlineSales[SalesAmount]] 10,OnlineSales[SalesAmount]])) ]( PFDATAID ( OnlineSales.[SalesAmount] ) ))FROMOnlineSalesLEFT OUTER JOIN Product ON OnlineSales.ProductKey Product.ProductKeyThe query shows a CallBackDataID call. This means that, during the table scan, prior to summing values theStorage Engine invokes Formula Engine for each row, passing to it the expression to evaluate (which is our IFstatement) and the value of the SalesAmount column for the current row.One of the good things about CallBackDataID is that FE is involved in the calculation, but only as part of amore complex SE process. Because SE is multithreaded, one instance of FE is called for each thread of SE,processing the query in a multithreaded environment.Thus, with CallBackDataID you get the best of the two worlds: the richness of FE and the speed of SE.CallBackDataID is not as fast as a pure VertiPaq query, but it is much faster than a pure FE query.The only big drawback of CallBackDataID is that the cache does not store its result, even if computed by SE.Thus, if your query makes a heavy usage of the mixed environment, it will not benefit much from the cache.This might improve in future releases of SSAS but, as of now, cache usage is a limitation you need to keep inmind.It is useful to note, at this point, that you can be express the previous query in a much more efficient wayusing this syntax:EVALUATEADDCOLUMNS (VALUES ( Product[ColorName] ),"Sales",CALCULATE (SUM ( OnlineSales[SalesAmount] ),OnlineSales[SalesAmount] 10))Looking at the query plan of this DAX code, you will note that there is no CallBackDataID and the query isresolved by first looking at the values of SalesAmount that are greater than 10, and then summing the valuesfrom the fact table only for the relevant ones. This final query, in fact, is completely resolved inside SE, so itis faster and takes full advantage from the DAX cache system, resulting in optimal performance.The first VertiPaq query computes the values of SalesAmount that are greater than 10:

SELECTOnlineSales.SalesAmount,COUNT ()FROMOnlineSalesWHERECOALESCE( PFCAST( OnlineSales.SalesAmount AS INT)) COALESCE( [CallbackDataID(10)]())Once VertiPaq knows which values of SalesAmount are greater than 10, it can use the information in thefollowing query, which computes the SUM of SalesAmount already grouped by color:SELECTProduct.ColorName,SUM ( OnlineSales.SalesAmount),COUNT ()FROM OnlineSalesLEFT OUTER JOIN Product ONOnlineSales.ProductKey Product.ProductKeyWHEREOnlineSales.SalesAmount INB (103600, 399680, 239920, 2079920, 3192000, 1664000,3040000, 256000, 2400000, 333840.[2359 total values, not all displayed])The final step is gathering the different colors to perform the final JOIN in FE:SELECTProduct.ColorName,COUNT()FROMProductYou can observe the final JOIN in FE looking at the physical query plan:AddColumns: IterPhyOp ('Product'[ColorName], ''[Sales])Spool Iterator Spool : IterPhyOp ('Product'[ColorName]) #Records 16AggregationSpool Cache : SpoolPhyOp #Records 16VertipaqResult: IterPhyOpSpool: LookupPhyOp ('Product'[ColorName]) Currency #Records 15AggregationSpool Cache : SpoolPhyOp #Records 15VertipaqResult: IterPhyOp

In order to make some practice with the analysis of DAX query plans, we use the “event in progress” scenario,looking at different DAX queries to solve it. The “event in progress” scenario applies to any business thathandles “events”, where an event is something that happens at a certain point in time and has a duration. Itis aimed to count the number of events that are active during a specific period of time. For example, if theevent is an order, it happens as soon as the order is placed and lasts until it is shipped. The question is: howmany orders are active in a specific point in time? For an order to be active, today, it need to have beenplaced before yesterday and not have been shipped before today. We use the AdventureWorks Tabularsample database for this demo.A first query that solves this problem, simple both to write and understand, is the following:EVALUATEADDCOLUMNS (VALUES ( 'Date'[Date] ),"OpenOrders",COUNTROWS (FILTER ('Internet Sales','Internet Sales'[Order Date] 'Date'[Date] &&'Internet Sales'[Ship Date] 'Date'[Date])))The query runs for 22 seconds before returning. This execution time, for AdventureWorks’ size, looksdefinitely too long. In order to understand what is happening, let us look at the VertiPaq queries and the DAXquery plan.The query is resolved with two VertiPaq queries and a complex physical query plan. The VertiPaq queries arevery simple: one gathers RowNumber, Order Date and ShipDate from the Internet Sales table, the other onereturns the dates from the Date table. The issue is understanding the query plan, which is non-trivial:1 AddColumns: IterPhyOp ('Date'[Date], ''[OpenOrders])2Spool Iterator Spool : IterPhyOp ('Date'[Date]) #Records 21913AggregationSpool Cache : SpoolPhyOp #Records 21914VertipaqResult: IterPhyOp5Spool: LookupPhyOp LookupCols(0)('Date'[Date]) BigInt #Records 11326AggregationSpool Count : SpoolPhyOp #Records 11327Filter: IterPhyOp ('Date'[Date], [RowNumber], [Ship Date])8Extend Lookup: IterPhyOp ('Date'[Date], [Ship Date])9Spool Iterator Spool : IterPhyOp10('Date'[Date], [RowNumber], [Ship Date]) #Records 6737306111AggregationSpool GroupBy : SpoolPhyOp #Records 6737306112Filter: IterPhyOp ('Date'[Date], [RowNumber], [Order Date], [Ship Date])13Extend Lookup: IterPhyOp ('Date'[Date], [Order Date])14CrossApply: IterPhyOp ('Date'[Date], [Order Date])15Spool Iterator Spool : IterPhyOp ('Date'[Date]) #Records 219116AggregationSpool Cache : SpoolPhyOp #Records 219117VertipaqResult: IterPhyOp18Spool Iterator Spool : IterPhyOp19([RowNumber], [Order Date], [Ship Date]) #Records 6039820AggregationSpool Cache : SpoolPhyOp #Records 6039821VertipaqResult: IterPhyOp22LessThan: LookupPhyOp ('Date'[Date], [Order Date])23[Order Date]: LookupPhyOp ([Order Date])24'Date'[Date]: LookupPhyOp ('Date'[Date])25GreaterThan: LookupPhyOp ('Date'[Date], [Ship Date])26[Ship Date]: LookupPhyOp ([Ship Date])27'Date'[Date]: LookupPhyOp ('Date'[Date])

If you look at rows 17 and 21, you will notice two VertiPaqResults used, i.e. data coming from VertiPaq SEqueries subsequently used by the FE to calculate the expression. Row 17 reads 2191 rows, which are thedates of the calendar table, whereas row 21 reads 60938 rows with the columns RowNumber, OrderDateand ShipDate from the Internet Sales table. The row 4 uses the same set of dates used at row 17, in order toproduce the result set.Looking at the query plan in textual form is hard. We produced a nicer chart showing the query plan in agraphical way:LegendADDCOLUMNSVertiPaq ScansDate[Date]AGGREGATE(Count)FE OperatorsFILTERAdditionalInformationFILTERCROSS APPLYDate[Date]ShipDate DateOrderDate DateInternet SalesAt the bottom of the diagram, you can see the VertiPaq queries (in red). The first operation is a CROSS APPLYof the dates and the internet sales. This cross apply produces a high number of rows (namely, the Cartesianproduct between dates and sales), which are filtered by the first condition (lower FILTER) and by the secondcondition (upper FILTER). Finally, a COUNT operation aggregates the result of the filtered cross-join. Theresult set contains the date and the count of rows that survived the filter. The final ADDCOLUMNS producesthe result shown to the user.The way DAX answers to the query is somewhat different from the original expression. There is no iterationand two different operators handle the two conditions in the FILTER expressions, even if they work on thesame flow of data. By looking at the query plan, it seems that the huge number of rows generated for thecross join is responsible for the poor performance of the query.Now, which parts of the query plan do the formula engine and the storage engine execute, respectively? Inthis case, the storage engine is not really making a lot of work. Its only task is materializing part of the facttable (namely the pair of OrderDate and ShipDate) and the values of the date column in the date table. Theformula engine carries on the remaining part the work, starting from the cross join, the filtering and all theother calculations.

We can try a different formulation of the same query. This time, we use CALCULATE and we express thefiltering as two different FILTER, one for each column, relying on the fact that the CALCULATE semanticshandles automatically the AND between them.EVALUATEADDCOLUMNS (VALUES ( 'Date'[Date] ),"OpenOrders",CALCULATE (COUNTROWS ('Internet Sales' ),FILTER ( 'Internet Sales', 'Internet Sales'[Order Date] 'Date'[Date] ),FILTER ( 'Internet Sales', 'Internet Sales'[Ship Date] 'Date'[Date] )))This query produces a completely different query plan and performs much better, because it returns thesame data in less than one second. The query is resolved with four VertiPaq queries and a slightly complexDAX query plan.The first VertiPaq query returns the values of the OrderDate column from the fact table:SELECT[Internet Sales].OrderDate,COUNT()FROM[Internet Sales]The second VertiPaq query returns the values of the Date key:SELECTDate.FullDateAlternateKey,COUNT ()FROMDateFinally, the third VertiPaq query returns the values of the ShipDate column:SELECT[Internet Sales].ShipDate,COUNT()FROM[Internet Sales]The plan optimization phase executes all of the queries above. There is a fourth query executed during therun of the DAX plan:

SELECT[Internet Sales].[OrderDate],[Internet Sales].[ShipDate],COUNT()FROM[Internet Sales]WHERE[Internet Sales].[OrderDate] IN(38913.000000, 39634.000000,39560.000000, 38685.000000,[1124 total values, not allVAND[Internet Sales].[ShipDate] IN(38920.000000, 39641.000000,39567.000000, 38692.000000,[1124 total values, not all39178.000000, 39597.000000, 39141.000000,39104.000000, 38648.000000, 39067.000000.displayed])39185.000000, 39604.000000, 39148.000000,39111.000000, 38655.000000, 39074.000000.displayed])This is the first time we see a VertiPaq query used during the query execution and not during queryoptimization. This query returns the pairs of OrderDate and ShipDate with a condition that filters only the

The final event visible in the profiler is the Query End event, which is basically useful to look at the total number of milliseconds needed to run the query (i.e. the duration). In our example, the sum was computed in 16 milliseconds. If, at this point, you run the query once again, your profiler