SystemGetCrossValidationResults (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.

Partitions the mining structure into the specified number of cross-sections, trains a model for each partition, and then returns accuracy metrics for each partition.

Note

You can't use this stored procedure to cross-validate clustering models or models that use the Microsoft Time Series algorithm or the Microsoft Sequence Clustering algorithm. To cross-validate clustering models, use the separate stored procedure - SystemGetClusterCrossValidationResults (Analysis Services - Data Mining).

Syntax

  
SystemGetCrossValidationResults(  
<mining structure>  
[, <mining model list>]  
,<fold count>  
,<max cases>  
,<target attribute>  
[,<target state>]  
[,<target threshold>]  
[,<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 a model name contains characters that aren't valid in an identifier, enclose the name in brackets.

If you don't specify a mining model list, the procedure cross-validates all models that belong to the specified structure and have a predictable attribute.

Note

To cross-validate clustering models, use a separate stored procedure, SystemGetClusterCrossValidationResults (Analysis Services - Data Mining).

(optional)

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

Each partition contains approximately max cases divided by fold count cases.

There's no default value.

Note

The number of folds greatly affects cross-validation time. If you select too many folds, the query might take a long time. In some cases, the server can stop responding or the request can time out.

(required)

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

When you specify 0, the procedure uses all cases in the data source.

If the specified value exceeds the number of cases in the data set, the procedure uses all cases in the data source.

There's no default value.

(required)

target attribute
String that contains the name of the predictable attribute. A predictable attribute can be a column, nested table column, or nested table key column of a mining model.

Note

The procedure validates the target attribute only at run time.

(required)

target state
Formula that specifies the value to predict. If you specify a target value, the procedure collects metrics for that value only.

If you don't specify a value or specify null, the procedure computes metrics for the most probable state for each prediction.

The default is null.

The procedure raises an error during validation if the specified value isn't valid for the specified attribute, or if the formula isn't the correct type for the specified attribute.

(optional)

target threshold
Double greater than 0 and less than 1. Indicates the minimum probability score that the prediction must reach for the specified target state to be correct.

A prediction that has a probability less than or equal to this value is considered incorrect.

If you don't specify a value or set it to null, the procedure uses the most probable state, regardless of its probability score.

The default is null.

Note

SQL Server Analysis Services doesn't raise an error if you set target threshold to 0.0, but don't use this value. A threshold of 0.0 causes the procedure to count predictions with zero percent probability as correct.

(optional)

test list
A string that specifies testing options.

Note This parameter is reserved for future use.

(optional)

Return Type

The rowset that is returned contains scores for each partition in each model.

The following table describes the columns in the rowset.

Column Name Description
ModelName The name of the model that was tested.
AttributeName The name of the predictable column.
AttributeState A specified target value in the predictable column. If this value is null, the most probable prediction was used.

If this column contains a value, the accuracy of the model is assessed against this value only.
PartitionIndex An 1-based index that identifies to which partition the results apply.
PartitionSize An integer that indicates how many cases were included in each partition.
Test Category of the test that was performed. For a description of the categories and the tests that are included in each category, see Measures in the Cross-Validation Report.
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

Use - SystemGetAccuracyResults (Analysis Services - Data Mining) to return accuracy metrics for the complete data set.

When the mining model contains folds, use SystemGetAccuracyResults (Analysis Services - Data Mining) to bypass processing and return only cross-validation results.

Examples

The following example splits the [v Target Mail] mining structure into two folds for cross-validation and tests its [Target Mail DT] and [Target Mail NB] mining models.

Line three lists the mining models to test. If you don't specify the list, the procedure tests all nonclustering models associated with the structure. Line four sets the number of partitions. Because you don't specify a value for max cases, the procedure uses all cases in the mining structure and distributes them evenly across the partitions.

Line five specifies the predictable attribute, Bike Buyer, and line six specifies the value to predict, 1 (meaning "yes, will buy").

In line seven, NULL sets no minimum probability threshold. The assessment uses the first prediction with a nonzero probability to calculate accuracy.

CALL SystemGetCrossValidationResults(  
[v Target Mail],  
[Target Mail DT], [Target Mail NB],  
2,  
'Bike Buyer',  
1,  
NULL  
)

Sample results:

ModelName AttributeName AttributeState PartitionIndex PartitionSize Test Measure Value
Target Mail DT Bike Buyer 1 1 500 Classification True Positive 144
Target Mail DT Bike Buyer 1 1 500 Classification False Positive 105
Target Mail DT Bike Buyer 1 1 500 Classification True Negative 186
Target Mail DT Bike Buyer 1 1 500 Classification False Negative 65
Target Mail DT Bike Buyer 1 1 500 Likelihood Log Score -0.619042807138345
Target Mail DT Bike Buyer 1 1 500 Likelihood Lift 0.0740963734002671
Target Mail DT Bike Buyer 1 1 500 Likelihood Root Mean Square Error 0.346946279977653
Target Mail DT Bike Buyer 1 2 500 Classification True Positive 162
Target Mail DT Bike Buyer 1 2 500 Classification False Positive 86
Target Mail DT Bike Buyer 1 2 500 Classification True Negative 165
Target Mail DT Bike Buyer 1 2 500 Classification False Negative 87
Target Mail DT Bike Buyer 1 2 500 Likelihood Log Score -0.654117781086519
Target Mail DT Bike Buyer 1 2 500 Likelihood Lift 0.038997399132084
Target Mail DT Bike Buyer 1 2 500 Likelihood Root Mean Square Error 0.342721344892651

Requirements

Starting with SQL Server 2008, cross-validation is available only in SQL Server Enterprise.

See Also

  • Last updated on