Overview
Linear Regression is a data‑mining algorithm in Microsoft SQL Server Analysis Services (SSAS) that predicts a continuous value using one or more independent variables. It builds a regression model of the form Y = β0 + β1·X1 + β2·X2 + … + ε, where β0 is the intercept, βi are coefficients, and ε is the error term.
When to Use
- Forecasting sales, prices, or any numeric metric.
- Identifying relationships between a dependent variable and multiple predictors.
- Situations where the assumption of linearity holds.
Syntax (MDX)
SELECT
[Measures].[Predicted Value] ON COLUMNS,
NON EMPTY { [Dimension].[Attribute].Members } ON ROWS
FROM
[MiningModel]
WHERE
( { [Parameter].[Input].<value1> }, { [Parameter].[Input].<value2> } )Example
The following example demonstrates how to create a linear regression model to predict SalesAmount based on UnitsSold and Discount.
CREATE MINING MODEL SalesLinearModel
FROM [Sales]
WITH (
CONTENT = (
SELECT
UnitsSold,
Discount,
SalesAmount
FROM dbo.FactSales
),
MODELING_ALGORITHM = LINEAR_REGRESSION,
TARGET_COLUMN = SalesAmount,
INPUT_COLUMNS = (UnitsSold, Discount)
);