Create a DMX Query in SQL Server Management Studio
APPLIES TO: SQL Server Analysis Services Azure Analysis Services Power BI Premium
SQL Server provides a set of features to help you create prediction queries, content queries, and data definition queries against mining models and mining structures.
The graphical Prediction Query Builder is available in both SQL Server Data Tools (SSDT) and SQL Server Management Studio, to simplify the process of writing prediction queries and mapping data sets to a model.
The query templates provided in the Template Explorer jump-start the creation of many kinds of DMX queries, including many types of prediction queries. Templates are provided for content queries, queries used nested data sets, queries that return cases from the mining structure, and even data definition queries.
The Metadata Explorer in the MDX and DMX query panes provides a list of available models and structures that you can drag and drop into the query builder, as well as a list of DMX functions. This feature makes it easy to get object names right, without typing.
This topic describes how to build a DMX query by using the Metadata Explorer and the DMX query editor.
DMX Query Templates
Templates for creating basic DMX queries are available in Template Explorer. The DMX folder contains data mining templates, which are divided into these categories:
You can also create custom templates, for queries or commands that you run frequently.
XMLA Query Templates
Analysis Services also provides templates for XMLA queries.
There is some overlap between the types of queries that you can perform by using XMLA and DMX. For example, you can create some model content queries by using either DMX or the data mining schema rowsets, but the schema rowsets sometimes contain information that is not exposed in DMX content queries.
There are also some key differences in the way that operations are handled in DMX and in XMLA. For example, you can use XMLA to perform administrative operations such as backup of an entire Analysis Services database, but if you want to back up a single mining model, DMX provides a simple command, EXPORT (DMX), that is better suited to that purpose.
Build and Run a DMX Query
Open a new DMX Query window
Click New Query in Management Studio, and then select New Analysis Server DMX Query.
When the Connect to Server dialog box appears, select the instance of Analysis Services that contains the mining models you want to work with.
Open Template Explorer
In SQL Server Management Studio, on the View menu, select Template Explorer.
Click Analysis Server to see a tree view of the templates that apply to Analysis Services.
Apply a template to build a query
Right-click the appropriate query type and select Open.
Or, drag the template into the query editor.
You can also fill in the parameters for the query by using the option, Specify Values for Parameters, from the Query menu.
For examples of how to create specific types of queries from templates, see the following topics: