prediction in ms sql server 2005

Hey

Does anyone know if the following is possible:

I want to add a column to a table that contains the predicted value according to a decision tree mining model. (I know that this is possible). But now I would like that when a new row is added to this table, and every column except the prediction column is filled in manually, can ms sql server add the predicted value automatically for this row?
I know it is possible to execute a Singleton query for this kind of single prediction, but I would like to integrate this in my data table, because for now my steps would be:
- Create the table with one prediction column
- Add the known values of all columns for one row
- Use singleton query in Mining model prediction tab to know the predicted value
- Fill in the predicted value manually in my table.

I hope my question is clear.

Thanks in advance for the help.

SmileykeYou could probably do this with an INSERT trigger on your SQL Server database table that makes a singleton prediction query via a linked server to the AS server that holds your mining model.|||And how would this query look like?
I mean, you put me in the right direction I think, but I can't make it work.

Thnx|||Please see this article I just posted for details on how to do this: http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3914.aspx|||Ok, I tried this, and it was very helpful, but it still doesn't work here.

Do you have any idea why the first query here works, but the second one doesn't? The error is given at the end:

1st working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings from [Abalone Training Half]')

2nd not working query:
SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

The error is:
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "MSOLAP" for linked server "DMServer" reported an error. The provider did not give any information about the error.
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT I AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T" against OLE DB provider "MSOLAP" for linked server "DMServer".

As you notice, the predicted class is here Rings, and the input attributes are sex, length diameter and height.

I really hope you can still help me.

Smileyke|||

I may be wrong, but the Sex column seems TEXT. In this case, shouldn't "SELECT I AS Sex" be actually "SELECT 'I' AS Sex" ?

In this case, your OPENQUERY should look like below (2 single quotes around I )

SELECT * FROM OPENQUERY(DMServer,
'SELECT Rings FROM [Abalone Training Half]
NATURAL PREDICTION JOIN
(SELECT ''I'' AS Sex,
12 AS Length,
12 AS Diameter,
12 AS Height)
AS T')

|||Thank you. That was indeed the problem.

Now the complete trigger works, so thank you all.

smileyke