BLOGS BY TOPIC▼
BLOGS BY AUTHOR▼
BLOGS BY YEAR▼
Knowing how to write DAX queries is one thing, but where are you going to use them? This blog shows how you can integrate DAX queries into SSMS, SSRS, SSIS, Excel and PowerPivot.
- Using DAX queries in SQL Server and other applications
- Running DAX queries within Management Studio
- Basing SSRS (Reporting Services) reports on DAX queries
- Writing DAX queries in Integration Services (SSIS)
- Retrieving data into Excel using DAX queries (this blog)
- Loading data into PowerPivot data models using DAX queries
This blog is part of our online SSAS Tabular tutorial; we also offer lots of other Analysis Services training resources.
Posted by Andy Brown on 18 February 2016
You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
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!
A quick commercial: in addition to training in DAX, we also train in Excel.
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.
Although this method may involve less mouse clicks, I think the alternative method shown below is easier to use and understand.
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.
It's a lot of effort to go to, when you could just base a pivot table on the underlying tabular model ...
For the final part of this blog, I'll show how to import data into PowerPivot using a DAX query.
- Using DAX queries in SQL Server and other applications
- Running DAX queries within Management Studio
- Basing SSRS (Reporting Services) reports on DAX queries
- Writing DAX queries in Integration Services (SSIS)
- Retrieving data into Excel using DAX queries (this blog)
- Loading data into PowerPivot data models using DAX queries