How to write queries in DAX to interrogate SSAS tabular models
Part one of an eight-part series of blogs

As well as creating measures to aggregate data in tabular models using DAX, you can also write queries to extract data - this blog shows you how!

  1. Writing DAX queries (this blog)
  2. DAX and SQL compared
  3. The EVALUATE command in the DAX query language
  4. Using SUMMARIZE to group or aggregate DAX query data
  5. Filtering in DAX queries using CALCULATETABLE or FILTER
  6. Adding columns in a DAX query using ADDCOLUMNS
  7. Combining the results of two or more tables
  8. Other useful DAX functions

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

Writing DAX queries

This blog explains how to write queries to get information out of a tabular model - and also discusses where you might use these queries, and why!

If you're following through these tutorials using the MAM database, you may find you need to rename some columns and import others in your data model (it'll be obvious when you need to do this, as your DAX query will report an error saying that a particular column can't be found).

What are DAX queries?

This blog has already exhaustively explained how to use DAX measures to aggregate statistics in pivot tables.  However, you can also write DAX queries to list out "rows".  Here's an example:

-- list out the species in name order

EVALUATE Species

ORDER BY

Species[SpeciesName]

Here's what this would show:

Results of running query

This simple query would list out all of the rows in the Species table.

 

This is analogous to the following SQL statement, which would do more or less the same thing:

-- list out the rows from the species table

SELECT

*

FROM

tblSpecies

ORDER BY

SpeciesName

The difference is that SQL allows you to display data from a relational database, whereas DAX allows you to display data from a cube (usually a deployed tabular model).

Where to write DAX

The best place (I think) to write DAX queries is in DAX Studio, if you're lucky enough to be able to use it.  Failing that, you can use Management Studio (SSMS):

Creating a new query

Right-click on a deployed model in SSMS and choose to create a new query in MDX (yes, really).

You'll now see a blank query, into which you can type DAX (again, yes, really):

Typing DAX

A tabular model is actually deployed as a cube. You can interrogate this using either DAX or (see below) MDX.

 

You could type in any valid DAX query.  Here's a simple one to list out quadrants:

-- list out quadrants

EVALUATE

Quadrant

ORDER BY

Quadrant[QuadrantName]

You can press F5 or click on the Execute button to run this query:

Running a DAX query

Notice the way that the query parser underlines the word EVALUATE to show it isn't valid MDX (which we knew!).

Not only is there no way to check query syntax using this method, but you can't even drag things in from the explorer window on the left (if you do, the query editor uses MDX syntax, not DAX).

DAX or MDX? A comparison

There are two languages you can use to interrogate cubes:

Language Stands for Notes
DAX Data Analysis Expressions The standard way to query tabular models.
MDX Multi-Dimensional Expressions The standard way to query multi-dimensional models.

Which is better?  DAX suffers from the disadvantage that it has more limited support:

Application Limitations of DAX support
Management Studio As we've seen, you can paste DAX queries in, but there is no Intellisense support.
Reporting Services You can paste DAX queries in by a back-door route, but it's not straightforward.
Report Builder There is no support for DAX.

Despite this, I would strongly recommend using DAX to interrogate deployed tabular models.  Why?

  • It's a bit easier to learn than MDX.
  • It seems silly to use one language for your measures and another for your queries.
  • It is likely (almost certain, I'd say) that future versions of SQL Server will have much better support for DAX.

See this separate blog for how to include DAX within other applications like Reporting Services and Integration Services.

 

The rest of this blog shows where and how to write DAX ... beginning with a comparison of DAX and SQL. 

This blog has 0 threads Add post