Data Mining Query Tools

Applies to: SQL Server Analysis Services Azure Analysis Services Power BI Premium


Data mining is deprecated in SQL Server Analysis Services 2017 and will be discontinued in a future release. Documentation is not updated for deprecated features. To learn more, see Analysis Services backward compatibility.

All data mining queries use the Data Mining Extensions (DMX) language. DMX can be used to create models for all kinds of machine learning tasks, including classification, risk analysis, generation of recommendations, and linear regression. You can also write DMX queries to get information about the patterns and statistics that were generated when you processed the model.

You can write your own DMX, or you can build basic DMX using a tool such as the Prediction Query Builder and then modify it. Both SQL Server Management Studio and SQL Server Data Tools provide tools that help you build DMX prediction queries. This topic describes how to create and execute data mining queries using these tools.

Prediction Query Builder

Prediction Query Builder is included in the Mining Model Prediction tab of Data Mining Designer, which is available in both SQL Server Management Studioand SQL Server Data Tools.

When you use the query builder, you select a mining model, add new case data, and add prediction functions. You can then switch to the text editor to modify the query manually, or switch to the Results pane to view the results of the query.

Query Editor

The Query Editor in SQL Server Management Studio also lets you build and run DMX queries. You can connect to an instance of SQL Server Analysis Services, and then select a database, mining structure columns, and a mining model. The Metadata Explorer contains a list of prediction functions that you can browse.

DMX Templates

SQL Server Management Studio provides interactive DMX query templates that you can use to build DMX queries. If you do not see the list of templates, click View on the toolbar, and select Template Explorer. To see all SQL Server Analysis Services templates, including templates for DMX, MDX, and XMLA, click the cube icon.

To build a query using a template, you can drag the template into an open query window, or you can double-click the template to open a new connection and a new query pane.

For an example of how to create a prediction query from a template, see Create a Singleton Prediction Query from a Template.


The Data Mining Add-in for Microsoft Office Excel also contains a number of templates, along with an interactive query builder which can help you compose complex DMX statements. To use the templates, click Query, and click Advanced in the Data Mining Client.

Integration Services Data Mining Components

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 prediction queries and DMX statements.

Component Description
Data Mining Query task Executes DMX queries and other DMX statements as part of a control flow.

The task editor provides the Prediction Query Builder, and a text box for modifying the DMX query manually. However, the task editor cannot validate the query against objects in an SQL Server Analysis Services solution. Therefore, it is best to create a query within SQL Server Data Tools or Management Studio and then paste the text of the statement or query into the task editor.
Data Mining Query transformation Executes a prediction query within a data flow, using data supplied by a data flow source.

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

The transformation can only be used for creating queries that use data in the data flow; that is, queries that use the PREDICTION JOIN syntax. This component cannot be used for executing content queries or other kinds of DMX statements.

Application Programming Interfaces

You can create custom applications that execute queries against data mining models by using a variety of programming languages, in combination with server protocols such as OLE DB or Analysis Services ADOMD client. For more information, see Data Mining Programming.

However, XMLA constitutes the underlying message format for all interactions with an Analysis Service server. Within an XMLA message, queries are represented differently depending on whether you are sending a prediction query based on DMX, a content query, or a query that retrieves model metadata using the data mining schema rowsets.

See Also

Data Mining Extensions (DMX) Reference
Data Mining Solutions
Understanding the DMX Select Statement
Structure and Usage of DMX Prediction Queries
Create a Prediction Query Using the Prediction Query Builder
Create a DMX Query in SQL Server Management Studio