Regression Algorithm Overview
The Regression algorithm in SQL Server Analysis Services (SSAS) builds a statistical model that predicts a numeric target column based on one or more predictor columns. It supports both linear and nonlinear (logistic) regression, handling continuous and categorical input data.
- Suitable for predicting continuous values such as sales amount, temperature, or risk scores.
- Can automatically handle missing values and discretize continuous predictors.
- Provides model evaluation metrics like R‑squared, RMSE, and MAE.
How It Works
The algorithm fits a regression equation of the form:
Y = β0 + β1·X1 + β2·X2 + … + εwhere Y is the target variable, Xn are predictor variables, βn are coefficients learned from the data, and ε is the error term.
SSAS supports:
- Linear regression (default)
- Logistic regression for binary targets
- Regularization options (L2 Ridge)
MDX Syntax for Creating a Regression Model
CREATE MINING MODEL [SalesRegression]
(
[UnitsSold] DOUBLE CONTINUOUS,
[UnitPrice] DOUBLE CONTINUOUS,
[Discount] DOUBLE CONTINUOUS,
[Profit] DOUBLE CONTINUOUS,
[Region] STRING DISCRETE
)
USING Microsoft_Regression
WITH (
TARGET = [Profit],
INPUT = ([UnitsSold], [UnitPrice], [Discount], [Region]),
ALGORITHM_OPTION = 'Linear',
MAX_TRAINING_ITERATIONS = 1000
);
Interactive Regression Demo
Enter pairs of x,y separated by commas; separate points with semicolons.