Friday, April 17, 2009

Linear Regression

Linear regression is a statistical technique for drawing a line through a set of data points that “best fits” the data. It is a useful and standard technique for quantifying trends.

For example, consider the following sample data set, graphed as a simulated dot plot in OBI EE.

ScreenShot069

Fitting a regression line is a two-step process: finding the slope of the line and finding the y-intercept (where the regression line would cross the y axis).

If you don’t remember the formula for determining the slope from the statistics class you took 10 to 40 years ago (!), you can easily find it with a search on the web. This site has this:

ScreenShot070

You can calculate this formula directly in Answers. I recommend doing it term by term, using a different calculated column for each term in the formula and then combining terms at the end.

Click on any column in the left panel to get a column to write the first term of the formula in. Here, the column clicked was Revenue.

ScreenShot071

The first term will be x*y. I label the column headings to correspond to the formula I am building.

ScreenShot074

ScreenShot075

Then add another column to continue building the formula, using the Column button in the formula window to pick the x*y column already built. This technique saves a lot of headaches down the road as the formula becomes more complex and you start hunting down unmatched parentheses.

ScreenShot080

Clicking on x*y gives this formula.

ScreenShot076

It is useful to look at results for each step along the way. In this case, the results are not what are needed.

ScreenShot077

So the formula needs to be modified. I added the “by” clause to get the sum for all rows.

ScreenShot078

ScreenShot079

As you continue building the regression formula step by step, one of the most problematic seems to be the calculation of the number of rows (N in the formula).

The formula shown here does not work and gives you an error message that is, parenthetically, the answer to the question “What use are logical keys in fact tables?”

ScreenShot082

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 15036] Logical table Facts needs a primary key to support count aggregates on that table. (HY000)

However, instead of creating fact table keys, calculate the count another way. The following formula appears to work (but oddly, as we’ll see, only for a while).

ScreenShot081 

ScreenShot083

Continue building.

ScreenShot084

ScreenShot085

Tip: Use the Power() function in your formulas when you can.

ScreenShot086

Now, the odd thing mentioned above happens. All these columns compute.

ScreenShot089

However, when you subtract the last column from the next to last to get the denominator in the formula, Answers throws a puzzling error.

ScreenShot090

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22023] An arithmetic operation is being carried out on a non-numeric type. (HY000)

At least one of the two numbers in the final column is now regarded as non-numeric?

A solution to this seems to be to change the formula for N, which was max(count(rsum(1))). Both max(rsum(1)) or sum(1 by) work.ScreenShot091

When you have the numerator(the first column in the following screen shot) and denominator (next to last column) built, calculate the slope.

ScreenShot092

To plot the regression line, you also need the y intercept. The Criteria tab already has all the terms you need to plug into the intercept formula.

Once you have the slope and intercept, the regression line (values of y vs x) are easily calculated with the formula y=mx+b explained above (m is the slope, b is the y-intercept).

ScreenShot093

The plot looks like this:

image

This is a line chart. Revenue is shown with blue round  symbols and a 0 pixel line width. The regression line is a red dotted line with symbols turned off.

ScreenShot095

Another statistic that is important is the R-squared statistic, which measures how well the regression line fits the data. The formula for R (the correlation coefficient) is:

ScreenShot096

The Criteria tab already contains the formula for the numerator and several terms of the denominator (circled in this picture).

image

Build the rest needed for the denominator, then calculate R and finally R-squared.

ScreenShot098

The final value, 0.58, is the goodness of fit (goodness ranging from 0, bad, to 1, perfect).

At this point all the unneeded columns can be hidden or deleted.

It is always good to translate this into business (i.e. not statistical terms) by renaming things (“Regression” –> “Trend”) and using the narrative view to spell things out as explicitly as possible. Perhaps you can come up with a good business translation for R-square.

image