singleton query agains OLAP based model?

[Jeje]

Hi,

I have created a decision tree DM model from a cube.
now I try the singleton query, but how to create the query????

the only samples I found works against an RDBMS model.
so I'm not sure how to write my query:

SELECT
flattened
[Test Dec Tree].[Performance Levels]
,PredictProbability([?????]) <-- error here
From
[Test Dec Tree]
NATURAL PREDICTION JOIN
(SELECT 'Male' AS [Gender],
'Asian' AS [Race Group]
) AS t

I have tried a lot of different syntaxes in the predictprobabilty function
without success.

because it's OLAP based, I only have "existing" and "missing" in my model
result, while the samples found are differents.

can you point me on a sample anywhere?

thanks.
Jerome.

[Dejan Sarka]

Jeje,

I created a DT model on Adventure Works DW demo cube. I used Number Of Cars
Owned as predictable, Education, Gender and Marital Status as input column.
I checked the content of the predictable column - when it was read from the
cube, it was Continuous; I changed it to Discrete. Deployed the model, and
here is a query that works:

SELECT [Number of Cars Owned],
PredictProbability([Number of Cars Owned], 2)
AS TwoCarsProb
FROM [CubeDT]
NATURAL PREDICTION JOIN
(SELECT 'High School' AS Education,
'Single' AS [Marital Status],
'M' AS [Gender]) AS t

What kind of error do you get? Can you test the way I did?
--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

Post by Jeje
Hi,
I have created a decision tree DM model from a cube.
now I try the singleton query, but how to create the query????
the only samples I found works against an RDBMS model.
SELECT
flattened
[Test Dec Tree].[Performance Levels]
,PredictProbability([?????]) <-- error here
From
[Test Dec Tree]
NATURAL PREDICTION JOIN
(SELECT 'Male' AS [Gender],
'Asian' AS [Race Group]
) AS t
I have tried a lot of different syntaxes in the predictprobabilty function
without success.
because it's OLAP based, I only have "existing" and "missing" in my model
result, while the samples found are differents.
can you point me on a sample anywhere?
thanks.
Jerome.

[Jeje]

this works, only if my predictable content is not a nested table.

I have a survey like cube:
customer dimension, product dimension and evaluation dimension (good, avg,
bad) and #Evaluations.
I have create a decision tree model using the customer key as the key, what
I want to predict is the evaluation (so a nested table) and also I'm using
the product characteristics in my model (color, type, size etc...)
it's ok.
1 thing is missing here: I have to select the tree what I want to track (ie
select bad, avg or good evaluations) and the model just display "existing"
"missing"
I will prefer to have the 3 evaluation types and the probability to fall
into 1 of these, but it's a small issue.

now, the PredictProbability formula...
the formula doesn't works.

in adv. works I can reproduce it:
SELECT [Subcategories]
, PredictProbability([Subcategories], 2) AS TwoCarsProb
FROM [decision]
NATURAL PREDICTION JOIN
(SELECT 'High School' AS Education,
'Single' AS [Marital Status],
'M' AS [Gender]) AS t


The error is:
Error (Data mining): Only a scalar column reference is allowed in the
prediction function at line 2, column 29.

So how to write a singleton query to use it against the Subcategories
prediction?

Thanks.

Jerome.

Post by Dejan Sarka
Jeje,
I created a DT model on Adventure Works DW demo cube. I used Number Of
Cars Owned as predictable, Education, Gender and Marital Status as input
column. I checked the content of the predictable column - when it was read
from the cube, it was Continuous; I changed it to Discrete. Deployed the
SELECT [Number of Cars Owned],
PredictProbability([Number of Cars Owned], 2)
AS TwoCarsProb
FROM [CubeDT]
NATURAL PREDICTION JOIN
(SELECT 'High School' AS Education,
'Single' AS [Marital Status],
'M' AS [Gender]) AS t
What kind of error do you get? Can you test the way I did?
--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

Post by Jeje
Hi,
I have created a decision tree DM model from a cube.
now I try the singleton query, but how to create the query????
the only samples I found works against an RDBMS model.
SELECT
flattened
[Test Dec Tree].[Performance Levels]
,PredictProbability([?????]) <-- error here
From
[Test Dec Tree]
NATURAL PREDICTION JOIN
(SELECT 'Male' AS [Gender],
'Asian' AS [Race Group]
) AS t
I have tried a lot of different syntaxes in the predictprobabilty
function without success.
because it's OLAP based, I only have "existing" and "missing" in my model
result, while the samples found are differents.
can you point me on a sample anywhere?
thanks.
Jerome.

[Jeje]

ok
I have found
I have to use:
PredictAssociation
instead of PredictProbability

Post by Jeje
this works, only if my predictable content is not a nested table.
customer dimension, product dimension and evaluation dimension (good, avg,
bad) and #Evaluations.
I have create a decision tree model using the customer key as the key,
what I want to predict is the evaluation (so a nested table) and also I'm
using the product characteristics in my model (color, type, size etc...)
it's ok.
1 thing is missing here: I have to select the tree what I want to track
(ie select bad, avg or good evaluations) and the model just display
"existing" "missing"
I will prefer to have the 3 evaluation types and the probability to fall
into 1 of these, but it's a small issue.
now, the PredictProbability formula...
the formula doesn't works.
SELECT [Subcategories]
, PredictProbability([Subcategories], 2) AS TwoCarsProb
FROM [decision]
NATURAL PREDICTION JOIN
(SELECT 'High School' AS Education,
'Single' AS [Marital Status],
'M' AS [Gender]) AS t
Error (Data mining): Only a scalar column reference is allowed in the
prediction function at line 2, column 29.
So how to write a singleton query to use it against the Subcategories
prediction?
Thanks.
Jerome.

Post by Dejan Sarka
Jeje,
I created a DT model on Adventure Works DW demo cube. I used Number Of
Cars Owned as predictable, Education, Gender and Marital Status as input
column. I checked the content of the predictable column - when it was
read from the cube, it was Continuous; I changed it to Discrete. Deployed
SELECT [Number of Cars Owned],
PredictProbability([Number of Cars Owned], 2)
AS TwoCarsProb
FROM [CubeDT]
NATURAL PREDICTION JOIN
(SELECT 'High School' AS Education,
'Single' AS [Marital Status],
'M' AS [Gender]) AS t
What kind of error do you get? Can you test the way I did?
--
Dejan Sarka
http://blogs.solidq.com/EN/dsarka/default.aspx

Post by Jeje
Hi,
I have created a decision tree DM model from a cube.
now I try the singleton query, but how to create the query????
the only samples I found works against an RDBMS model.
SELECT
flattened
[Test Dec Tree].[Performance Levels]
,PredictProbability([?????]) <-- error here
From
[Test Dec Tree]
NATURAL PREDICTION JOIN
(SELECT 'Male' AS [Gender],
'Asian' AS [Race Group]
) AS t
I have tried a lot of different syntaxes in the predictprobabilty
function without success.
because it's OLAP based, I only have "existing" and "missing" in my
model result, while the samples found are differents.
can you point me on a sample anywhere?
thanks.
Jerome.