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


Forecasting with SQL


Forecasting with SQL

Author
Message
Chad Downey
Chad Downey
SSC Rookie
SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)SSC Rookie (32 reputation)

Group: General Forum Members
Points: 32 Visits: 151
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!

Chad E. Downey, CDMP - Certified Data Management Professional
Consultant - SQL Server, SSIS, SSRS, SSAS
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 611
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
Saravanan_tvr
Saravanan_tvr
SSC Eights!
SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)SSC Eights! (890 reputation)

Group: General Forum Members
Points: 890 Visits: 1354
Its very useful for the beginner. If the same sample, if available using SSAS would be great...

Many Thanks!
S.saravanan
“I am a slow walker, but I never walk backwards-
Abraham Lincoln”
Bigtoerag
Bigtoerag
SSC Rookie
SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)SSC Rookie (27 reputation)

Group: General Forum Members
Points: 27 Visits: 12
Mark Lovely work and making my life much easier for a painful problem of how to cast time series data forward.

On addition i would to understand is how to make ll the NULL values come through as blank so I can automatically process them into a charting web service.
There seems to be multiple places as I have tried to zero them out but i would prefer blanks so the charting does not plot them at all.

I will go hunting for more tidbits you have uploaded to broaden my knowledge.

Thanks a lot
james.waugh28
james.waugh28
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 1
mark.wojciechowicz@gmail.com - Monday, February 8, 2010 8:33 PM
Comments posted to this topic are about the item Forecasting with SQL

Hi Mark, I believe there is an error in your code. When looping and using y = a+bx to forecast yi you are using x(i-1) then adding 1 (i.e. your parenthesis are off) instead of using xi:

MAX(A) + (MAX(B) * MAX(Forecastkey) + 1), -- Trendline
(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))*
(SELECT ...


Should be:
MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)), -- Trendline
(MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)))*
(SELECT ...


Outside of this, thanks for the very useful code! Feel free to contact me direct if you'd like to discuss.

James

mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 611
james.waugh28 - Thursday, March 30, 2017 3:51 PM
mark.wojciechowicz@gmail.com - Monday, February 8, 2010 8:33 PM
Comments posted to this topic are about the item Forecasting with SQL

Hi Mark, I believe there is an error in your code. When looping and using y = a+bx to forecast yi you are using x(i-1) then adding 1 (i.e. your parenthesis are off) instead of using xi:

MAX(A) + (MAX(B) * MAX(Forecastkey) + 1), -- Trendline
(MAX(A) + (MAX(B) * MAX(Forecastkey) + 1))*
(SELECT ...


Should be:
MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)), -- Trendline
(MAX(A) + (MAX(B) * (MAX(Forecastkey) + 1)))*
(SELECT ...


Outside of this, thanks for the very useful code! Feel free to contact me direct if you'd like to discuss.

James

James,
Thanks for reviewing this and finding the bug. I have submitted a correction to the article and attachment.
m

rchantler
rchantler
SSC-Addicted
SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)SSC-Addicted (439 reputation)

Group: General Forum Members
Points: 439 Visits: 611
We are starting to investigate SQL + R. Would that be a good approach to this problem? Is it still too soon for this technology combination to have caught hold in the SQL community?
mark.wojciechowicz@gmail.com
mark.wojciechowicz@gmail.com
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 611
rchantler,
R would be a brilliant approach and probably much easier to implement. If you google forecasting + R or linear regression + R you'll come up with a lot of links. Here's one from the top of the search http://a-little-book-of-r-for-time-series.readthedocs.io/en/latest/src/timeseries.html
Glancing through it, they support many different forecast models, including this one. Ideally, you would test with a number of models and select the one that performed the best for that particular dataset. In our company, we use different models for every product, because their behavior is different.
Give R a try and write an article!
xsevensinzx
xsevensinzx
SSCertifiable
SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)SSCertifiable (5.1K reputation)

Group: General Forum Members
Points: 5091 Visits: 3128
rchantler - Friday, May 19, 2017 8:18 AM
We are starting to investigate SQL + R. Would that be a good approach to this problem? Is it still too soon for this technology combination to have caught hold in the SQL community?

Depends on what you're trying to do. R + SQL is a good in my opinion, but in the sense R lives outside of SQL versus on the server to allow for separation of church and state, especially if you're analyzing larger datasets with R.

When it comes to SSAS over R, then R may be the better option to actually validate your data. This article for example is not actually validating the forecast with statistics and common approaches why the specific variables were chosen over the others. It's using the SSAS features to easily do a linear regession over a set of data to show you how easily it can be done. But, if you can't explain the results, then it's very dangerous to attempt. R with SQL on the other hand makes it a bit easier to understand what is going on and provides greater access to validate not only the end result, but your variables one-by-one with their own analysis. It's just a matter if you want to go through the process of creating R scripts versus a more drag-and-drop approach.

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