Time Series Analysis (Numeric) Functions

OLE DB Programmer's Reference

These are functions pertaining to finding covariance, correlation, and linear regression by using the least squares method.

COVARIANCE(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

CORRELATION(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

Covariance and correlation are measures of linear association. Both functions evaluate <set> against the first numeric value expression to get the values for the y-axis. The set is evaluated against the second numeric value expression (if present) to get the set of values for the x-axis. If the second numeric value expression is not present, the members of <set> are used as values for the x-axis.

The latter case does not make much sense for most dimensions (for example, SalesRep). However, it does make sense for the Time dimension. Covariance and correlation are calculated for these x and y values.

LINREGINTERCEPT(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

LINREGSLOPE(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

LINREGVARIANCE(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

LINREGR2(<set>, <numeric_value_expression>
   [, <numeric_value_expression>])

These methods evaluate <set> against the first numeric value expression to get the set of values for the y-axis, and they evaluate <set> against the second numeric value expression (if present) to get the set of values for the x-axis. If the second numeric value expression is not present, it uses the members of <set> as values for the x-axis. The latter case does not make much sense for most dimensions (for example, SalesRep). However, it does make sense for the Time dimension.

Linear regression by using the least squares method calculates the equation of the best-fit line for a series of points. For the purposes of this section, let the regression line be given by the equation

y = ax + b

where a is called the intercept and b is called the slope.

After obtaining the set of points, LINREGINTERCEPT returns the intercept of the regression line (a in the equation above). LINREGSLOPE returns the slope of the regression line (b in the equation above). LINREGVARIANCE returns the statistical variance that describes the fit of the linear equation to the points, and LINREGR2 returns the statistical R² that describes the fit of the linear equation to the points.

LINREGPOINT(<numeric_value_expression>, <set>,
   <numeric_value_expression> [, <numeric_value_expression>])

Uses the last three arguments—<numeric_value_expression>,<set>, and <numeric_value_expression>—like the other LINREGxxx functions to calculate the regression line. Next it evaluates the first argument (<numeric_value_expression>) and uses the resulting number as the x value in the regression equation (y = ax + b) to calculate the y value.

For example, the dimension Campaign contains members that represent the various advertising campaigns. There are two measures associated with each campaign—the cost of the campaign and the revenues resulting from the campaign. To find the correlation between the cost of the campaign and the revenues that it generates, use the regression line y = ax + b, where x is the cost and y is the revenue. The a and b values are calculated as follows:

a = LinRegCoef(Campaigns.Members, Revenues, Cost) b = LinRegConst(Campaigns.Members, Revenues, Cost)

For another example, find the trend in Sales for the sales of the last nine periods and forecast the next period, as follows:

a = LinRegSlope(LastPeriods(9), Sales) b = LinRegIntercept(LastPeriods(9), Sales) NextPeriod = LinRegPoint(10, LastPeriods(9), Sales)

1998-2001 Microsoft Corporation. All rights reserved.