Excel – Using a Custom MDX Query in Excel Table

I had a requirement where I wanted to use my own Custom MDX Query within an Excel Table. And from the output of the table I then wanted to Pivot this data. I do know that you can do this in Power Pivot, but the requirement was to not use Power Pivot.

Below are the steps and it is a little bit of manipulation to get it working, but it works well in the end. I did not want to use any VBA or custom code to get this working. Only the standard tools and interface with Excel.

I did get the idea from Chris Webb’s Blog as a reference point: http://cwebbbi.wordpress.com/2009/12/18/binding-an-excel-table-to-the-results-of-an-mdx-query/

NOTE: THIs has been built using Excel 2013

In our example below we want to extract from the AdventureWorksDW2012 Analysis Services Database and select the following:

·         Date

·         Product Category

·         Sales Territory Country

You can get a copy of this here: http://msftdbprodsamples.codeplex.com/releases/view/55330

·         I used the AdventureWorksDW2012 Data File and AdventureWorks Multidimensional Models SQL Server 2012

NOTE: In this section we are going to connect to a SQL Server database.

·         The reason is because we need to create a connection file which later we can then modify.

·         And we can only do this with a connection to the SQL Server Database connection type.

1.       Open a new Excel Work book.

2.       Click on the Data Tab, then click on the Drop Down for From Other Sources and select From SQL Server

a.       

3.       This will then start the Data Connection Wizard

a.        In the Server Name put the name of a SQL Server that you can connect to.

b.       Click Next

4.       Then in the Select database and Table which you can see we selected our AdventureWorksDW2012 as shown below:

a.       

b.       ClickNext

5.       On the Save Data Connection File and Finish click on Finish

6.       On the Import Data window click Ok to let it insert the data into your spreadsheet

a.       

7.       If you selected the name vAssocSeqLineItems you will see the following below:

a.       

NOTE: Here is where we use our existing SQL Server connection and modify it so that it will now connect to an Analysis Services Instance, and then add our Custom MDX Query.

1.       In your Excel Workbook click on the Data Tab

2.       Then under the Connections section click on Properties as shown below:

a.       

3.       This will then open the External Data Properties Window

4.       Now in the External Data Properties Window on the right hand side is the Connection Properties Button as shown below.

a.        Click on the Connection Properties Button

b.      

5.       Now you will see the Connection Properties Window.

6.       Click on the Definition Tab as shown below:

a.       

7.       Now where it says Connection string you are going to put in the following below.

a.        NOTE: This is the connection string to connect to Analysis Services 2012.

                                                               i.      That is why you will see the Provider is set to MSOLAP.5 because I currently have got SQL server 2012 installed.

                                                              ii.      If you are running SQL Server 2008R2 I think currently that is set to MSOLAP.4

b.       You can copy and paste this below into your Connection string, and I will explain what needs to change afterwards

Provider=MSOLAP.5;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=AdventureWorksDW2012;Data Source=AnalysisServices\BI_OLAP;MDX Compatibility=1;Safety Options=2;MDX Missing Member Mode=Error

c.        The first part is the Provider which is highlighted in RED

                                                               i.      This is the SQL Server 2012 Provider for Analysis Services

                                                              ii.      EG: Provider=MSOLAP.5

d.       Next we are using Integrated or Windows Authentication which is highlighted in Green

                                                               i.      EG: ;Integrated Security=SSPI;Persist Security Info=True;

e.       Next is the Initial Catalog which is the DatabaseName that you want to connect to on your Analysis Services Instance. This is highlighted in Purple

                                                               i.      As per our example we are connecting to the Analysis Services database called: AdventureWorksDW2012

                                                              ii.      EG: Initial Catalog=AdventureWorksDW2012

f.         Next is the Data Source, which is the Analysis Services Instance where your database from the above step is running on. This is highlighted in Orange.

                                                               i.      As per our example, our server name is called: AnalysisServices\BI_OLAP

                                                              ii.      EG: ;Data Source=AnalysisServices\BI_OLAP

g.        The rest of the options you can leave as the defaults.

8.       Now where it says Command Type change this from Table to Default.

9.       Finally where it says Command text this is where you will put in your Custom MDX Query.

a.        As per our Example we put in the following MDX Query:

Selectnonempty {[Measures].[Internet Sales Amount]} on 0

,nonempty {(

                      [Date].[Date].Children

                     ,[Product].[Category].children

                     ,[Sales Territory].[Sales Territory Country].children

                     )} on 1

from [Adventure Works]

10.    So now once Complete your Connection Properties Window will look like the following:

a.       

11.    Then Click Ok

12.    You will now get prompted with a Window, with the options of Yes or No

a.        What this Window is saying is that due to you changing the details in the connection it is no longer identical to the original connection.

b.       And because of this, the External Connection File will be removed.

c.        Finally it is asking if you want to proceed with making the changes to the connection.

d.       Click Yes

e.        

13.    Then on the External Data Properties Window click Ok.

14.    And now as seen below you can see the results of the Custom MDX Query being run

a.       

15.    You could then use Power View to then create a Power View Map from the table above.

a.