Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Data Mining: Part 12 DMX for Data Mining

By Daniel Calbimonte,

Introduction

In the lesson 11, we learned XMLA to process Data Mining objects, delete them and create or modify them. We also mentioned that DMX  is similar to T-SQL. In this lesson, we will learn:

  • How to create a Mining Structure in DMX
  • How to create a Mining Model with DMX
  • How to query a Model with DMX.
  • Some functions in DMX that will be useful.

The DMX means Data Mining Extensions. These is the language used by Microsoft to create and query mining models. We will learn the basis in this lesson.

Requirements

We will work with the Adventurewok Project used in earlier chapters.

Getting Started

Let's start creating a Data Mining Structure. In order to start, start SQL Server Management Studio and open Analysis Services.

Press the DMX icon to start doing DMX queries.

In the Connect to Analysis Services Window, specify your server and press Connect.

In the Database combobox, select your Adventure Works DW database.

The first query is similar to a create table in T-SQL. Instead of using the create table you create a mining structure.  The script creates a structure Named MyStructure. Press F5 to execute these DMX statements:

CREATE MINING STRUCTURE [MyStructure]
(
   [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
)

The structure can be used by many Data Mining Algorithms.  In this sample, the name of the structure is MyStructure. The columns can be:

  • Text 
  • Boolean
  • Long
  • Double 
  • Date

For more information about Data Types, go to the Microsoft Data Types help.

The columns can also have a content type. The content type can be:

  • Discrete (which contains a finite number)
  • Discretized (a continue numbers converted to discrete numbers)
  • Continuos  (which contains an ifinite number)
  • Key (unique identifier)
  • Key sequence (for the sequence cluster algorithm)
  • Table (when the column contains a table)
  • Cyclical (for a cyclical order set)
  • Ordered (for sequencial order)
  • Classified (to create relationships between columns).

For more information about  Content Types, you can go to the Microsoft Documentation.

If you refresh the object browser you will be able to see the Data Mining Structure just created.

The next step is to create Mining Models using the current structure. As I said before, the Mining Structure can have many models. In this sample we are going to Alter the Mining Structure just created and add a Decision Tree Model named MyTreeModel:

ALTER MINING STRUCTURE [MyStructure]
ADD MINING MODEL [MyTreeModel]
(
   [Customer Key],
   [Age],
   [Bike Buyer] PREDICT,
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]
) USING Microsoft_Decision_Trees
WITH DRILLTHROUGH

We are using the Alter sentence to modify the Mining Structure. The Add Mining Model Sentence will add the new model to the structure. The value to Predict is the Bike Buyer column which returns a value of 0 if the customer is not a buyer and 1 if he is. 

The  USING Microsoft_Desicion_Trees specified the algorith used by the model and the WITH DRILLTHROUGH option allows to get details about the model.

Execute the script with F5 and verify that the new Mining model named MyTreeModel is created.

The next step is to add information to the mining Structure. We are going to use the Database from the SQL Server Database Engine. The database name is AdventureWorksDW2012. Verify the name of your DW Adventureworks to include the exact name in the OPENQUERY Sentence. The view with the information about the customers is dbo.TargetMail. We are going to load the information from this view to the Mining Structure named MyStructure using the INSERT INTO Sentence:

INSERT INTO MINING STRUCTURE [MyStructure]
(
   [Customer Key],
   [Age],
   [Bike Buyer],
   [Commute Distance],
   [Education],
   [Gender],
   [House Owner Flag],
   [Marital Status],
   [Number Cars Owned],
   [Number Children At Home],
   [Occupation],
   [Region],
   [Total Children],
   [Yearly Income]   
)
OPENQUERY([AdventureWorksDW2012],
   'SELECT CustomerKey, Age, BikeBuyer,
         CommuteDistance,EnglishEducation,
         Gender,HouseOwnerFlag,MaritalStatus,
         NumberCarsOwned,NumberChildrenAtHome, 
         EnglishOccupation,Region,TotalChildren,
         YearlyIncome 
    FROM dbo.vTargetMail')
We now have a Mining Structure, a mining model and data.  The model is ready for prediction queries:
SELECT
  Predict([MyTreeModel].[Bike Buyer])
 From
  [MyTreeModel]
NATURAL PREDICTION JOIN
 (SELECT 37 AS [Age],
  '5-10 Miles' AS [Commute Distance],
  'Bachelors' AS [Education],
  'M' AS [Gender],
  'S' AS [Marital Status],
   0   AS [Number Cars Owned],
  'Professional' AS [Occupation],
   0 AS [Total Children]) AS t

The query is a select to the Mining Model MyTreeModel. We are using the predict function which will return the value of  1 if the customer with a Commute Distance of 5-10 miles, who is single (S) with 0 Number of Cars and Professional, 37 years old, with Bachelors Education, Male (M) will buy a bike.

If you run the query the Result will be 1. That means that a user with the characterists specified will buy a bike.

Now if we change the query with different information about the customer we will notice that this new customer with the new characteristics will not buy a bike and the result of the query will be 0.

SELECT
  Predict([MyTreeModel].[Bike Buyer])
 From
  [MyTreeModel]
NATURAL PREDICTION JOIN
(SELECT 37 AS [Age],
  '2-5 Miles' AS [Commute Distance],
  'Bachelors' AS [Education],
  'M' AS [Gender],
  'S' AS [Marital Status],
  2 AS [Number Cars Owned],
  'Professional' AS [Occupation],
  2 AS [Total Children]) AS t

This new sample uses the predictProbability. Instead of returning a 0 or a 1 it returns the exact value between 0 and 1. In this sample the value returned is 0.5989. 

The PredictHistogram shows more complete information. It gives probabilities, the support, adjusted Probability, Variance, The Standard Deviation.

These new sample shows how to use the PredictSupport function. It shows the number of cases that follows the queries.

Finally, you can create your DMX queries using the  Build Prediction Query option.

You can specify the values of the columns with the information to query.

Right click to switch to the Query mode:

Now you have your DMX query ready.

Conclusion

In this sample, we learned simple samples to create a Data Mining Structure with DMX, we created a Mining Model, we then load the model with information from a SQL Server View and then we used DMX queries to predict if a customer with specific information will buy or not a bike.

References

http://technet.microsoft.com/en-us/library/ms174796.aspx

http://msdn.microsoft.com/en-us/library/ms174572.aspx

http://msdn.microsoft.com/en-us/library/cc645772.aspx

Resources:

DMXQuery2.dmx

This article is part of the series Data Mining with Daniel Calbimonte:

Total article views: 2000 | Views in the last 30 days: 130
 
Related Articles
FORUM

Hierarchy Structure SQL Query

Hierarchy Structure SQL Query

FORUM

Query Structure Grouping/Averaging

Query Structure Grouping/Averaging

BLOG

SSAS Dimension Attribute Discretization Methods

Discretization (one of my favorite tech words to say J) is a really great feature of Analysis Servic...

FORUM

Creating database tables from models

How to create a database when given the model - usually with nested entities and inheritance

FORUM

Created database but used Model

new database was created with Model selected as db rather than Master

Tags
basic    
data    
data mining    
mdx    
microsoft    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones