DAX (Data Analysis eXpressions) and MDX (MultiDimensional eXpressions) are the two main BI (business intelligence) query languages for SSAS (Microsoft SQL Server Analysis Services). For most of the time, these two main query languages (DAX and MDX) can return exactly the same results – at present, MDX has the edge on functionality, so some results are not yet possible in DAX, but this will change in the future. A third BI language, DMX (Data Mining eXtensions) is for more specialised work against an SSAS data mining model.
Whether you choose to write queries in DAX or MDX is often a matter of personal preference. You can use both DAX and MDX against an SSAS tabular model, you can also use both against an SSAS multidimensional cube. There is even currently limited support for writing DAX inside MDX!
It is difficult to decide between MDX and DAX as a first-choice query language. Both work against tabular models and multidimensional cubes. Both can return the same results. Of course, both are ultimately much easier and less verbose than SQL to produce meaningful BI results. It is up to you to decide between MDX and DAX as your main query language. Ideally, you should try both. Here are some very broad generalisations, but, they do not apply in every case! MDX has more functionality and is supported by more client tools – this is likely to change in the future. DAX can be faster than MDX at returning the results – although highly-aggregated results may be quicker in MDX. Both are tricky to learn at first if you are from an SQL background – but, eventually they become much more intuitive than SQL. Often DAX is preferable for tabular models and MDX for multidimensional cubes – but not always so! MDX has some limitations when used against a tabular model. DAX has some more fundamental limitations when used against a multidimensional cube – in particular, the multidimensional metadata is not always suitable – DAX likes measures to part of tables (or dimensions) and you are unlikely to have measures in dimensions in a multidimensional database, except in the specific case of fact or degenerate dimensions (even then, the measures in question are very unlikely to be suitable). It is possible to have a multidimensional database in the Standard Edition of SSAS, full SSAS tabular models (as opposed to Power Pivot) require the Business Intelligence or Enterprise Edition of SSAS. A list at the end of this article illustrates client tool support for DAX and MDX.
This blog shows how to use both MDX and DAX to perform a common BI requirement, namely, how to return the three best-selling products for each year. The data is from the Adventure Works 2012 Tabular Model, which you can download from codeplex (you could also easily adapt the MDX examples for the Adventure Works 2012 Multidimensional database – also from codeplex – the DAX examples will not all work against the multidimensional metadata). Some simplifications have been made in the syntax to best illustrate the similarities and differences between MDX and DAX – some of the DAX, in particular, could be better-written from a performance point-of-view. The DAX and MDX queries were all written and executed in the SSMS (SQL Server Management Studio) MDX query editor. They are generally in pairs so you can run the MDX and DAX together and have two result sets at the same time, you will need GO between each query for this to work. Let’s start by looking at sales for each year written in MDX then DAX (where the MDX and DAX results are the same, only one result is shown – where the results are different, the DAX results are shown after the MDX results):
-- MDX sales for 2005 to 2010 select [Measures].[Internet Total Sales] on columns, [Date].[Calendar Year].[Calendar Year] on rows from [Model] go -- DAX sales for 2005 to 2010 evaluate summarize('Date', 'Date'[Calendar Year], "Sales", [Internet Total Sales])
2005 $3,266,373.66
2006 $6,530,343.53
2007 $9,791,060.30
2008 $9,770,899.74
2009 (null)
2010 (null)
Now to hide 2009 and 2010 when there were no sales:
-- MDX non empty, hides years with no sales 2009 2010 select [Measures].[Internet Total Sales] on columns, non empty [Date].[Calendar Year].[Calendar Year] on rows from [Model] go -- DAX 'Internet Sales' not 'Date', hides years with no sales 2009 – -- 2010 evaluate summarize('Internet Sales', 'Date'[Calendar Year],"Sales", [Internet Total Sales])
2005 $3,266,373.66
2006 $6,530,343.53
2007 $9,791,060.30
2008 $9,770,899.74
The table used by the DAX query has been changed from Date to Internet Sales – in general, in DAX it is better to use a ‘fact’ rather than a ‘dimension’ table in a SUMMARIZE query. Now to list our best-selling products (results have been truncated for space reasons):
-- MDX product sales descending for all time Mountain-200 Black -- sizes 46 and 42 and Silver 38 select [Measures].[Internet Total Sales] on columns, order([Product].[Product Name].[Product Name], [Measures].[Internet Total Sales], desc) on rows from [Model] go -- DAX product sales descending for all time Mountain-200 Black -- sizes 46 and 42 and Silver 38 evaluate summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]) order by [Internet Total Sales] desc
Mountain-200 Black, 46 $1,373,469.55
Mountain-200 Black, 42 $1,363,142.09
Mountain-200 Silver, 38 $1,339,462.79
Mountain-200 Silver, 46 $1,301,100.10
Mountain-200 Black, 38 $1,294,866.14
Mountain-200 Silver, 42 $1,257,434.57
Road-150 Red, 48 $1,205,876.99
Road-150 Red, 62 $1,202,298.72
Road-150 Red, 52 $1,080,637.54
And just the three best-selling products across all time:
-- MDX best 3 products for all time select [Measures].[Internet Total Sales] on columns, topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales]) on rows from [Model] go -- DAX best 3 products for all time evaluate topn (3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales]) order by[Internet Total Sales] desc
Mountain-200 Black, 46 $1,373,469.55
Mountain-200 Black, 42 $1,363,142.09
Mountain-200 Silver,38 $1,339,462.79
This time we use the MDX and DAX CROSSJOIN functions to cross join the years with the three best products:
-- MDX crossjoin -- same three products for every year -- 2005 missing select [Measures].[Internet Total Sales] on columns, non empty crossjoin ([Date].[Calendar Year].[Calendar Year], topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales])) on rows from [Model] go -- DAX crossjoin -- same three products for every year -- sales for each product is total across all time, and we have -- 2005, 2009, 2010! evaluate crossjoin(all('Date'[Calendar Year]), topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales]) ) order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc
2006 Mountain-200 Black, 46 $163,927.86
2006 Mountain-200 Black, 42 $159,829.66
2006 Mountain-200 Silver, 38 $136,713.69
2007 Mountain-200 Black, 46 $679,399.00
2007 Mountain-200 Black, 42 $627,269.94
2007 Mountain-200 Silver, 38 $613,471.64
2008 Mountain-200 Black, 46 $530,142.69
2008 Mountain-200 Black, 42 $576,042.49
2008 Mountain-200 Silver, 38 $589,277.46
2005 Mountain-200 Silver, 38 1339462.7904
2005 Mountain-200 Black, 42 1363142.0934
2005 Mountain-200 Black, 46 1373469.5482
2006 Mountain-200 Black, 46 1373469.5482
2006 Mountain-200 Black, 42 1363142.0934
2006 Mountain-200 Silver, 38 1339462.7904
The second DAX results are truncated but include 2007 to 2010 as well. Both the MDX and the DAX return the incorrect results! The same three products appear to be the three best-sellers for each year. The MDX result is missing 2005 when there were sales – see our queries earlier on. The truncated DAX results include 2005, but also 2009 and 2010 when there were no sales. Also, the DAX result is showing total sales across all years for each individual year! What really happened in 2005:
-- MDX for just 2005 -- road bikes not mountain bikes, as mountain bikes not sold in 2005 select [Measures].[Internet Total Sales] on columns, crossjoin([Date].[Calendar Year].[Calendar Year], topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales])) on rows from [Model] where [Date].[Calendar].[Year].[2005] go -- DAX for just 2005 -- road bikes not mountain bikes, as mountain bikes not sold in 2005 evaluate crossjoin(filter(all('Date'[Calendar Year]), 'Date'[Calendar Year] = 2005), calculatetable(topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales]), 'Date'[Calendar Year] = 2005)) order by [Internet Total Sales] desc
2005 Road-150 Red, 62 $593,992.82
2005 Road-150 Red, 48 $547,475.31
2005 Road-150 Red, 44 $500,957.80
Both queries return the correct results for just 2005. The reason why the earlier CROSSJOIN results for all years are wrong is due to how MDX and DAX both handle ‘filter context’. Filter context is a difficult concept in MDX (though important) and a very difficult concept in DAX (though vitally important). Filter context warrants a blog (or ten blogs) on its own – maybe for a later blog or blogs. In the meantime, you are referred to the BISM Tabular Model book by Marco Russo, Alberto Ferrari, and Chris Webb. In this blog, I merely show you the solution – by using the MDX and DAX GENERATE functions – which propagate the filter context from the year onto the three best-selling products:
-- MDX generate 1 of 1 -- now 2006 shows road bikes and 2005 is back also with road bikes -- and 2007 and 2008 show slightly different mountain bikes select [Measures].[Internet Total Sales] on columns, non empty generate([Date].[Calendar Year].[Calendar Year], crossjoin([Date].[Calendar].currentmember, topcount([Product].[Product Name].[Product Name], 3, [Measures].[Internet Total Sales]))) on rows from [Model] go -- DAX generate 1 of 2 -- now 2005 shows road bikes not mountain bikes evaluate generate(all('Date'[Calendar Year]), topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales])) order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc go -- DAX generate 2 of 2 -- hiding zero sales evaluate filter(generate(all('Date'[Calendar Year]), topn(3, summarize('Internet Sales', 'Product'[Product Name], "Sales", [Internet Total Sales]), [Internet Total Sales])), [Internet Total Sales] > 0) order by 'Date'[Calendar Year] asc, [Internet Total Sales] desc
2005 Road-150 Red, 62 $593,992.82
2005 Road-150 Red, 48 $547,475.31
2005 Road-150 Red, 44 $500,957.80
2006 Road-150 Red, 48 $658,401.68
2006 Road-150 Red, 52 $608,305.90
2006 Road-150 Red, 62 $608,305.90
2007 Mountain-200 Black, 46 $679,399.00
2007 Mountain-200 Black, 42 $627,269.94
2007 Mountain-200 Silver, 46 $617,531.62
2008 Mountain-200 Silver, 38 $589,277.46
2008 Mountain-200 Black, 38 $587,517.44
2008 Mountain-200 Silver, 46 $579,997.50
At last, we got there. Both the MDX and the second of the two DAX queries return the results we are looking for. The first of the two DAX queries is also largely correct – only it includes 2009 and 2010 for all products, which are all empty.
Finally, here is a summary of where and how you can use DAX and MDX queries in full, or in part (DAX and MDX expressions/calculations have many more options):
SSMS – MDX – in MDX query editor – MDX queries against multidimensional cubes
SSMS – DAX – in MDX query editor – DAX queries against multidimensional cubes
SSMS – MDX – in DMX query editor – MDX queries against multidimensional cubes – but no metadata
SSMS – DAX – in DMX query editor – DAX queries against multidimensional cubes – but no metadata
SSMS – MDX – in MDX query editor – MDX queries against tabular models
SSMS – DAX – in MDX query editor – DAX queries against tabular models
SSMS – MDX – in a DAX query – currently not supported
SSMS – DAX – in a MDX query – limited supported – for calculated measures only
SSRS Report Designer – MDX – in MDX query designer – click the set-square last button (Design Mode)
to paste MDX query written in SSMS
SSRS Report Designer – DAX – in DMX query designer – click the set-square last button (Design Mode)
to paste DAX query written in SSMS
SSRS Report Builder – MDX – click the set-square last button (Design Mode) to paste MDX query
written in SSMS
SSRS Report Builder – DAX – not supported
MDX Studio (unsupported free download) – MDX – for writing MDX queries – does not support
tabular models
DAX Studio (unsupported free download) – DAX – for writing DAX queries from Excel – supports
multidimensional, full SSAS tabular, and Power Pivot tabular
Power Pivot – MDX – paste MDX as you import
Power Pivot – DAX – paste DAX as you import
Excel – MDX – Named Sets, Calculated Measures, Calculated Members, also OLAP Pivot Table
Extensions (unsupported free download)
Excel – DAX – currently not directly supported
Power View – MDX – currently not directly supported – but supports multidimensional via DAX
Power View – DAX – currently not directly supported – DAX is generated automatically
PerformancePoint – MDX – queries for analytic grids and charts
PerformancePoint – DAX – currently not directly supported
.NET – MDX – fully supported
.NET – DAX – fully supported
By Art Tennick
Art Tennick is a Microsoft Business Intelligence trainer, consultant and author based in the UK with over 15 years of BI exposure and the author of 20 published books and hundreds of magazine articles. To contact Art, email [email protected] or visit him on LinkedIn.
Have a comment? Let us know below, or send an email to [email protected]
