Lesson 1: Creating the Bike Buyer Mining Structure

Article
12/03/2008

New: 5 December 2005

In this lesson, you will create a mining structure that allows you to predict whether a potential customer of Adventure Works Cycles will purchase a bicycle. If you are unfamiliar with mining structures and their role in data mining, see Mining Structures (Analysis Services).

The Bike Buyer mining structure that you will create in this lesson supports adding mining models based on the Microsoft Decision Trees Algorithm and Microsoft Clustering Algorithm. In later lessons, you will use the clustering mining models to explore the different ways in which customers can be grouped, and will use decision tree mining models to predict whether or not a potential customer will purchase a bicycle.

CREATE MINING STRUCTURE Statement

In order to create a mining structure, you use the CREATE MINING STRUCTURE (DMX) statement. The code in the statement can be broken into the following parts:

Naming the structure
Defining the key column
Defining the mining columns

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

CREATE MINING STRUCTURE [<mining structure name>]
(
    <key column>,
    <mining structure columns>
)

The first line of the code defines the name of the structure:

CREATE MINING STRUCTURE [<mining structure name>]

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

The next line of the code defines the key column for the mining structure, which uniquely identifies an entity in the source data:

<key column>,

In this mining structure, the customer identifier (CustomerKey) defines an entity in the source data.

The final line of the code is used to define the mining columns that will be used by the mining models associated with the mining structure:

<mining structure columns>

You can use the DISCRETIZE function within <mining structure columns> to change values in a continuous column into a series of discrete values, as in the following syntax:

DISCRETIZE(<method>,<number of buckets>)

For more information about discretizing columns, see Discretization Methods. For more information about the types of mining structure columns that you can define, see Mining Structure Columns.

Lesson Tasks

You will perform the following tasks in this lesson:

Create a new blank query
Alter the query to create the mining structure
Execute the query

Creating the Query

The first step is to connect to an instance of Analysis Services and create a new DMX query in SQL Server Management Studio.

To create a new DMX query in SQL Server Management Studio

Open SQL Server Management Studio.
In the Connect to Server dialog box, for Server type, select Analysis Services. In Server name, type LocalHost, or type the name of the instance of Analysis Services that you want to connect to for this lesson. Click Connect.
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.

Altering the Query

The next step is to modify the CREATE MINING STRUCTURE statement described above to create the Bike Buyer mining structure.

To customize the CREATE MINING STRUCTURE statement

In Query Editor, copy the generic example of the CREATE MINING STRUCTURE statement into the blank query.
Replace the following:
```
[<mining structure>] 
```
with:
```
[Bike Buyer]
```
Replace the following:
```
<key column> 
```
with:
```
CustomerKey LONG KEY
```

Replace the following:

<mining structure columns>

with:

   [Age] LONG DISCRETIZED(Automatic,10),
   [Bike Buyer] LONG DISCRETE,
   [Commute Distance] TEXT DISCRETE,
   [Education] TEXT DISCRETE,
   [Gender] TEXT DISCRETE,
   [House Owner Flag] TEXT DISCRETE,
   [Marital Status] TEXT DISCRETE,
   [Number Cars Owned]LONG DISCRETE,
   [Number Children At Home]LONG DISCRETE,
   [Occupation] TEXT DISCRETE,
   [Region] TEXT DISCRETE,
   [Total Children]LONG DISCRETE,
   [Yearly Income] DOUBLE CONTINUOUS

The complete mining structure statement should now be as follows:

CREATE MINING STRUCTURE [Bike Buyer]
(
   [Customer Key] LONG KEY,
   [Age]LONG DISCRETIZED(Automatic,10),
   [Bike Buyer] LONG DISCRETE,
   [Commute Distance] TEXT DISCRETE,
   [Education] TEXT DISCRETE,
   [Gender] TEXT DISCRETE,
   [House Owner Flag] TEXT DISCRETE,
   [Marital Status] TEXT DISCRETE,
   [Number Cars Owned]LONG DISCRETE,
   [Number Children At Home]LONG DISCRETE,
   [Occupation] TEXT DISCRETE,
   [Region] TEXT DISCRETE,
   [Total Children]LONG DISCRETE,
   [Yearly Income] DOUBLE CONTINUOUS
)

On the File menu, click Save DMXQuery1.dmx As.
In the Save As dialog box, locate the appropriate folder, and name the file Bike Buyer Structure.dmx.

Executing the Query

The final step is to execute the query. After you create and save a query, you must run the statements in the query to create the mining structure on the server. For more information about executing queries in Query Editor, see SQL Server Management Studio Transact SQL Query.

To execute the query

In Query Editor, on the toolbar, click Execute.

The status of the query is displayed in the Messages tab at the bottom of Query Editor after the statement finishes executing. Messages should display:
```
Executing the query 
Execution complete
```
A new structure named Bike Buyer now exists on the server.

In the next lesson, you will add mining models to the structure you just created.

Next Lesson

Lesson 2: Adding Mining Models to the Bike Buyer Mining Structure