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 prediction for a time series model and displays them 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, but want to 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, all you need to do is 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
On the Mining Model Prediction tab of Data Mining Designer, in the Mining Model box, click Select Model.
In the Select Mining Model dialog box, expand the Forecasting structure, select the Forecasting model from the list, and then click OK.
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.
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.
In the Field column, select Model Region.
This adds the series identifier to the prediction query to signify which combination of model and region the prediction applies to.
Click the next empty row in the Source column, and then select Prediction Function.
In the Field column, select PredictTimeSeries.
Note
You can also use the Predict function with a time series model. However, by default it creates only one prediction for each series. Therefore, to specify the number of prediction steps, you will use the PredictTimeSeries function.
In the Mining Model pane, select the mining model column, Amount, and drag it into the Criteria/Arguments box for the PredictTimeSeries function that you just added.
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 look like this:
[Forecasting].[Amount],5
In the Alias column, type PredictAmount.
Click the next empty row in the Source column, and then select Prediction Function again.
In the Field column, select PredictTimeSeries.
In the Mining Model pane, select the column Quantity, and drag it into the Criteria/Arguments box for the second PredictTimeSeries function.
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 look like this:
[Forecasting].[ Quantity],5
In the Alias column, type PredictQuantity.
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 time step and predicted values, like the following.
Example results:
ModelRegion |
PredictAmount |
PredictQuantity |
|---|---|---|
M200 Europe |
$TIMEAmount
7/25/2004264039.4
8/25/2004323995.1
9/25/2004346405.6
10/25/2004337472.8
11/25/2004342890.8
|
$TIMEQuantity
7/25/2004121
8/25/2004142
9/25/2004152
10/25/2004149
11/25/2004154
|
M200 North America |
$TIMEAmount
7/25/2004372986.4
8/25/2004411315.3
9/25/2004356186.6
10/25/2004412292.1
11/25/2004473739.2
|
$TIMEQuantity
7/25/2004163
8/25/2004178
9/25/2004156
10/25/2004173
11/25/2004203
|
Saving the Prediction Results
After you have obtained the prediction results, you have several options for working with this data. You can flatten the results, copy the data from the Results view, and paste into an Excel worksheet or other file, or 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
In the Prediction Query Builder, click Switch to query design view.
The view changes to allow manual editing of the DMX query text.
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]Click Switch to query result view.
To export prediction query results
Click Save query results.
In the Save Data Mining Query Result dialog box, for Data Source, select Adventure Works DW2008. You can also create a new data source if you want to save the data to a different relational database.
For Table Name, type a new temporary table name, such as Test Predictions.
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.