PowerPivot Has A Data Model. Is There A Language Too?

Well … it turns out there is and its name is DAX (Data Analysis EXpressions). The fundamental purpose of DAX is to help you extend the basic data model of PowerPivot by defining calculated columns and measures.

Some might question why do we need yet another language when we already have so many in Microsoft’s BI world? It’s enough to think about T-SQL, MDX, DMX, RDL just to name a few. As surprising as it sounds, the reality is that we do need a new language when it comes to multidimensional analysis. Isn’t there a language named MDX (Multi-Dimensional EXpressions) already in this domain? one might ask. The answer is obviously positive. MDX is a very powerful and flexible language used in SQL Server’s Analysis Services to extend multidimensional data models. The problem with it is that this power and flexibility come with a price: MDX scripts get very quickly very complicated. In fact, they get so complicated that in my opinion there is no way an information worker or an analyst (who are supposed to work with PowerPivot) will ever be able to master MDX in order to be able to leverage its power with the PowerPivot data model.

Consequently, Microsoft faced a tough question: do we use a flavor of MDX to extend PowerPivot data models or do we introduce yet another language? Eventually, they settled for the second option, which is the best one given the circumstances. Enters DAX, the language specifically designed to meet three major goals:

• Abstract some tough-to-grasp multidimensional concepts
• Provide some primitives similar to MDX for extending the data model
• Shield PowerPivot users from the complicated world of multidimensional models and MDX while still enabling them to extend PowerPivot data models in powerful ways

There is an inherent trade-off generated by DAX’s relative simplicity: it’s not as powerful as MDX. This means there will be things that you can achieve with MDX but you cannot with DAX. Given this, I’d still argue that things which cannot be expressed via DAX will probably not be needed too often by PowerPivot users. On the other hand, using DAX and building on the abstraction level provided by PowerPivot itself they will be able to create fairly complex extensions to the data model without the need to plunge into the details of multidimensional programming with MDX.

You can read more about the Data Analysis Expressions language here.