THE SQL Server Blog Spot on the Web

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

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
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:

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

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

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

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

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement