OLAP with non-linear regression tests

[VolkerBarth]

I have just found out that these OLAP functions (introduced in v9.0.2) are very handy to do linear regression tests, namely REGR_SLOPE(), REGR_INTERCEPT() and REGR_R2(). It was much easier to do so than I had thought - being no expert in this field at all...and they seem real fast.

(A deep thanks to Glenn for the OLAP whitepaper, by the way!)

Now I would like to ask if it's possible with theses or other functions to test for other forms of regression tests, e.g. with the help of polynoms or logarithical functions - as can be done with Excel.

(Yep, I'm aware that this might be more a mathematical question...)

[VolkerBarth]

I should add that I'm asking just to apply regresstion tests - not to understand the whole theory behind:)

So if there are quite simple ways to use the existing OLAP functions to perform different tests, I'd like to try that. Otherwise the linear approach will suffice for my needs.

[VolkerBarth]

So I assume the answer is: No, there's just support for linear regression tests.

[Former Member · Accepted solution]

Only linear regression of a function y = a + bx is supported by the current set of functions, but that doesn't necessarily mean that you can only use the regression functions for linear equations. For example, you might model an exponential regression y = bx**a by taking the logarithm of both sides, that is, ln y = ln b + a ln x. You could then solve for ln y using the supported linear regression functions, and take the exponent after that to determine the value of y. There are examples of doing these sorts of transformations in Raj Jain's book, referenced in the OLAP whitepaper, and there is a brief example of doing this in the capacity planning whitepaper (see Section 5.4.2).

Handling arbitrary types of regressions could be supported by user-defined aggregate and window functions, which SQL Anywhere does not currently support.