MDX vs DAX
I've read numerous articles on this topic. But its still blurry. Is there a definitive guide of the differences between these?
I know that DAX is newer than MDX. So I'm assuming its better. Is this true?
Both DAX and MDX can be used to query PowerPivot and Tabular models, however only MDX may be used to query multidimensional SSAS models (cubes) in versions of SSAS up to SQL Server 2012 RTM. Future versions of SSAS (both multidimensional & tabular models) will support DAX natively.
DAX is not a subset of MDX, but a new formula language that is considered an extension of the formula language in Excel.
As for comparison,
try this link https://bistuffwithdigven.com/2012/07/06/comparedaxvsmdx/
The following table is from there.
Performance: (Prefer DAX but not on all cases)
DAX - has some part of better performance on Tabular Model. I was looking at CPU Time comparison for same result set given by MDX and DAX. DAX wins all the time.
MDX - Performance is good but when we compare with DAX (on tabular model), it’s a bit low.
Support: (Prefer DAX)
DAX - Support In-Memory, DirectQuery and Hybrid query modes of Tabular Model
MDX - No support for DirectQuery modes of Tabular Model.
Query Language: (Prefer MDX)
DAX - Not a query language, still we can leverage it by few work around (using tabular expressions):a) Use it as query language in SSRS by writing it in place of DMXb) Writing and executing it on MDX pane of SSMS to validate the query and data
MDX - A well known query language and being used in SSRS and SSMS as separate query option of MDX.
Ease of writing: (Prefer MDX)
DAX - Difficult to write as compare to MDX (although many blogs says it’s easy, with perspective of end user).Reason:
- It behaves as SQL query and we expect a Multidimensional Behaviour.
- Need use of lot of functions for even few small requirements.
- Need to know the appropriate relationship between the tables used (in case of non-related table in query, no result will come)
- Difficult to handle Non Empty behaviour through query
- Difficult to manipulate filtration in queries. Specially multi-valued filters.
MDX - Easy to write as compared to DAX.
Reporting supports: (Prefer MDX)
DAX- Long way to go for this investigation. (Decide your reporting tools requirement and investigate or query here in blog)It works with SSRS ( in place of DMX) and ADOMD.net (please refer the link)
MDX- Works with SSRS, PPS andADOMD.net code and lot many other reporting tools and codes.
Support for Ad-hoc reporting: (Depends on end user, if he/ she know DAX.)
a) Power View Reporting: No user interaction is required for querying the Tabular Model. So no use of DAX or MDX.
b) Power Pivot: End user needs to know DAX for creating new measure
a) Power View Reporting: No use.
b) Power Pivot: No use
Data Mining: (Prefer MDX)
DAX - Can’t be used for Data Mining purposes even in Excel.
MDX - MDX is required for Data Mining
Project is on SQ Server 2012, BI Semantic Tabular Model. Else DAX will not work on Multidimensional SSAS solution anyhow.
Out of 7 points, 4 support MDX and 2 DAX.
But along with that, I believe for a long run MDX is better. It’s been used in many legacy system, developers are more conformable and it provides more flexibility on different reporting services (as per my knowledge).
I hope this helps in someway and not getting you confused even more.
MDX was the "original" BI query language available with the first editions of Microsoft OLAP. DAX came to be with the first iterations of Microsoft tabular models.
One is not better than the other, but they do have various areas where one is stronger or easier to use. I've attached a quick list (MDXDAX.txt) for reference purposes.
The great news is that with BI Office, it can blend the power of both. When browsing an OLAP cube, we provide wizards to abstract the creation of logic built on MDX. For a tabular model the experience is similar, where BI Office provides an efficient translation of our wizards to create logic/queries on a tabular model. We also have a full MDX editor to create any custom MDX you would like. Custom DAX can be created during and after the Data Modeler data model creation wizard.
More third-party resources include:
And another great article on OLAP vs. Tabular. Again, BI Office lets you blend content from both in a seamless fashion. http://www.pyramidanalytics.com/pages/resources/analytics-reports/olap-analytics.aspx
Thank you - John HormaecheaReply