Chris Webb's BI Blog

Analysis Services, MDX, PowerPivot, DAX and anything BI-related

Binding an Excel table to the results of an MDX query

with 19 comments

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.

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."

Written by Chris Webb

December 18, 2009 at 11:15 pm

Posted in MDX

19 Responses

Subscribe to comments with RSS.

  1. Thanks for the solution, but it is not correctly working. to get it working i also needed to change the connection string from Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Data Source=server_name;Initial Catalog=catalog_name
    to Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=catalog_name;Data Source=server_name;Safety Options=2;Locale Identifier=1031

    AN_AS2008

    January 12, 2011 at 3:26 pm

  2. [...] a comment » One of the more popular posts on my blog is one I wrote just over a year ago on binding the results of an MDX query to a table inside Excel. I was thinking about it again recently when I was looking at the list of DMVs (=Dynamic Management [...]

  3. An alternative Example using direct vba automation to accomplish the MDX Query to ListObject/QueryTable

    Option Explicit

    Sub Create_Report_Based_On_ListObject_QueryTable()

    ‘The connection string.

    Const stCon As String = “OLEDB;Provider=MSOLAP.4;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=CUBENAME;Data Source=SERVERNAME;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error”

    ‘The MDX Expression Can be any MDX Expression but here is an example.

    Const stMDX As String = “SELECT NON EMPTY ({({[Date].[Calendar Quarter Name].[All].children})}) DIMENSION PROPERTIES PARENT_UNIQUE_NAME ON COLUMNS FROM [Breathout Test] WHERE ([Region].[Parent RegionKey].&[10],[DataRow].[Parent DataRowKey].&[99],[Measures].[Sales Amount Quarterly Hidden]) CELL PROPERTIES VALUE, FORMAT_STRING, LANGUAGE, BACK_COLOR, FORE_COLOR, FONT_FLAGS”

    ‘Excel variables.

    Dim wbBook As Workbook
    Dim wsSheet As Worksheet
    Dim rnStart As Range
    Dim qtTable As QueryTable

    Set wbBook = ActiveWorkbook
    Set wsSheet = ActiveSheet
    Set rnStart = wsSheet.Range(“A1″)

    ‘The creation of the ListObject and the associated QueryTable.

    Set qtTable = wsSheet.ListObjects.Add( _
    SourceType:=xlSrcQuery, _
    Source:=stCon, _
    Destination:=rnStart).QueryTable
    ‘qtTable.ListObject.ShowHeaders = False

    ‘Populate some major properties of the QueryTable.
    With qtTable
    .CommandText = stMDX
    .CommandType = xlCmdDefault
    ‘In order to see the output for the first time
    ‘we need to use the Refresh command.

    .Refresh
    .RefreshOnFileOpen = True

    End With
    End Sub

    Scott Gall

    August 17, 2011 at 8:20 pm

    • Thanks!

      Chris Webb

      August 18, 2011 at 8:28 pm

    • Hi Scott,

      I have copied your code and substituted the Cube Name, the Data Source and the MDX expression with my own ‘values’ however I am getting the following error:

      “Run TIme error ‘1004’

      Application-defined or object-defined error”

      when the code reaches:

      .refresh

      Any ideas on what may be causing the error?

      Any help would be greatly appreciated.

      Thanks

      Carl

      Carl Thompson

      August 8, 2012 at 2:19 pm

      • Hi again,

        Never mind.

        As per AN_AS2008’s post of Jan 2011 I needed to include the following:

        ;Locale Identifier=1031

        Thanks

        Carl Thompson

        August 8, 2012 at 2:24 pm

  4. [...] tricks discussed here can be used to create an Excel table bound to a DAX query; in particular it’s very easy to use [...]

  5. [...] Webb wrote this post: Binding an Excel table to the results of an MDX query.  In a comment at the bottom, Scott Gall suggested using VBA.  I want to take this one step [...]

  6. [...] When binding the results of an MDX query to a table in Excel, as described here [...]

  7. [...] PivotTables (either with or without named sets) or Excel cube functions. Having said that, the post I wrote a few years ago about binding a table in Excel to an MDX query has been one of the most [...]

  8. Any idea how to make the table headings nice ? A [date].[calendar year].[calendar year].[MEMBER_CAPTION] doesn’t look user friendly. Of course you can change it to Year but if you refresh your query you get the original column name back.

    Enders

    May 7, 2014 at 8:41 am

    • You can’t, sorry!

      Chris Webb

      May 7, 2014 at 2:46 pm

    • Although you can turn off the header row and write your own text in the cells above the table

      Chris Webb

      May 8, 2014 at 2:46 am

      • How to do it? thanks.

        stacie

        May 19, 2014 at 9:32 pm

      • On the Design tab in the Excel 2013 (when the cursor is inside a PivotTable) there are check boxes to control whether the row headers and the column headers are displayed

        Chris Webb

        May 20, 2014 at 9:07 am


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

Follow

Get every new post delivered to your Inbox.

Join 3,083 other followers

%d bloggers like this: