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.
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.
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.
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.
Create Relational Model
Clear Model Content
Discrete Column Value
Continuous Column Values
Train Nested Model
Create from PMML
Nested Singleton Prediction