Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Forecasting with SQL Expand / Collapse
Author
Message
Posted Monday, February 08, 2010 8:33 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 24, 2014 6:45 AM
Points: 17, Visits: 568
Comments posted to this topic are about the item Forecasting with SQL
Post #862177
Posted Tuesday, February 09, 2010 6:46 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 8:19 AM
Points: 327, Visits: 123
SSAS is completely capable of handling forecasting tasks utilizing a variety of different econometric algorithms.

I would STRONGLY urge readers to consult with a professional statistician or economist before undertaking this type of project.

There are a number of different data afflictions that can have serious adverse impacts on the accuracy of an econometric forecasting solution.

Autocorrelated error terms, unit roots, non-linear time series and others that can cause issues when building ARIMA or regression models and result in spurrious relationships. A trained practitioner will know what to look for and, more importantly, how to correct for these conditions. The spurrious relationships that can result from improper econometric data analysis can result in using independent variables that really have no impact on the dependent variable or throwing out independent variables that actually do have a statistically significant relationship but just need a little prep work that a trained econometrician would understand.

When these forecasting tools are used to base critical business decisions that impact people's lives and livelihoods, PLEASE be sure and leave this type of work to the experts.

Post #862388
Posted Tuesday, February 09, 2010 7:11 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:57 AM
Points: 1,242, Visits: 1,590
Martin Vrieze (2/9/2010)
When these forecasting tools are used to base critical business decisions that impact people's lives and livelihoods, PLEASE be sure and leave this type of work to the experts.


Point of Order: Those who created the credit default swap catastrophe using these types of methods were experts. Having an expert consultant is no substitute to understanding at least the underlying principles of the models you're using. Don't just leave the work to the experts; work with them. If it's important enough to pay someone to do, it's important enough for you to understand it.
Post #862407
Posted Tuesday, February 09, 2010 7:16 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, December 07, 2010 8:19 AM
Points: 327, Visits: 123
Point of Order: Those who created the credit default swap catastrophe using these types of methods were experts. Having an expert consultant is no substitute to understanding at least the underlying principles of the models you're using. Don't just leave the work to the experts; work with them. If it's important enough to pay someone to do, it's important enough for you to understand it.


Spoken with true wisdom. Well said.
Post #862412
Posted Tuesday, February 09, 2010 7:55 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, January 24, 2014 6:45 AM
Points: 17, Visits: 568
Hi Martin,
I appreciate your comments and words of pause. It is important to realize that picking up this forecast model will not get you a job as a forecaster ;P

This article was not intended to be a replacement for a forecast system or to ignore the tools available in analysis services. It was intended to be an entry point into the world of forecasting, in the case where analysis services is not an option. You are certainly aware that linear regression is only one of many models to apply and that each model differs in it's usefulness for each application.

Hopefully, folks will find this exercise useful in understanding the some of the basic language of forecasting and, fundamentally, what this model is and how it works. Learning about a new skill can be daunting, but I hope that we will not be discouraged from starting merely by the possible depth of the subject.

Mark
Post #862446
Posted Tuesday, February 09, 2010 8:57 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977, Visits: 1,499
Hey! That's just good stuff.

Thanks for a well written article.


Tom Garth
Vertical Solutions

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
Post #862500
Posted Tuesday, February 09, 2010 9:29 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 7:09 PM
Points: 4,127, Visits: 5,834
sknox (2/9/2010)
Martin Vrieze (2/9/2010)
When these forecasting tools are used to base critical business decisions that impact people's lives and livelihoods, PLEASE be sure and leave this type of work to the experts.


Point of Order: Those who created the credit default swap catastrophe using these types of methods were experts. Having an expert consultant is no substitute to understanding at least the underlying principles of the models you're using. Don't just leave the work to the experts; work with them. If it's important enough to pay someone to do, it's important enough for you to understand it.


IIRC most of the debacle was the result of ONE FORMULA that EVERYONE USED. Just took it for granted that it worked. But it was flawed. Businesses WANTED to use it though (serious bias) because it gave essentially all buckets of risk an AAA rating.


Best,

Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru at GMail
Post #862536
Posted Tuesday, February 09, 2010 11:15 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: 2 days ago @ 7:36 AM
Points: 471, Visits: 822
Great topic. I had to dig through all of my fearful stats days to do trending a few years back with SSRS 2005 as we were not going to be buying add on tools... So there I was building my own trend lines.

It isn't just trending where lack of understanding can bite us. To many people write reports with no understanding of the data and far more consume the reports with even less understanding. It is so frightening the number of business decisions that are made with invalid, incorrect, and incomplete data.
Post #862622
Posted Tuesday, February 09, 2010 12:05 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Yesterday @ 7:28 PM
Points: 20,453, Visits: 14,063
Nice article. Well written.



Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Posting Data Etiquette - Jeff Moden
Hidden RBAR - Jeff Moden
VLFs and the Tran Log - Kimberly Tripp
Post #862652
Posted Tuesday, February 09, 2010 3:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, April 06, 2014 7:18 PM
Points: 30, Visits: 113
Nice work, Mark.

A couple of points, though. First, the word "variance" is frequently misused when people mean to say "variation". This can cause ambiguity in math/stats where "variance" is defined as the mean of the squared residuals (a residual being the difference between the regression line at point Xi and the raw data value Yi). The square root of variance is actually the more familiar "standard deviation".

Second, I can explain why the notation used in linear regression is at odds with that of trigonometry and Cartesian geometry. The reason for writing "y = a + bx" is generality. Suppose you wanted to fit a 2nd-order poynomial to a data series that that had curvature, not just slope; you would then be solving "y = a + bx + cx^2". You could keep adding powers of x if you had good reason to believe that the underlying phenomenon possessed many degrees of freedom.

And, as if that wasn't enough, in the general problem (where any kind of approximating function is possible), you would actually write the coefficients as a0, a1, a2,... and the "basis functions" as f0(x), f1(x), f2(x),... . For linear regression, f0(x) = 1 and f1(x) = x.

Of course, there are weird and wonderful techniques for fitting data to nonlinear functions, but it might not be practical to do it with SQL.

Cheers!

- Al
Post #862797
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse