Export (0) Print
Expand All

Understanding the DMX Select Statement

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.

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

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

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft