Creating DMX Queries in SQL Server Management Studio

SQL Server Management Studio provides a full set of features, including a query editor and a query builder, which you can use to build and run Data Mining Extensions (DMX) queries in Microsoft SQL Server Analysis Services. The prediction query editor contains tools to simplify the process of writing and editing queries. Prediction Query Builder is designed to help you create prediction queries.

For More Information:Editing Scripts and Files in SQL Server Management Studio, Using the Prediction Query Builder to Create DMX Prediction Queries, Data Mining Extensions (DMX) Reference

Within the query editor, you can either write your own query, or you can use a template for common tasks such as creating a new mining model.

Building and Running Queries

To open a new DMX query, 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.

The query editor contains the following elements:

  • A drop-down list box that contains all the mining models in the selected Analysis Services database.

  • A tab that displays a tree view of all the columns in the selected mining model.

  • A tab that contains all the functions that can be used with each model type.

  • A text editor that you use to create the DMX queries.

To examine the syntax of a query, click Parse. To run a query, click Execute. To stop a query that is running, click Cancel Executing Query.

The Results tab at the bottom of the screen shows the results of a query. The Messages tab shows any messages that are associated with the execution of the query.

After you select a model in the Mining Model list, you can drag columns or functions from the tree viewers on the left of the screen onto the text editor. Keywords are blue.

For More Information: Writing, Analyzing, and Editing Scripts with SQL Server Management Studio, Color Coding in Code Editors, Editor Convenience Commands and Features

Templates

Templates for creating basic DMX queries are available in Template Explorer. To open Template Explorer, select Template Explorer on the View menu. Click Analysis Server to see a tree view of the templates that apply to Analysis Services. The DMX folder contains data mining templates, which are divided into Model Content, Model Management, and Prediction Queries folders.

To open a new template, right-click the appropriate query type and select Open. The following table lists the query types that are available in SQL Server Management Studio.

Base Prediction

Create Relational Model

PMML Content

Clear Model Content

Discrete Column Value

Rename Model

Content Query

Drop Model

Singleton Prediction

Continuous Column Values

Export Model

Train Model

Create

Export Structure

Train Nested Model

Create from PMML

Nested Prediction

 

Create Nested

Nested Singleton Prediction

 

For More Information:Using SQL Server Management Studio Templates, How to: Use Templates in SQL Server Management Studio