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."
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 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
Thanks!
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
Hi again,
Never mind.
As per AN_AS2008’s post of Jan 2011 I needed to include the following:
;Locale Identifier=1031
Thanks
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.
You can’t, sorry!
Although you can turn off the header row and write your own text in the cells above the table
How to do it? thanks.
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
Hi Chris,
There is any way to run more than one MDX Query in one odc file to Multiple excel sheet.
Thanks
No, I don’t think so
Hi Jory, see my comment below. My solution will allow you to use multiple MDX statements and add the data to your Excel workbook. 🙂
Is there any char size restriction for the MDX qry in ODC file?
I’m not sure, but I think there is a char size restriction on MDX queries in general – maybe around 3000 characters.
Thanks it was very useful. How to execute those Mdx ,which consists of some parameters. since mdx contains a parameter ,@para1 .The query does not return any output. Where to declare the parameter in the od file
I’m pretty sure you can’t declare a parameter in the odc file. Maybe you could do something with VBA?
Hi Nidhin. See my reply below. My approach will allow you to use parameters. Though they have to be calculated or semi-static (you can’t base them on a Dialoge/input field in Excel) they can still be part of the MDX executed, and the statement will return their result as data usable in Excel. 🙂
Chris, even after so many years your post continue to be relevant. Thanks! 🙂
I have come to realize that unfortunately, in some cases, Excel outputs some fairly rubbish MDX. Specifically, Excel has a bad tendency to add all dimensions using a simple CrossJoin() and then applying the NonEmpty property. This is a terrible approach when you have many dimensions and/or large fact tables, and especially in scenarios where the added dimensions are unrelated or when calculated measures are used (the NonEmpty property is ignored apparently).
I was attempting to make a report for a customer with a fairly complex ERP solution using 9 separate dimensions and with a number calculated measures. Some of the dimensions are fairly large, so after 9 crossjoins it obviously crawls to a halt and produces a timeout from SSAS (or sometimes Excel crashes).
For me the workable, though less flexible solution, was to add the data to Excel without using the regular OLAP pivot table. It was greatly inspired by what is described your original post.
1.) Install Excel PowerPivot if you haven’t already
2.) Create a new Excel workbook and open PowerPivot
3.) Add a new data source and choose Analysis Services. This lets you insert your own MDX statement directly, rather than using Excel to generate the query.
3.1.) If you don’t know how to make your own MDX statement just click on the “Design” button. This brings up a primitive OLAP browsing tool (same as in Enterprise Manager and SQL Server Data Tools it seems) where you can drag-n-drop your desired attributes and measures into a canvas. Make sure you add all the fields you need, but ignore they layout and the names – you can change that later. Filter your data if possible, in case you want to filter by values the end-user does not need in the final report. It reduces the amount of data Excel has to fetch.
4.) You now have a PowerPivot model with your desired OLAP data, but without using CrossJoin() between each dimension. Yay!
5.) Insert a Pivot table into your Excel workbook from PoverPivot using the “Pivot table” button.
6.) You now have a pivot table in Excel based on SSAS data without using CrossJoin. As an added bonus powerusers can also insert MDX with much more complexity than Excel could normally generate.
7.) As a special note to “Enders” above, this mehod also allows you to rename all columns and measures into something more user friendly. The best way to do it is to edit the column names in your PowerPivot model before inserting the pivot table into Excel (so before my step 5). And your custom names do NOT get overwritten with the next data refresh 🙂
—
Now, obviously this method has a few drawbacks. The biggest one being that you will end up with a local copy of data in PowerPivot, so you should not use this approach to read very large facts unless you filter them in the MDX statement prior to importing the data into PowerPivot. My fact is 200K lines and it works reasonably well, but I imagine 10 million lines would be a different matter.
Let us look at the benefits of this approach though:
a.) No CrossJoin() in the MDX so it works with as many dimensions and calculated measures as you like. You’re not using Excels horrible MDX generator.
b.) The method does not require manually editing files or editing XML. It is all pure Excel GUI.
c.) You can rename attributes and measures, and a data refresh does not overwrite your custom names.
d.) The added data can be fully filtered/sliced within your Pivot table just as you are used to with the normal OLAP tables.
e.) You can use MDX much more complex that Excel would normally allow. This includes things like making calculated measures, using SCOPE, named sets, etc.
f.) You can actually add multiple SSAS datasources to your PowerPivot model. For example multiple facts using different MDX statemens, or even from different cubes/servers. They datatypes and column-names can be edited in PowerPivot to consolidate the data from multiple sources into a single report. Obviously other non-SSAS data sources can be used as well.
g.) You can use DAX for calculations even with a Multidimensional data source. Though I am no DAX expert myself, for some this would be a killer feature.
Thanks again for the inspiration Chris. 🙂
No problem, happy to help! Incidentally, the problem with the MDX that Excel generates that you’re running into is probably not the fact that it’s doing a simple Crossjoin and Non Empty – that in itself isn’t bad. It’s the fact that Excel returns subtotals even when you don’t want to display them, and with nine hierarchies crossjoined that can lead to a lot of subtotals (see https://cwebbbi.wordpress.com/2011/10/07/excel-subtotals-when-querying-multidimensional-and-tabular-models/). An alternative to using Power Pivot is to use Excel’s named sets functionality to specify your own MDX for the rows or columns axis of your query. This allows you to write MDX that doesn’t bring back the unwanted subtotals and will usually perform much better and doesn’t require a local copy of the data.
Thanks. I did consider using named sets, but I was worried about the filtering options afterwards. It seemed that to gain performance I needed to put all dimensions and attributes in a single set (or few sets), but to have the normal filtering possibilities (for each attribute) Excel wants a named set for each, and it still does a full crossjoin+subtotals for each. Maybe I did it wrong though. 🙂
Why is it “normal” to do a complete crossjoin on dimensions like this? And why is the NonEmpty apparently applied client-side when there are calculated measures in the query? In my case the result would be around 2,618e+32 lines … which is … a mindblowing lot of lines!
You need to have a single named set to get good performance, but doing that will reduce your options for filtering unfortunately…
Ever since SSAS 2005 nothing (including NonEmpty) is evaluated client-side for an MDX query, it all happens on the server. NonEmpty can be very slow when calculated measures are involved though, especially when those calculated measures are being evaluated in cell-by-cell mode; rewriting and optimising those calculated measures is the only way to solve this problem.