SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by Mark Wojciechowicz / Forecasting with SQL / Latest Posts

RE: Forecasting with SQL

Saravanan_tvr — Tue, 30 Oct 2012 06:51:52 GMT

Its very useful for the beginner. If the same sample, if available using SSAS would be great...

RE: Forecasting with SQL

mark-786476 — Wed, 01 Feb 2012 20:08:08 GMT

Yes. That should work fine for you to give a directional insight into your data. Products, customers or risk types are simply discrete values by which to partition the forecast to make it more useful. Any type of discrete data can be plugged in. Good luck and have fun exploring forecasting!Mark

RE: Forecasting with SQL

Chad Downey — Wed, 01 Feb 2012 15:11:18 GMT

Hi Mark, Nice article on forecasting using T-SQL! I know it's been a while since you wrote this article but wanted to know if you think this technique would work with a scenario I am faced with on a project.My scenario is a table of risks with a value for severity, probability and a criticality index value. I would like to predict or forecast the number of risks most likely on the fly so not stored in a table for a charting solution. Basically, I would chart the actual values and then based upon a choosen date display the forecast values to the right and the actual values to the left of the choosen date. As of now I am rolling up the number of risks by a category or risk type for a monthly rollup which works fine. I'd like to take this data and then do a forecast for future months like you do in this article for say 6 to 12 months ahead from a choosen (parameter) now date. So in your article you use products and in my scenario I have risks. Do you think I could use this technique to do the forecasts? The forecasts would only have to project an integer or count value based on the historical number of risks in previous months of a project.Thanks in advance!

RE: Forecasting with SQL

SwePeso — Sat, 28 May 2011 00:24:07 GMT

Here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93911 is examples of both moving average, weighted average and weighted moving average.

RE: Forecasting with SQL

mark-786476 — Fri, 27 May 2011 14:03:56 GMT

Hi Grasshopper,I do not have any examples of weighted averages, but you may considering looking at exponential smoothing which weights, more heavily, recent periods. I do suggest making sure that you prepare the data before forecasting on it. By using a moving median, you can eliminate a lot of the outliers. Also, it's worth looking at the forecasting datamining algorithm in SSAS, which is a great tool.Good luck,Mark

RE: Forecasting with SQL

jolifox — Fri, 06 May 2011 10:31:27 GMT

Mark,Very informative and useful article, Do you have any suggestion on how to incorporate a weighting element into the SQL. i.e. if i wanted to give more weight to last years data over the year before etc.

RE: Forecasting with SQL

Edward Boyle-478467 — Fri, 26 Feb 2010 05:50:04 GMT

Excellent article. I have a lot of forecast and actual time-series data and will check out the methods this weekend.

RE: Forecasting with SQL

L' Eomot Inversé — Wed, 17 Feb 2010 13:23:49 GMT

[quote][b]Allen Nugent (2/9/2010)[/b][hr]Nice work, Mark.[/quote]I'll second that - a very nice article indeed. [quote]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.[/quote]Well, if you can do it say in Fortran+Dystal+LAPACK+FSPAK it's probably not too hard in SQL. What sort of package do people generally use for this sort of thing today? I'm way out of date (forgotten nearly all of my stats and most of my curve fitting) so I wouldn't try to do it myself. But if I asked an expert for predictions I would definitely want to know what methods he used, what assumptions he made, and maybe look at the code to check it didn't include the most common idiocies (and SQL is a language I can read, because it was designed for readability rather than to enable maximum obscurity like some pouplar languages).

RE: Forecasting with SQL

mark-786476 — Thu, 11 Feb 2010 09:35:39 GMT

[quote][b]ltaylor 73774 (2/11/2010)[/b][hr]Very good article, thanks. When I picked up the code snippet and tried to use it, I saw one problem. In the last section of code, labeled "Create Forecast", the term that's multiplied by the seasonality factor is missing a parenthesis. That is, MAX(A) + (MAX(B) * MAX(Forecastkey) + 1) should be(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))Correct me if I'm wrong, but this is the change that it took to make my forecast look correct.[/quote]Yes, you are correct. When the trend is multiplied against seasonality, the parenthese are needed. Thank you for finding that error! I will get the download updated.Mark

RE: Forecasting with SQL

ltaylor 73774 — Thu, 11 Feb 2010 07:44:19 GMT

Very good article, thanks. When I picked up the code snippet and tried to use it, I saw one problem. In the last section of code, labeled "Create Forecast", the term that's multiplied by the seasonality factor is missing a parenthesis. That is, MAX(A) + (MAX(B) * MAX(Forecastkey) + 1) should be(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))Correct me if I'm wrong, but this is the change that it took to make my forecast look correct.

RE: Forecasting with SQL

SwePeso — Wed, 10 Feb 2010 12:48:07 GMT

[quote][b]piet_dj (2/10/2010)[/b][hr]Do you have any useful info on calculating multiple linear regression with T-SQL?[/quote]Here http://developerworkshop.net/software.htmlIt also handles logarithmic, exponential and power regression.

RE: Forecasting with SQL

piet_dj — Wed, 10 Feb 2010 05:37:07 GMT

Do you have any useful info on calculating multiple linear regression with T-SQL?

RE: Forecasting with SQL

mark-786476 — Tue, 09 Feb 2010 21:14:51 GMT

Thanks for the clarifications, Al. I appreciate the finer points and I am sure this will be helpful to any readers as well.Mark

RE: Forecasting with SQL

Allen Nugent — Tue, 09 Feb 2010 15:54:23 GMT

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

RE: Forecasting with SQL

SQLRNNR — Tue, 09 Feb 2010 12:05:48 GMT

Nice article. Well written.

RE: Forecasting with SQL

Robert Hermsen — Tue, 09 Feb 2010 11:15:32 GMT

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.

RE: Forecasting with SQL

TheSQLGuru — Tue, 09 Feb 2010 09:29:50 GMT

[quote][b]sknox (2/9/2010)[/b][hr][quote][b]Martin Vrieze (2/9/2010)[/b][hr]When these forecasting tools are used to base critical business decisions that impact people's lives and livelihoods, [b][u]PLEASE[/u][/b] be sure and leave this type of work to the experts.[/quote][b]Point of Order:[/b] Those who created the credit default swap catastrophe using these types of methods [b]were[/b] experts. Having an expert consultant is no substitute to understanding [b]at least[/b] 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.[/quote]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.

RE: Forecasting with SQL

Tom Garth — Tue, 09 Feb 2010 08:57:37 GMT

Hey! That's just good stuff.Thanks for a well written article.

RE: Forecasting with SQL

mark-786476 — Tue, 09 Feb 2010 07:55:15 GMT

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 ;PThis 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

RE: Forecasting with SQL

Martin Vrieze — Tue, 09 Feb 2010 07:16:51 GMT

[quote][b]Point of Order:[/b] Those who created the credit default swap catastrophe using these types of methods [b]were[/b] experts. Having an expert consultant is no substitute to understanding [b]at least[/b] 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.[/quote]Spoken with true wisdom. Well said.

RE: Forecasting with SQL

sknox — Tue, 09 Feb 2010 07:11:40 GMT

[quote][b]Martin Vrieze (2/9/2010)[/b][hr]When these forecasting tools are used to base critical business decisions that impact people's lives and livelihoods, [b][u]PLEASE[/u][/b] be sure and leave this type of work to the experts.[/quote][b]Point of Order:[/b] Those who created the credit default swap catastrophe using these types of methods [b]were[/b] experts. Having an expert consultant is no substitute to understanding [b]at least[/b] 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.

RE: Forecasting with SQL

Martin Vrieze — Tue, 09 Feb 2010 06:46:10 GMT

SSAS is completely capable of handling forecasting tasks utilizing a variety of different econometric algorithms.I would [b][u]STRONGLY[/u][/b] 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, [b][u]PLEASE[/u][/b] be sure and leave this type of work to the experts.

Forecasting with SQL

mark-786476 — Mon, 08 Feb 2010 20:33:08 GMT

Comments posted to this topic are about the item [B]Forecasting with SQL[/B]