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'
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!
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?
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 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?
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?
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?
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?
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.
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?
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 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.
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.
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.
I would really appreciate if you could help me out. I am trying to create a taxonomy to be taken into account into the association rules algorithm. For example, if my data is a group of purchases from a supermarket I could have one client who bought milk, cookies and shampoo, and another who bough cheese, cookies and soap.
I would like to specify that milk and cheese belong to the category "dairy" and shampoo and soap belong to "personal hygene". If there are interesting rules regarding the categories I would like them to be taken into account. Additionally, I would not like to have rules like "milk -> dairy". If one specific object appers in a rule, its corresponding category should not. In this scenario I could have milk and "personal hygene" in the same rule, but not shampoo and "personal hygene".
I have seen this done by other mining tools but I've been having trouble finding a way to make this possible in Analysis Services.
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?
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!
I build a data mining model to predict what are the best studying methods for the student to pass the examinaton.
Create Mining Model StudentAssociation ( Student_No long key, Gender text discrete predict, PassOrFail text discrete predict, StudyMethod table predict ( MethodName text key ) ) Using Microsoft_Association_Rules ( Minimum_Support=0.02, Minimum_Probability=0.03 )
The mining table will contain all the methods that the students use, no matter their examination is passed or failed. The value of PassOrFail will have either 'Pass' or 'Fail'.
According to the above model, can I query the best studying methods? Or I should only train the model with the student who pass the examination, and ignore all the failed.
"If you have a pressing need for this fix, please contact our customer support team."
Yes I have a pressing need for this fix, where I have to contact your customer support team. How do I do that?
You know I only have the evaluation version of SQL Server, but I have to show that this programm is good enough to solve Association Analysis. If I can not show this it wouldn't be bought. Please help me.
I am using Microsoft association algorithm to find the association between PATIENT CITY ---> likely Disease. I like to know how can i import association model after creating from SQL Server BI studio to use in my ASP.NET web form? such a way when the user enters PATIENT CITY, system prompts associated Disease.
I do have Data mining with SQL Server 2005 book, could't find any resource for my objective.
Please suggest best source or tutorial how can i do
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?
I want to filter the itemsets or rules based on more than 2 attributes, how can we achieve that? (I can only filter them by only one attribute?). Is it possible to achieve that?
Thanks a lot and I am looking forward to hearing from you shortly.
i'm new to this forum .. Maybe my way of expression is not very good, but I hope to be understandable.
I've a sql server 2005 database with 90 columns and more or less 185 thousands records. I've to run microsoft associations rules on my laptop (sony vaio sz3, core 2 duo, 2gb ram).
The problems is that the amount of ram seems not to be enough [it starts to swap when it's reading 240th case)
Because of this, i decided to sample my data by extracting 10thousands records randomnly ... it lasts 25minutes (more or less) now, but it's still to much...
Does a better way exists? What's the problem: column or row numbers?
Hi After building a model in BI, I want to view the chart of model in mining model viewer, in the chart tab I can just see one prediction value that means for my model do prediction for some time slice and in prediction steps I can specify how many steps, I want to show this chart In mining model viewer tab we can see the chart of prediction also decision tree and the chart is for showing all of value prediction, and with choosing prediction steps we can specify that show just one value prediction or two or several values. But sometime I can see just one value in chart and sometime I can see several values in chart, This difference is for my data or no? And also for viewing historic prediction I should choice €œshow historic prediction€? and before that I should set Two parameters: Historic_ model _count and historic _model _count, But I can€™t see historic prediction (sometime this happens) Please help me.
Where I am trying to find out the associations between various service activities so that when a customer buys a service activity we can recommend him/her others
Is it possible to use two algorithms together?I need to write prediction Query so that its should both models having clustereing algorithm and timeseries algorithm.
for example
I am having student information.I ve to predict performance of students for certain period.The students should be classified by their types like rich kids,poorkids..like that.I need to predict the performance of the rich kids??
Dear friends, I'm reading Wiley's Data mining with SQL Server 2005... There are MANY things I can't understand about MovieClick example (Chapter 3). I hope someone is going to help me with this troubles...
WARNING (1): I'm a dummy both with sql server and data mining. WARNING (2): My English is not good at all.
Just two questions for now:
1) When I create the model to predict the number of bedrooms for homeowners, the book says to check BEDROOMS as Predictable... question: is it also an INPUT for the model, or PREDICTABLE only?
2) I'd like to keep this model (number of bedrooms.......) and make a prediction query.
- Query builder - select case table -> Homeowners - Drag the Customer ID column from the Homeowners table and drop it on the grid - Drag the BEDROOMS column from the mining model and drop it on the grid. - On the last row: Source=PredictionFunction, Field=PredictProbability - Drag the BEDROOMS column from the mining model and drop it into Criteria/Argument - Add (i.e.) 'Two or Three' to the field Criteria/Argument
I execute the query and I obtain many rows in a table with the following colums: CustomerID, BEDROOMS and Expression: WHAT DOES THIS MEAN? WHICH INFO DO I GET FROM THOSE NUMBERS? WHAT CAN I LEARN FROM THEM?
I am doing this right now this way: 1) I do the DMX prediction query where I get the PredictNodeId(predict_var), my query is like this:
SELECT PredictNodeId(predict_var), model_1.predict_var, t.var_1, t.var_2 FROM model_1 PREDICTION JOIN OPENQUERY([DATA_SOURCE_1], 'SELECT var_1, var_2 FROM table_1') AS t ON model_1.var_1 = t.var_1 AND model_1.var_2 = t.var_2 2)I do the DMX query to get the node_description from the model.content iterating each row from the result of my prediction query, this query is like this:
SELECT node_description FROM model_1.content WHERE node_name = 'node_name_var'
In this query node_name_var = PredictNodeId(predict_var) from my prediction query. What I want to know if there is a way to merge Query 1 and Query 2 so I can get the node_description in the same query qhere I get the PredictNodeId.
Can i use a CASE statement in a prediction query. the following query is throwing me an error
SELECT CASE [Sales Forecast Time Series].[City Code] when 'LA' then 'Los Angeles' WHEN 'CA' THEN 'California' ELSE 'OTHERS' END, PredictTimeSeries([Sales Forecast Time Series].[Sales Value],5) From [Sales Forecast Time Series]
ERROR: Parser: The statement dialect could not be resolved due to ambiguity.
Also
Is it possible to discretize the Sales Value column using a the CASE statement, the output column of PredictTimeSeries function.
Is there a link that can give me a comprehensive info on what can be achieved and what cant be using DMX queries
I need to implement one logic similar to rule engine. Below is the example.how to execute all queries in second table order?? what is the best way to implements this
declare @tblRules AS Table (RuleNo INT, RuleDesc NVARCHAR(500), RuleQuery NVARCHAR(MAX), QueryExecutionInterval NVARCHAR(50)) declare @tblRuleResults AS Table (RuleResultID INT, RuleNo INT, ExecuteTime DateTime, NextExecutionTime DateTime, Result NVARCHAR(10)) INSERT INTO @tblRules VALUES ('1','Fail - 2 times within 1 Hour','XXX','Every 15 Minutes') INSERT INTO @tblRules VALUES ('2','Fail- 2 times within 2 Hour','YYY','Every 30 Minutes')
hi,I am a novice SSAS Programmer.I need a prediction Query in time series algorithm, so that it should predict for a particular date.I dont know how to use where condition in a prediction Query.
