Retrieving data into Excel using DAX queries

You can write DAX queries and use them to bring data into Excel, but you need to jump through a few hoops first! 

Step 1 - creating a connection

The first step is to create a new connection (a file with extension .odc) in Excel:

On the Data tab of the ribbon, choose to link to Analysis Services.

Choose the Analysis Services server to connect to:

Your SSAS server will be different.

Now choose the model to which you want to connect:

Choose a model from the drop list, then click on any perspective it contains (here I have no perspectives, so I'm linking to the entire Model).

You can now give your connection a name, and finish it:

Give your connection a memorable name.  Where did that Z come from, I wonder?

Choose to create the connection only:

Choose just to create the connection (we don't create a pivot table at this point).

Step 2: editing the connection to use a DAX query

Having created your connection, locate it in Windows Explorer:

A good place to look is the My Data Sources subfolder of My Documents.

Choose to edit the connection in a text editor:

Right-click on the new connection, and choose to edit it in a package like NotePad.

Search for the text CommandType:

Searching for CommandType takes you to the required line.

Change the word Cube to Query:

Overtype the word Cube with Query.  This tells Excel to run a query (not surprisingly).

Step 3a - manually editing the command text

You now have a choice of two ways of proceeding.  The easier way (if you're comfortable with editing text files) is to find and replace the command text for the query:

Here I've searched for the phrase CommandText, and replaced this property with the words EVALUATE Species, a legitimate (if simple) DAX Query.

You can now save this text file, and use the connection to import data into Excel.

Step 3b - changing the command text through the menu

The other way to change the DAX query for a connection is as follows.  First create a new Excel workbook, and choose to look at your existing connections:

Don't use your existing workbook, as you want to make sure that the connection isn't cached.

The easiest way to find the connection you've just created is to browse for it:

Click on the Browse for More... button to find a connection.

Find and double-click on your connection:

This is the connection I just created, complete with misprint.

Choose to bring data in from your connection as a table (say):

Choose to import the data into your existing worksheet (it won't work!).

You should now get the following error message: 

You need to specify the DAX query (as below) in order to proceed.

Now choose to edit the underlying query for this connection:

Right-click on your failed import, and choose to edit the underlying query for it.

Type or paste in your DAX query command:

We'll use the same query again (no point re-inventing wheels, eh?).

Lo and behold, the table refreshes!

The table brings in the results from the DAX query.

For the final part of this blog, I'll show how to import data into PowerPivot using a DAX query.