Creating DMX Prediction Queries

The main goal of most data mining projects is to use mining models to create predictions. Creating predictions includes such diverse tasks as classification, risk evaluation, and linear regression.

Prediction queries are based on the Data Mining Extensions (DMX) language. DMX is an extension of the SQL language that provides support for working with mining models. The syntax for a prediction query using DMX is similar to the syntax for a query in Transact-SQL. Both SQL Server Management Studio and Business Intelligence Development Studio provide tools that help you build DMX prediction queries.


In addition to prediction queries, you can create content queries to retrieve the patterns and statistics that were generated when you processed the model. For more information about content queries and other query types, see Querying Data Mining Models (Analysis Services - Data Mining).

Prediction Query Tools

SQL Server provides the following tools that you can use to build prediction queries:

  • Prediction Query Builder

  • Query Editor

  • DMX templates

Prediction Query Builder is included in the Mining Model Prediction tab of Data Mining Designer, which is available in both SQL Server Management Studio and Business Intelligence Development Studio. When you use the query builder, you can use graphical tools to select a mining model, add new case data, and add custom prediction functions. The Prediction Query Builder includes a text editor that you can use to modify the query manually, and a simple results pane to view the results of the query.

The Query Editor in SQL Server Management Studio provides tools that you can use to build and run DMX queries. You can connect to an instance of SQL Server Analysis Services, select a database and a mining model from a drop-down list, and browse a list of mining models columns and prediction functions.

SQL Server Management Studio also provides interactive DMX query templates that you can use to build DMX queries.

Creating Prediction Queries in Integration Services

You can also include prediction queries as part of a SQL Server Integration Services package. The following tasks and transformations in Integration Services support the creation and execution of DMX statements.



Data Mining Query task

Executes DMX queries as part of a control flow.

The task editor provides the Prediction Query Builder, and a text box for modifying the DMX query manually.

Data Mining Query transformation

Executes a prediction query using data supplied by a source that you define in the data flow.

The task editor provides the Prediction Query Builder, and a text box for modifying the DMX query manually.