How to get data from tabular models using DAX queries in various programs
Part five of a six-part series of blogs

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.

  1. Using DAX queries in SQL Server and other applications
  2. Running DAX queries within Management Studio
  3. Basing SSRS (Reporting Services) reports on DAX queries
  4. Writing DAX queries in Integration Services (SSIS)
  5. Retrieving data into Excel using DAX queries (this blog)
  6. 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! 

Step 1 - creating a connection

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

Creating a connection

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

Choose the Analysis Services server to connect to:

Choosing the server

Your SSAS server will be different.


Now choose the model to which you want to connect:

Choosing a model

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:

Connection names

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


Choose to create the connection only:

Create 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:

My data sources

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


Choose to edit the connection in a text editor:

Editing connection

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


Search for the text CommandType:

Searching for the command type

Searching for CommandType takes you to the required line.

Change the word Cube to Query:

Change to run a 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:

Command text property

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:

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:

Browsing for connections

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


Find and double-click on your connection:

Open the connection

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


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

Importing as a table

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

You should now get the following error message: 

Error message - the syntax is incorrect

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


Now choose to edit the underlying query for this connection:

Edit this query

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


Type or paste in your DAX query command:

The DAX query

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

Lo and behold, the table refreshes!

Refreshed table

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.

This blog has 0 threads Add post