PredictTimeSeries

Returns predicted future or historical values for time series data. Time series data is continuous and can be stored in a nested table or in a case table. The PredictTimeSeries function always returns a nested table.

Syntax

PredictTimeSeries(<table column reference>)
PredictTimeSeries(<table column reference, n>)
PredictTimeSeries(<table column reference, n-start, n-end>)
PredictTimeSeries(<scalar column reference>)
PredictTimeSeries(<scalar column reference, n>)
PredictTimeSeries(<scalar column reference, n-start, n-end>)

Return Type

A <table expression>.

Remarks

If the n parameter is specified, the PredictTimeSeries function returns the following values:

• If n is greater than 0, the most likely time series values in the next n steps.
• If n is less than 0, predicted historical values.
• If both n-start and n-end are specified, the time series values from n-start to n-end. If n-start is negative, the predicted series includes -(n-start) predicted historical values.

Historical prediction is limited by the following Microsoft Time Series algorithm parameters: HISTORIC_MODEL_COUNT and HISTORICAL_MODEL_GAP. To perform historical predictions, n-start should be greater than the result of the following formula:

-HISTORICAL_MODEL_COUNT*HISTORICAL_MODEL_GAP

For more information about using HISTORICAL_MODEL_COUNT and HISTORICAL_MODEL_GAP, see Microsoft Time Series Algorithm.

The PredictTimeSeries function does not support prediction flags, and does not support parameters such as INCLUDE_STATISTICS.

Examples

The following example uses the PredictTimeSeries function to return a prediction for the next three time steps for the M200 Europe series.

SELECT
  [Model Region],
  PredictTimeSeries([Forecasting].[Amount],3)
From
  [Forecasting]
WHERE [Model Region]= 'M200 Europe'

Although the PredictTimeSeries function does not support INCLUDE_STATISTICS as a parameter, the following query can be used to return the prediction statistics for a time series query. This approach can also be used with models that have nested table columns.

In this particular model, the predictable attribute is Quantity. Therefore, you must use Quantity as the first argument to the PredictTimeSeries function. If your model uses a different predictable attribute, you can substitute a different column name.

SELECT FLATTENED [Model Region],
(SELECT 
     $Time,
     [Quantity] as [PREDICTION], 
     PredictVariance([Quantity]) AS [VARIANCE],
     PredictStdev([Quantity]) AS [STDEV]
FROM
      PredictTimeSeries([Quantity], 3) AS t
) AS t
FROM Forecasting
WHERE [Model Region] = 'M200 Europe'
OR [Model Region] = 'M200 North America'

See Also

Reference

Data Mining Extensions (DMX) Function Reference
Functions (DMX)
Mapping Functions to Query Types (DMX)

Other Resources

Data Mining Algorithms

Help and Information

Getting SQL Server 2005 Assistance

Change History

Updated content
Remove mentions of parameter that cannot be used with this model type.
Added sample of query that provides prediction statistics.