Binding an Excel table to the results of an MDX query

I like using Excel tables, and one of the things that has mildly annoyed me in the past is that you can’t bind the results of an MDX query to an Excel table in the way you can do with a SQL query. I mean, pivot tables are all very well, but sometimes a plain old table is a better fit – for example, think of the cool stuff the data mining addin works can do with tables. Luckily, with a bit of hacking, you can do it… and here’s how.

The trick is to create a .odc file that contains the MDX query you want. What I did was create an odc file for a SQL query and then edit it in Notepad so the connection string pointed to SSAS and the SQL query was replaced with an MDX query. To do this, open Excel and go to the Data tab, click From Other Data Sources and then choose From Data Connection Wizard. Choose Other/Advanced on the first step, then create a connection to the OLEDB source of your choice so long as it isn’t an SSAS source. The resulting odc file will then be saved to the My Data Sources; go there and edit it in Notepad.

The hacking is fairly easy to do – a quick inspection of the odc file format reveals that there’s a lot of junk and the important stuff is contained in a bit of XML near the beginning. Here’s an example of what that XML needs to be for an MDX query:

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Query Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>MDX</odc:CommandType>

  14:    <odc:CommandText>select {[Measures].[Internet Sales Amount], 

  15:     [Measures].[Internet Tax Amount]} on  0, 

  16:     [Date].[Calendar Year].members on 1 from [Adventure Works]

  17:     </odc:CommandText>

  18:   </odc:Connection>

  19:  </odc:OfficeDataConnection>

  20: </xml>

Once you’ve done this, you can go back to Excel, go to the Data tab and click Existing Connections to open the file:

 

Choose to view this data in a table, and you get something that looks like this in your worksheet:

Incidentally, since you can query Analysis Services with a basic dialect of SQL, you can also bind an entire dimension or measure group (which are treated as ‘tables’ in SSAS SQL) or the results of a DMV to a table. Here’s an example of what the xml for the odc file looks like:

   1: <xml id=docprops><o:DocumentProperties

   2:   xmlns:o="urn:schemas-microsoft-com:office:office"

   3:   xmlns="http://www.w3.org/TR/REC-html40">

   4:   <o:Name>SSAS Table Test</o:Name>

   5:  </o:DocumentProperties>

   6: </xml><xml id=msodc><odc:OfficeDataConnection

   7:   xmlns:odc="urn:schemas-microsoft-com:office:odc"

   8:   xmlns="http://www.w3.org/TR/REC-html40">

   9:   <odc:Connection odc:Type="OLEDB">

  10:    <odc:ConnectionString>Provider=MSOLAP.4;Integrated Security=SSPI;

  11:     Persist Security Info=True;Data Source=localhost;

  12:     Initial Catalog=Adventure Works DW 2008</odc:ConnectionString>

  13:    <odc:CommandType>Table</odc:CommandType>

  14:    <odc:CommandText>Adventure Works.$Source Currency</odc:CommandText>

  15:   </odc:Connection>

  16:  </odc:OfficeDataConnection>

  17: </xml>

In this example I’m retrieving the entire contents of the Adventure Works Source Currency dimension. The output looks like this:

Since we’re all going to be doing a lot more reporting in Excel in the future, hopefully this tip will turn out useful to someone creating dashboards in Excel using SSAS data.

UPDATE: Greg Galloway just told me about another way of doing this: "If you have an ODC file you build a PivotTable, then if you drillthrough on a cell, it brings up a QueryTable with the drillthrough results. At that point, you can right click on the QueryTable and edit the query. It’s not elegant, but at least you don’t have to create an ODC file per query."