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

Possible to speed up the decision tree by Clustering the Server 2003

Dear All,

I have a dataminig programming that need to run for days. Is it possibile to speed up the training process by clustering several server by Windows 2003 clustering services? Is it actually that clustering 2 QUAD core computer is almost giving comparable performance as the sum of the speed of two (There must be some overhead, I know). I am actually familiary with the use of clustering. Is it just for making the server farm more reliable or it will collaborate and speeed up the whole training process?

If it is, is there any limit on the number of cluster is in the cluster. What version of Windows and SQL Server do I need to achieve speed up of data mining training process?

Thanks and regards

Tony Chun Tung Siu

I think your problem can be reduced at clustering Analysis Services Server because the model is in a AS database.

As is wrote in SQL Server 2005 Failover Clustering White Paper : "The ability to cluster Analysis Services is a new feature in SQL Server 2005. Before SQL Server 2005, the only way to make Analysis Services more available was to either configure it as read-only in a Network Load Balancing cluster, or create it as part of a standard Windows server cluster as a generic resource".

|||

The clustering solution will not help you with processing a single mining model. It could help if you need to apply a model to a very large data set (that is assuming you could partition your data set using a few queries, then execute the queries from multiple clients against the cluster -- the workload can be shared between cluster nodes and the performance will scale out almost linearly)

A very unrefined possible workaround, assuming you are training a decision tree model:

- start by building a model on one machine. Use a very large COMPLEXITY_PENALTY factor -- this would give you a very small tree. However, the first split in the tree will be the same as in the actual model you want to use

- create two different views on top of the relational data, one for each branch of the first split.

- train a model on each machine, using only data from one query

- copy both models on a single machine

- write some sort of stored procedure to execute predictions on the server, and have the stored procedure logic figure out which model to actually use (or, in your client application, decide based on the input which model to query)

Possible to speed up the decision tree by Clustering the Server 2003

Dear All,

I have a dataminig programming that need to run for days. Is it possibile to speed up the training process by clustering several server by Windows 2003 clustering services? Is it actually that clustering 2 QUAD core computer is almost giving comparable performance as the sum of the speed of two (There must be some overhead, I know). I am actually familiary with the use of clustering. Is it just for making the server farm more reliable or it will collaborate and speeed up the whole training process?

If it is, is there any limit on the number of cluster is in the cluster. What version of Windows and SQL Server do I need to achieve speed up of data mining training process?

Thanks and regards

Tony Chun Tung Siu

I think your problem can be reduced at clustering Analysis Services Server because the model is in a AS database.

As is wrote in SQL Server 2005 Failover Clustering White Paper : "The ability to cluster Analysis Services is a new feature in SQL Server 2005. Before SQL Server 2005, the only way to make Analysis Services more available was to either configure it as read-only in a Network Load Balancing cluster, or create it as part of a standard Windows server cluster as a generic resource".

|||

The clustering solution will not help you with processing a single mining model. It could help if you need to apply a model to a very large data set (that is assuming you could partition your data set using a few queries, then execute the queries from multiple clients against the cluster -- the workload can be shared between cluster nodes and the performance will scale out almost linearly)

A very unrefined possible workaround, assuming you are training a decision tree model:

- start by building a model on one machine. Use a very large COMPLEXITY_PENALTY factor -- this would give you a very small tree. However, the first split in the tree will be the same as in the actual model you want to use

- create two different views on top of the relational data, one for each branch of the first split.

- train a model on each machine, using only data from one query

- copy both models on a single machine

- write some sort of stored procedure to execute predictions on the server, and have the stored procedure logic figure out which model to actually use (or, in your client application, decide based on the input which model to query)

possible to save up the progress at some point of Decision Tree Training?

Dear All,

If I have a decision tree training work which might last for many days or months. Is it possible to tell the data mining training program to save up the progress at some point? In case the computer hangs or power fail in the middle, the computer can resume the rest of the work at the saving point?

Thanks

Tony Chun Tung Siu

No, SQL Server 2005 Data Mining does not support interactive training for the mining models. A training request is a transactional operation so, if it fails for any reason (including the reasons you mentioned) the transaction is not commited and, when the server is restarted, it is rolled back

possible to save up the progress at some point of Decision Tree Training?

Dear All,

If I have a decision tree training work which might last for many days or months. Is it possible to tell the data mining training program to save up the progress at some point? In case the computer hangs or power fail in the middle, the computer can resume the rest of the work at the saving point?

Thanks

Tony Chun Tung Siu

No, SQL Server 2005 Data Mining does not support interactive training for the mining models. A training request is a transactional operation so, if it fails for any reason (including the reasons you mentioned) the transaction is not commited and, when the server is restarted, it is rolled back