SQL Server Analysis Services Documentation

Model Evaluation

Evaluating a data mining model determines how well it predicts outcomes for new data. SQL Server Analysis Services (SSAS) provides multiple techniques to assess model quality, including cross‑validation, holdout validation, and the use of statistical metrics such as accuracy, precision, recall, lift, and ROC curves.

Overview
Metrics
Sample Code
Video Guide

Evaluation Techniques

  • Cross‑validation: Splits the data set into k folds, training on k‑1 and validating on the remaining fold.
  • Holdout validation: Reserves a percentage of the data for testing after model training.
  • Bootstrap: Builds multiple models on random samples with replacement.

SSAS automates these processes when you run the CREATE MINING MODEL statement with the USING clause. The SELECT * FROM $SYSTEM.MDSCHEMA_MINING_MODEL_CONTENT view provides detailed evaluation results.

Key Metrics

Metric Description
Accuracy Proportion of correctly predicted cases.
Precision True positives divided by all predicted positives.
Recall (Sensitivity) True positives divided by all actual positives.
F‑Measure Harmonic mean of precision and recall.
Lift Improvement over random guessing.
ROC AUC Area under the ROC curve, indicating discrimination ability.

Sample T‑SQL for Model Evaluation

-- Create a mining model with cross‑validation
CREATE MINING MODEL SalesModel
FROM [AdventureWorksDW2019].[dbo].[DimCustomer] AS c
USING Microsoft_Regression
(
    PredictProbability = 1,
    TargetColumn = 'Income',
    Algorithm = 'LinearRegression',
    CrossValidate = 5
);
                
-- Retrieve evaluation statistics
SELECT *
FROM $SYSTEM.MDSCHEMA_MINING_MODEL_CONTENT
WHERE MODEL_NAME = N'SalesModel';

Adjust the CrossValidate value to control the number of folds.

Video Guide

Watch the step‑by‑step walkthrough of model evaluation in SSAS.

Further Reading