Lesson 2: Adding Mining Models to the Market Basket Mining Structure

Article
12/03/2008

New: 5 December 2005

In this lesson, you will add two mining models to the Market Basket mining structure that you created in Lesson 1: Creating the Market Basket Mining Structure. These mining models will allow you create predictions.

To predict the types of products that customers tend to purchase at the same time, you will create two mining models using the Microsoft Association Algorithm using two different values for the MINIMUM_PROBABILTY parameter.

MINIMUM_PROBABILTY is a Microsoft Association algorithm parameter that is important in determining the number of rules that a mining model will contain by specifying the minimum probability that a rule must have. For example, setting this value to 0.4 specifies that no rule with less than forty percent probability of occurring is generated.

You will explore how the effect of changing the MINIMUM_PROBABILTY parameter in a later lesson.

ALTER MINING STRUCTURE Statement

In order to add a mining model that contains a nested table to the mining structure, you use the ALTER MINING STRUCTURE (DMX) statement. The code in the statement can be broken into the following parts:

Identifying the mining structure
Naming the mining model
Defining the key column
Defining the input and predictable columns
Defining the nested table columns
Identifying the algorithm and parameter changes

The following is a generic example of the CREATE MINING MODEL statement:

ALTER MINING STRUCTURE [<Mining Structure Name>]
ADD MINING MODEL [<Mining Model Name>]
(
    [<key column>],
    <mining model columns>,
    <table columns>
    (  [<nested key column>],
       <nested mining model columns> )
) USING <algorithm>( <algorithm parameters> )

The first line of the code identifies the existing mining structure that the mining model will be added to:

ALTER MINING STRUCTURE [<mining structure name>]

The next line of the code names the mining model that is added to the mining structure:

ADD MINING MODEL [<mining model name>]

For information about naming an object in DMX, see Identifiers (DMX).

The next lines of the code define columns from the mining structure that will be used by the mining model:

[<key column>],
<mining model columns>,

You can only use columns that already exist in the mining structure, and the first column in the list must be the key column from the mining structure.

The remaining lines of the code define the nested table. The first line in the nested table portion of the code defines the name of the nested table:

<table columns>

The second line in the nested table portion of the code defines the key column that binds the nested table to the parent table:

(  [<nested key column>],

The next line in the nested table portion of the code represents the remaining columns in the nested tables that will be used by the mining model.

   <nested mining model columns> )

The last line in the code defines the algorithm and algorithm parameters that will be used to generate the mining model.

) USING <algorithm>( <algorithm parameters> )

You can specify that a column in the mining model will be used for prediction by using the following syntax:

<column name> PREDICT,

Adding an Association Mining Model to the Structure Using the Default MINIMUM_PROBABILTY

The first step is to add a new mining model to the Market Basket mining structure based on the Microsoft Association algorithm using the default value for MINIMUM_PROBABILTY.

To add an Association mining model

In Object Explorer, right-click the instance of Analysis Services, point to New Query, and then click DMX.

Query Editor opens and contains a new, blank query.
Copy the generic example of the ALTER MINING STRUCTURE statement into the blank query.
Replace the following:
```
<mining structure name> 
```
with:
```
Market Basket
```

Replace the following:

<mining model name>

with:

[Default Association]

Replace the following:

<mining model columns>,
<table columns>
(  [<nested key column>],
   <nested mining model columns> )

with:

OrderNumber TEXT KEY,
    [Products] TABLE PREDICT (
        [Model] TEXT KEY
    )

In this case, the [Products] table has been designated as PREDICT.

Replace the following:

USING <algorithm>( <algorithm parameters> )

with:

Using Microsoft_Association_Rules

The resulting statement should now be as follows the following:

ALTER MINING STRUCTURE [Market Basket]
ADD MINING MODEL [Association]
(
    OrderNumber TEXT KEY,
    [Products] TABLE PREDICT (
        [Model] TEXT KEY
    )
)
Using Microsoft_Association_Rules

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

Adding an Association Mining Model to the Structure Changing the Default MINIMUM_PROBABILTY

The next step is to add a new mining model to the Market Basket mining structure based on the Microsoft Association algorithm, and change the default value for MINIMUM_PROBABILTY to 0.01. Changing the parameter will cause the Microsoft Association algorithm to create more rules.

To add an Association mining model

In Object Explorer, right-click the instance of Analysis Services, point to New Query, and then click DMX.

Query Editor opens and contains a new, blank query.
Copy the generic example of the ALTER MINING STRUCTURE statement into the blank query.
Replace the following:
```
<mining structure name> 
```
with:
```
Market Basket
```

Replace the following:

<mining model name>

with:

[Modified Association]

Replace the following:

<mining model columns>,
<table columns>
(  [<nested key column>],
   <nested mining model columns> )

with:

OrderNumber TEXT KEY,
[Products] TABLE PREDICT (
        [Model] TEXT KEY
    )

In this case, the [Products] table has been designated as PREDICT.

Replace the following:

USING <algorithm>( <algorithm parameters> )

with:

USING Microsoft_Association_Rules (Minimum_Probability = 0.1)

The resulting statement should now be as follows:

ALTER MINING STRUCTURE [Market Basket]
ADD MINING MODEL [Modified Assocation]
(
    OrderNumber TEXT KEY,
    [Products] TABLE PREDICT (
        [Model] TEXT KEY
    )
)
USING Microsoft_Association_Rules (Minimum_Probability = 0.1)

The 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_Model.dmx.
On the toolbar, click the Execute button.

In this next lesson you will process the Market Basket mining structure its associated mining models.

Lesson 3: Processing the Market Basket Mining Structure

Lesson 2: Adding Mining Models to the Market Basket Mining Structure

ALTER MINING STRUCTURE Statement

Adding an Association Mining Model to the Structure Using the Default MINIMUM_PROBABILTY

Adding an Association Mining Model to the Structure Changing the Default MINIMUM_PROBABILTY

Next

Additional resources