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