I’ve been asked this question a few times and never thought of an answer until I was asked again today on the data mining newsgroups. We’ll be posting a tip or trick on sqlserverdatamining.com on this, but since it’s fresh in my mind I’ll let everyone have it now.
The problem that some people are having is the behavior of associative prediction returning popular results if the algorithm doesn’t actually predict anything. Many seem to want only results returned by predictions (we thought that if you asked for 5 you wanted 5 – who knew?). In any case you can check the node id to see if the prediction was based on a rule – if there’s no node id, no rule – you can drop it.
A sample query using this technique looks like this:
(SELECT * FROM PredictAssociation([MovieModel].[Movies],5,
NATURAL PREDICTION JOIN
(SELECT (SELECT ‘What”s New, Pussycat’ AS [Movie]
UNION SELECT ’28 Days’ AS [Movie]) AS [Movies]) AS t
Another interesting flag to try out for PredictAssociation is INCLUDE_STATISTICS, so you can determine the probability and support for each suggestion.