Showing posts with label association. Show all posts
Showing posts with label association. Show all posts

Tuesday, March 20, 2012

Association Scenario

Hi,

I have a product basket scenario in where I have to recommend contracts to a customer based on the product and the quantity he/she buys. Product Quantity is an important factor which administers the user in the purchase of a particular contract

I have the following tables with me.

Customer product transaction table, Customer Contract transaction tablebut there is no direct relationship between contract and product in the database. The only way the two can be linked is through the customer.

If I create a mining structure with Customer-Product information as the nested table and Customer-Contract information as the nested table with customer being the link between the two, the model is showing some irreverent contract recommendations.

what is the solution for the above problem? Is it because the is no direct relationship between the product and the contract?

How can I overcome this problem?

You are on the right track - you have the two nested tables that you need. What you want to do is to make the Product nested table as Input and the Contract nested table as Predict Only. Also, you may want to try excluding the product quantity to see how that impacts results.

Another important factor is how you set parameters for the AR algorithm. Setting the MINIMUM_SUPPORT and MINIMUM_PROBABILITY parameters are crucial - try lowering them until you find interesting results.

|||

Hi,

I tried the above changes but there was not much difference in the results. It still gives irrelevant recommendations.

Can you tell me how differentare the following two mining structures

1. Customertable => CASE Table

Customer – Product table => NESTED Table

Customer – Contract table => NESTED Table

2. Customer – Product table => CASE table

Customer – Contract table => NESTED Table

|||I'm not an expert in association but i think that in the first case

you're bound to have three kind of information : contract 1 /contract 2

OR product1 / product 2 OR contract / product (in fact it depends on

the key of your nested tables but in any case you'll get

contact/product association).
In the second case you won't have product1/product2 association. and the contract/ product association will have a support of 1

I don't really catch your problem but if you want to make the second mining structure, you will have to add customerID to your tables product and contract. With some sql queries, you should be able to prepare correctly your data|||

In the first structure it is possible for a single customer to have many products. In the second structure, each customer can only have one product.

Using the first model you can make Product INPUT and Contract PREDICT ONLY and you are asking the question the right way.

When you say it gives "irrelevant recommendations" what do you mean exactly? It could be that only the "irrelevant" ones are supported by the data, or you need to tweak the parameters a bit more.

If you aren't seeing what you want, use the same structure to create a decision tree model. This way you can look at the trees that are generated to see if there are meaningful patterns in the data. By default you will have 255 inputs (e.g. products) and 255 outputs (e.g. contracts), but that should be enough to get you started, you can adjust those parameters as necessary to refine the models.

Also, just for a question, how many customers, products, and contracts are there?

Thx

-Jamie

|||

Hi Jamie,

In the database there are totally around 800 customers, 300 products and 9 contracts.

These contracts are grouped logically in to long term, short term (depending on the product quantity) and some are applicable to certain products only.

Data in my database may look like the following

Customer - Product

Customer

Product

Quantity

Date

C1

P1

X

Jan 2005

C1

P1

Y

Feb 2005

C1

P2

X

March 2005

Customer – Contract

Customer

Contract

Date

C1

Contract1

June 2005 (For P1 bought in Jan 2005 as above)

C1

Contract2

March 2005 (For P1 bought in Feb 2005 as above)

C1

Contract3

March 2005 (For P2 bought)

So the relevant combinations would be

P1 – Quantity X – Contract1 (long term contract due to quantity X)

P1 – Quantity Y – Contract2 (short term contract due to quantity Y)

P2 – Quantity X – Contract 3 (Contract for the product type P3)

The other combinations are irrelevant.

These combinations or groupings are high level understanding and are not reflected at the technical level.

Some of the mining model recommendations are

1 . For the purchase of P1 of Quantity X apart from recommending Contract1 it also lists Contract2 and Contract3( irrelevant).

2. For P2 it recommends both Contract3 and Contract1 (irrelevant).

Association Rules: discretization

Hi there,

it has been a long i'm trying to execute Microsoft Association Rules on my database.

I solved memory leak problem now, but i still can't understand output rules.

Database contain all the italian student who took a degree last year. Here in Italy, they have to compile a summary where they speak about universitary experience. ie: they talk about experience with teachers (pointage from 1 to 5); they says if they want to continue in the universitary field or not, and so on.

Most of the rules, says:

Int_Stud=1-2, RapDoc>4

Int_Stud is the column where i store student intention to continue university. 1 means they want to go on, 2 means they do not want to continue to study. So, this rules has no sense, because it relates all the student (in my mind): the one who wants to continue university and the one who do not want to.

I think problem is that visual studio 2005 and analysis service has no understanding of Int_Stud world, they've no idea that Int_Stud can have just 2 values and that they're opposite each other. Is there a solution to this problem? Can i discretize this column?

Even if I know not to have perfect english, I hope to be understandable

I think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that. I think the system consider 1 and 2 as a number. This replacing conduct to understandability too.|||

do u mean i have to modify value in database? I mean, i've something similar to 90 column in the same situation of Int_Stud ... does exist another solution? Moreover, most of fact table - i worked on - had only numeric values in (no text) ...|||

In this discretized situations I think u have to make necessary transformations. Let's think you have to present this project to your big boss. You have to explain in a legend what you mean with Int_stud that have value 1 or 2 etc. I don't have to be in your position ... Smile

|||

ggciubuc wrote:

In this discretized situations I think u have to make necessary transformations. Let's think you have to present this project to your big boss. You have to explain in a legend what you mean with Int_stud that have value 1 or 2 etc. I don't have to be in your position ...

I continue not understand what you mean when you speak about transformations .. why can't i make a discretization procedure on my numeric value? Can't i divided them into bucket?

|||

think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that.

|||

ggciubuc wrote:

think you have to modify your data source in sense of replacing 1 with "Continue" and 2 with "NotContinue" or something like that.

so, you mean that i've to modify my source data by changing numeric value with text one? Why? This will be text analysis, won't it?

I used weka one time, and it has a tool which allows to discretize numeric value by dividing it into bin (and you can set your preference too)

|||

You don't have to replace int values with text to make association rules model work correctly. All you need to do is change the column from Discretized to Discrete. (Open Mining Structure tab and look at the Content property of the column)

|||

My ideea is to make things more readable and understandable. I should do in this way.

|||

Tatyana Yakushev wrote:

You don't have to replace int values with text to make association rules model work correctly. All you need to do is change the column from Discretized to Discrete. (Open Mining Structure tab and look at the Content property of the column)

thanks so much .. it works exactly as i wanted to Smile

Association Rules Model for problem

What would be the right design approach for the following problem?

I have a single table called SelectionFactors, which has the following columns and sample data:

ProjectID Factor FactorValue 1000 Countries USA 1000 Countries Canada 1000 Countries France 1000 Languages English 1000 Languages French 1000 Company Type Consulting 1000 Company Type Software 2000 Countries India 2000 Countries China 2000 Countries USA 2000 Languages English 2000 Languages Chinese (Simplified) 2000 Languages Chinese (Traditional) 2000 Languages Spanish 2000 Company Type Retail 2000 Company Type Dairy Products

The problem is to allow a descriptive analysis of the data to find patterns in the users selections. For instance,

if Languages->English is selected, what are the counts of projects for other Factor->Factor Value combinations?

Countries->USA = 2, Countries->Canada=1, Company Type->Consulting=1 and so on.

Since all the data is in this single table, are both the case and nested tables the same? What are the keys and inputs? I only need a descriptive analysis (no prediction) and ALL possible combinations MUST be part of the results; how should the model be designed?

Thank you,

Anna.

This is a good problem. What you want to do is to create a model with three nested tables all marked predict and input. Event though you want descriptive analysis, you need to mark them "predict" such that rules will be formed (only items marked predict will show on the right hand side of rules).

The way you will create such a model is to use the "Named Query" feature of the data source view(DSV). You can right-click on the DSV and select "New Named Query" to create these.

The named queries you will want to create are these:

Projects: SELECT DISTINCT ProjectID FROM SelectionFactors

Companies: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Companies'

Languages: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Languages'

Company Type: SELECT ProjectID, FactorValue FROM SelectionFactors WHERE Factor='Company Type'

You will then need to relate the ProjectID in each of the transaction named queries (Companies, etc) to the ProjectID in the Projects named query. The system will prompt you to set ProjectID as the key of the Projects named query . At this point, the named queries are equivalent to tables as far as Analysis Services is concerned.

When you create the model, you will make the Projects table the case table and the other tables nested tables. ProjectID will be the key of the Projects table and FactorValue will be the key of the other tables. Make sure to mark FactorValue as Key, Input, and Output.

When you process your model you will see rules relating projects, companies, and company types (both between factos and in the same factor).

Note that you may need to adjust the MINIMUM_SUPPORT and MINIMUM_PROBABILITY parameters to get the results you need (in fact, it's recommended)

HTH

-Jamie

|||

Thanks for your response.

However, I should have mentioned that there is an open-ended number of "Factors". New factors are added often and the number of factors is nearly 100.

The type of questions I am trying to answer using this model is:

"Among projects that have Languages-> English, how many (distinct projects) have Company Type->Consulting, Countries->USA" and so on.

Ideally, I would like to provide this analysis in a free, OLAP environment where the user can select one factor-> factor value combination and then see all the other related combinations.

I currently have a Cube with SelectionFactors serving as both the Fact and the dimension table with a single measure - DISTINCT COUNT of projectIDs.

Thanks once again.

|||

Given the information in my previous post, what are my design options?

Thanks.

|||

My question for you, then, is if you have a cube for this, what are you not getting that you need?

If the factor/factorvalue can be viewed as a complete unit, and you don't need to provide analysis between factors - e.g. given Language X, which Countries and Company Types are prevalent, you could create a calculated column in the DSV that combines the factor and factor value.

|||

Perhaps you can help me understand why I am unable to get the expected results. I am fairly new to OLAP and Data Mining and would appreciate any pointers and guidance.

The full picture of my problem is:

Dimension Project Profile - Project ID, (Other Project related attributes)

Dimension Selection Factors - Factor, Factor Value

Fact Selection Factors - Project ID, Factor, Factor Value

Project Cube Measure - DISTINCT COUNT of Project IDs

My understanding is that this is a problem of intersection of factors, which I cannot achieve with a single dimension. In fact there is this other thread http://forums.microsoft.com/msdn/showpost.aspx?postid=856304&siteid=1 that seems to come very close to my problem.

In my case, the following MDX returns the projects that have the intersection of specific factor values.

SELECT {[Measures].[Project Count]} ON COLUMNS,

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

ON ROWS

FROM PROJECTCUBE

The questions are:

1. How do I get all the other Factor->Factor Values that are part of the selection factors for the projects returned by this MDX. This, if I understand correctly, is Basket analysis.

and

2. Is there a generic way to perform this analysis through cube design rather than writing MDX for each case?

Thanks once more.|||

The following MDX gives me the contents of the "baskets" of projects that have the selected items. However, the counts of the projects is not restricted to the subset retrieved through the Intersect. Is there a way to get the counts to only be within the subset returned by the Intersect?

With this MDX, I can get very close to what I need. The only thing I am missing is to get the counts (perhaps through a calculated measure). Help on this will be much appreciated!

__

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTY

(

([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children),

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Selection Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

ON ROWS

FROM PROJECTCUBE

|||

In order to obtain counts restricted to the subset returned by the Intersect, this MDX takes the constraint out of the ROWS into the WHERE clause.

I would still like to know how to design an Association rules model that can "automate" the generation of results by case. Hopefully, the MDX can point directly to the expected results.

SELECT [Measures].[Project Count] ON COLUMNS,

NONEMPTYCROSSJOIN([Selection Factor].[Factor].Children, [Selection Factor].[Factor Value].Children)

ON ROWS

FROM PROJECTCUBE

WHERE

NONEMPTY(

INTERSECT

(

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Annual Revenue].&[$500 million - $3 billion in revenues]

)

),

NONEMPTY

(

[Project Profile].[Project ID].Children,

(

[Measures].[Project Count],

[Selection Factor].[Selection Factors].[Factor].&[Concurrent Users].&[1,000–10,000 users]

)

)

)

)

sql

Association Rules algorithm [support,confidence]

In assotiation rules each rule has a [support, confidence] part. In Microsoft Association Rules there is a [probability,importance] measure in each rule and importance can be greater that 1.

I found the following in msdn but i'm not sure if i understood correctly.

MINIMUM_PROBABILITY: Specifies the minimum probability that a rule is true. For example, setting this value to 0.5 specifies that no rule with less than fifty percent probability is generated.
The default is 0.4.

MAXIMUM_SUPPORT: Specifies the maximum number of cases in which an itemset can have support. If this value is less than 1, the value represents a percentage of the total cases. Values greater than 1 represent the absolute number of cases that can contain the itemset.
The default is 1.

My questions are
1) Can i explain the [probability,importance] in [support,confidence]? If yes, how?
2) What importance>1 means?

Thank you in advance.

MS Association Rules still has [support, confidence] like any other AR implementation. If you take a rule of the form

A -> B

Support is the number of times the itemset A occurs, and confidence is the probability that B occurs when A occurs. Importance is a different measure that indicates the lift of the rule e.g. the increase in probability of the target over the probability of the target at random, which is why it can be greater than 1.

In the DM viewers, the support is indicated on the Itemset page.

|||

If i have a rule A,B => C and an itemset A,B,C with support 2000 then the support of the rule is 2000? How can i calculate the confidence then?

Thank you for your reply.

|||

If your rule is A,B=>C then:

Confidence == Probability == Numberof(A,B,C) / Numberof(A,B)

|||That was very helpful thank you :)|||Hi all,

I am trying to make an algorithm that also takes into account how "important" an association rule is. I found that "importance" is something that I am missing.

Ho is importance calculated ?

greetz,
Alex|||

The formula, with some details, is discussed in this thread:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=290186&SiteID=1

Association Rules

1) I use the identifier of transaction and attribute in one table.

Do I can to build a association rules structure without the use of the nested tables?

I tried - did not turn out...

2) As it is necessary to use a main and child table, can not build a prediction query.

When I try to add the predict column in a criteria/argument (Field=PredictSupport), i'll given message:

"Nested table column cannot be used as an argument in a data mining function."

I can not use other columns, because they are not predicable.

You have to have a nested table, but you can use the same table as the case and nested tables. Just mark the transaction key as the key for the "case" table, and the transaction item as the key for the "nested" table.

In your prediction query you would predict the nested table ( you are essentially predicting which rows will be there). For example, if your model was like this:

TransID LONG KEY,
Products TABLE PREDICT
(
ProductName TEXT KEY
)

You would issue a statement like

SELECT Predict(Products, 5) FROM MyModel PREDICTION JOIN ....

|||

Yes, turned out to build a model! Thanks!
But at an attempt to take advantage of builder of queries, there is an error:

Unable to retrieve the list of distinct states for mining model column Utp Doc Id.

ADDITIONAL INFORMATION:

Error (Data mining): The specified DMX column was not found in the context at line 1, column 17. (Microsoft SQL Server 2005 Analysis Services)


Without a builder - a query works :)
And yet question - as possible to apply a function, as though, PredictSupport? She needs a column, and for me does not turn out to use her from a table...

P.S.: I am sorry for my English =)

Association Questions

I'm wondering if anyone can give me some help with an association model I'd like to setup. It's a typical market-basket analysis, but rather than grouping by individual customers, I'd like to group by customer grouping. (In our database, customers are grouped into categories like: large, small, medium) If this is possible, I'd like to generate the most popular items (so just querying the most probable itemsets), for each customer grouping (I'll refer to this as 'segments' from here on out), and then create a listing of customers in each segment which do not have the most popular items for their segment. I know for this last part I can use reporting services to tackle that problem, however, I'm not really sure how I can really do the rest of this with an association model in SSAS.

Our table structure looks like this:

Code Snippet

CustomerTable PurchasesTable

- --

CustomerName(key) CustomerName

CustomerGroup PurchasedProduct

And the data is arranged in this fashion:

Code Snippet

Customer Table:

CustomerName CustomerGroup

- -

A large

B large

C small

Purchases Table:

CustomerName PurchasedProduct

-

A ProductA

A ProductB

B ProductA

C ProductC

C ProductD

I know this is a lot of information but any help you guys may be able to offer would be great! Thanks!

Just a suggestion which might not do exactly what you requested, but might help:

You could create one association model looking like below:

(

Customer TEXT KEY,

CustomerGroup TEXT DISCRETE, // i.e. used as input, on the left hand side of the rules

Products TABLE PREDICT

(

Product TEXT KEY

)

)

Then, for each customer, get the recommended association predictions based on:

- the products currently owned by the customer

- the group that the customer belongs to

This will create rules that might include the customer group on the left hand side (if that it found to be interesting). It may also generate rules which ignore the group, containing items that sell together across groups of customers.

In the prediction query, if you ask solely for predictions that are NOT based on rules (but on popular itemsets), you will get a list of popular itemsets that should match each customer according to previous purchases and possibly group. Of course, excluding predictions that are based on rules might actually reduce the accuracy of the recommendations. You might want to sort the prediction results by support and use some sort of threshold

If you must really separate the groups (i.e. require to take the customer group into account for each individual customer), you could also build 3 models, one for each group. Then, you can use the same kind of predictions, but a different model for each customer (based on the customer group). This way, the recommendations will be based solely on popular items within the current customer group.

Hope this helps

|||

I was thinking that might be the way to go about doing that, but just have a couple quick questions to clarify.

If I do it the way you suggested, you said that I might get rules that ignore the group/contain the group. How may I filter(?) the results so I only obtain those with with the groups, as I'm not as interested in what individual customers purchased, so much as just purchases made within the groups?

If I built three models, how would I tell the model to only be concerned with the 'small' group when all the groups belong in the same database? Is there somewhere I can specify a filter on the mining model to only focus on certain fields...or something?

Thanks for your help so far!

|||

In SQL Server 2005 you would need to build three separate views (or named queries in the DSV) each showing only a single group of customers. Then for each group you build a separate mining structure and a separate model.

We are working on some advances that may make this easier in future versions, but for now, you should find that this approach works well.

Association Prediction by Rules Still Returns Itemsets

If I use this code with an association model, it still returns itemsets for me - when it should be returning only nodes with rules associated with them (according to sqlserverdatamining.com). If I try adding 'AND $PROBABILITY > .25' to the where clause, it returns 0 results for every query I try. Any clue why this may be happening?

Code Snippet

SELECT FLATTENED
(SELECT * FROM PredictAssociation([Product],20,
INCLUDE_NODE_ID,INCLUDE_STATISTICS)
WHERE $NODEID<>'')
FROM
[ProductRecommend]
PREDICTION JOIN
OPENQUERY([ds],
'SELECT
[PRODUCTCLASSID],[DESCRIPTION]
FROM
[Product_Table]
WHERE
[PRODUCTCLASSID] = ''1234'' AND [DESCRIPTION] = ''DESC''
') AS t

ON
[ProductRecommend].[Product].[PRODUCTCLASSID] = t.[PRODUCTCLASSID] AND
[ProductRecommend].[Product].[DESCRIPTION] = t.[DESCRIPTION]

This query returns more relevant results than those lacking the filtering by $NODEID, however the results should have higher probabilities than .047! Please help! Thanks!

Okay, I just reconstructed the same query using my data in a relational mining model (instead of OLAP) and got reasonable results. Would anyone know how to fix this for OLAP or be able to point me in a direction where I could go to learn how to do it? Thanks.

|||What is the MINIMUM_PROBABILITY value for the OLAP mining model?
Is it different than the one from the relational model?

One more thing (which you probably know already) -- the query you posted initially executes one prediction (PredictAssociation ... 20) for each row in the data source query (does not group together multiple input rows belonging to the same transaction).
Basically, only rules having a single item on the left hand side will be used in prediction.

Association model prediction not using itemsets

I have a market basket model using associations. It generated several dozen itemsets. However when I attempt to run a singleton prediction like this:

select (Predict(Orderproduct3q,INCLUDE_STATISTICS,10)) as [Recommendation]

From

[Case All]

NATURAL PREDICTION JOIN

(SELECT (SELECT '16407' AS [Pname])) AS t1

the resulting predictions don't take the itemsets into account. Instead, the predictions consist of the ranked products in the training set, ordered by frequency. This appears to happen regardless of the precise query specified within the "natural prediction join".

What's going on here and how do I generate a singleton prediction which makes use of the itemsets?

The nested table inside the input should have the same name as the nested table of the model, for Natural prediction Join. Could you please try this:

select (Predict(Orderproduct3q,INCLUDE_STATISTICS,10)) as [Recommendation]

From

[Case All]

NATURAL PREDICTION JOIN

(SELECT (SELECT '16407' AS [Pname]) AS Orderproduct3q ) AS t1

Otherwise, the input rowset (t1) contains an unnamed nested table which cannot be mapped to your model's table

|||

Thanks for the suggestion, which was on the right lines. Something like this does now pick up one of the itemsets.

select (Predict(Orderproduct3q,INCLUDE_STATISTICS,10)) as [Recommendation]

From

[Case All]

NATURAL PREDICTION JOIN
(SELECT (SELECT '17717' AS Pname Union SELECT '16415' AS Pname) as Orderproduct3q ) AS t1

|||Also look at the tip/trick at http://www.sqlserverdatamining.com/DMCommunity/TipsNTricks/3514.aspxsql

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. )

Association and Dependency Network

Hello,

How do I get n and only items predicted by a specific item(s) either directly or indirectly as shown in the dependency network diagram?

For instance, the predict function won’t work for me - because running this query on AdvantureWorks:

SELECT PREDICT([Association].[Products], 5)

From [Association]

NATURAL PREDICTION JOIN

(SELECT (SELECT 'Touring Tire Tube' AS [Model]) AS [Products]) AS t

Returns Sport-100 as a second result, although it is not predicted by any mean by Touring Tire Tube as shown in the dependency network diagram.

My query should have returned just one row - Touring Tire.

See this tip and trick

Returning Associative Predictions Based Solely on Rules

Note that the Dep Net only shows pairwise rules so this technique may still return more results if there are rules that don't show in the Dep Net. You can avoid this by setting the maximum itemset length to 2.

sql

Association algorithm itemsets

What is the algorithm that generates the itemsets in the Association model? I'm looking to possibly use this part of the Association algorithm (i.e. the grouping into itemsets) in a separate plug-in algorithm.

The algorithm is based on the 'a priori' technique. Basically, 1-itemsets are filtered based on certain thresholds, then the algorithm moves to computing 2-itemsets and so on. Brief details on how Microsoft Association Rules work: http://msdn2.microsoft.com/en-us/library/ms174916.aspx

Hope this helps

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability

importance


0.938

0.105302438

F3 = NotMuffin -> F2 = Donut










0.833

0.218055761

F2 = Donut -> F3 = NotMuffin




0.75

-0.105302438

F3 = Muffin -> F2 = Donut




0.5

-0.218055761

F2 = NotDonut -> F3 = NotMuffin




0.5

0.458637849

F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,

Association algorithm - Importance of a rule

Can anyone tell me, how the Business íntelligence Studio calculates the importance of a rule. I can't find the formula. I know some formulas, but the result in SQL Server is completly different.

Thanks!

For rules, the importance is calculated using the following formula:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

An importance of 0 means there is no association between A and B. A positive

importance score means that the probability of B goes up when A is true. A

negative importance score means that the probability of B goes down when A

is true.

Below is an example of the correlation counts of donut and muffin derived

from a purchase database. Each cell value represents the number of

transactions. For example, 15 out of 100 transactions include a customer

purchasing both donuts and muffins.

Donut Not Donut Total

Muffin 15 5 20

Not muffin 75 5 80

Total 90 10 100

The support, probability, and importance of related itemsets and rules for

donut and muffin:

Support({Donut}) = 90

Support({Muffin}) = 20

Support ({Donut, Muffin}) = 15

Probability({Donut}) = 90/100 = 0.9

Probability({Muffin}) = 20/100 = 0.2

Probability({Donut, Muffin}) = 15/100 = 0.15

Probability(Donut|Muffin) = 15/20 = 0.75

Probability(Muffin|Donut) = 15/90 = 0.167

Importance({Donut, Muffin}) = 0.15/(0.2*0.9) = 0.833

Importance (Donut=>Muffin) = ln(Probability(Donut|Muffin)

/Probability(Donult|Not Muffin))= ln(0.8) = -0.223

Importance(Muffin=>Donut) = ln(Probability(Muffin|Donut)

/Probability(Muffin| Not Donut)) = ln(0.33) = -1.100

From the importance of the itemset {Donut, Muffin}, we can see Donut and

Muffin are negatively correlated; it is rather unlikely for someone who buys

a Muffin to also buy a Donut.

The Importance score is also known as Weight of Evidence (WOE).

|||

Hi,thanks a lot for your answer!

I recalculated the importance with your formulas and compared this with the results of the microsoft association algorithm.

Your formula for the importance is almost right, but it calculates the importance for

Muffin =>Donut and not Donut => Muffin

and it must be "log" and not "ln" !!

So at the end, this must be the right formula:

Importance(Muffin =>Donut) = log(Probability(Donut|Muffin) / Probability(Donut|Not Muffin) )

and for

Importance(Donut=> Muffin) = log(Probability(Muffin|Donut) / Probability(Muffin|Not Donut) )

UllaH

|||

Acutually at the beginning of Jamie's answer, the formular has been already there correctly:

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

Regards,

|||

Importance (A=>B) = log ( p(a|b) / p(a|not b) )

It makes more sense to me if a and b are switched in the log function

Can some one point me to a Microsoft Research Paper "With all due respect to all" not just odiscussion Onions that discusses the theoretical background for calculating Rule importance?

Musa

|||Dear all,

I try to run the "donuts and muffins" example by using SQL 2005 BI but I didnot have the results as the formula you instruct (Importance (A=>B) = log ( p(a|b) / p(a|not b) ) ). Please explain me more detail.

probability importance
0.938 0.105302438 F3 = NotMuffin -> F2 = Donut








0.833 0.218055761 F2 = Donut -> F3 = NotMuffin


0.75 -0.105302438 F3 = Muffin -> F2 = Donut


0.5 -0.218055761 F2 = NotDonut -> F3 = NotMuffin


0.5 0.458637849 F2 = NotDonut -> F3 = Muffin

Thank you very much.
Your truthly,
sql