Compare DAX vs MDX

Once I was asked to give an opinion on MDX vs DAX. But we know in Software world everything has some pros and cons when compared to other, otherwise if it just have cons it would be thrown to dustbin (say deprecated).

Well I tried a lot to look for direct comparison of MDX and DAX and to bring my best opinion as per Project Requirements but as usual,no answer in Google for “Compare MDX vs DAX”.

I prepared a comparison as per my experience in both the languages. Keep in mind DAX is not a query language but it can be used as one, using DAX Query Syntax Reference.

So below are some comparison points and I wish these will help you out.

S.no

DAX

MDX

Prefer

1

Performance

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. Performance is good but when we compare with DAX (on tabular model), it’s a bit low.

DAX

2

Support:

Support In-Memory, DirectQuery and Hybrid query modes of Tabular Model No support for DirectQuery modes of Tabular Model.       DAX
3

Query Language:

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 A well known query language and being used in SSRS and SSMS as separate query option of MDX.

MDX

4

Ease of writing:

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.
Easy to write as compared to DAX.

MDX

5

Reporting supports:

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) Works with SSRS, PPS and ADOMD.net code and lot many other reporting tools and codes.

MDX

6

Support for Ad-hoc reporting:

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

Depends on end user, if he/ she know DAX.

7

Data Mining:

Can’t be used for Data Mining purposes even in Excel. MDX is required for Data Mining

MDX

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

Note: I will soon post on query behaviors of MDX and DAX. Saying DAX has better performance than MDX is not always true.

Advertisements

Posted on July 6, 2012, in BI Semantic Tabular Model, DAX, MDX and tagged , , , , , . Bookmark the permalink. 2 Comments.

  1. Fine way of telling, and good article to take facts regarding my presentation focus, which i am going to convey
    in university.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: