Create a DMX Query in SQL Server Management Studio

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:

  • Model Content

  • Model Management

  • Prediction Queries

  • Structure Content

    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

  1. Click New Query in Management Studio, and then select New Analysis Server DMX Query.

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

  1. In SQL Server Management Studio, on the View menu, select Template Explorer.

  2. Click Analysis Server to see a tree view of the templates that apply to Analysis Services.

Apply a template to build a query

See Also

Data Mining Query Tools
Data Mining Extensions (DMX) Reference