SystemGetClusterCrossValidationResults (Analysis Services - data mining)

Applies to: SQL Server 2019 and earlier Analysis Services Azure Analysis Services Fabric/Power BI Premium

Important

Data mining was deprecated in SQL Server 2017 Analysis Services and now discontinued in SQL Server 2022 Analysis Services. Documentation is not updated for deprecated and discontinued features. To learn more, see Analysis Services backward compatibility.

This stored procedure partitions the mining structure into the specified number of cross-sections, trains a model for each partition, and returns accuracy metrics for each partition.

Note Use this stored procedure only with a mining structure that has at least one clustering model. To cross-validate nonclustering models, use SystemGetCrossValidationResults (Analysis Services - Data Mining).

Syntax

  
SystemGetClusterCrossValidationResults(  
<structure name>,   
[,<mining model list>]  
,<fold count>}  
,<max cases>  
<test list>])

Arguments

mining structure
Name of a mining structure in the current database.

(required)

mining model list
Comma-separated list of mining models to validate.

If you don't specify a list of mining models, cross-validation runs against all clustering models for the specified structure.

Note

To cross-validate models that aren't clustering models, use a separate stored procedure, SystemGetCrossValidationResults (Analysis Services - Data Mining).

(optional)

fold count
Integer that specifies the number of partitions to separate the data set into. The minimum value is 2. The maximum number of folds is maximum integer or the number of cases, whichever is lower.

Each partition contains roughly this number of cases: max cases/fold count.

There is no default value.

Note

The number of folds greatly affects the time required to perform cross-validation. If you select a number that's too high, the query might run for a very long time. In some cases, the server might become unresponsive or time out.

(required)

max cases
Integer that specifies the maximum number of cases that can be tested.

A value of 0 indicates that the procedure uses all the cases in the data source.

If you specify a number that's higher than the actual number of cases in the data set, the procedure tests all cases in the data source.

(required)

test list
A string that specifies testing options.

Note This parameter is reserved for future use.

(optional)

Return Type

The Return Type table contains scores for each individual partition and aggregates for all models.

The following table describes the columns returned.

Column Name Description
ModelName The name of the model that was tested.
AttributeName The name of the predictable column. For cluster models, always null.
AttributeState A specified target value in the predictable column. For cluster models, always null.
PartitionIndex An 1-based index that identifies which partition the results apply to.
PartitionSize An integer that indicates how many cases were included in each partition.
Test The type of test that was performed.
Measure The name of the measure returned by the test. Measures for each model depend on the type of the predictable value. For a definition of each measure, see Cross-Validation (Analysis Services - Data Mining).

For a list of measures returned for each predictable type, see Measures in the Cross-Validation Report.
Value The value of the specified test measure.

Remarks

To return accuracy metrics for the entire data set, use SystemGetClusterAccuracyResults (Analysis Services - Data Mining).

If the mining model already contains folds, use SystemGetClusterAccuracyResults (Analysis Services - Data Mining) to skip processing and return only the cross-validation results.

Examples

This example partitions a mining structure into three folds and tests two associated clustering models.

Line three lists the mining models to test. If you don't list any models, the code tests all clustering models associated with the structure.

Line four specifies the number of folds, and line five specifies the maximum number of cases to use.

Clustering models don't require you to specify a predictable attribute or value.

CALL SystemGetClusterCrossValidationResults(  
[v Target Mail],  
[Cluster 1], [Cluster 2],  
3,  
10000  
)

Sample results:

ModelName AttributeName AttributeState PartitionIndex PartitionSize Test Measure Value
Cluster 1 1 3025 Clustering Case Likelihood 0.930524511864121
Cluster 1 2 3025 Clustering Case Likelihood 0.919184178430778
Cluster 1 3 3024 Clustering Case Likelihood 0.929651120490248
Cluster 2 1 1289 Clustering Case Likelihood 0.922789726933607
Cluster 2 2 1288 Clustering Case Likelihood 0.934865535691068
Cluster 2 3 1288 Clustering Case Likelihood 0.924724595688798

Requirements

Beginning with SQL Server 2008, only SQL Server Enterprise supports cross-validation.

See Also

SystemGetCrossValidationResults (Analysis Services - Data Mining)
SystemGetAccuracyResults (Analysis Services - Data Mining)
SystemGetClusterCrossValidationResults
SystemGetClusterAccuracyResults (Analysis Services - Data Mining)

  • Last updated on