How to Filter a Rule via DMX

Question

Hello All!

I'm working with the Association Rules feature and now I've run into another dead end. 

I know you can use a customer's history to make recommendations, but is there anyway to limit the rules that can be used based on what item is implied?  I know in the "Rules" Tab of the "Model Viewer" you can write in some .NET syntax to restrict the rules, but I can't find any way to do that through SSMS with DMX. 

Thank you in adavnce!

Michael

---

Michael Van Wyke

Answer 1

In association Model,

Node_Type =7 indicates item sets And Node_type = 8 indicates rules.

So, if you just want to use rules based on certain Items, using ADW lets say if you  want to restrict rules based on item "Water", you can write:

Select

Node_Description, Node_support from [yourAsscModel].

content

where

node_type= 8 and VBA!LEFT(Node_description,5)= 'Water'

hth,

Rok

Answer 2

Thank you for that suggestion!  However, this seems very cumbersome if I wanted to select rules based out of a list of items.  Is there a more dynamic way to filter the rules?

 

Thank you!

Michael

---

Michael Van Wyke

Answer 3

Well, it depends upon your criteria for selecting rules out of list of items, most likely you'll be looking at statistically. You can select only those rules that happen more often and its probability, you can write something like this. Where you can see the combination of items and probablity of it happening more often based on support cases.

select

Node_description,Node_probabiltiy * 100 as [Probability],node_support

from

[yourAsscModel].

content

where

node_support>50

order

by node_probability

desc

Answer 4

That's a very good point, and I do think that ultimately the selection should be based on the probability of the rule occurring, but I am hoping to even cull the possible list down before recommendations are made.  The basic idea is I wouldn't want to make a recommendation for something that might not be in stock, even if it has strong probability.  Does that make sense?

---

Michael Van Wyke

Answer 5

I understand your requirement.

You can easily

write a prediction query based on what you have in stock with something like this: 

Select

Flattened

(

select

[model] from

Predict

([yourAsscModel],2))

from

prediction

join

openquery

([YourDataSource],'select Item from dbo.InStockTable') AS S

on

S.Item1 = [yourAsscModel].[Item]

 

----------------------------------------------

Your solution should

work as you've defined above, if you can a write a prediction query based on nested subquery on model.content query as I've mentioned previously! But I'm not sure you can write a Prediction query on content query, sounds possible, but I didn't come accross any examples online. Maybe if you/I research more we'll find something.

select

Node_description,Node_probabiltiy * 100 as [Probability],node_support

from [yourAsscModel]. content where node_support>50

order by node_probability

-------------------------------

So, your end query should "Roughly" look something like this if it's possible. Maybe a Moderator

from DM Team can help us on this, or provide a better solution to the problem??? 

Select

Flattened

(

select

[model] from

Predict

([yourAsscModel],2))

from

 

---------------------- 

select

Node_description,Node_probabiltiy * 100 as [Probability],node_support

from

[yourAsscModel].

content where node_support>50

 

-----------------------

 

prediction Join

 

openquery([YourDataSource],'select Item from dbo.InStockTable') AS S

on

S.Item1 = [yourAsscModel].[Item]

 

hth in someway :)

Rok

Answer 6

Thank you for all your help, Rok!

I'm trying to work on your suggestions, but as I understand the query, it looks like what it is trying to do is make 2 suggestions based on what items are in stock, but do not take into account what the person has bought. 

I haven't tried this yet but will soon, but just in case someone has already got some insight to this, I was wondering if you can make several Prediction joins, such that I could do basically

SELECT FLATTENED CUSTOMERID PREDICT([ASSOC.MODEL].[TABLE],2) FROM [ASSOC.MODEL]

PREDICTION JOIN ([QUERY ON AVAILABLE ITEMS]) AS S

PREDICTION JOIN ([QUERY ON CUSTOMER HISTORY]) AS T

WHERE S.ITEMS=[ASSOCMODEL].ITEMS AND

T.ITEMS=[ASSOCMODEL].ITEMS AND

T.CUSTOMERID=[ASSOCMODEL].CUSTOMER ID

 

My concern with the query you suggested is that it will treat the list of in-stock items as the items the model can use for prediction and recommendation, when in reality I would like to use their entire shopping history to make their recommendations altered by only what is in-stock at that time.

 

But again, you gave me a lot of great ideas and I really appreciate all of your help!  Thank you!

-Michael

---

Michael Van Wyke

Answer 7

Michael,

NP. You shared me several of your ideas as well.  

I hope a moderator from DM Team can help me understand 2 questions

1. Can we write multiple prediction joins as Michael mentioned above.

2. Can we write prediction queries on content queries? In a way to restrict rules...

 

thanks,

Rok

Answer 8

Michael,

Can you please explain again what problem you are trying to solve. Are you trying to perform shopping basket analysis and want to get recommentation for other items customer might be interested in but you want recommendations to be limited to what you have in stock?

---

Tatyana Yakushev [PredixionSoftware.com]

Answer 9

I am not sure I understand perfectly your scenario, so I will try to answer the questions individually.

First, to Michael's point: "I wouldn't want to make a recommendation for something that might not be in stock, even if it has strong probability. ". I assume you want to do this recommendation in real time. How about adding AS as a linked server? A stored procedure could model your DMX query on the fly and you can send it to AS. The result can be joined (using SQL's primitives) against your stock table and then only the products that are currently available will be recommended. This article contains more details on how to issue AS queries from SQL - http://www.sqlserverdatamining.com/ssdm/Default.aspx?tabid=102&Id=15. Please drop a note if you find this solution interesting and need more details

Rok - the second question is a bit easier - you can restrict rules during prediction (or, in general, patterns), within certain limits, during prediction. The solution for this is to write a C# stored procedure (actually, a function) in an AS extension DLL, deploy it on the server, and use the AS server side object model to look at the pattern used in prediction. You would then invoke the function like this

SELECT MyValidationFunction(PredictNodeId([TargetColumn])) AS ValidationFilter FROM <Model> PJ ...

WHERE ValidationFilter = TRUE

An example of this is available here: http://social.msdn.microsoft.com/Forums/en/sqldatamining/thread/cb66d9aa-86d3-4156-b0be-f262c155bfeb
Details on PredictNodeId are here: http://www.sqlserverdatamining.com/ssdm/Default.aspx?tabid=102&Id=59 . In the case of Association Rules, though, the Node Id will be empty unless the prediction is based on a rule.

Regarding the first question -- in Michael's example, joining with the historic data is, indeed, a prediction join. The historic data is used as argument for predictions. Joining with available data, though, is not actually a prediction join, but a standard join, an operation which is not directly supported by DMX. You can do multiple prediction join queries, such as applying multiple models to your input, but the scenario that Michael describes requires an inner join (or at least this is how I understand it), not supported in DMX. 

 

Hope this helps!

---

bogdan crivat / http://www.bogdancrivat.net/dm

Answer 10

Thank you both for replying, Bogdan and Tatyana!

Let me restate the scenario that I am dealing with.

I have a table of customers and the items that they have purchased which I'll call [dbo.History].  I built [AssociationModel] based on a sample from [dbo.History].  I also have a table that has items in stock at the start of the coming week, [dbo.InStock].  So the ultimate problem that I am trying to solve is the following.  I want to make "k" recommendations to all the customers where the recommendations are impacted by their purchase history in [dbo.History].  Thanks to help I received from Bogdan earlier, I am able to do this and save these predictions in a SQL table: [dbo.Prediction].

However, right now these offers might include recommendations that are not available in [dbo.InStock].  Currently I can work around this by just recommending all customers get some number of recommendations larger than "k" and then joinging [dbo.Prediciton] with [dbo.InStock] and hoping that I've recommended enough items so that everyone has at least "k" items.  However that is not a very efficient or practical solution.  What I am hoping to do is to first limit the available Rules in [AssociationModel] so that the RHS items all exist in [dbo.InStock].  I would like to then apply this modified structure to the customers and their purchase history to create "k" recommendations and insert them into [dbo.Predicion]. 

I am looking over that link you provided about "Executing Predictions from the SQL Server Relational Engine" and trying to wrap my head around it, but I am still not sure if this quite resolves my scenario. 

Thank you all again for your help!  I really appreciate it!

-Michael

---

Michael Van Wyke

Answer 11

Bogdan,

I understood the #2 question from your reply= Creating c# function and using it through AS assemblies.

Regarding #1 question,what did you mean by "standard Join"? Let's say there's an online bookstore. For registered(returning) customers, it recommends other products based on their purchase history. --so we're doing a prediction join.

If a totally new customer buys a book, we can make a recommendation based on other customers who bought similar (category) books.--so we're still doing prediction join.

In what scenario, we would do standard join? I'm little confused.

I appreciate your help as always!

thanks,

Rok

p.s : Michael sorry to sway away a little from your initial thread.

 

Answer 12

Rok1 wrote:

p.s : Michael sorry to sway away a little from your initial thread.

 

Not a problem!!! :)

---

Michael Van Wyke

Answer 13

Rok,

By standard I mean the relational inner join, which consist in matching records based on shared property values.

So, if a new customer buys a book, we do a prediction join. The algorithms don't necessarily match the new customer with other customers based on the book. It may recommend certain books that never appeared together with the input book (the most popular books, if no rule triggers).

Now, if I understoof correctly Michael's problem, an inner join is needed to match the recommendations against the availability table (perhaps an inner join against an OPENQUERY statement) and DMX does not support inner joins.

 

Hope this helps!

bogdan

---

bogdan crivat / http://www.bogdancrivat.net/dm

Answer 14

Bogdan,

that clears my confusion.

 

Thanks for your help!

Rok