SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Forecasting with SQL


Forecasting with SQL

Author
Message
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 612
Comments posted to this topic are about the item Forecasting with SQL
Martin Vrieze
Martin Vrieze
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 125
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.
sknox
sknox
SSCarpal Tunnel
SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)SSCarpal Tunnel (4.1K reputation)

Group: General Forum Members
Points: 4105 Visits: 2935
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.
Martin Vrieze
Martin Vrieze
Right there with Babe
Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)Right there with Babe (794 reputation)

Group: General Forum Members
Points: 794 Visits: 125
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.
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)SSC Veteran (207 reputation)

Group: General Forum Members
Points: 207 Visits: 612
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
Tom Garth
Tom Garth
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2033 Visits: 1499
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

TheSQLGuru
TheSQLGuru
SSC-Dedicated
SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)SSC-Dedicated (33K reputation)

Group: General Forum Members
Points: 33307 Visits: 8681
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 on googles mail service
BJ Hermsen
BJ Hermsen
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1005 Visits: 880
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.
SQLRNNR
SQLRNNR
SSC Guru
SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)SSC Guru (68K reputation)

Group: General Forum Members
Points: 68501 Visits: 18570
Nice article. Well written.



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


SQL RNNR

Posting Performance Based Questions - Gail Shaw

Allen Nugent
Allen Nugent
SSC-Enthusiastic
SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)SSC-Enthusiastic (116 reputation)

Group: General Forum Members
Points: 116 Visits: 119
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search