Analysis Services answers to query in MDX, in DAX (by now just for Tabular models) and has a limited capability to answer SQL queries. It is not useful for any development or client tool, but I wanted to write a blog post on it in order to be able to retrieve these information I gathered during study of DAX and MDX queries sent to Tabular ...

A few weeks ago I received a question that inspired me in writing this article about how to handle data at different granularities in PowerPivot and BISM Tabular. I think this is a common pattern when you have a budget table that contains data at an aggregated level (like month and product category) and you want to compare it with sales that are ...

Jeffrey Wang started the 2012 (or finished the 2011, depending on your time zone) by publishing the first article dedicated to DAX Query plan. While I look forward to reading next articles, it is interesting the explanation of what is the relationship between MDX and DAX in Analysis Services 2012. An MDX query sent to a Tabular model is not ...

In DAX you don’t have the LIKE operator and you have to use SEARCH instead. However, performance are not very good and it is better to use LEFT and RIGHT if you just need to compare the initial (or ending) match of a string. I just wrote an article about string comparison in DAX. During my exploration of LIKE replacement functions in DAX, I have ...

I wrote a short article about query projection in DAX. To make the story short, you should always use SUMMARIZE in order to remove columns from a table in a query. Thus, if you just want three columns from a table, instead of writing this in SQL: SELECT Col1, Col2, Col3 FROM Table you should write this in DAX: EVALUATE SUMMARIZE( Table, ...

You should have already read that DAX is simpler than MDX. It’s true, and sometime you have to pay a price for simplicity. I am used to say that DAX is somewhere between SQL and MDX. In fact, SQL requires you to put any relationship in the query (using JOIN or subqueries) whereas MDX can only leverage on existing relationships in the underlying ...

The video of the session Vertipaq vs OLAP: Change Your Data Modeling Approach that I delivered at SQLBits 9 is finally available! You can find here the video of sessions I and Alberto Ferrari delivered at the last two SQLBits. Last week we also submitted some proposals for SQLBits 10 in London, which will be a big SQLbits edition. Here is the ...

One of the major criticism to DAX is the lack of a decent editor and more in general of a dedicated IDE, like the one we have for T-SQL or MDX. Well, this is no more true. On Codeplex a very interesting an promising Visual Studio 2010 extension has been released by the beginning of November 2011: http://daxeditor.codeplex.com/ Intellisense, ...

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 ...

In September 2006 I had announced in this blog the release of the first version of The Many-to-Many Revolution, a whitepaper that describes how to leverage the many-to-many dimension relationships feature that had being available since Analysis Services 2005. The paper contains many generic patterns that can be applied in many common data ...