Cluster Euclidean Distance

I am new to data mining so please excuse my ignorance. Lets assume

- i have created a cluser model

- identified 3 clusters ( a, b, c)

- each record consists of 15 columns

- collecting new records( 15 variables) real time

what i would like to do is plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).

ClusterDistance() could be Y but what will be X.

thanks.

Cluster() will return cluster that is most likely contain an input case (in your case, the new case). You can also use ClusterProbability() to get the probability that a case belongs to a particular cluster. This basically serves as the (reversed) cluster distance you are talking about; and it works with more general data (including both numeric and discrete data). Moreover, you can use PredictHistogrom(…) to return a histogram of the likelihood of the input case existing in each of the model’s clusters. You can also use CaseLikelihood(…) to return a measure from 0 to 1 that indicates how likely an input case is to exist considering the model learned by the algorithm.

For your reference, we have a live sample The Art of Clustering demonstrating how to use all these features to render 2D data points according to clustering results. I hope this sample will be of help to your project.

Good luck,

|||Your x and y are whatever you choose them to be. For example, the way our cluster diagram works is to plot cluster locations on a 2d plane by arbitrarily laying them out and using a "point-charge" approach to move the clusters around until they converge (or we get tired....). If you were to use such a method for identifying cluster "locations" in 2d space, you could then use ClusterDistance() (which is 1-ClusterProbability) for each case vs. each cluster to approximate where the case would land in the 2d space. You could then color the case by the most likely cluster, and you would have a diagram that looked similar to Yimin's Art of Clustering example, but with arbitrarily dimensioned cluster models.|||

Thanks Wu. I have one more question.

lets assume the new record( all floats columns ) belongs to cluster A but is significantly away from the center of the cluster.

there can be one or more than one columns that caused this record to be far away from the center of the cluster.

Is there any way we can find out the most significant columns in this record that caused it .

thanks

|||This is exactly how we implemented the outlier detection in the data mining addins for Excel. Code that shows how to do this is at http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/46.aspx

Cluster Euclidean Distance

I am new to data mining so please excuse my ignorance. Lets assume

- i have created a cluser model

- identified 3 clusters ( a, b, c)

- each record consists of 15 columns

- collecting new records( 15 variables) real time

what i would like to do is plot these new records programmatically as i collect them realtime. I assume this new record will belong to one of these three clusters. I believe we can find the cluster this new record belongs to by ' SELECT Cluster()....' and distance from the center of the cluster by ClusterDistance(). To plot this on a 2-dimentional space i need (x, y).

ClusterDistance() could be Y but what will be X.

thanks.

Cluster() will return cluster that is most likely contain an input case (in your case, the new case). You can also use ClusterProbability() to get the probability that a case belongs to a particular cluster. This basically serves as the (reversed) cluster distance you are talking about; and it works with more general data (including both numeric and discrete data). Moreover, you can use PredictHistogrom(…) to return a histogram of the likelihood of the input case existing in each of the model’s clusters. You can also use CaseLikelihood(…) to return a measure from 0 to 1 that indicates how likely an input case is to exist considering the model learned by the algorithm.

For your reference, we have a live sample The Art of Clustering demonstrating how to use all these features to render 2D data points according to clustering results. I hope this sample will be of help to your project.

Good luck,

|||Your x and y are whatever you choose them to be. For example, the way our cluster diagram works is to plot cluster locations on a 2d plane by arbitrarily laying them out and using a "point-charge" approach to move the clusters around until they converge (or we get tired....). If you were to use such a method for identifying cluster "locations" in 2d space, you could then use ClusterDistance() (which is 1-ClusterProbability) for each case vs. each cluster to approximate where the case would land in the 2d space. You could then color the case by the most likely cluster, and you would have a diagram that looked similar to Yimin's Art of Clustering example, but with arbitrarily dimensioned cluster models.|||

Thanks Wu. I have one more question.

lets assume the new record( all floats columns ) belongs to cluster A but is significantly away from the center of the cluster.

there can be one or more than one columns that caused this record to be far away from the center of the cluster.

Is there any way we can find out the most significant columns in this record that caused it .

thanks

|||This is exactly how we implemented the outlier detection in the data mining addins for Excel. Code that shows how to do this is at http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/46.aspx

CLR Trigger to write file.......

Hello, theres,

I have a request to write to a file whenever new record added to the table.

When records insert row by row, it goes well. but when more than 2 session insert at the same time, sometimes it duplicate some record in the file. I try to add synchonize code ( like lock , Monitor) but it doesn't work. any idea ?

Regards,

Agi

I assume you are using this to generate an Audit log or something similar. Here is the route I took.

http://sqljunkies.com/Article/4CD01686-5178-490C-A90A-5AEEF5E35915.scuk

|||

Jonathan,

Thanks for your reply, I just want to write everything to the flat file whenever new record inserted to my table. I write a trigger using c#. I just wonder if more than 2 threads (sessions) insert new record at the same time, what will be write to the flat file ?

Regards,

Agi

|||Hi,
I think the “CLR Triggers for SQL Server 2005” article on
http://aspalliance.com/1273_CLR_Triggers_for_SQL_Server_2005.all
may be helpful in this discussion.

This popular white paper is written by a software engineer from our organization Mindfire Solutions (http://www.mindfiresolutions.com).

I hope you find it useful!

Cheers,
Byapti

CLR and fire event

Is it posible to fire an event in sql 2005 DB when e.g. one record changes i
n
the table and...main part ... respond to that event in some external c#
service?Take a look at 'create trigger' in help.
-oj
"Basic" <Basic@.discussions.microsoft.com> wrote in message
news:D979C99C-8CBE-46A4-BA8F-A2E2967B0092@.microsoft.com...
> Is it posible to fire an event in sql 2005 DB when e.g. one record changes
> in
> the table and...main part ... respond to that event in some external c#
> service?|||examnotes <Basic@.discussions.microsoft.com> wrote in
news:D979C99C-8CBE-46A4-BA8F-A2E2967B0092@.microsoft.com:

> Is it posible to fire an event in sql 2005 DB when e.g. one record
> changes in the table and...main part ... respond to that event in some
> external c# service?
>
You do not have an automatic way of raising events to external resources
from inside SQL Server. There are however a couple of things you can do
to make this happen:
1. Have a trigger who talks tom a CLR proc, in the clr proc you
initiates an external app.
2. Use SQL Server Servie Broker. The SSB team has created a sample how
to activate an external resource based on a message appearing in a
queue.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||thx niels, this was constructive... one more thing, where can I find this
example (have you link) pls
"Niels Berglund" wrote:

> examnotes <Basic@.discussions.microsoft.com> wrote in
> news:D979C99C-8CBE-46A4-BA8F-A2E2967B0092@.microsoft.com:
>
> You do not have an automatic way of raising events to external resources
> from inside SQL Server. There are however a couple of things you can do
> to make this happen:
> 1. Have a trigger who talks tom a CLR proc, in the clr proc you
> initiates an external app.
> 2. Use SQL Server Servie Broker. The SSB team has created a sample how
> to activate an external resource based on a message appearing in a
> queue.
> Niels
> --
> ****************************************
**********
> * Niels Berglund
> * http://staff.develop.com/nielsb
> * nielsb@.no-spam.develop.com
> * "A First Look at SQL Server 2005 for Developers"
> * http://www.awprofessional.com/title/0321180593
> ****************************************
**********
>|||examnotes <Basic@.discussions.microsoft.com> wrote in
news:19EE2685-6BDE-4149-8EB8-D8AAAD2EF59E@.microsoft.com:

> thx niels, this was constructive... one more thing, where can I find
> this example (have you link) pls
>
Go to here: http://www.gotdotnet.com/codegaller...allery.aspx?id=
9f7ae2af-31aa-44dd-9ee8-6b6b6d3d6319 (watch out for linebreaks).
You should find it there.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Clone a record

Hello --
I want to create a new record in the "sales_quotes" table so the salesperson
can explore alternate quote(s) to present to a customer.
The new record will have these characteristics:
- identical values for all fields with the exception of the primary key
field
- the primary key value of the new record will have a user-entered value,
validated to assure it is unique
Can someone suggest how to do this ...
- updatable query
- create record in a temp table with identical structure, modify the primary
key field, then append to quotes table
- other
Thanks for any help.
Larry Mehl
L Mehl wrote:

>Hello --
>I want to create a new record in the "sales_quotes" table so the salesperso
n
>can explore alternate quote(s) to present to a customer.
>The new record will have these characteristics:
>- identical values for all fields with the exception of the primary key
>field
>
Identical for all fields of what row? I don't see the point of
inserting a new sales quote that is identical to an existing quote
except for the primary key (which appears to be entirely artificial).
But if you have some real need to do this, it would look something like
insert into sales_quotes
select
@.new_primary_key_value,
column_A,
column_B,
column_C,
.
from sales_quotes
where primary_key_column = @.old_primary_key_value
It makes more sense to me that you would want to add a new
row to a quotes table when there is a new, different quote, not
when there is no new quote.
Steve Kass
Drew University

>- the primary key value of the new record will have a user-entered value,
>validated to assure it is unique
>Can someone suggest how to do this ...
>- updatable query
>- create record in a temp table with identical structure, modify the primar
y
>key field, then append to quotes table
>- other
>Thanks for any help.
>Larry Mehl
>
>|||No reason to copy a row in your table. I guess what you mean is that you
want to present that data to the salesperson so that she can review it,
modify some data and then save a *modified* version of the row - not just
modify the key, which would serve no obvious purpose. Do this the same way
the enter any new row - the "cloned" values just become the defaults in the
user's entry form. This is client-side functionality.
David Portas
SQL Server MVP
--|||If [sales_quotes] is a table seperate from sales, then just do a select into
[sales_quotes] from [sales] and then let the salesperson can edit the new
record using a form. I don't see why the primary key should be user entered;
instead let them enter a descriptive text or key from cutomer and date
entered.
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
> salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
> primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
Thanks. Sorry for the brief description of the need for this process.
The reason for the cloned record is so that the sales person's manager can
perform sensitivity analysis on the quote, with the possible result that it
becomes the quote that is submitted to the customer, and the "original"
quote is discarded.
I was aware of the method you show. I was looking for a way to avoid having
to enter all the non-primary-key values.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||All --
Thank you for the suggestions.
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:LGWMd.556$mG6.504@.newsread1.news.pas.earthlink.net...
> Hello --
> I want to create a new record in the "sales_quotes" table so the
salesperson
> can explore alternate quote(s) to present to a customer.
> The new record will have these characteristics:
> - identical values for all fields with the exception of the primary key
> field
> - the primary key value of the new record will have a user-entered value,
> validated to assure it is unique
> Can someone suggest how to do this ...
> - updatable query
> - create record in a temp table with identical structure, modify the
primary
> key field, then append to quotes table
> - other
> Thanks for any help.
> Larry Mehl
>|||Steve --
I am a newbie concerning SQL queries in VB connected to a SQL Server
database.
In an ACCESS/VBA application I used SQL as in your example "insert into
sales_quotes ..." below, in a command
CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
Can you tell me how to do the same thing in VB connected to a SQL Server
database, or suggest some articles or other resoulced from which I can learn
how?
I can connect to the db and populate a datagrid using ADO. Is your example
"compatible" with ADO, or is it another technique entirely?
I will eventually convert my app to dotNET, so I would appreciate knowing if
your method will work well on that platform.
Thank you for any more help.
Larry
"Steve Kass" <skass@.drew.edu> wrote in message
news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
>
> L Mehl wrote:
>
salesperson
> Identical for all fields of what row? I don't see the point of
> inserting a new sales quote that is identical to an existing quote
> except for the primary key (which appears to be entirely artificial).
> But if you have some real need to do this, it would look something like
> insert into sales_quotes
> select
> @.new_primary_key_value,
> column_A,
> column_B,
> column_C,
> ..
> from sales_quotes
> where primary_key_column = @.old_primary_key_value
> It makes more sense to me that you would want to add a new
> row to a quotes table when there is a new, different quote, not
> when there is no new quote.
> Steve Kass
> Drew University
>
primary|||The answer appears to be
connection.Execute
After I understand the third parameter 'Options' I think it will work.
Does anyone have examples or resources for using connection.Execute?
Larry Mehl
"L Mehl" <mehl_nospam@.cyvest.com> wrote in message
news:94CNd.2141$UX3.1163@.newsread3.news.pas.earthlink.net...
> Steve --
> I am a newbie concerning SQL queries in VB connected to a SQL Server
> database.
> In an ACCESS/VBA application I used SQL as in your example "insert into
> sales_quotes ..." below, in a command
> CurrentDb.Execute "insert into sales_quotes ...", dbFailOnError
> Can you tell me how to do the same thing in VB connected to a SQL Server
> database, or suggest some articles or other resoulced from which I can
learn
> how?
> I can connect to the db and populate a datagrid using ADO. Is your
example
> "compatible" with ADO, or is it another technique entirely?
> I will eventually convert my app to dotNET, so I would appreciate knowing
if
> your method will work well on that platform.
> Thank you for any more help.
> Larry
>
> "Steve Kass" <skass@.drew.edu> wrote in message
> news:#cCT9q0CFHA.3728@.TK2MSFTNGP14.phx.gbl...
> salesperson
value,
> primary
>