Report Portal

Binding an Excel table to the results of an MDX query

Reposted from Chris Webb's blog with the author's permission. 

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:

image 

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

image

 

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:

image

 

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.


chris-webb

Chris has been working with Microsoft BI tools since he started using beta 3 of OLAP Services back in the late 90s. Since then he has worked with Analysis Services in a number of roles (including three years spent with Microsoft Consulting Services) and he is now an independent consultant specialising in complex MDX, Analysis Services cube design and Analysis Services query performance problems. His company website can be found at http://www.crossjoin.co.uk and his blog can be found at http://cwebbbi.spaces.live.com/ .


Tags: excel, mdx

 

2007-2015 VidasSoft Systems Inc.