PerformancePoint Blog

Over the past couple of weeks I’ve shared my thoughts on using Tableau to visualize Microsoft’s Time Series and Clustering algorithms. Today, I’ll finish off with Association Rules.

The Association Rules algorithm focuses on itemsets, which you can think of as a bundle of “things” (attributes) which frequently go together. For example, a popular itemset might be {Peanut Butter, Jelly, Bread}. Itemsets don’t have to be related to shopping, however. A perfectly valid itemset might be {Red Sox Fan, Cretin, Convict}: We’re just creating groups of attributes which go together.

Given many itemsets, the Association Rules algorithm discovers most frequently occurring ones. Then, it creates association rules using those frequently occurring itemsets.  Here’s what a rule looks like:

{Peanut Butter = Existing , Jelly = Existing => Bread = Existing}

If we see Peanut Butter and Jelly in someone’s shopping cart, they are likely to buy Bread, as well.

The concepts of support, probability, and importance are used to tell us how many times we see an item or itemset, the confidence we have in the rule, and the “lift” we might expect for a rule.

If you’d like to understand more about these concepts, read this thread by Jamie Maclennan.

Here’s a basic query to return information from our data mining model:

SELECT * FROM OpenQuery (SSAS,’SELECT NODE_CAPTION as [Rule], NODE_SUPPORT AS [Support], MSOLAP_NODE_SCORE AS [Importance],

NODE_PROBABILITY AS [Probability] from

[Association].content WHERE NODE_TYPE=8′)

Executing this puppy will return all our rules and their support,  probability & importance values. We’re looking for rules with relatively high importance (> 0) which indicate that the existence of peanut butter and jelly will impact bread buying positively:

If you open the image above, you’ll see that each rule looks pretty much like a “formula”. To make these formulae useful, we’re going to need to parse them into individual fields that be used as labels on an axis. To do that, we write lots of kludgy string parsing code:

SELECT

    [Rule],

    — Cast NTEXT to nvarchar then determine number of items in itemset by keying on number of = symbols before the -> characters

    — Divide by 2 since DATALENGTH against NVARCHAR will be twice the string length

    (DATALENGTH(LEFT(CAST([rule] as nvarchar(300)),CHARINDEX(‘->’,cast([RULE] as nvarchar(300)))))- DATALENGTH (REPLACE(LEFT(CAST([rule] as NVARCHAR(300)),CHARINDEX(‘->’,[RULE])),’=',”)))/2   as [ItemSet Size],

    [Support],

    [Importance],

    [Probability],

    — Grab first item in itemset

    LEFT(CAST([rule] as nvarchar(300)), CHARINDEX(‘=’, CAST([rule] as nvarchar(300))) -1) as [Basket Item 1],

    — If two items exist in itemset, grab second using kludgy string parsing

    CASE (DATALENGTH(LEFT(CAST([rule] as nvarchar(300)),CHARINDEX(‘->’,cast([RULE] as nvarchar(300)))))- DATALENGTH (REPLACE(LEFT(CAST([rule] as NVARCHAR(300)),CHARINDEX(‘->’,[RULE])),’=',”)))/2

        WHEN 2 Then SUBSTRING(CAST([rule] as NVARCHAR(300)),CHARINDEX(‘,’,CAST([rule] as nvarchar(300)))+2, ((CHARINDEX(‘->’,cast([RULE] as nvarchar(300)))-12) – (CHARINDEX(‘,’,CAST([rule] as nvarchar(300)))+2) ))

    END as [Basket Item 2],

    — Recommended Item

    REPLACE(RIGHT(CAST([rule] as NVARCHAR(300)), (DATALENGTH(CAST([rule] as NVARCHAR(300)))/2) – (CHARINDEX(‘->’,cast([RULE] as nvarchar(300)))+2)),’ = Existing’,”) as [Recommended Item]

FROM

OpenQUery (SSAS,’SELECT NODE_CAPTION as [Rule], NODE_SUPPORT AS [Support], MSOLAP_NODE_SCORE AS [Importance],

NODE_PROBABILITY AS [Probability] from

[Association].content WHERE NODE_TYPE=8′)

Writing the SQL above was by far the ugliest bit. On to the results:

The resultset above adds “Basket Item 1”, “Basket Item 2”, and “Recommended Item” fields by parsing the the rule column.

Now for the fun stuff. First, let’s look at our rules by Importance and Probability:

Next, lets look at the actual products which are being recommended:

Support is being used you determine our bubble size, so we can see below that Mountain Bottle cages are recommended by 5 different rules:

By highlighting a specific item (product), we can see that while we generated lots of rules which recommend it, those rules aren’t very important – I’d probably ignore these.

Finally, let’s see which items  tend to go together in the same basket. Here we can see that the Mountain Bottle Cage gets grouped with a lot of other items.