General Prediction Functions (DMX)

THIS TOPIC APPLIES TO:yesSQL Server (starting with 2008)noAzure SQL DatabasenoAzure SQL Data Warehouse noParallel Data Warehouse

You can use the SELECT statement in Data Mining Extensions (DMX) to create different types of queries. A query can be used to return information about the mining model itself, to make new predictions, or alter the model by training it with new data. Analysis Services provides a variety of specialized functions that control the type of information that is returned in a query. By adding these functions to a DMX query, you can retrieve additional statistics or columns of data. However, each query type and each model type supports certain functions only.

Common Functions

You can use functions to extend the results that a mining model returns. You can use the following functions for any SELECT statement that returns a table expression:

BottomCount (DMX) RangeMin (DMX)
BottomPercent (DMX) TopCount (DMX)
Predict (DMX) TopPercent (DMX)
RangeMax (DMX) TopSum (DMX)
RangeMid (DMX)

In addition, the following functions are supported for almost all model types:

Functions Specific to SELECT Syntax

The following table lists the functions that you can use for each type of SELECT statement.

For general information about functions in DMX, see Data Mining Extensions (DMX) Function Reference.

Query type Supported functions Remarks

RangeMid (DMX)

RangeMax (DMX)
These functions can be used to provide maximum values, minimum values, and means for any column that contains numeric data type, regardless of whether the column is continuous or has been discretized.


IsDescendant (DMX) This function retrieves child nodes for the specified node in the model, and can be used, for example, to iterate through the nodes in the mining model content. The arrangement of the nodes in the mining model content depends on the model type. For information about the structure for each mining model type, see Mining Model Content (Analysis Services - Data Mining).

If you have saved the mining model content as a dimension, you can also use other Multidimensional Expressions (MDX) functions that are avaialble for querying an attribute hierarchy.

ClientSettingsGeneralFlag Class

IsTrainingCase (DMX)

IsTestCase (DMX)
The Lag function is supported only for time series models.

The IsTestCase function is supported in models that are based on a structure that was created using the holdout option, to create a testing data set. If the model is not based on a structure with holdout test set, all cases are considered training cases.
SELECT FROM <model>.SAMPLE_CASES IsInNode (DMX) In this context, the IsInNode function returns a case that belongs to a set of idealized sample cases.
SELECT FROM <model>.PMML Not applicable. Use XML query functions instead. PMML representations are supported only for the following model types:

Microsoft Decision Trees

Microsoft Clustering
SELECT FROM <model> PREDICTION JOIN Prediction functions that are specific to the algorithm that you use to build the model. For a list of prediction functions for each model type, see Data Mining Queries.
SELECT FROM <model> Prediction functions that are specific to the algorithm that you use to build the model. For a list of prediction functions for each model type, see Data Mining Queries.

See Also

Data Mining Extensions (DMX) Reference
Data Mining Extensions (DMX) Function Reference
Data Mining Extensions (DMX) Operator Reference
Data Mining Extensions (DMX) Statement Reference
Data Mining Extensions (DMX) Syntax Conventions
Data Mining Extensions (DMX) Syntax Elements
Structure and Usage of DMX Prediction Queries
Understanding the DMX Select Statement