PORTS USED

Friday, March 30, 2012

Predicting from a clustering model

Hi,

I have built a Clustering model that captures customer demographic information

and identify various hidden clusters based on the information.

What kind of predictions can I make using the above model?

You can do multiple types of queries with a clustering model:

Get the most likely cluster for each case (or a new singleton case) - SELECT Cluster() FROM [model]|||

Thanks Raman,

The 1st, 2nd and the 4th points are clear but I have certain doubts

regarding the 3^rd point you mentioned

Firstly as the attributes are customer characteristics like

age, gender and other demographic information and we suppose that the values

for these always exist for all the cases (new/old). What will be the point of

making them predictable and predicting their values again.

Secondly if I want to predict an attribute(s) like

MovieBuyer (determines whether a customer has bought a movie(s) or no) for new

cases, wouldn’t it be better if I use ‘decision trees’ or ‘neural networks’

algorithm rather than clustering.

Basically I cannot imagine a scenario in which clustering

stands out as the best suited algorithm for prediction.

It seems that Clustering is best suited for exploring and

understanding the data rather than for prediction.

I hope I have expressed my problem adequately and clearly.

Any help would be most appreciated.

|||

Your assessment is correct in that clustering is more suitable for data exploration/understanding - I was just pointing out that it is *possible* to do prediction with the SQL Server DM Clustering algorithm (which was your original question).

|||Thanks raman

PredictCaseLikelihood

I'm working with the cluster analysis algorithm (EM) in SQL 2005. I have tried to find documentation on the function PredictCaseLikelihood without luck. Is there any reference on how this function is defined?

Here's an excerpt from my book Data Mining with SQL Server 2005

PredictCaseLikelihood

PredictCaseLikelihood returns a measure from 0 to 1 that indicates how likely an input case is to exist considering the model learned by the algorithm.This measure is very good for use in anomaly detection as it quickly and easily tells you if new data is similar to any data seen before.This function operates in two modes, normalized and nonnormalized.

In the nonnormalized mode, the value of the measure is the raw probability of the case, that is, the product of the probabilities of each of the attributes in the case.For instance, if the probability of Home Ownership = ‘Yes’ is 40% and the probability of Occupation = ‘Craftsmen’ is 10% then the probability of the case is 40% x 10% = 4%.

Nonnormalized likelihoods can be useful, but due to the nature of the probabilities, as you increase the number of attributes in a case the probability of the case becomes increasingly small.Additionally, as a user, you can not understand if a 4% probability for a certain combination of attributes is a good thing or a bad thing.The normalized likelihood divides the probability of the case as provided by the model by the probability computed without the model using raw statistics.This provides a “lift” number that is normalized between 0 and 1 using the formula (lift)/(lift + 1).This is interpreted that cases with likelihood values greater than 0.5 have positive lift and are more likely than random to occur and that values less than 0.5 have negative life and are less likely than random to occur.

For continuous attributes, the probability distribution is used for this computation.

This query returns the normalized case likelihood for each case in the input set.

SELECT t.id, PredictCaseLikelihood()

FROM CustomerClusters

NATURAL PREDICTION JOIN <Input Set> AS t

This query returns the nonnormalized case likelihood for each case in the input set.

SELECT t.id, CaseLikelihood(NONNORMALIZED)

FROM CustomerClusters

NATURAL PREDICTION JOIN <Input Set> AS t

predict products ( data mining 2000)

i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).

how i do in data mining to predict that products ?

more: i want know how much percent of product is like by buyer

or i want show products with desc % of the like of people

You can use Microsoft Association Rules to resolve this problem: you can include the time info (such as month or summer) in each trasaction, our Assocation Rules will do the counting and find any rules apply. Please check the live sample http://www.sqlserverdatamining.com/DMCommunity/LiveSamples/54.aspx on about the train Association Rules. You might also want to check out the tips and tricks on our data mining web site http://www.sqlserverdatamining.com. SQL Server books online is another resouce you can use to learn about SQL Server in general and SQL Server Data Mining.

Besides time info, you can also include basket info (other products the user has already chosen) and the user's demographic info(such as Geneder, Income --if you have those info) in your model.

DMX is the langue you can use to do prediction. The function PredictHistogram(Product) will given the list of products and their probability when you run queries against your model.

Good luck,

|||

For SQL Server 2000 you would use Microsoft_Decision_Trees. You will build a model based on your customer/shopping basket table using the wizard. Since 2000 doesn't support creating nested tables in the wizard, you then need to add the nested table from your transaction table in the data mining editor.

Your model will look something like this

CREATE MINING MODEL MyModel
(
BasketID LONG KEY,
Season TEXT DISCRETE
Products TABLE PREDICT
(
ProductName TEXT KEY
)
) USING Microsoft_Decision_Tree

If you have a large number of products (which you likely do) you will have to set the MAXIMUM_INPUT_ATTRIBUTES and MAXIMUM_OUTPUT_ATTRIBUTES on the model as well, otherwise the algorithm will perform feature selection to the default 255.

|||Thanks.sql

predict products ( data mining 2000)

i want to make a web page and when somebody come in. i want show for him which products that everyone often buy at that time ( month or summer ).

how i do in data mining to predict that products ?

more: i want know how much percent of product is like by buyer

or i want show products with desc % of the like of people

Besides time info, you can also include basket info (other products the user has already chosen) and the user's demographic info(such as Geneder, Income --if you have those info) in your model.

DMX is the langue you can use to do prediction. The function PredictHistogram(Product) will given the list of products and their probability when you run queries against your model.

Good luck,

|||

Your model will look something like this

CREATE MINING MODEL MyModel
(
BasketID LONG KEY,
Season TEXT DISCRETE
Products TABLE PREDICT
(
ProductName TEXT KEY
)
) USING Microsoft_Decision_Tree

|||Thanks.

predict product with sex and age of customer.

i have table:

customer(customerid, age,sex....)

orderdata(orderid, customerid,day)

orderdetails(orderid, productid, quantity)

products(productid, productname,...)

now, i want to show some product for customer when i now him age and sex.

e.ct: if he is a man and age =20 i show product : ball, pull, sport close....... if man is a woman , i show lips, babara, t_shirt, skirt....

if man is a chirdren, i will show joy, story for chidren....

how i create my mining model. and how i query for result in DTS

The easiest way would be first to create a view containing the customer information along with the order id, e.g.

orderid, age, sex, ...

Then you would create a model using that view as the case table, and adding a nested table for products. The product nested table should have a single column - the product id (or product name if you denormalize) - which will be the key. Make the table "PREDICT" (or input and output) and make the non-key columns (i.e. other than orderid) all inputs.

You will then be able to predict based on age and sex like this

SELECT FLATTENED TopCount(Predict(Products),$AdjustedProbability,5) FROM MyModel
NATURAL PREDICTION JOIN
(SELECT 25 as Age, 'Male' as Gender) AS t

Predict Probability in Decision Trees

Hello,

I installed the bike buyer example and i am learning the DMX language. Now i wrote the following query (using MS decision trees):

SELECT
T.[Last Name],
[Bike Buyer],
PredictProbability(Predict([Bike Buyer])) AS [Probability]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY
(....... And so on..)

Now the result is surprising to me. In the resulttabel all the probabilities are equal.

Bike Buyer Probability
1 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
0 0.99994590500919611
1 0.99994590500919611

and so on.

Now i am wondering what predictProbability means. I thought that PredictProbability meant the probability that the prediction is correct. Now all the probabilities are the same and the input is different. Can somebody tell me what PredictProbability means or am I using it wrong?

Thanx in advance,

Joris Valkonet

This is an interesting query - I would write it as "PredictProbability([Bike Buyer])" however, the syntax is semantically the same. Another thing to try is PredictNodeId([Bike Buyer]) to see exactly the node used for the prediction and then check that node to see the distribution.

It's possible that PredictProbability(Predict([Bike Buyer])) is exposing a bug and you should use the simpler syntax above.

HTH

-Jamie

|||

Thank you for your reply.
I modified my query to PredictProbability([bike buyer]) and this made no difference. The result is the same.

Then I checked the PredictNodeId([Bike Buyer]) and this is the result:

FirstName

LastName

bike buyer

Expression

Abby

Malhotra

000000003

Abby

Prasad

000000003

Abby

Rodriguez

000000003

Abby

Srini

000000003

Abigail

Brown

000000003

Abigail

Bryant

000000003

Abigail

Davis

000000003

Abigail

Flores

000000003

Now the nodeId is the rootnode of the tree and the probability of the root node is indeed the value 0.99994590500919611. So if I understand this correct, the rootnode of the decision tree is used for all probability predictions. Is this correct?

(Maybe my query is incorrect and therefore the whole DMX query below)

SELECT
t.[FirstName],
t.[LastName],
[bike buyer],
PredictNodeId([bike buyer]) AS [Predicted NodeId]
From
[v Target Mail]
PREDICTION JOIN
OPENQUERY([Adventure Works DW],
'SELECT
[Gender],
[FirstName],
[MiddleName],
[LastName],
[BirthDate],
[MaritalStatus],
[EmailAddress],
[YearlyIncome],
[TotalChildren],
[NumberChildrenAtHome],
[HouseOwnerFlag],
[NumberCarsOwned],
[AddressLine1],
[AddressLine2],
[Phone]
FROM
[dbo].[ProspectiveBuyer]
') AS t
ON
[v Target Mail].[First Name] = t.[FirstName] AND
[v Target Mail].[Middle Name] = t.[MiddleName] AND
[v Target Mail].[Last Name] = t.[LastName] AND
[v Target Mail].[Birth Date] = t.[BirthDate] AND
[v Target Mail].[Marital Status] = t.[MaritalStatus] AND
[v Target Mail].[Gender] = t.[Gender] AND
[v Target Mail].[Email Address] = t.[EmailAddress] AND
[v Target Mail].[Yearly Income] = t.[YearlyIncome] AND
[v Target Mail].[Total Children] = t.[TotalChildren] AND
[v Target Mail].[Number Children At Home] = t.[NumberChildrenAtHome] AND
[v Target Mail].[House Owner Flag] = t.[HouseOwnerFlag] AND
[v Target Mail].[Number Cars Owned] = t.[NumberCarsOwned] AND
[v Target Mail].[Address Line1] = t.[AddressLine1] AND
[v Target Mail].[Address Line2] = t.[AddressLine2] AND
[v Target Mail].[Phone] = t.[Phone]

|||

Something wierd is happening here - Predict([Bike Buyer]), which is equivalent to saying just [Bike Buyer], returns the highest probability value for the prediction. That means that whatever node the tree goes to, it will return the highest probability state for that node. I can't see how you are getting different prediction results from the same node.

However, something else struck me as odd. The predict probability of 0.999... doesn't seem right. In fact the only time I have ever seen anything like this is when all the data at the node was missing, and the actual non-missing values only recieved the bayesian prior. By default prediction never returns missing (if you are asking for a value, it assumes you want an actual value). If you do a Predict([Bike Buyer], INCLUDE_MISSING) you will get predictions that include the missing value. If there were no observations in the training data of the states 1 and 0 then they would only have their prior probabilities which would be equal and then the choice of a state would be arbitrary.

What confuses me is that PredictProbabililty should behave the same way.

Can you do a SELECT FLATTENED NODE_DISTRIBUTION FROM [v Target Mail].CONTENT and post the results?

Thanks

|||

Thanks for your analysis. I tried to post the whole result of the query "SELECT FLATTENED NODE_DISTRIBUTION FROM [v Target Mail].CONTENT ", but then an unknown error occurs. I think because the resulttable is to big. So... i will give the first top rows of the result.

Thanks....

ATTRIBUTE_NAME

ATTRIBUTE_VALUE

SUPPORT

PROBABILITY

VARIANCE

.VALUETYPE

Bike Buyer

Missing

0.000432432

Bike Buyer

0.494048907

18484

0.999567568

0.249964584

Birth Date

5.45E-06

16892175

Birth Date

0.080783333

Birth Date

22673.85945

16892175

Date First Purchase

-0.000941648

76769.40019

Date First Purchase

3242.190525

Date First Purchase

37852.25763

76769.40019

Number Cars Owned

-0.079859089

1.295870198

Number Cars Owned

301.5988757

Number Cars Owned

1.502705042

1.295870198

Number Children At Home

-0.015796323

2.318367003

Number Children At Home

9.530872281

Number Children At Home

1.004057563

2.318367003

Total Children

-0.003992138

2.599718694

Total Children

2.60E+01

Total Children

1.844351872

2.599718694

Yearly Income

2.02E-06

1042319181

Yearly Income

116.9361238

Yearly Income

57305.77797

1042319181

36.04143269

0.166743652

Bike Buyer

Missing

0.000205761

0.00E+00

Bike Buyer

4858

0.999794239

5.14E-07

Bike Buyer

Missing

0.000536481

Bike Buyer

0.507518797

1862

0.999463519

0.249943468

Date First Purchase

-0.010374704

1160.841795

Date First Purchase

679.5475673

Date First Purchase

37822.50644

1160.841795

Geography Key

0.000170418

36083.71548

Geography Key

1.990727272

Geography Key

236.4581096

36083.71548

Number Cars Owned

-0.072103841

1.254812169

Number Cars Owned

2.37E+01

Number Cars Owned

1.523630505

1.254812169

Yearly Income

1.29E-06

1131929217

Yearly Income

7.102048444

Yearly Income

60633.72718

1131929217

392.8960717

0.113511443

Bike Buyer

Missing

0.000123274

Bike Buyer

0.256103576

8110

0.999876726

0.190514534

|||

I see the problem - you have Bike Buyer modeled as continuous where it should be discrete. PredictProbability was giving you the probability that BikeBuyer exists in this case. To get the confidence of the prediction for continuous predictions, you want to use PredictStdev.

Essentially what was happening was that the model was creating a linear regression to predict bike buyer rather than a classification model. Changing the content type to "Discrete" will fix your issue. If you are creating the model from the source table (rather than a cube), you can click on "Detect" on the data types page of the wizard and it will automatically determing that Bike Buyer should be discrete.

Predict % completed

I'm doing a web search page on a db that has about 1 million records, and
the query is quite complicated. I would like to provide the user some
feedback will the db server is performing the search.
What is the best way to do this? any examples or tutorials?
The only way I can think of is doing 10000 records at a time and add 1% to a
counter.
There must be a better way.
Any input is greatly appreciated,
AaronWhat about firing first the Query to the Optimizer and sshowing the
execution plan for it, you might see there how long (CPU cycles) it will
last and how many rows are estimated (Requirement of maintained statistics
:-)
See for this one:
USE NORTHWIND
GO
SET SHOWPLAN_ALL ON
GO
Select CompanyName from Customers where CompanyName LIKE '%A%'
GO
SET SHOWPLAN_ALL OFF
GO
(Result gonna spread over the page, best thing you execute in on your own.)
HTH, Jens Smeyer
http://www.sqlserver2005.de
--
"Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
> I'm doing a web search page on a db that has about 1 million records, and
> the query is quite complicated. I would like to provide the user some
> feedback will the db server is performing the search.
> What is the best way to do this? any examples or tutorials?
> The only way I can think of is doing 10000 records at a time and add 1% to
> a counter.
> There must be a better way.
> Any input is greatly appreciated,
> Aaron
>
>|||what's the Optimizer ? is that an addon to sql server?
i don't think i have that
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:eu5VT4YQFHA.1392@.TK2MSFTNGP10.phx.gbl...
> What about firing first the Query to the Optimizer and sshowing the
> execution plan for it, you might see there how long (CPU cycles) it will
> last and how many rows are estimated (Requirement of maintained statistics
> :-)
> See for this one:
>
> USE NORTHWIND
> GO
> SET SHOWPLAN_ALL ON
> GO
> Select CompanyName from Customers where CompanyName LIKE '%A%'
> GO
> SET SHOWPLAN_ALL OFF
> GO
> (Result gonna spread over the page, best thing you execute in on your
> own.)
> HTH, Jens Smeyer
>
> --
> http://www.sqlserver2005.de
> --
> "Aaron" <kuya789@.yahoo.com> schrieb im Newsbeitrag
> news:O503JyYQFHA.204@.TK2MSFTNGP15.phx.gbl...
>|||On Fri, 15 Apr 2005 16:06:44 -0700, Aaron wrote:

>what's the Optimizer ? is that an addon to sql server?
>i don't think i have that
(snip)
Hi Aaron,
The optimizer is an integral part of SQL Server. It's the part of the
server that reviews your query and determines the execution plan (the
order and methods in which data will be fetched and combined to satisfy
your request).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 30, 2012

Predicting from a clustering model

PredictCaseLikelihood

PredictCaseLikelihood

predict products ( data mining 2000)

predict products ( data mining 2000)

predict product with sex and age of customer.

Predict Probability in Decision Trees

Predict % completed

PORTS USED

Blog Archive

About Me