Tuesday, March 27, 2012

analysis services 2000 (data mining)

i create a model mining

CREATE MINING MODEL [a'S]
( [Cusid] LONG KEY ,
[Orderdetails] TABLE PREDICT ([Productid] LONG KEY )
) USING Microsoft_Decision_Trees

i can show for customer some products other when he choise a product.

ect: he choise A, i show he B, C, D ....

but for that, i must know his cusid.

now i want show B,C,D when one man choise A ( i don't know his cusid). how i do that.

( i'm using sql server and analysis service 2000)

You don't need to supply the customer id for prediction. The customer ID is only ysed to identify records and has no impact on the patterns in the model.|||

i can't do.

i use select query:

SELECT FLATTENED
[t1].[cusid],TopCount( Predict(Angel.[Orderdetails], INCLUDE_STATISTICS, EXCLUSIVE), $ADJUSTEDPROBABILITY, 3)
FROM
Angel
PREDICTION JOIN
SHAPE
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid" FROM "customers" ORDER BY "cusid"'
)

}
APPEND
(
{
OPENROWSET
(
'SQLOLEDB.1',
'Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SIT;Data Source=(local)',
'SELECT "cusid" AS "Cusid_1", "productid" AS "Productid" FROM "vwAllAgeSex" ORDER BY "cusid"'
)
}
RELATE [Cusid] TO [Cusid_1]
)
AS [Orderdetails]
AS [T1]
ON
Angel.[Cusid] = [T1].[Cusid] AND
Angel.[Orderdetails].[Productid] = [T1].[Orderdetails].[Productid]

the result:

cusid productid $support $adjustedprobability

1 12 119 0.92

1 320 40 0.34

1 300 23 0.31

3 12 54 0.76

3 19 54 0.76

.......

so when i select product with productid = 12. how i show other relation products

( examples: when i choose a t-shirt product, i want show relation products: shoes, jeans trousers.... )

sql

No comments:

Post a Comment