For those that haven’t yet heard of DAX, it’s an expression language developed by Microsoft to perform calculations against PowerPivot. Stepping back one step further, PowerPivot is essentially a local Analysis Services cube that runs within Excel 2010.
I’ve heard plenty of comments from various sources about how DAX is the [multi-dimensional] query language of the future and how it’s going to kill off MDX. Ok…. well no, it’s not ok.
For starters they both exist for two different purposes. DAX is not a query language but an expression language. You can use MDX to query information from a cube and generate a pivot, you can’t with DAX as it is not a query language.
The best way to think of DAX is as an extension to Excel formulas, you can use it to perform a calculation against an existing set of cells, in this case, a PowerPivot dataset.
It is also similar to an MDX calculated member, and in fact supports a number of MDX functions (TotalYTD, ParallelPeriod etc.).
If your data is in a database: You would use SQL to query data from a database and import the results into Excel. You would then use Excel expressions/calculations to enhance the data.
If your data is in a cube: You would use an Excel pivot (or MDX query) to query the data and import the results into Excel. You then have to use a third party tool such as OLAP PivotTable Extensions to add expressions/calculations to enhance the data.
If your data is in PowerPivot: You would use PowerPivot to query the data and import the results into Excel. You would then use DAX to add calculations to enhance the data.
DAX is a fantastic expression tool, and one that provides significant power to PowerPivot, but no, it won’t replace MDX. My hope is that Microsoft will provide DAX capability for MDX queries as well, and not restrict it to PowerPivot queries. As I’ve shown in my previous blog post it’s a great expression language that would provide significant benefit to cube users.
If Microsoft provides a programming interface to PowerPivot, then you might see MDX extended with DAX (which is really what it is, but it is “trapped” in the PowerPivot add-in).
No, DAX will not replace MDX in the recent future.
This was confirmed by Donald Farmer on last week’s Tech Ed Australia – both in a session and in private. The intention is not to replace MDX, but to allow for DAX and MDX to coexist peacefully – providing different functionality for different uses as you pointed out in your post.
I dont think DAX replace MDX… even I dont think that DAX will have the sucess of MDX… end users dont have pacience and knowledge to use it… and for almost people PowerPivot is for self-service BI…
hey, nice blog…really like it and added to bookmarks. keep up with good work
I agree. I expect DAX and MDX to co-exist. There are a lot of companies who have invested heavily in supporting the MDX Query Language and it is hard to see all of them migrating to something else en masse. If you are curious to know of more companies that support MDX, check out: http://www.xmla.org/mdx.htm.