Microsoft KB Archive/307276

= INF: How to Use the Linear Regression Functions =

Article ID: 307276

Article Last Modified on 2/20/2007

-

APPLIES TO


 * Microsoft SQL Server OLAP Services
 * Microsoft SQL Server 2000 Analysis Services

-



This article was previously published under Q307276



SUMMARY
This article explains and illustrates the use of the following Linear Regression functions in a calculated member:
 * LinRegIntercept
 * LinRegPoint
 * LinRegR2
 * LinRegSlope
 * LinRegVariance



MORE INFORMATION
Linear Regression is a statistical method that predicts the value of one variable, based on knowledge of another variable and the statistical relationship between the two. The Linear Regression functions use the method of least squares to calculate the equation of the best-fit line for a series of points. The general equation for linear regression is

Y'=aX+b

where Y' is the predicted value, a is the slope of the regression line, b is the intercept of the regression line, and X is a value of the X axis.

LinRegIntercept
The LinRegIntercept function returns the intercept of a regression line. In this example, you add a calculated member named &quot;Intercept&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:
 * 1) Open the Sales cube of the FoodMart sample for editing.
 * 2) Add a new calculated measure with the following properties:
 * 3) * Parent Dimension = Measures
 * 4) * Member Name = Intercept
 * 5) * Value = LinRegIntercept(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])

LinRegSlope
The LinRegSlope function returns the slope of a regression line. In this example, you add a calculated member named &quot;Regression Slope&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:
 * Add a new calculated measure with the following properties:
 * Parent Dimension = Measures
 * Member Name = Regression Slope
 * Value = LinRegSlope(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])

LinRegPoint
The LinRegPoint function returns a predicted value of one variable, based on the statistical relationship between the variables. In this example, you add a calculated member named &quot;Predict&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:


 * Add a new calculated measure with the following properties:
 * Parent Dimension = Measures
 * Member Name = Predict
 * Value = LinRegPoint([Measures].[Unit Sales],LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])
 * Format String = Standard

An alternate approach to returning a predicted value of one variable, based on the statistical relationship between the two sets of variables is to use the values returned by the LinRegIntercept and LinRegSlope functions. In this example, you add a calculated member named &quot;Predict Formula&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:


 * Add a new calculated measure with the following properties:
 * Parent Dimension = Measures
 * Member Name = Predict Formula
 * Value = ([Measures].[Slope]*[Measures].[Unit Sales])+[Measures].[Intercept]
 * Format String = Standard

Click the Data tab and compare the values you see in the Predict and Predict Formula columns. The values in the two columns should be identical.

LinRegR2
The LinRegR2 function returns the statistical R2 that describes the goodness of fit of the linear regression equation to the points. In this example, you add a calculated member named &quot;Good Fit&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:


 * Add a new calculated measure with the following properties:
 * Parent Dimension = Measures
 * Member Name = Good Fit
 * Value = LinRegR2(LastPeriods(10), [Measures].[Unit Sales],[Measures].[Store Sales])
 * Format String = #,#.00

LinRegVariance
The LinRegVariance function returns the statistical variance that describes the fit of the linear equation to the points. In this example, you add a calculated member named &quot;Variance&quot; to the Sales cube of the FoodMart sample. To add the calculated member, use these steps:


 * Add a new calculated measure with the following properties:
 * Parent Dimension = Measures
 * Member Name = Variance
 * Value = LinRegVariance(LastPeriods(10),[Measures].[Unit Sales],[Measures].[Store Sales])

Additional query words: LinRegIntercept LinRegPoint LinRegR2 LinRegSlope LinRegVariance OLAP

Keywords: kbinfo KB307276

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.