DAX Table Queries in Excel

Although the discovery of what Rob Collie calls a live specimen of Bigfoot itself i.e. automated refresh of PowerPivot models from VBA is indeed welcome, the activities that led to its discovery were intended to serve a different purpose, namely the use of DAX table queries in Excel.

Dax table queries are a new feature of DAX (part of the Denali PowerPivot upgrade) that in my opinion takes the DAX language away from its (very useful) pivot table birth-place and makes it a truly powerful alternative to SQL/MDX for analytical reporting.

It was Chris Webb’s excellent  series of articles on DAX that first wetted my appetite for this feature and then hinted that it could be accessed from Excel and ultimately automated if need be.

An so it can, the VBA code that I used to query DMVs and send XMLA process commands to refresh the PowerPivot model can also be used for MDX and DAX tabular queries.

And it gets better; you don’t need to use code to do this, there’s a very simple trick which will expose this functionality in plain Excel.

This trick only applies to Denali as it takes advantage of one of Denali’s most welcome new features, show detail (aka drill-thru). (An alternative method which can be used in PowerPivot V1 is detailed here  – if using V1, only DMV and MDX queries would be possible).

First thing to do is (in a PowerPivot Denali powered workbook) make a pivot table (doesn’t matter of what), such as this …

… then right-click and show-detail on any measure cell, which should result in a new sheet with a Excel table showing the drill-through, like so …

… if you then right-click, pick Tables and then Edit Query, you’ll see the MDX query associated with the table …

… you’re now free to edit this query, replacing it with whatever takes your fancy, here’s an MDX tabular query …

… you can also issue DMV queries such as “Select * from [Model].[$InvoiceHeaders]” or its equivalent DAX table query …

Note: both the DMV and DAX ‘dump a dimension’ are unlikely to work for very large tables (such as a multi-million row facts) unless you have loads of free memory and the ability to use it (i.e. 64 bit), without triggering a “could not allocate memory error”.

DAX is not limited to DMV type queries but can be used to express very complex OLAP queries, Being able to define new  temporary just-in-time Measures (and Variables) for use within a particular queries will help not just to produce the result required, but will make DAX easier to master for newcomers.

The current version of HAMMER only allows DMV and MDX queries as it restricts the “ADO” command  to “SELECT” statements; the next release will remedy that.

I’m at present working on an example of using HAMMER to build a DMV/MDX/DAX Web Service such as I did with MicroETL in Python-Powered PowerPivot. This time using IronPython and the .NET’s HttpListener and simply passing the queries straight-through to PowerPivot without any need for Excel Pivot Table involvement.  So stay tuned … Update: (here it is)


8 responses to “DAX Table Queries in Excel

  1. Pingback: xlns » DAX QuerieTables in Excel via VBA

  2. Great post… thnx
    found a easy way now to get data from PowerPivot table in excel via vba.
    If you are interested I posted it here: http://xlns.lamkamp.nl/?p=241

  3. That’s a nice way to fill an Excel table and will save having to issue a refresh (to ensure that the ADOConnection of the Workbook.Connections object is active) as would be the case using my ADODB Open method.

    You had me excited there for a moment when I saw the connection string in your code, I’d thought you’d managed to open a direct ADODB connection with PowerPivot! However, this method of opening a connection to the PP engine still requires going through the Excel Object model; looks like via-the-Object-Model is the only way to do it.


  4. Pingback: Exposing an Excel PowerPivot model as a Web Service | Gobán Saor

  5. Pingback: DAX Queries in Excel 2013 « The Data Specialist

  6. Pingback: Cross Join Three Tables via DAX Query to seed a Date Dimension. | Gobán Saor

  7. Pingback: Data from PowerPivot via RSS Feed into Excel

  8. Pingback: DISCOVER_COMMANDS DMV – Improve Drillthrough & Learn DAX Query | Gobán Saor