Understanding the DMX Select Statement

Applies to: SQL Server Analysis Services

The SELECT statement is the basis for most queries that you create with Data Mining Extensions (DMX) in Microsoft SQL Server Analysis Services. It can perform many different kinds of tasks, such as browsing and predicting against data mining models.

Following are the tasks that you can complete by using the SELECT statement:

• Browse a data mining model. The schema rowset defines the structure of a model.

• Discover the possible values of a mining model column.

• Browse the cases that are assigned to nodes in a mining model, or get a representative case.

• Create predictions using a variety of inputs.

• Copy mining models.

Each of these tasks uses a different set of data, which we'll call a data domain. You define the data domain in the FROM clause of the statement.

• You want to find objects in the data mining model itself, such as the rule that defines a set of data, or a formula used to make predictions.

  In that case, you need to look at the metadata that is stored in the model itself. Therefore, your data domain is the columns in the data mining schema rowset.

• You want to get detailed information from the cases used to build the model.

  In that case, you need to drill through to the mining structure, which is your data domain, and look at individual rows in columns such as Gender, Bike Buyer, and so on.

Important

Anything that is included in the expression list or in the WHERE clause must come from the data domain that is defined by the FROM clause. You cannot mix data domains.

SELECT Types

The syntax of SELECT statement supports many different tasks. Use the following patterns to perform these tasks:

• Predicting

• Browsing

• Copying

• Drillthrough

Predicting

You can perform predictions based on a mining model by using the following query types.

You can include any one of the browsing or predicting SELECT statements within the FROM and WHERE clauses of a prediction join SELECT statement.

Query Type	Description
SELECT FROM [NATURAL] PREDICTION JOIN	Returns a prediction that is created by joining the columns in the mining model to the columns of an internal data source. The domain for this query type is the predictable columns from the model and the columns from the input data source. SELECT FROM <model> PREDICTION JOIN (DMX) Prediction Queries (Data Mining)
SELECT FROM <model>	Returns the most likely state of the predictable column, based only on the mining model. This query type is a shortcut for creating a prediction with an empty prediction join. The domain for this query type is the predictable columns from the model. SELECT FROM <model> (DMX) Prediction Queries (Data Mining)

Back to Select Types

Browsing

You can browse the contents of a mining model by using the following query types.

Query Type	Description
SELECT DISTINCT FROM <model>	Returns all the state values from the mining model for the specified column. The data domain for this query type is the data mining model. SELECT DISTINCT FROM <model > (DMX) Content Queries (Data Mining)
SELECT FROM <model>.CONTENT	Returns content that describes the mining model. The data domain for this query type is the content schema rowset. SELECT FROM <model>.CONTENT (DMX) Content Queries (Data Mining)
SELECT FROM <model>.DIMENSION_CONTENT	Returns content that describes the mining model. The data domain for this query type is the content schema rowset. SELECT FROM <model>.DIMENSION_CONTENT (DMX)
SELECT FROM <model>.PMML	Returns the Predictive Model Markup Language (PMML) representation of the mining model, for algorithms that support this functionality. The domain for this query type is the PMML schema rowset. DMSCHEMA_MINING_MODEL_CONTENT_PMML Rowset

Back to Select Types

Copying

You can copy a mining model and its associated mining structure into a new model, and then rename the model within the statement.

Query Type	Description
SELECT INTO <new model>	Creates a copy of the mining model. The domain for this query type is the data mining model. SELECT INTO (DMX)

Back to Select Types

Drillthrough

You can browse the cases, or a representation of the cases, that were used to train the model, by using the following query types.

Query Type	Description
SELECT FROM <model>.CASES	Returns the cases used to train the mining model. The domain for this query type is the data mining model. SELECT FROM <model>.CASES (DMX) Create Drillthrough Queries using DMX
SELECT FROM <model>.SAMPLE_CASES	Returns a sample case, representative of the cases used to train the mining model. The domain for this query type is the data mining model. SELECT FROM <model>.SAMPLE_CASES (DMX)
SELECT FROM <structure>. CASES	Returns the detailed data rows from the underlying mining structure, even if some details were not used in training the mining model. SELECT FROM <structure>.CASES Drillthrough Queries (Data Mining)

Back to Select Types

See Also

Data Mining Extensions (DMX) Reference
Data Mining Extensions (DMX) Statement Reference
Data Mining Extensions (DMX) Syntax Conventions