Forecasting algorithm

Category: sql server datamining


panlondon on Mon, 12 Oct 2009 13:04:16


I am relatively new with Data Mining and I would like to predict future results by using the forecasting algorithm. At the moment I have a very simple situation. A date and customers per day. What I would like to do is predict the customers for the new few months on a daily basis. First of all is it correct that my date field has to be either date or numeric? It's string but can easily be converted to date. Secondly, how do I query my future results? I used forecasting but the graph doesn't seem to display any results but also can't see how I can query from a different tool for my future dates. I understand it's not possible within excel so will use reporting services to do this.

If you can give me an example in DMX how to query the future dates will be perfect.




rok1 on Mon, 12 Oct 2009 17:07:56

          You can model your solution using enhanced 2008 version fo Microsoft Time series Algorithm. Its always better to have your date field as Date Type.

You can query your results from Management studio ->New Query->DMX,
SSIS- Data mining query transformation, 
Reporting services,
BIDS -> Mining model prediction (Tab)

Please follow the link, there's a good tutorial on implementing Time series model(including example in DMX to query the model)



Jamie MacLennan on Mon, 12 Oct 2009 21:03:50

I don't know why if you are using the Forecasting tool you aren't seeing results.  In any case, the DMX is rather simple - essentially it is

SELECT PredictTimeSeries(MySeries, 10) FROM MyTimeSeriesModel

to predict out 10 values.  If you are using Excel and have the Data Mining Client installed as well as the Table Analysis Tools, you can run the Forecasting tool and then switch over to the Data Mining Ribbon and click on the "Trace" button.  This will show what DMX statements were executed so you can see the queries.

panlondon on Fri, 23 Oct 2009 16:03:33


Thank you for your replies, am trying again now. As I said I have only date and customers per day as my 2 fields. So I need to predict the customers per day. My time series works now, but the prediction doesn't seem right. I have a faily big variation something like that:
16 October 2009 109
17 October 2009 107
18 October 2009 136
19 October 2009 124
20 October 2009 135
21 October 2009 106
22 October 2009 109

So you can see that the actual customer numbers vary a lot. However the prediction I get for future days doesn't have a big variation.  I get a figure around 128 and there is +3 - 3 for future dates and then stays at around 128 for December. But my real figures vary alot. I've tried to change the parameters and tried the periodicity_hint to to 7 (7 days of the week).  Cause there is a strong correlation between days of the same week and my results. Is anything I can do with the parameters to make my prediction better? At the moment is not good enough. I am actually not sure that my model understands that my input is a date series, I have it as Key time in the definition, is that enough? Thank you for your help.


NamePankaj on Tue, 20 Jun 2017 08:28:48


I am new to ML and got a task to predict the weekly sale of soft drink bottles based on temperature.

I already have historical data for 10 years having a low and high temperature, date, sales qty of soft drink bottle.

I need to achieve next week sale prediction for soft drink bottles and I've next week weather forecast. so sales qty should be added next to the date, high and low-temperature data.

Any help will be highly appreciable.


Pankaj Srivastava