0

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?

2replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Hello Samuel,

    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:

    1. It behaves as SQL query and we expect a Multidimensional Behaviour.
    2. Need use of lot of functions for even few small requirements.
    3. Need to know the appropriate relationship between the tables used (in case of non-related table in query, no result will come)
    4. Difficult to handle Non Empty behaviour through query
    5. 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.)
    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
    MDX -
    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

     

    Assumptions:
    Project is on SQ Server 2012, BI Semantic Tabular Model. Else DAX will not work on Multidimensional SSAS solution anyhow.

    Conclusion:
    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.
    Kind Regards,
    Yakov.

    Reply Like 2
  • 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:

    http://tomislav.piasevoli.com/2010/05/03/dax-vs-mdx-vs-t-sql/

    http://sqlbits.com/Sessions/Event9/MDX_and_DAX-compare_and_contrast

     

    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 Hormaechea 

     

     

     

    Reply Like
login to reply
Like Follow
  • 1 yr agoLast active
  • 2Replies
  • 5401Views
  • 3 Following