SELECT FROM <model>.CONTENT (DMX)

Applies to: SQL Server Analysis Services

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.

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:

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

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:

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:

See Also

SELECT (DMX)
Data Mining Extensions (DMX) Data Manipulation Statements
Data Mining Extensions (DMX) Statement Reference