How to get data from tabular models using DAX queries in various programs
Part two 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 (this blog)
  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
  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.

Running DAX queries within Management Studio

So you've deployed a tabular model.  How can you write DAX queries to get information out of it within SSMS (SQL Server Management Studio)?

I've already mentioned this in my previous blog on how to write DAX queries, but thought it would be useful to explain this in more detail here.

Connecting to your deployed model

The first thing to do is to make sure you're using your Analysis Services database, by connecting to it:

Connecting to SSAS

Choose to connect to Analysis Services.

Now list your databases, and choose to create a new query in the model of interest:

Create a new query

Choose this option (even though you actually want to create a query in DAX, not MDX).

Writing your query

From now on, you're on your own!  You can type in your DAX query, but you'll need to get the syntax right:

DAX query

Here I'm summarising total sales by species.

Note that you can't drag columns in from the cube on the left, since SSMS will assume that you're writing MDX and use MDX format, not DAX, to type in the column names.

When you've finished writing your query, you can execute it to see the results as shown above:

Execute your DAX query

Click on the usual button to run your query.


Next up - including DAX queries within SSRS reports.

This blog has 0 threads Add post