Performing Association Data mining on IT Service requests continued ...

Other forms of associations including sequential associations can also be performed but the association rules are a sort of recommendation and something that is lateral and helpful to the users in their single-minded case request. User generally don’t have access to past request and mitigations to see the help that others received for the cases that are like theirs. Cases opened with IT contain internal and confidential information and number hundreds if not thousands. A public facing summary of request descriptions and resolutions database or knowledge-based articles are helpful too, but they are usually time-consuming and secondary to the case deluge that IT teams are faced with. Building an association rule set and evaluating it with the incoming request on the other hand requires less effort and time. 

Sample query to see the association set for problem type: 

SELECT TOP 10 (Node_Support),Node_Name, Node_Caption   

FROM Association 

WHERE Node_Type = 7 

To perform this query, we will need a one-hot encoding. We rearrange the data with each problem/product category one hot encoded and one transaction/service request per row. One hot encoding refers to data transformation technique where categorical values are converted into columns. If the categorical value is present, the column is given a value of 1. This is just like pivoting and the columns expand from the original set by a number equal to the number of categories. 

Support can be determined for any category based on 

SELECT COUNT(*) / (SELECT COUNT(*) FROM ONE_HOT_ENCODED_Transactions) as SUPPORT FROM ONE_HOT_ENCODED_Transactions where category_A = 1; 

Each of the metrics described above can be calculated with sample SQL queries as shown below 

SELECT COUNT(*) as Support_x  

FROM Transactions  

WHERE Product = ‘x’; 

 

SELECT Support_x _y/Support_x as Confidence_x 

FROM Transactions 

WHERE Product = ‘x’; 

 

SELECT Confidence_x / Support_y as Lift_y_x 

FROM Transactions 

WHERE Product = ‘x’; 

 

SELECT A.name, B.name into associations  

FROM Products as A  

CROSS JOIN Products as B 

WHERE A.name != B.name  

Evaluating the three metrics for each of the association results in an Association.content table where product pairs have support, confidence and lift. Then the associations can be filtered to have a lift > 1.0