Running DAX queries against a Multidimensional model

Rather than using Power View, in some cases, you may want to be able to run your own DAX queries against a Multidimensional model rather than using MDX. Scenarios where this might make sense include when you are building a SQL Server Reporting Services report and you find that a DAX query runs much faster than an equivalent MDX query, or you find that it's easier to write a calculation needed for your report in DAX rather than in MDX (although it is possible to declare DAX calculations in MDX queries—see http://tinyurl.com/DAXinMDX).

Executing DAX queries

DAX queries can be run from an MDX query window in SQL Server Management Studio, just like an MDX query. However, the problem with doing this is that you will see MDX metadata in the Metadata pane rather than DAX metadata, and this makes composing your query difficult. As an alternative, you can use DAX Studio, a free, community-developed Excel add-in that can be downloaded from http://tinyurl.com/DAXStudio. DAX Studio makes it easy to write DAX queries and displays the result of your query either in a grid or in an Excel worksheet. If you are using SQL Server Reporting Services, you have to use the DMX query editor to run your queries, though again you will not see any DAX metadata. Details on how to do this can be found in the following blog post: http://tinyurl.com/DAXSSRS. In all of these cases, when you are setting up your connection to Analysis Services Multidimensional, you must remember to specify the Cube connection string property, just as you do with Power View connections.

DAX queries and attributes

It is out of the scope of this book to provide a full description of the DAX query language; if you would like to learn more about it, a series of blog posts on the subject can be found here: http://tinyurl.com/DAXQueries. There is, however, one extra thing to take into account when using DAX queries on Analysis Services Multidimensional that is not relevant to DAX queries on Analysis Services Tabular: when you query a table representing a dimension, there are restrictions on the columns that you can use in your query. For example, the following DAX query returns all of the columns on the table representing the Date dimension in the Adventure Works database:

EVALUATE 'DATE' 

Here are the results of the query:

Each of the columns returned by this query represents a column used in either the KeyColumns, the NameColumn, the ValueColumn, or a member property of an attribute on the dimension. However, if you only wish to return some of the columns and not all of them, you will need to remember that if you select one column, you will also need to select all of the columns built from the KeyColumns property from the same attribute for the query to run.

So, for example, trying to run the following DAX query will result in an error because only one of the columns that represent the Fiscal Year hierarchy on the Date dimension is included:

EVALUATE SUMMARIZE('DATE', 'DATE'[Fiscal Year]) 

However, the following query will run successfully because it includes the column 'Date'[Fiscal Year.Key0]:

EVALUATE 
SUMMARIZE('DATE', 
'DATE'[Fiscal Year.Key0],
'DATE'[Fiscal Year])

Here are the results of this query; all of the distinct key and name values used in the Fiscal Year attribute of the Date dimension: