Share via

  • Article

Mapping Functions to Query Types (DMX)

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. SQL Server 2008 Analysis Services (SSAS) 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:

Individual algorithms may support additional functions. For a list of the functions that are supported by each model type, see Querying Data Mining Models (Analysis Services - Data Mining).

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

SELECT DISTINCT FROM <model>

RangeMin (DMX)

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.

SELECT FROM <model>.CONTENT

or

SELECT FROM <model>.DIMENSION_CONTENT

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.

SELECT FROM <model>.CASES

IsInNode (DMX)

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 Querying Data Mining Models (Analysis Services - Data Mining).

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 Querying Data Mining Models (Analysis Services - Data Mining).