560 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owls only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
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!
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).
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
Here's what this would show:
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
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).
The best place (I think) to write DAX queries used to be DAX Studio. Failing that, you can use Management Studio (SSMS):
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):
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
You can press F5 or click on the Execute button to run this 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).
There are two languages you can use to interrogate cubes:
Data Analysis Expressions
The standard way to query tabular models.
The standard way to query multi-dimensional models.
Which is better? DAX suffers from the disadvantage that it has more limited support:
Limitations of DAX support
As we've seen, you can paste DAX queries in, but there is no Intellisense support.
You can paste DAX queries in by a back-door route, but it's not straightforward.
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.
|Parts of this blog|
25 Aytoun Street