I've worked with MSAS a bit and I've had engineers who worked for me tell me things that confirm my brief experiences as I got further from the technology.
Most people who get up the rather steep curve for MDX admire it for its elegance and prefer it to SQL on that basis. Nevertheless the experience is that it is generally not worth it to learn the language if you have appreciable experience in SQL.
If you have become something of an expert in tuning databases in the generations of products before the Greenplum and Vertica days, say with Essbase, Microstrategy, Teradata, Oracle Express or Sybase ASE, then you will have some familiarity with arcane 4G languages and dialects of SQL without many cross-product similarities. MDX was the language destined to solve all that, a sort of OLAP Esperanto. Unfortunately, the number of programmers deciding to hack SQL to perform against this and that sort of schema tended to dominate the few cross-platform specialists. In the end, it is my opinion that the lack of a predominating visualization stack obviated the need for widespread MDX adoption. As fat visual programming OLAP clients matured, enterprise customers began demanding thin clients. As full featured stacks became available, developers wanted LAMP, and so on. Now we are met on a battlefield testing whether PHP and MySQL will long endure, with people like me wondering how they ever got started considering the maturity and performance of products like Sybase and Essbase.
My direct experience is that flatly, for applications of any sophistication, on MSAS, stored procedures with T-SQL always performed better than their functional equivalents in MDX. In their aborted product stack, Performance Point, Microsoft engineers created a middle language PEL(?) that would supposedly choose which language was best suited for a task and then generate that code. All jokes about code-generators aside, my engineers always had to second guess PEL and ended up writing it all in T-SQL. So as a practical matter, there was no sense in learning MDX if you had already mastered T-SQL on the platform for which MDX was specifically designed. It would have been nice if MDX performed with speed commensurate with its elegance, but it simply didn't. This was two years ago.
I know MDX lives on in Essbase but that Essbase expresses it differently than does MSAS. It does rather boil down to 'it depends', because language to language different platforms have different strengths, etc.
I expect that we will not learn the definitive answer to this question until there is a shakeout of DBs that survive the transition to cloud infrastructure. And in that regard I think a Greenplum or a Vertica or a Hadoop-based solution will win out. In other words it won't come down to the semantic layer. The market never forced it to because there is no real OLAP standard (chicken or egg?).
In the end I say MDX iff you love MDX.
