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

SQL Server Data Mining: Time Series Algorithm Tips

  • The Time Series algorithm has several parameters that may be adjusted if needed.  I have tried adjusting several of these parameter settings and determined that the following appear to have the most impact:
    • MISSING_VALUE_SUBSTITUTION:  If you have any blanks or gaps in your data and the MISSING_VALUE_SUBSTITUTION parameter is not set appropriately, your model processing will fail.  There are several options you can select from when setting this parameter:
      • Previous:  The prior value is used when missing data is encountered.
      • Mean: The missing data is replaced with the mean of the entire series.
      • A Number:  You may also specify a hard-coded value to be used whenever missing data is encountered.
    • PERIODICITY_HINT:  If your data has periodic or seasonal trends, you’ll want to make sure this parameter is set to accurately reflect these trends.  For example, if your data has quarterly and yearly periodicity you should set the PERIODICITY_HINT to {3,12}.
    • AUTO_DETECT_PERIODICITY:  This parameter controls how aggressive the algorithm is at finding periodicities in the data.  A value close to 1 causes the algorithm to detect very subtle periodicities.  In contrast, setting this parameter to 0 will only detect the strongest periodicities (if any exist).  If you set the PERIODICITY_HINT parameter, then the AUTO_DETECT_PERIODICITY is automatically set to a low value and the algorithm will abide by any settings you specified in the PERIODICITY_HINT parameter.
    • MINIMUM and MAXIMUM_SERIES_VALUE:  These parameters allow you to specify a top and bottom range of valid values for the predictions.
  • The time series algorithm is extremely sensitive to the amount and quality of historic data.  Minimal or erratic historic data is an issue since the algorithm doesn’t have enough consistent history to learn from or discern useful patterns.  Too much history may also be an issue.  Extremely old data may no longer be indicative of future trends; therefore, it may make sense to exclude this data from the model.  In my experience, roughly 20 historic data points seems to be the minimum amount of data to obtain reasonable predictions.  If your data has periodicity, you’ll want to make sure you have enough history to cover several iterations of the periodicity interval (if your data has yearly periodicity you’ll want to have several years of history).
  • A common problem in time series predictions is limited historic data.  As discussed earlier, you need a reasonable amount of consistent history to obtain accurate predictions.  If you only have a few data points, but want to make predictions based off of the data then you may be able to use a model built with a different dataset, assuming you anticipate the new series to behave similarly.  This technique involves using the REPLACE_MODEL_CASES flag.  An example of the technique is detailed in the article ‘Predicting Future Steps When You Do Not Have Enough History’ on the website www.sqlserverdatamining.com
  • Testing the accuracy of a Time Series model is also a challenge – waiting for several months into the future to see if your model works well is often not a viable option.  The HISTORICAL_MODEL_GAP and HISTORICAL_MODEL_COUNT parameters can be used to test your model.  When these parameters are set, the Microsoft Time Series algorithm  builds additional models equal to the HISTORICAL_MODEL_COUNT value.  These models are trained with a truncated series.  The HISTORICAL_MODEL_GAP parameter determines how much of the data is truncated. You can then use the PredictTimeSeries function with negative values to ‘predict’ historic data.  An example of this technique is covered in the article ‘Determining the Accuracy of a Time Series Forecast’ on the website www.sqlserverdatamining.com
  • Another means to check the quality of predictions is via supplemental statistics (standard deviation, variance, and content node ID).  The article named ‘Determining the Accuracy of a Time Series Forecast’ on the website www.sqlserverdatamining.com includes example DMX to obtain the supplemental statistics from a data mining model.

The sqlserverdatamining site has a wealth of information related to many of the SQL Server data mining algorithms.  The ‘Tips and Tricks’ section of the site is especially useful.  An online forecasting tutorial covering the Time Series algorithm is also available at http://technet.microsoft.com/en-us/library/ms169846.aspx.

Salvo(z) SQL

Adam and Jennifer Salvo are IT professionals with over 10 years of diverse experience. Jennifer is a Business Intelligence developer focusing on the Microsoft BI stack (SSIS, SSAS and SSRS). Her prior work experience includes software development, systems analysis, end-user support, training, and SharePoint administration. Adam is a .NET technical lead with a current emphasis on Dev Ops and Windows Azure. His prior work experience includes .NET development, SQL Server administration, and BizTalk development. They also maintain a personal blog at salvoz.com.


Leave a comment on the original post [salvoz.com, opens in a new window]

Loading comments...