SQL PASS: DAX, the language of PowerPivot

PowerPivot, the topic that has been getting all the big attention here at PASS, has introduced ‘yet another’ expression language to define calculations. So, introducing DAX, Data Analysis eXpressions.

The idea behind this language is to provide OLAP type query functionality to the Excel end user. So the basic syntax looks and feels exactly like standard Excel functions. A set of Excel functions have been implemented (80 in total), with additional functions from the Vertipaq engine to allow OLAP functionality.

Ed Note: The additional functions in DAX actually come directly from the VertiPaq engine. Meaning that it is not a wrapper for MDX, it is actually the language used to define and execute the calculation within the engine.

DAX can be used in two places

  • Calculated Columns
    • Similar to creating measures in fact tables, or in the DSV in SSAS. Basically used for line-by-line calculations. These values are calculated and stored at design time, and not recalculated at execution time.
  • Measures
    • Similar to Calculations in SSAS. Calculated at run time, in the context of the current pivot table or query. Best place to define ratios or custom aggregations that don’t make sense in a line-by-line method.

IMHO, the single most important aspect of this language is the underlying relationships defined within the PowerPivot model. As most calculations require some form of navigation between tables, whether it be date calculations or aggregations over groups.

The basic groups of functions that are supported include:

  • Excel Functions
  • Relationships
  • Aggregation
  • Table Functions
  • Time Intelligence

The Time Intelligence ones are by far the most fun ones for me. it is possible to define MDX-type calculations including Year on Year growth, Period to Date and Parallel Period navigation. A bit of a different model though, as the “time dimension” is assumed based on date fields. Only limitation is that it assumes calendar time.

e.g. YOY Growth looks like

Fact([Sales]) – Fact([Sales])(DateAdd(Time[Date], -1, YEAR)

Overall impression is very positive. Full descriptions available at http://blogs.msdn.com/powerpivot/archive/2009/10/01/introduction-to-data-analysis-expressions-dax-in-gemini.aspx

Advertisements

,

  1. Leave a comment

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: