This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

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. 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.

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.

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

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.

Parts of this blog
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

Some other pages relevant to the above blogs include:

This blog has 0 threads

Add a new post

Book any course as classroom, onsite or online training, or as one-to-one consultancy

Free resources

Paid services

Getting help

Where we are

Where you are

Online

Transparent reviews

Delivery of courses

Nicer to work with

Reliable and established

Our training courses

C#

Excel

MySQL

Power Apps

Power Automate

Power BI and DAX

Python

Report Builder

SQL

SQL Server

VBA Macros

Other training resources

Blogs

Exercises

YouTube tutorials

YouTube shorts

Test your skills

Consultancy

Courseware

Self-paced courses

Newsletters

Publications

Our training venues

London

Manchester

Across the UK

At your site

Online

Why we are different

Hundreds of published reviews

Small class sizes

Outstanding courseware

Online classroom

(Almost) no cancellations

Wise Owl trainers only

Genuine integrity

Invoices after training

30+ years of Wise Owl

Our top 100 clients

BLOGS BY TOPIC

BLOGS BY AUTHOR