Association Rule Mining, also known as Market Basket Analysis, is a powerful technique used in data mining to discover relationships between items. It is commonly used in shopping applications to identify items that are frequently purchased together by customers. However, the applications of Association Rule Mining extend beyond shopping and can be utilized in various domains such as troubleshooting, medicine, and marketing.

Imagine a scenario where a supermarket chain discovers that customers who buy beer also tend to buy diapers for their kids. By strategically placing the beer and diaper displays close to each other, the supermarket was able to increase sales and improve customer satisfaction. This is just one example of how Association Rule Mining can be used to optimize article procurement and allocation.

In SQL Server, Association Rule Mining can be implemented using the SQL Server Analysis Services (SSAS) data mining project. Let’s take a look at how to use Association Rule Mining in SQL Server.

Setting up the Association Rule Mining Project

To begin, we need to create an SSAS project in SQL Server Data Tools (SSDT) and set up a data source pointing to the relevant database. In this example, we will be using the AdventureWorksDW database.

Next, we need to add the necessary views to the data source view. In this case, we will be using the vAssocSeqOrders and vAssocSeqLineItems views, which contain information about orders and order lines.

Once the views are added, we need to manually join them to establish the relationship between the two tables. This can be done by double-clicking the arrow sign and verifying that the source and destination tables are correctly linked.

Configuring the Association Rule Mining

After setting up the data source view, we can proceed to configure the Association Rule Mining. In this step, we need to choose the case table (vAssocSeqOrders) and the nested table (vAssocSeqLineItems). The objective of Association Rule Mining is to find out which models are selling together, so the product model will be the input as well as the predict attribute.

Once the configuration is completed, the model can be processed, and users can review the prediction model and perform predictions.

Exploring the Association Rule Model

In the Mining Model Viewer, we can explore the data patterns discovered by the Association Rule Mining model. There are three tabs available to view the data patterns: Rules, ItemSets, and Dependency Network.

The Rules tab displays the qualified Association Rule Minings along with their probabilities and importance scores. The importance score indicates the usefulness of the rule, with higher scores indicating higher quality rules.

The ItemSets table displays the frequent itemsets discovered from the Association Rule algorithm. Users can set the minimum support and select the minimum item set to refine the results.

The Dependency Network tab graphically illustrates the relationship between the itemsets. Users can click on nodes to highlight their relations with other nodes in the diagram.

Setting Model Parameters

To improve the performance and accuracy of the Association Rule Mining, various model parameters can be set. These parameters include:

• MAXIMUM_ITEMSET_COUNT: Defines the number of predications to be generated.
• MAXIMUM_ITEMSET_SIZE: Defines the maximum number of itemsets.
• MAXIMUM_SUPPORT: Defines the maximum support threshold of a frequent itemset.
• MINIMUM_IMPORTANCE: Filters out rules with importance scores below the defined threshold.
• MINIMUM_ITEMSET_SIZE: Defines the minimum number of itemsets.
• MINIMUM_PROBABILITY: Specifies the minimum probability that a rule is true.
• MINIMUM_SUPPORT: Specifies the minimum number of cases that must contain the itemset before generating a rule.

Prediction in Association Rule Mining

Prediction is an important aspect of any data mining algorithm. In Association Rule Mining, predictions can be made to determine the items that will be bought by customers who have purchased specific items in the past.

Conclusion

Association Rule Mining is a valuable technique in SQL Server for discovering relationships between items and optimizing article procurement and allocation. By leveraging the power of data mining, businesses can gain insights into customer behavior and make informed decisions to improve sales and customer satisfaction.

Stay tuned for more articles in our SQL Server data mining series, where we will explore other techniques such as clustering, linear regression, and text mining.