# Association Algorithm Itemsets

Jan 20, 2007

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.

## Why Do Association Itemsets Have Probabilities?

May 21, 2007

Why do association itemsets have probabilities associated with them when its rules that generate probabilities? Any queries I do against my model are using these itemset probabilities rather than the probabilities that the rules generate. More over, the probabilities generated for these itemsets are far less than the MINIMUM_PROBABILITY tag in the algorithm properties menu.

## Association Model Prediction Not Using Itemsets

Dec 31, 2006

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?

## Association Prediction By Rules Still Returns Itemsets

May 22, 2007

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!

## Process Association Algorithm Using ISS

Feb 20, 2008

Hi!

I need to deploy several Association algorithms, so I want to do it using ISS. Can anyone help me telling me which task should I have to use to do it?

Thanks!

Ezequiel

## Association Rules Algorithm, Help?

Nov 22, 2007

I need to create a set of cases for a project that uses the Microsoft Association Rules algorithm to make recommendations for products to customers. My question is: the set of scenarios must include all transactions of customers for training?. or is it sufficient some percentage of total transactions? If i do not use all transactions of customers, could be that the algorithm does not consider some products in their groups or rules and could not make recommendations about these?

thanx
Diego B.

## Association Algorithm - Importance Of A Rule

Mar 6, 2006

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!

## A Priori Algorithm In Association Rules

Jul 10, 2006

MS uses the a priori algorithm in Association Rules, while other DM software have gone to the Novel Algorithm. Can you tell us why MS decided to stay with the a priori? Did you overcome the limitations that it's accused of having? Thanks!

## Association Rules Algorithm [support,confidence]

Dec 16, 2006

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?

## Case Table And Nested Tables In Association Algorithm

Aug 30, 2006

hi

i m trying to build microsoft association model using Microsoft association algorithm. i got

1) patient table(patientid, name, city)

2) diseases(diseaseid, dieseasename)

It is M:N [many to many] relationship between above tables, so

3)Patient_diseases(patientid,disease_id). [RELATIONSHIP TABLE]

i am trying to associate city in patient table --> disease in diseases table.   I want to build association data mining model and use it on web form, such a way when the user enters city associated disease will be displayed.

should i select all 3 table to build the model? could help me to decide what tables should i select as Case and what tables as Nested? what attributes from the table should i select as key, input, predictive ?

i am using data mining tutorials on sqlserverdatamining.com to build this model. is there anything further during my model building i get into confusion? please suggest me where i can find complete resource or inform here.

i appreciate Mr.Jamie for his guidance so far in my academic project. i do have the book 'Data mining with sql server 2005'.  I left with just one day to do this and document.

hoping someone could suggest. your help is much appreciated.

regards

raju

## Plug-in Algorithm In Data Mining Using Sql Server 2005-- Modification For Association , Classification??

Sep 7, 2006

This package includes the source code for a sample plug-in algorithm written in C#.

in this source code all .cs files are modified for clustering algorithm

if my plugin algorithm is of association or classification type then what modifications are requried in source code???

## Factorized Association

May 16, 2007

Hello,
Let€™s say (for simplicity), in my site you can do one of two things €“ look at products and buy products.
I want to build an association structure between my products based on those two actions, but(!) when a user looks at two products it creates less important association than when the user actually bought those two products.
So basically, I want to give a different factor base on different actions occurred on my products.
How do I build my structure? How do I query it?

## Personalization Rather Than Association

May 14, 2007

I'm trying to figure out how to build a personalization engine.

If my structure is built with users as case, and products as nested - I€™d like to predict best products per user (rather than associated products), and If possible, ignoring products he already bought.

How do I do it?

## Use Of Ms Association Algo.

Jul 24, 2007

Hi,

I am working on a table that has following fields transaction_id, product_name,product_brand,product_size,product_quantity.

fyi, If a customer purchases 3 items, all have same transaction_id.

I need to use this table (in BIDS) for finding associations between different products,, but I am unable to do so.

Can anyone help me as to which fields should be used as input so that I can predict the association.

Thanks a lot.

Aashutosh Magdum

## HELP!!:about The MS Association Rules

May 14, 2006

When i use the MS association rules ,i don't know how it is worked on the background .I stuy the Fp-Growth algorithm , but there're some questions , I don't kown what's the meaning of transcation database. who can give me one example ? thanks .I know we can store the data in relation database,but in basket Analysis ,how a transaction stroed in relation database?

## How To Detect *.cer Association

May 29, 2007

I am entering to administration of
SS2005 SP1 (Windows 2003) having files mdf, ndf, ldf in
C:Program FilesMicrosoft sql serverMSSQLData
This dir also has two *.cer files.

Apparently no encryption is used

How can I get known what these *.cer files are for?

## Association Scenario

Aug 10, 2006

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 table but 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?

## Association Rules

Dec 3, 2006

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.

## Association Questions

May 31, 2007

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 SnippetCustomer 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!

## Association Rules: Discretization

Aug 8, 2007

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

## Association Browser Error

Jun 3, 2007

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

## Association Rules Web Viewers

Jan 28, 2008

I note that there exist three web viewers for data mining algorithms, namely, DMNaiveBayesViewer, DMDecisionTreeViewer and DMClusterViewer. How come there are no viewers for association rules (itemsets, rules, dependency network)? Can you suggest any alternative way of showing such valuable information in a web application?

## Association And Dependency Network

May 2, 2007

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.

## Association Rules - Importance

Feb 14, 2008

I understand Mr. MacLennan's explanation provided at http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=282651&SiteID=1 and appreciate the time he took to explain how importance works. However, like the user with username "sang", I also ran the data in BI 2005 and got the same results listed by the aforementioned user. I did this using the following data:

donut
muffin

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

y
y

n
y

n
y

n
y

n
y

n
y

etc.

The rule muffin -> donut has an importance of -0.105302438, which is not the same as Mr. MacLennan's results. I tried switching the roles of a and b in a -> b and using different bases on the logarithms. I don't get the result of -0.105302438 with any of these. I also tried to calculate importance with a small data set I have and can't get the results using Mr. MacLennan's explanation with that data set either. Any thoughts on the descrepancy?

## A Question About Association Rules

Jul 17, 2007

Hi

I am doing the Market basket analysis for a retailer using association rule. The whole data set is huge which contains grocery, clothes and books etc. If I want to check out the relationship between several different clothes brands, (e.g. LEVI'S and adidas), should I just remove all the grocery and books transactions, use the subset which only contains clothes transactions to re-run the association rules? Is this gonna work?

## The Data For Association Rules

Jun 20, 2007

Hi

I got a question about the data preparation of market basket analysis.

There are always some transactions with only one single SKU product. It seems that these kind of transactions have nothing to do with association. Shall I just exclude them or what?

## The Mean Of Using Association With Importance And Probability

Apr 12, 2007

hi,
i have a exercise using association datamining
my database have 350 records,
i use 90 records for datamining and it release some rules which i choose on top of mSOLAP_NODE_SCORE,
but when i use select statement to check my result i have 1 records, the same as my result, and 5 records not true;
for example:
rules A=a,B=b-> C=c
select * from <my_table> where A='a' and B='b' and C='c'; ==>1 record return
select * from <my_table> where A='a' and B='b' and C<>'c'; ==>5 records return
C with 3 values c1,c2,c
with the second statement C includes 2 c1 and 3 c2

i don't understand how they work.
i want to choose some best rules can present my database.
how can i choose importance and probability to get best rules.
with database have 90 records and a database have 350 records which values i should use for minimum_probability, Minimum_Support, Minimum_importance...
when i choose rules i should choose on importance or probability.

## Usage Of Association Rules

Aug 15, 2006

Hello,

I want to score my data by only the assoziation rules I filtered in Mining-Model-Viewer.

Is this possible?

I recognized that MiningModel Predictionquery uses the generated Model (all rules).

Is there any way to influence the model at generating time.

Thanks a lot.

Hi,

I specified the point to solve my problem but I did not understand why MS SQL Server didn't recognize the Association parameters I but in. It uses the Minimum_Probability I put in, but the Minimum_Importance i wrote didn't care it, it use the default value.

I want to paste a screenshot here but it isn't possible. By the way I'm using the evaluation version, which should be same as the enterprise edition.

Best Regard

## Mining Association Rules Using SQL

Nov 26, 2006

Hello everybody,

I am interested in mining association rules field

I have found some posted statements but I didn't undertand them. I need your help badly.

Just tell from where should I start?

Thanks alot

## Trouble Filtering Association Rules

Jan 7, 2008

I've set up an association rules mining model on SQL Server 2005.

Below is the output for the "rules" tab in the model viewer:

0.457 0.480925197502775 BAKERS BASKET = Existing -> FR ORANGE JUICE = Existing
0.423 0.467835626151174 ONE EGG = Existing -> FR ORANGE JUICE = Existing
0.416 0.42489020548496 GRANOLA PARFAIT = Existing -> FR ORANGE JUICE = Existing
0.416 0.344523279228151 CROISSANT = Existing -> REG CAPPUCCINO = Existing
0.430 0.338244068974039 PAIN AU CHOC = Existing -> REG CAPPUCCINO = Existing
0.424 0.332652863071932 FR ORANGE JUICE = Existing, REG LATTE = Existing -> REG CAPPUCCINO = Existing

The problem is that I can't seem to reduce the minimum probability below .42 to view more rules.

I've considered that it might be becuase these are the only rules discovered, however I know quite a bit about the data and I would excpect many more associations.

The data is transactional in the format

TransactionID, ItemID

## Quick Question Regarding Association Rules...

May 28, 2007

Hello,

I'm new to analysis services and hopefully this is a quick & easy question. I have a couple of quite large (163,000 tuple) tables with columns essentially representing a bit vector. I would like to mine for association rules but the number of '1' values are very, very sparse and they are the only objects of interest. How can I get more control over the algorithm---that is, how can I stipulate that the state of the column must be '1' to be considered? Any help or direction to the proper documentation would be great.

## Prediction Query In MS Association Rules

May 22, 2006

Hi!

I'm building a mining model wiht MS Association Rules. After processing this model, the result includes some rules(example):

E = Existing, C = Existing -> B = Existing
F = Existing -> E = Existing
C = Existing, B = Existing -> E = Existing
F = Existing -> B = Existing
B = Existing, A = Existing -> C = Existing
F = Existing, B = Existing -> E = Existing
F = Existing, E = Existing -> B = Existing
D = Existing -> A = Existing
C = Existing -> A = Existing
E = Existing, A = Existing -> B = Existing

I want to buid a query that has two or more items on the left of the rules, example: E = Existing, C = Existing -> B = Existing
->I want to buid a query to predict that: when a customer buy 'E' and 'C' then he likely buys 'B'

## Sequence Clustering And Association Rules

Jul 13, 2006

Hi

I read the paper of sequence clustering. It seems that the major application of the algorithm is for the web site. I was just thinking that can I apply this algorithm on the purchase sequences of credit card data?

If so,please also tell me the difference between sequence clustering and association rules on credit card data application. Although I realize that sequence clustering is a fully probabilistic model and it has the capability of prediction, association rules also give the probabilities of purchasing the other products.