#DAX Query Plan in SQL Server 2012 #Tabular

The SQL Server Profiler provides you many information regarding the internal behavior of DAX queries sent to a BISM Tabular model. Similar to MDX, also in DAX there is a Formula Engine (FE) and a Storage Engine (SE). The SE is usually handled by Vertipaq (unless you are using DirectQuery mode) and Vertipaq SE Query classes of events gives you a SQL-like syntax that represents the query sent to the storage engine.

Another interesting class of events is the DAX Query Plan, which contains a couple of subclasses (Logical Plan and Physical Plan). I’d like to know more about internals of query plans, but there is still no documentation on that. However, you can still get some hint by observing its content for different DAX queries returning the same results.

For example, you should know that using RELATEDTABLE( table ) returns the same as CALCULATETABLE( table ). (in case you don’t know, read my PowerPivot book – DAX is coming, learning it early on PowerPivot is smart move.) But are they really equal? Should we prefer one against the other? By examining the SQL Profiler events, now I can say they are identical.

For example, this query:

EVALUATE

ADDCOLUMNS(

    ‘Product Category’,

    “SubCategories”, COUNTROWS( RELATEDTABLE( ‘Product Sub-Category’ ) ),

    “Products”, COUNTROWS( RELATEDTABLE( ‘Product’ ) ) )

produces exactly the same query plan (and calls to the storage engine) as the following one:

EVALUATE

ADDCOLUMNS(

    ‘Product Category’,

    “SubCategories”, COUNTROWS( CALCULATETABLE( ‘Product Sub-Category’ ) ),

    “Products”, COUNTROWS( CALCULATETABLE( ‘Product’ ) ) )

At this point, my suggestion is to favor the semantic – if you don’t have to put other filters, use RELATEDTABLE to simply follow the relationship!

Now back to writing the next book on BISM Tabular…