From what I gather, MDX is typically used for OLAP multidimensional data stores. In SSAS 2012, it looks like DAX is used for tabular models, but MDX is supported as well. So why are there two query languages available for one system? Furthermore, which is the recommended one to use for tabular model and why? Is DAX faster than MDX in these scenarios?
I've yet to install or play with SSAS 2012 yet so if I might be missing something.
DMX is a data mining query language, which you use to identify inherent patterns in the data. User Smith is correct regarding which languages can be used on which platform. DAX utilises the xVelocity in-memory analytics engine which performs faster on average than the multidimensional cube technology (ROLAP, MOLAP). MDX is not as fast as DAX queries on a tabular model.
I don't have all the answers but from my understnading this is how it goes.
MDX is for the developer. DAX is for the superuser (excel user/ business user).
Microsoft thought that MDX was too complicated for a business user so they conjured up DAX which is very similar to Excel functions, and they thought it would be quick for the end user to catch on to this if they were already familiar with Excel.
I don't know which is necessarily faster, I think it is more about what you are comfortable using. Personally, I use both languages, but I really only tend to use DAX in powerpivot and excel (obviously). So, I am more comfortable using MDX wherever I can.
HTH, oh and by the way there is also DMX which I have yet to use but believe this can also be used with SSAS. And yes I agree two/three query languages for one system is a pretty confusing thought but once you read up on it and use them a bit it makes a little more sense because their situational usage is so much different.
