data mining 2000

i want show other products when somebody choose a product,

ect:

product1 product2

A B, C ,D

B C,D,E

C F, G

.......

i create model:

I create a mining model:

CREATE MINING MODEL [bh'S]

([Cusid] LONGKEY,

[Products] TABLE PREDICT ([Productid] LONGKEY

)) USING Microsoft_Decision_Trees

INSERT INTO [bh'S]

(SKIP, [Products] (SKIP, [Productid]))

SHAPE

{OPENROWSET(…, 'SELECT DISTINCT "dbo"."customers"."cusid" AS "Cusid" FROM "dbo"."customers" ORDER BY "dbo"."customers"."cusid"')}

APPEND

( {OPENROWSET(…, 'SELECT DISTINCT "dbo"."orderdata"."cusid" AS "UI_GENERATED_PARENT_KEY_COLUMN", "dbo"."products"."productid" AS "Productid" FROM "dbo"."orderdata", "dbo"."orderdetails", "dbo"."products" WHERE (("dbo"."orderdata"."orderid"="dbo"."orderdetails"."orderid") AND ("dbo"."orderdetails"."productid"="dbo"."products"."productid")) ORDER BY "dbo"."orderdata"."cusid"')}

RELATE [Cusid] TO [UI_GENERATED_PARENT_KEY_COLUMN]) AS [Products]

I use DTS to select data:

SELECT FLATTENED

[T1].[Cusid] AS PRODUCTID ,topcount(PREDICT ( [bh].[Products],INCLUDE_STATISTICS),$probability,5) AS ReProductid

FROM

[bh]

PREDICTION JOIN

SHAPE

{

OPENROWSET

(

…,'SELECT "productid" AS "Cusid" FROM "products" ORDER BY "productid"'

)

}

APPEND

(

{

OPENROWSET

(

…,'SELECT "productid" AS "Cusid_1", "productid" AS "Productid" FROM "products" ORDER BY "productid"'

)

}

RELATE [Cusid] TO [Cusid_1]

)

AS [Products]

AS [T1]

ON

[bh].[Cusid] = [T1].[Cusid] AND

[bh].[Products].[Productid] = [T1].[Products].[Productid]

My result

productid1 productid2

1 2372

1 5422

1 1223

...

2 2372

2 5422

2 1223

.....

3 2372

3 5422

3 1223

...

how can i do ?

i can't write predicts(products,5) or predicts([model name].[products],5) . when i run -> error

If you are useing SQL Server 2000, you cannot use the syntax Predict(Products, 5) as that syntax was introduced in SQL Server 2005.

Your query is correct - I would only say that you don't need to bind "cusid" in the prediction query - you don't really need to alias it to "cusid" either.

Is there something else needed? If you want product names rather than ID's, simply build the models using names

|||

i don't need name products . but productid to recomend is loop and almost it only recomend 5 products when you choose any products in market. ( same set in productid_2, when i use predict(....,5) ).

|||

Here's a few things to try:

1: Look at the trees generated by the model - do they show any patterns? It may be the case that the patterns aren't there to examine.

2: Use $AdjustedProbability instead of $Probability. This will favor products that are less likely to occur. For example, if Milk occurs in 99% of all baskets, it will be the #1 item regardless of what is actually in the basket. $AdjustedProbability takes this into account

3: Increase the values of MAXIMUM_INPUT_ATTRIBUTES and MAXIMUM_OUTPUT_ATTRIBUTES to include all the products in your catalog

|||

first:

i can see patterns. but it doesn't predict productid equal 1 to 150 and some other productid. so when i choose productid in (1,....,150) it show same set.

second:

how i increase the values of maximum _ input and output attributes ( i use wizar to create).