SQL Server Analysis Services Tabular DAX




By:
Overview

In this chapter we will review the methods of selecting data by evaluating the DAX expressions in SQL Server Management Studio (SSMS). The evaluate clause is the equivalent of the SELECT statement in T-SQL, which can be used to select data from tabular models.

Explanation

Open SSMS, connect to the tabular SSAS instance where the AdventureWorks tabular model is deployed, and open a new MDX query window. In the Cube pane, the dropdown at the top lists all the perspectives in AdventureWorks tabular database. Select the Internet Operation perspective in the Cube pane, and type the below expression.

evaluate ( 'Sales Territory' )
order by 'Sales Territory'[Sales Territory Country]
start at "NA"  

Execute the above expression and you should be able to find the results as shown below.

Sample MDX Evaluate Statement Executed in SQL Server Management Studio

Let's understand this expression in detail:

1) The evaluate clause returns each and every row and column from the table 'Sales Territory'.

2) The Order By clause sorts the resultset. You can sort the resultset in descending order by using the keyword desc. You can also sort the resultset based on more than one fields by specifying the fields in a comma-separated order.

3) Start At clause can be used for every columns appearing in the order by clause. In the above expression, we have specified to start the resultset where the Sales Territory Field is has a value of NA. Try to execute the query without the Start At clause, and you will be able to see the difference.

4) You can also select specific fields from the table using the evaluate clause. Try the below expression as an example. This expression returns distinct values of the Sales Territory Country.

evaluate ( values ('Sales Territory'[Sales Territory Country]) )
order by 'Sales Territory'[Sales Territory Country]
start at "NA"

In this way, we can use SSMS to evaluate DAX expressions and query the data from a tabular data model.

Additional Information

Last Update: 4/18/2017




More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.






download





get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools