KB38431: How to create a market basket report based on association analysis and use Item set in MicroStrategy Developer

Introduction:
Association rules look for relationships between items. The most common example of this is market basket analysis. Market basket analysis studies retail purchases to determine which items tend to appear together in individual transactions.
The key to this type of analysis is the ability to find associations amongst the items in each transaction. This can include associations such as which items appear together the most frequently, and which items tend to increase the likelihood that other items will appear in the same transaction.
 
Refer to following technical note to create Market Basket Analysis report without item set:
KB4485   How to create a Market Basket Analysis - Affinity Report in the Strategy Product Suite
 
Background:
There are business cases that itemset needs to be included in the Market Basket Analysis report. Following diagrams shows and explains an example of such report.
Support is a key concept that describes the relative frequency of transactions that contain an item or set of items, called an itemset. Itemset is another key concept in association rules since you can calculate associations not only for individual items but also between groups of items.
 
Sample:
For example, five transactions from a grocery store are summarized in the table below:
1 denotes that the item is included in the transaction, while a 0 denotes that the item is not included in the transaction.
 

 
The table below shows the support for all possible combinations of one, two or three items per itemset (in other words, a maximum of three items per itemset).
 

 
How to achieve a similar report in Tutorial:

• Order is used to substitute Transaction; metric Percentage is used to substitute Support.
• With Consolidation only can’t get the desired data, as expression like “{Item=Item1} +{Item=Item2}” can only get Orders that contains Item1 or Item2, but can’t get the Orders that contain Item1 and Item2 at the same time.
• To get the desired data, relationship filter and custom group have to be used.

Follow steps below to achieve a Market Basket analysis with item set in Tutorial project:
 
     1.    Create a list of relationship filters for each of the items, the expression is like “Set of Order where (Item=Item1) Relate by the Table ORDER_DETAIL” as shown below:
 

 
     2.    Create a Custom Group called “CG_Item sets”, each of the custom group element is an item or item set. For example, Item1 with the relationship filter RF_Item1, (Item1, Item2) with relationship RF_Item1 AND RF_Item2; (Item1, Item2, Item3) with relationship filter RF_Item1 AND RF_Item2 AND RF_Item3 as shown below:
 

 
     3.    Create a metric called “Order Count” with expression “Count<Distinct=True, FactID=Discount>(Order){~+}” as shown below:
 

 
     4.    Create a metric “Total Order” with expression “Count<Distinct=True, FactID=Discount>(Order){!Order%}”, the level is Order with filtering ignore and grouping None as shown below:
 

 
     5.    To get the percentage(The Support key), Create a smart metric “Percentage” with expression “/” as shown below:
 

 
     6.    Create a report, with custom group “CG_Item sets” in row and metric “Order Count”, “Total Order”, “Percentage” in the column as shown below:
 

 
 
 
 
KB38431