The management team at Adventure Works Cycles wants to predict monthly bicycle sales for the coming year. The company is especially interested in whether the sale of one bike model can be used to predict the sale of another model. By using the Microsoft Time Series algorithm on historical data from the past three years, the company can produce a data mining model that forecasts future bike sales. Additionally, the company can perform cross predictions to see whether the sales trends of individual bike models are related.

Each quarter, the company plans to update the model with recent sales data and update their predictions to model recent trends. To correct for stores that do not accurately or consistently update sales data, they will create a general prediction model, and use that to create predictions for all regions.

In SQL Server 2005, the Microsoft Time Series algorithm used a single algorithm, ARTxp. The ARTXp algorithm was optimized for short-term predictions, and therefore, predicted the next likely value in a series. In SQL Server 2008, the Microsoft Time Series algorithm uses both the ARTxp algorithm and a second algorithm, ARIMA. The ARIMA algorithm is optimized for long-term prediction. For a detailed explanation about the implementation of the ARTxp and ARIMA algorithms, see Microsoft Time Series Algorithm Technical Reference.

By default, the Microsoft Time Series algorithm uses a mix of the algorithms when it analyzes patterns and making predictions. The algorithm trains two separate models on the same data: one model uses the ARTxp algorithm and one model uses the ARIMA algorithm. The algorithm then blends the results of the two models to yield the best prediction over a variable number of time slices. Because ARTxp is best for short-term predictions, it is weighted more heavily at the beginning of a series of predictions. However, as the time slices that you are predicting move further into the future, ARIMA is weighted more heavily.

You can also control the mix of algorithms to favor either short- or long-term prediction in the times series. In SQL Server 2008 Standard, you can specify that the Microsoft Time Series algorithm use one of the following settings:

• Use only ARTxp for short-term prediction.
  
• Use only ARIMA for long-term prediction.
  
• Use the default blending of the two algorithms.
  

In SQL Server 2008 Enterprise, you can customize how the Microsoft Time Series algorithm blends the models for prediction. When you use a mixed model, the Microsoft Time Series algorithm blends the two algorithms in the following way:

• Only ARTxp is always used for making the first couple of predictions.
  
• After the first couple of predictions, a combination of ARIMA and ARTxp is used.
  
• As the number of prediction steps increases, predictions rely more heavily on ARIMA until ARTxp is no longer used.
  
• You control the mixing point, the rate at which the weight of ARTxp is decreased, and the weight of ARIMA is increased by setting the PREDICTION_SMOOTHING parameter.
  

Both algorithms can detect seasonality in data at multiple levels. For example, your data might contain monthly cycles nested within yearly cycles. To detect these seasonal cycles, you can either provide a periodicity hint or specify that the algorithm should automatically detect periodicity.

In addition to periodicity, there are several other parameters that control the behavior of the Microsoft Time Series algorithm when it detects periodicity, makes predictions, or analyzes cases. For information about how to set algorithm parameters, see Microsoft Time Series Algorithm Technical Reference.

When you prepare data for use in training any data mining model, make sure that you understand the requirements for the particular model and how the data is used.

Each forecasting model must contain a case series, which is the column that specifies the time slices or other series over which change occurs. For example, the data in the previous diagram shows the series for historical and forecasted bicycle sales over a period of several months. For this model, each region is a series, and the date column contains the time series, which is also the case series. In other models, the case series can be a text field or some identifier such as a customer ID or transaction ID. However, a time series model must always use a date, time, or some other unique numeric value for its case series.

The requirements for a time series model are as follows:

• A single key time column   Each model must contain one numeric or date column that is used as the case series, which defines the time slices that the model will use. The data type for the key time column can be either a datetime data type or a numeric data type. However, the column must contain continuous values, and the values must be unique for each series. The case series for a time series model cannot be stored in two columns, such as a Year column and a Month column.
  
• A predictable column   Each model must contain at least one predictable column around which the algorithm will build the time series model. The data type of the predictable column must have continuous values. For example, you can predict how numeric attributes, such as income, sales, or temperature, change over time. However, you cannot use a column that contains discrete values, such as purchasing status or level of education, as the predictable column.
  
• An optional series key column   Each model can have an additional key column that contains unique values that identify a series. The optional series key column must contain unique values. For example, a single model can contain sales for many product models, as long as there is only one record for each product name for every time slice.
  

You can define input data for the Microsoft Time Series model in several different ways. However, because the format of the input cases affects the definition of the mining model, you must consider your business needs and prepare your data accordingly. The following two examples illustrate how the input data affects the model. In both examples, the completed mining model contains patterns for four distinct series:

• Sales for Product A
  
• Sales for Product B
  
• Volume for Product A
  
• Volume for Product B
  

In both examples, you can predict new future sales and volume for each product. You cannot predict new values for product or for time.

Example 1: Two Predictable Columns

Example 2: Four Predictable Columns

After the model has been trained, the results are stored as a set of patterns, which you can explore or use to make predictions.

To explore the model, you can use the Time Series Viewer. The viewer includes a chart that displays future predictions, and a tree view of the periodic structures in the data.

If you want to know more about how the predictions are calculated, you can browse the model in the Microsoft Generic Content Tree Viewer. The content stored for the model includes details such as the periodic structures detected by the ARIMA and ARTxp algorithms, the equation used to blend the algorithms, and other statistics.

By default, when you view a time series model, Analysis Services shows you five predictions for the series. However, you can create queries to return a variable number of predictions, and you can extra columns to the predictions to return descriptive statistics. For information about how to create queries against a time series model, see Querying a Time Series Model (Analysis Services - Data Mining). For examples of how to use Data Mining Extensions (DMX) to make time series predictions, see PredictTimeSeries (DMX).

When using the Microsoft Time Series algorithm to make predictions, you should consider the following additional restrictions and requirements:

• Cross-prediction is only available when you use a mixed model, or a model based on the ARTxp algorithm. If you use a model based only on the ARIMA algorithm, cross-prediction is not possible.
  
• A time series model can make predictions that differ, sometimes significantly, depending on the 64-bit operating system that the server uses. These differences occur due to the way that an Itanium-based system represents and handles numbers for floating-point arithmetic, which differs from the way that an x64-based system does these calculations. Because prediction results can be specific to the operating system, we recommend that you evaluate models on the same operating system that you will use in production.
  

• Does not support using the Predictive Model Markup Language (PMML) to create mining models.
  
• Supports the use of OLAP mining models.
  
• Does not support the creation of data mining dimensions.
  
• Supports drillthrough.
  

Reference

Concepts

Other Resources

Help and Information