Share via

  • Article

SELECT FROM <model>.CONTENT (DMX)

Returns the mining model schema rowset for the specified data mining model.

Syntax

SELECT [FLATTENED] [TOP <n>] <expression list> FROM <model>.CONTENT 
[WHERE <condition expression>]
[ORDER BY <expression> [DESC|ASC]]

Arguments

  • n
    Optional. An integer that specifies how many rows to return.

  • expression list
    A comma-separated list of columns derived from the Content schema rowset.

  • model
    A model identifier.

  • condition expression
    Optional. A condition to restrict the values that are returned from the column list.

  • expression
    Optional. An expression that returns a scalar value.

Remarks

The SELECT FROM <model>.CONTENT statement returns content that is specific to each algorithm. For example, you might want to use the descriptions of all the rules of an association rules model in a custom application. You can use a SELECT FROM <model>.CONTENT statement to return values in the NODE_RULE column of the model.

The following table lists the columns that are included in the mining model content.

Note

Algorithms might interpret the columns differently in order to correctly represent the content. For a description of the mining model content for each algorithm, and tips on how to interpret and query the mining model content for each model type, see Mining Model Content (Analysis Services - Data Mining).

CONTENT rowset column

Description

MODEL_CATALOG

A catalog name. NULL if the provider does not support catalogs.

MODEL_SCHEMA

An unqualified schema name. NULL if the provider does not support schemas.

MODEL_NAME

A model name. This column cannot contain a NULL.

ATTRIBUTE_NAME

The name of the attribute that corresponds to the node.

NODE_NAME

The name of the node.

NODE_UNIQUE_NAME

The unique name of the node within the model.

NODE_TYPE

An integer that represents the type of the node. .

NODE_GUID

The node GUID. NULL if no GUID.

NODE_CAPTION

A label or a caption that is associated with the node. Used primarily for display purposes. If a caption does not exist, NODE_NAME is returned.

CHILDREN_CARDINALITY

The number of children that the node has.

PARENT_UNIQUE_NAME

The unique name of the node's parent.

NODE_DESCRIPTION

A description of the node.

NODE_RULE

An XML fragment that represents the rule embedded in the node. The format of the XML string is based on the PMML standard.

MARGINAL_RULE

An XML fragment that describes the path from the parent to the node.

NODE_PROBABILITY

The probability of the path that ends in the node.

MARGINAL_PROBABILITY

The probability of reaching the node from the parent node.

NODE_DISTRIBUTION

A table that contains statistics that describe the distribution of values in the node.

NODE_SUPPORT

The number of cases in support of this node.

Examples

The following code returns the ID of the parent node for the decision trees model that was added to the Targeted Mailing mining structure.

SELECT MODEL_NAME, NODE_NAME FROM [TM Decision Tree].CONTENT
WHERE NODE_TYPE = 1

Expected results:

MODEL_NAME

NODE_NAME

TM_DecisionTree

0

The following query uses the IsDescendant function to return the immediate children of the node that was returned in the previous query.

Note

Because the value of the NODE_NAME is a string, you cannot use a sub-select statement to return the NODE_ID as an argument to the IsDescendant function.

SELECT NODE_NAME, NODETYPE, NODE_CAPTION 
FROM [TM Decision Tree].CONTENT
WHERE ISDESCENDANT('0')

Expected results:

Because the model is a decision trees model, the descendants of the model parent node include a single marginal statistics node, a node that represents the predictable attribute, and multiple nodes that contain input attributes and values. For more information, see Mining Model Content for Decision Tree Models (Analysis Services - Data Mining).

Using the FLATTENED Keyword

The mining model content frequently contains interesting information about the model in nested table columns. The FLATTENED keyword lets you retrieve data from a nested table column without using a provider that supports hierarchical rowsets.

The following query returns a single node, the marginal statistics node (NODE_TYPE = 26) from a Naïve Bayes model. However, this node contains a nested table, in the NODE_DISTRIBUTION column. As a result, the nested table column is flattened and a row is returned for every row in the nested table. The value of the scalar column MODEL_NAME is repeated for each row in the nested table.

Also, notice that if you specify only the name of the nested table column, a new column is returned for each column in the nested table. By default, the name of the nested table is prefixed to the name of each nested table column.

SELECT FLATTENED MODEL_NAME, NODE_DISTRIBUTION
FROM [TM_NaiveBayes].CONTENT
WHERE NODE_TYPE = 26

Example results:

MODEL_NAME

NODE_DISTRIBUTION.ATTRIBUTE_NAME

NODE_DISTRIBUTION.ATTRIBUTE_VALUE

NODE_DISTRIBUTION.SUPPORT

NODE_DISTRIBUTION.PROBABILITY

NODE_DISTRIBUTION.VARIANCE

NODE_DISTRIBUTION.VALUETYPE

TM_NaiveBayes

Bike Buyer

Missing

0

0

0

1

TM_NaiveBayes

Bike Buyer

0

6556

0.506685215240745

0

TM_NaiveBayes

Bike Buyer

1

6383

0.493314784759255

0

The following example demonstrates how to return only some of the columns from the nested table by using a sub-select statement. You can simplify the display by aliasing the table name of the nested table, as shown.

SELECT MODEL_NAME, 
(SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE, [SUPPORT] AS t
FROM NODE_DISTRIBUTION) 
FROM TM_NaiveBayes.CONTENT
WHERE NODE_TYPE = 26

Example results:

MODEL_NAME

t.ATTRIBUTE_NAME

t.ATTRIBUTE_VALUE

t.SUPPORT

TM_NaiveBayes

Bike Buyer

Missing

0

TM_NaiveBayes

Bike Buyer

0

6556

TM_NaiveBayes

Bike Buyer

1

6383