The Mining Model Prediction Builder pane allows you to build and edit prediction queries, view the results, and save the results back to a table. The Query Builder has three views, Design View, Query View, and Results View, which you select using the View button on the toolbar.
The Design View for building queries is similar to those in other products such as Access, so if you are familiar with them, you should feel right at home.
The process of selecting input tables is the same as in the Mining Accuracy Chart. The only difference is that you also have to indicate which model from the structure you are using as well. After doing so, you need to build your query. You do so by dragging and dropping columns from the model or input tables to the grid below. In addition to dragging and dropping columns, the grid control allows you to manually specify output columns by clicking in empty cells and selecting from drop-down controls that appear or typing as appropriate.
Note: You can drag only columns that are predictable from the model. These columns are marked with a diamond in the Query Builder.
When selecting a prediction function, the drop-down list in the Field column contains all of the functions that are allowed on the algorithm used by the selected model. You specify the function parameters in the Criteria/Argument column, which will contain a description of what type of parameters you can use.
The Query View shows you the query generated by the builder. You can copy and paste this query into your applications if needed or edit it here as necessary. This view is very handy for modifying queries to use constructs that aren't supported by the query builder, such as SELECT TOP n <column> FROM <model> ORDER BY PredictProbability(<column>) to retrieve the respondents above the threshold you determined using the accuracy charts. Note that the edits you make to the query are not reflected in Design View, so switching back to that view will override your changes.
Switching to the Results View executes your query and displays the results in a table. You can copy the results to other applications or click the Save Query Result button to export the results to a database table.
Note: You can export your results to a table and add this table to your DSV to perform iterative data mining.
Executing a Query on the MovieClick Model
We will execute a query that will return the predicted frequency that a customer goes to movies at theaters, along with the probability that the customer would have done so on a monthly basis.
1. Switch to the query builder by choosing the Mining Query Builder icon in the Data Mining designer.
2. Click Select case table on the Select Input Tables window in the top pane of the view.
3. Select the Homeowners table in the dialog box that appears, and click OK.
4. Drag the Customer ID column from the Homeowners table, and drop it on the grid.
5. Drag the Theater Freq column from the mining model, and drop it on the grid.
6. In the Source column of the last row, select Prediction Function.
7. In the Field column of that row, select PredictProbability.
8. In the Alias column of that row, type ProbMonthly.
9. Drag the Theater Freq column from the mining model, and drop it into the Criteria/Argument column of that row.
10. Edit the Criteria/Argument column of that row, and add 'Monthly' to the end of the text already there.
11. Switch to the Query view using the drop-down button on the Query Builder toolbar to view the query and see how much typing you saved.
12. Switch to the Results view to execute the query and see the results.
The query is executed on a separate thread and the results are streamed to the client. This means that the user interface is still active while the query is running, so you can cancel it or perform any other user interface task while it runs. When it is complete, you can copy the results or save them to a database.
Click here to return to the complete list of book excerpts from Chapter 3, 'Using SQL Server 2005 data mining,' from the book Data Mining with SQL Server 2005.
This was first published in July 2006