Data mining development lead Jamie Mac at Microsoft blogged about executing the multiple dmx query statements in one single document. I was very excited about this post because just like TSQL queries, I really wanted to execute my dmx queries in one document. My understanding was that it can't be done and I did try it in different variations with out success.
Executing multiple statements in a single document really helps in testing out your queries specially when creating/dropping structures/models. I don't want to have my test structures/models laying around while I am testing concept. Also, when I post a sample code on news group, it helps to copy past the entire script and some one could just execute it with a single F5 (provided they have all the data source objects in place)....
After reading his post, I went ahead and tried it and it works beautiful.
Query text is as follows. Please go ahead and try it out.
Thanks
Happy data mining,
ZULFIQAR SYED,
Have you mined your data today?
======
=== WARNING: THIS SCRIPT DROPS STRUCTURE/MODEL ====
=== WARNING: EXECUTE THIS SCRIPT AT YOUR OWN RISK ====
// ===========================
// Create Structure
// ============================
CREATE MINING STRUCTURE bb_st
(
CustomerKey long key,
Age long continuous,
BikeBuyer long discrete ,
commutedistance text discrete,
gender text discrete,
houseownerflag long discrete,
numbercarsowned long discrete,
yearlyincome long continuous
) ;
GO
// ============================
// Add Model
// ============================
ALTER MINING STRUCTURE bb_st
ADD MINING MODEL bb_dt
(
CustomerKey ,
Age ,
BikeBuyer predict,
commutedistance ,
gender ,
houseownerflag ,
numbercarsowned ,
yearlyincome
) USING Microsoft_Decision_Trees;
GO
// ==========================
// Train Mining Model
//
// Trains a model inside a
// previously trained structure
// ==========================
INSERT INTO MINING MODEL bb_dt
(
CustomerKey ,
Age ,
BikeBuyer ,
commutedistance ,
gender ,
houseownerflag ,
numbercarsowned ,
yearlyincome
)
openquery([Adventure Works DW],
'select
CustomerKey ,
Age ,
BikeBuyer predict,
commutedistance ,
gender ,
houseownerflag ,
numbercarsowned ,
yearlyincome
from train');
// ==========================
// Base Prediction
// ==========================
go
SELECT
t.CustomerKey,
t.Age,
t.BikeBuyer ,
t.[commutedistance],
t.gender,
t.[houseownerflag],
t.[numbercarsowned],
t.[yearlyincome],
PredictProbability([bb_dt].[BikeBuyer],1) as Probabity
FROM
bb_dt
natural PREDICTION JOIN
OPENQUERY(
[Adventure Works DW],
'select
CustomerKey,
Age,
BikeBuyer ,
[commutedistance],
gender,
[houseownerflag],
[numbercarsowned],
[yearlyincome]
from test')
AS t;
go
--ON <on clause,mapping,>
--WHERE <where clause,boolean expression,>
DROP MINING MODEL bb_dt;
go
DROP MINING STRUCTURE bb_st;
go
======
Recent Comments