Delen via

  • Artikel

Creating Time Series Predictions (Intermediate Data Mining Tutorial)

In the previous tasks in this lesson, you created a time series model and explored the results. By default, Analysis Services always creates a prediction for a time series model and displays it as part of the forecasting chart. However, you can also create custom forecasts by building Data Mining Extensions (DMX) prediction queries.

In this task, you will create a prediction query that generates the same predictions that you saw in the viewer. This task assumes that you have already completed the lessons in the Basic Data Mining Tutorial and are familiar with how to use Prediction Query Builder. You will now learn how to create queries specific to time series models.

Creating Time Series Predictions

Typically, the first step in creating a prediction query is to select a mining model and input table. However, a time series model does not require additional input for a regular prediction. Therefore, you do not need to specify a new source of data when making predictions, unless you are adding data to the model or replacing the data. For this lesson, you must specify the number of prediction steps. You can also specify a key value to get a prediction for a particular combination of a product and a region.

Note

A later task in this tutorial introduces a scenario where you replace data in the model with new data to perform cross-prediction. For more information, see Predicting Using the Averaged Forecasting Model (Intermediate Data Mining Tutorial).

To select a model and input table

  1. On the Mining Model Prediction tab of the Data Mining Designer, in the Mining Model box, click Select Model.

  2. In the Select Mining Model dialog box, expand the Forecasting structure, select the Forecasting model from the list, and then click OK.

  3. Ignore the Select Input Table(s) box.

    Note

    For a time series model, you do not need to specify a separate input unless you are doing cross-prediction.

  4. In the Source column, in the grid on the Mining Model Prediction tab, click the cell in the first empty row, and then select Forecasting mining model.

  5. In the Field column, select Model Region.

    This action adds the series identifier to the prediction query to indicate the combination of model and region to which the prediction applies.

  6. Click the next empty row in the Source column, and then select Prediction Function.

  7. In the Field column, select PredictTimeSeries.

    Note

    You can also use the Predict function with time series models. However, by default, the Predict function creates only one prediction for each series. Therefore, to specify multiple prediction steps, you must use the PredictTimeSeries function.

  8. In the Mining Model pane, select the mining model column, Amount. Drag Amount to the Criteria/Arguments box for the PredictTimeSeries function that you added earlier.

  9. Click the Criteria/Arguments box, and type a comma, followed by 5, after the field name.

    The text in the Criteria/Arguments box should now display the following:

    [Forecasting].[Amount],5

  10. In the Alias column, type PredictAmount.

  11. Click the next empty row in the Source column, and then select Prediction Function again.

  12. In the Field column, select PredictTimeSeries.

  13. In the Mining Model pane, select the column Quantity, and then drag it into the Criteria/Arguments box for the second PredictTimeSeries function.

  14. Click the Criteria/Arguments box, and type a comma, followed by 5, after the field name.

    The text in the Criteria/Arguments box should now display the following:

    [Forecasting].[ Quantity],5

  15. In the Alias column, type PredictQuantity.

  16. Click Switch to query result view.

    The results of the query are displayed in tabular format.

Remember that you created three different types of results in the query builder, one that uses values from a column, and two that get predicted values from a prediction function. Therefore, the results of the query contain three separate columns. The first column contains the list of product and region combinations. The second and third columns each contain a nested table of prediction results. Each nested table contains the time step and predicted values, such as the following table:

Example results:

ModelRegion

PredictAmount

PredictQuantity

M200 Europe
$TIMEAmount
7/25/2008264039.4
8/25/2008323995.1
9/25/2008346405.6
10/25/2008337472.8
11/25/2008342890.8
$TIMEQuantity
7/25/2008121
8/25/2008142
9/25/2008152
10/25/2008149
11/25/2008154

M200 North America
$TIMEAmount
7/25/2008372986.4
8/25/2008411315.3
9/25/2008356186.6
10/25/2008412292.1
11/25/2008473739.2
$TIMEQuantity
7/25/2008163
8/25/2008178
9/25/2008156
10/25/2008173
11/25/2008203

Saving the Prediction Results

You have several different options for using the prediction results. You can flatten the results, copy the data from the Results view, and paste it into an Excel worksheet or other file. You can also save the data to a data source view. The functionality for saving results to a data source view is available only in Business Intelligence Development Studio, and the results are stored in the exported table in a flattened format.

To flatten the results in the Results pane

  1. In the Prediction Query Builder, click Switch to query design view.

    The view changes to allow manual editing of the DMX query text.

  2. Type the FLATTENED keyword after the SELECT keyword. The complete query text should be as follows:

    SELECT FLATTENED
  [Forecasting].[Model Region],
  (PredictTimeSeries([Forecasting].[Amount],5)) as [PredictAmount],
  (PredictTimeSeries([Forecasting].[Quantity],5)) as [PredictQuantity]
FROM
  [Forecasting]

  3. Click Switch to query result view.

To export prediction query results

  1. Click Save query results.

  2. In the Save Data Mining Query Result dialog box, for Data Source, select AdventureWorksDW2008R2. You can also create a data source if you want to save the data to a different relational database.

  3. In the Table Name column, type a new temporary table name, such as Test Predictions.

  4. Click Save.

    Note

    To view the table that you created, create a connection to the database engine of the instance where you saved the data, and create a query.