THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

Please visit the same blog at - comments are now disabled here but you can comment on the new blog (all blog posts available here are available there, too).
You can follow me on Twitter: @marcorus

#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:



    '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:



    '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

Published Monday, November 21, 2011 2:15 PM by Marco Russo (SQLBI)


No Comments
New Comments to this post are disabled

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog



Privacy Statement