Lesson 4: Executing Market Basket Predictions

Article
08/31/2016

Applies To: SQL Server 2016 Preview

In this lesson, you will use the DMX SELECT statement to create predictions based on the association models you created in Lesson 2: Adding Mining Models to the Market Basket Mining Structure. A prediction query is created by using the DMX SELECT statement and adding a PREDICTION JOIN clause. For more information about the syntax of a prediction join, see SELECT FROM <model> PREDICTION JOIN (DMX).

The SELECT FROM PREDICTION JOIN form of the SELECT statement contains three parts:

A list of the mining model columns and prediction functions that are returned in the result set. This list can also contain input columns from the source data.
A source query that defines the data that is being used to create a prediction. For example, if you are creating many predictions in a batch, the source query could retrieve a list of customers.
A mapping between the mining model columns and the source data. If the columns names match, you can use the NATURAL PREDICTION JOIN syntax and omit the column mappings.

You can enhance the query by using prediction functions. Prediction functions provide additional information, such as the probability of a prediction occurring, or the support for a prediction in the training dataset. For more information about prediction functions, see Functions (DMX).

You can also use the prediction query builder in SQL Server Data Tools (SSDT) to create prediction queries.

Singleton PREDICTION JOIN Statement

The first step is to create a singleton query, by using the SELECT FROM PREDICTION JOIN syntax and supplying a single set of values as input. The following is a generic example of the singleton statement:

SELECT <select list>  
    FROM [<mining model>]   
[NATURAL] PREDICTION JOIN  
(SELECT '<value>' AS [<column>],   
    (SELECT 'value' AS [<nested column>] UNION  
        SELECT 'value' AS [<nested column>] ...)   
    AS [<nested table>])  
AS [<input alias>]

The first line of the code defines the columns from the mining model that the query returns, and specifies the name of the mining model used to generate the prediction:

SELECT <select list> FROM [<mining model>]

The next line of the code indicates the operation to perform. Because you will specify values for each of the columns and type the column names exactly so as to match the model, you can use the NATURAL PREDICTION JOIN syntax. However, if the column names were different, you would have to specify mappings between the columns in the model and the columns in the new data by adding an ON clause.

[NATURAL] PREDICTION JOIN

The next lines of the code define the products in the shopping cart that will be used to predict additional products that a customer will add:

(SELECT '<value>' AS [<column>],   
    (SELECT 'value' AS [<nested column>] UNION  
        SELECT 'value' AS [<nested column>] ...)   
    AS [<nested table>])

Lesson Tasks

You will perform the following tasks in this lesson:

Create a query that predicts what other items a customer will likely purchase, based on items already existing in their shopping cart. You will create this query by using the mining model with the default MINIMUM_PROBABILITY.
Create a query that predicts what other items a customer will likely purchase based on items already existing in their shopping cart. This query is based on a different model, in which MINIMUM_PROBABILITY has been set to 0.01. Because the default value for MINIMUM_PROBABILITY in association models is 0.3, the query on this model should return more possible items than the query on the default model.

Create a Prediction by Using a Model with the Default MINIMUM_PROBABILITY

To create an association query

In Object Explorer, right-click the instance of Analysis Services, point to New Query, and then click DMX to open the Query Editor.
Copy the generic example of the PREDICTION JOIN statement into the blank query.
Replace the following:
```
<select list>   
```
with:
```
PREDICT([Default Association].[Products],INCLUDE_STATISTICS,3)  
```
You could just include the column name [Products], but by using the Predict (DMX) function, you can limit the number of products that are returned by the algorithm to three. You can also use INCLUDE_STATISTICS, which returns the support, probability, and adjusted probability for each product. These statistics help you rate the accuracy of the prediction.

Replace the following:

[<mining model>]

with:

[Default Association]

Replace the following:

(SELECT '<value>' AS [<column>],   
    (SELECT 'value' AS [<nested column>] UNION  
        SELECT 'value' AS [<nested column>] ...)   
    AS [<nested table>])

with:

(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]  
  UNION SELECT 'Mountain Tire Tube' AS [Model]  
  UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t

This statement uses the UNION statement to specify three products that must be included in the shopping cart together with the predicted products. The Model column in the SELECT statement corresponds to the model column that is contained in the nested products table.

The complete statement should now be as follows:

SELECT  
  PREDICT([Default Association].[Products],INCLUDE_STATISTICS,3)  
From  
  [Default Association]  
NATURAL PREDICTION JOIN  
(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]  
  UNION SELECT 'Mountain Tire Tube' AS [Model]  
  UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t

On the File menu, click Save DMXQuery1.dmx As.
In the Save As dialog box, browse to the appropriate folder, and name the file Association Prediction.dmx.
On the toolbar, click the Execute button.

The query returns a table that contains three products: HL Mountain Tire, Fender Set - Mountain, and ML Mountain Tire. The table lists these returned products in order of probability. The returned product that is most likely to be included in the same shopping cart as the three products specified in the query appears at the top of the table. The two products that follow are the next most likely to be included in the shopping cart. The table also contains statistics describing the accuracy of the prediction.

Create a Prediction by Using a Model with a MINIMUM_PROBABILITY of 0.01

To create an association query

In Object Explorer, right-click the instance of Analysis Services, point to New Query, and then click DMXto open the Query Editor.
Copy the generic example of the PREDICTION JOIN statement into the blank query.

Replace the following:

<select list>

with:

PREDICT([Modified Association].[Products],INCLUDE_STATISTICS,3)

Replace the following:

[<mining model>]

with:

[Modified Association]

Replace the following:

(SELECT '<value>' AS [<column>],   
    (SELECT 'value' AS [<nested column>] UNION  
        SELECT 'value' AS [<nested column>] ...)   
    AS [<nested table>])

with:

(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]  
  UNION SELECT 'Mountain Tire Tube' AS [Model]  
  UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t

This statement uses the UNION statement to specify three products that must be included in the shopping cart together with the predicted products. The [Model] column in the SELECT statement corresponds to the column in the nested products table.

The complete statement should now be as follows:

SELECT  
  PREDICT([Modified Association].[Products],INCLUDE_STATISTICS,3)  
From  
  [Modified Association]  
NATURAL PREDICTION JOIN  
(SELECT (SELECT 'Mountain Bottle Cage' AS [Model]  
  UNION SELECT 'Mountain Tire Tube' AS [Model]  
  UNION SELECT 'Mountain-200' AS [Model]) AS [Products]) AS t

On the File menu, click Save DMXQuery1.dmx As.
In the Save As dialog box, browse to the appropriate folder, and name the file Modified Association Prediction.dmx.
On the toolbar, click the Execute button.

The query returns a table that contains three products: HL Mountain Tire, Water Bottle, and Fender Set - Mountain. The table lists these products in order of probability. The product that appears at the top of the table is the product that is most likely to be included in the same shopping cart as the three products specified in the query. The remaining products are the next most likely to be included in the shopping cart. The table also contains statistics that describe the accuracy of the prediction.

You can see from the results of this query that the value of the MINIMUM_PROBABILITY parameter affects the results returned by the query.

This is the last step in the Market Basket tutorial. You now have a set of models that you can use to predict the products that customers might purchase at the same time.

To learn how to use DMX in another predictive scenario, see Bike Buyer DMX Tutorial.

Share via

Lesson 4: Executing Market Basket Predictions

Singleton PREDICTION JOIN Statement

Lesson Tasks

Create a Prediction by Using a Model with the Default MINIMUM_PROBABILITY

To create an association query

Create a Prediction by Using a Model with a MINIMUM_PROBABILITY of 0.01

To create an association query

See Also

Additional resources