Association Mining Model Predictions

Hi,

I've been playing around with the association mining model in SQL server 2005 and built a market-basket analysis of my data that I'm pretty happy with. The next task for me is figuring out how to run DMX queries against the data that I've just mined, so we may possibly use it in a web based application. This wouldn't necessarily be a difficult problem (and still may not be), but every example I've seen for the Mining Model Prediction Designer uses relational databases and I built my mining model off OLAP. Therefore, my predictable attribute is nested and when relating the mining model structure to the relational database that the cube was built off always gives me an error:

"Errors in the high-level relational engine. The 'CompanyName' column could not be found in the top-level clause of the SHAPE statement."

What I would like to do, and I'm not really even sure how I should structure any of my queries, is feed the model a product and have it return a listing of all the products it predicts. Currently, I've only been able to get the designer mode to process a singleton query, and even that didn't return any useful data. I know that this probably can be done pretty easily so any advice you may be able to offer would be greatly appreciated!!

So you may better understand my question, my association mining structure hierarchy looks as this..

[Model] ProductRecommend

[Case][Key]CustomerList

[Case][Attribute]CompanyName

[NestedTable]Product

[Nested][Key]PRODUCTCLASSID

[Nested][Attribute]PRODUCT

With that in mind, I'm trying to perform a query simliar to this:

SELECT

PredictProbability([ProductRecommend].[Product].[PRODUCTCLASSID]), <- Throws Error for PredictProbability syntax no matter what I try to get to [PRODUCTCLASSID]

(SELECT [PRODUCT] FROM [ProductRecommend].[Product])

From

[ProductRecommend]

NATURAL PREDICTION JOIN

(SELECT 'test' AS [COMPANYNAME],

(SELECT '1234' AS [PRODUCTCLASSID],

'ProductA' AS [PRODUCT]) AS [Product]) AS t

Thanks again for any help!

Just incase someone out there runs into the same trouble I had, I think I've made some progress. My mistake was that I was using the PredictProbability function when I should have been using the PredictAssociation function. PredictAssociation does exactly what I wanted to do with the query, now I just need to figure out how to structure the query so it's simply based off an input product.|||What do you mean "simply based off an input product"?|||

Well it looks like 'simply based off an input product' isn't so simple... Since I've finally gotten queries to run, they've all pretty much returned the same associations. I feel like I've been everywhere on the internet looking for how to do what I described above, but just can't find enough information to solve my problem. My overall goal is to create a recommendation system similiar to Amazon.com. I'm pretty sure my mining model achieves this goal, but writing the DMX do so is a little bit difficult based on the structure of our database, and the fact that I modeled the Mining Structure off our OLAP cube.

Basically I just want to say:

SELECT

Predict Association([Product],10)

FROM

[ProductRecommendation]

NATURAL PREDICTION JOIN

(SELECT (SELECT 'ProductCustomerHasSelected' AS [ProductName], 'ProductKey' AS [PRODUCTCLASSID]) AS [Product])

And have it return:

ProductCustomerHasSelected -- Predicts Product A, B, D, and E

Is this the wrong way to go about doing that?

|||

BTW, have you looked at Raman Iyer and Jesper Lind's article? Create a Web Cross-sell Application

It has code examples, including how to construct the queries. You can see the app they build in action on a little webcast I did at.

In this article they use the following query:

SELECT FLATTENED

TopCount(Predict([Customer Movies], INCLUDE_STATISTICS), $AdjustedProbability, 5)

FROM [Movie Recommendations]

NATURAL PREDICTION JOIN

( SELECT ( SELECT 'Star Wars' AS [Movie] UNION SELECT 'The Matrix' AS [Movie] ) AS [Customer Movies] ) AS t

This returns the top 5 movies associated with the input movies: Star Wars and the Matrix in this case.

There is an excellent explanation of the thinking behind this query in the article.

hth

Association Browser Error

Hello Developers,

I used the add mining model to mining structure to modify a model so that maximum itemset =2, min prob=.01, min support= 2.

When i select maximum rows to anything higher than 2000 (default) i get duplicate rules.

The maximum rules returns is exactly16000 even though i set it higher than that.

Any ideas on the causes?

Thanks

Davy

Can you post the version/build you're using?|||

I am using the newest version of the Excel 2007 Data mining Add-in

I'm thinking that the rules are duplicated because I built a new model into the structure with a lower min_sup and lower min_prob, than the original model in the structure. I will test my hypothesis and let you know how it goes.

|||The duplicates appeared when I selected any Maximum Row level higher than 2000. The duplicates disappeared after I made another selection after that.|||

This may be a bug. Can you get in touch with Microsoft Support so we can investigate?

( Re. you previous post: The viewer behavior should not be affected by the presence of other models in the mining structure. )

Association Browser Error

Hello Developers,

I used the add mining model to mining structure to modify a model so that maximum itemset =2, min prob=.01, min support= 2.

When i select maximum rows to anything higher than 2000 (default) i get duplicate rules.

The maximum rules returns is exactly16000 even though i set it higher than that.

Any ideas on the causes?

Thanks

Davy

Can you post the version/build you're using?|||

I am using the newest version of the Excel 2007 Data mining Add-in

I'm thinking that the rules are duplicated because I built a new model into the structure with a lower min_sup and lower min_prob, than the original model in the structure. I will test my hypothesis and let you know how it goes.

|||The duplicates appeared when I selected any Maximum Row level higher than 2000. The duplicates disappeared after I made another selection after that.|||

This may be a bug. Can you get in touch with Microsoft Support so we can investigate?

( Re. you previous post: The viewer behavior should not be affected by the presence of other models in the mining structure. )

Association Browser Error

Hello Developers,

I used the add mining model to mining structure to modify a model so that maximum itemset =2, min prob=.01, min support= 2.

When i select maximum rows to anything higher than 2000 (default) i get duplicate rules.

The maximum rules returns is exactly16000 even though i set it higher than that.

Any ideas on the causes?

Thanks

Davy

Can you post the version/build you're using?|||

I am using the newest version of the Excel 2007 Data mining Add-in

I'm thinking that the rules are duplicated because I built a new model into the structure with a lower min_sup and lower min_prob, than the original model in the structure. I will test my hypothesis and let you know how it goes.

|||The duplicates appeared when I selected any Maximum Row level higher than 2000. The duplicates disappeared after I made another selection after that.|||

This may be a bug. Can you get in touch with Microsoft Support so we can investigate?

( Re. you previous post: The viewer behavior should not be affected by the presence of other models in the mining structure. )

Assistance on implementing Data Mining

Hi,

I'm new to SQL Server and data mining, so please forgive my ignorance...

I'm working on a project which requires me to use the datamining provided by SQL Server 2005. I've a table for which i want to predict the values in a table (Encyclopedia)

The table contains the following fields:

Component

Major Attribute

Minor Attributes(which is basically a list of CSV for attributes in no particular order)

I want to predict the component if i enter the attributes ..... my questions:

1. Should i change the table structure in any way to assist in data mining?

2. What model would be preferrable?

3. If i'm using the model will it extend to the data added to the table automatically or do i have to update it regularily.

I need to submit the project by 20th... and i'm not even started. I tried a lot on my own..... but couldn't get anywhere without definitive assistance from anyone.

Please help

Thanks and Regards,

Sundeep Singh

You should probably use a nested table which contains multiple rows for each of the Minor Attributes associated with a Component - so your mining model might look something like this:

CREATE MINING MODEL CompPredict(
CaseID LONG KEY,
Component TEXT DISCRETE PREDICT,
MajorAttribute TEXT DISCRETE,
MinorAttributes TABLE(
MinorAttribute TEXT KEY
)
)
USING Microsoft_Decision_Trees

If you add data to the source database that you process your model from, you will need to reprocess the model either manually or using scheduled job or Integration Service package.

|||

Hi Raman

Thankx for ur answer..................

I converted the tables as you said

TableComponent(ComponentID, BodyPart,MajorAttribute,ComponentName)

TableMinor(ComponentID,MinorAttribute)

Used TableComponent as Case(ComponentID as Key), and TableMinor as Nested(MinorAttribute as key)

I tried predicting Component Name using BodyPart, MajorAttribute and Minor Attribute as Input.....

I tried it using Microsoft Decision trees and it generated only single node for it.........

What do you think can be the problem....................

Moreover i need to generate the association rules from the data but again it is generating no rules from the given data...........

Thanks

Sundeep Singh

|||How big is your data set? Have you tried tweaking the algorithm parameters (lower COMPLEXITY_PENALTY, MINIMUM_LEAF_CASES, MINIMUM_SUPPORT)?|||

Hi,

I tried to reduce the parameters and got some rules... but there is a problem. the association rules that are being generated use a single minorAtt at a time.

I mean that in the entire collection there is not one that uses two MinorAttributes to Predict the column.

And can you please help me with the query... assuming that i have a major symptom and some minor symptoms and i want to predict what Components they can be for along with the probability of correctness..

Thanks

Sundeep Singh

|||

Hi,

played with the algo params and it worked.. i don't know how as yet.. but i got some rules with more than one minor symptom.

But the problem with the query remains... somebody please help!!!!!!!! only four days left for submission....

Thanks

Sundeep Singh

|||

You can do a SELECT COUNT(*) FROM TableMinor and SELECT COUNT(DISTINCT ComponentID) FROM TableMinor. If these numbers are the same, than you have one minor/component, otherwise you do have multiple minors/component and everything's OK (except maybe your data).

If you don't have multiple minors/component, you don't need a nested table. From the initial description it seemed like there were.

|||

The number of minor are more than one, but the query that i asked for was to predict the component when i enter the minor and major attributes along with the probability of match...

Supposing that i have named my model as Encyclopedia.

Thanks

|||Try this to get the top 3 predictions for component:

SELECT FLATTENED
TopCount(PredictHistogram(Component), $AdjustedProbability, 3)
FROM [ComponentPredictModel]
NATURAL PREDICTION JOIN
(SELECT 'xxx' as MajorAttribute,
(SELECT ( SELECT 'x' AS Minor UNION
SELECT 'y' AS Minor )
AS [MinorAttributes])
) AS t