Export data from MDX Query directly on Excel file.

Export data from MDX Query directly on Excel file.

Hi Guys,

While generating data result thru MDX query some time we face many issues like taking or coping data result on SSMS, because MDX query generally does not give column names on sql management studio. It gives the column names only for the Measures.
also if you are using any property field of any dimension in MDX that field also does not come in result set.

Now I am sharing below scenario to extract data with column names and property fields.

First we have to make a data source connection thru excel data tab as below screen shot.

1. Now enter your server name .. click on next
2. Select Database and Cube.. click on next
3. Save Data Connection and Finish.
4. One pop up window will appear "Import Data". Click on CANCEL button.

NOW, go to this datasource location, by default it will be under Libraries\Documents\My Data Sources. There would be a .ODC file. Open with / Edit this in notepad by right clicking on .odc file.
below are some steps that should be edit.

We have to first change highlighted text Cube (as highlighted in yellow) and write "MDX" (as highlighted in Green) then remove CubeName (as highlighted in yellow) with your MDX query (as highlighted in Green).

Save it and close it.

Come to excel sheet -> go to open file -> open that datasource->click on enable
now you will get desired MDX output on same sheet.

Some extra columns could be appeared which you have to remove it like below mentioned.

Now save this excel file and use it. :)