Use Association Rules of SQL Server Analysis Services data mining to implement commodity recommendation

If you have a shopping website, how do you recommend products to your customers? This function is available on many e-commerce websites. You can easily build similar functions through the data mining feature of SQL Server Analysis Services.

It is divided into three parts to demonstrate how to implement this function.

1. Build a Mining Model

2. Compile service interfaces for the Mining Model

3. Develop simple front-end applications

4. Use EXCEL to analyze related products

5. Organize data and use Excel to analyze related products

6. Use DMX to create a Data Mining Model

 

The first section describes how to use SQL Server Analysis Services to build a simple Mining Model Based on this problem.

Data Mining is simply a process of discovering information from massive data. If business intelligence is divided into three levels: Tell you what happened, why it happened, and what will happen in the future. Data Mining is definitely the highest level in business intelligence, telling you what will happen in the future, that is, prediction. The foundation of prediction is to tell you the trend of a certain attribute of a new data based on the massive historical data, combined with certain algorithms, and based on probability.

Many data mining models, such as Bayesian, time series, and association rules, are common models. Different model algorithms can be applied based on different problem features. For example, the product recommendation mentioned in this article is typically suitable for solving with association rules. The typical beer and diapers problems in data mining are basically based on this method.

 

Through the first article, you can basically master how to build a Data Mining Model Based on association rules.

 

 

 

The second article describes how to build service interfaces based on the previous article that has built a mining model.

There are many methods to write service interfaces, such as using WCF. For ease of use, ASP. NET web services is directly used here.

Write the implementation part of the specific query of the Mining Model to the Web Service Code, mainly considering the code layering to reduce coupling. In addition, this part of logic exposes interfaces through services, which can be called by any other platform, such as ASP. NET, winform, WPF, or other Java-based technical frameworks. XML is the most important part of Web services. A service interface is often a URL address. The client program sends an XML request to this address to obtain the server code response, the result is returned in XML format. So since XML data is used for communication back and forth, does this mean that we need to manually write code to serialize and deserialize XML data? This involves the concept of soap, a concept that was very popular 10 years ago, Simple Object Access Protocol. Through this SOAP protocol, the client can know what XML data should be sent to access a web service, and how to parse the data locally after receiving the XML data from the server. In Visual Studio, When you reference a service, it will generate several proxy classes locally based on this protocol, in this way, when you call a remote web service, you will feel as convenient as calling a local class method.

 

Through the second article, you can master the basic methods for data mining and query, and how to use the data mining model.

 

 

 

The third article describes how to build a simple front-end application for product recommendation based on the mining model and service layer.

Because we use the web service method to encapsulate the query of the Mining Model, theoretically this service can be accessed by many client applications, such as ASP. net, winform, and WPF. Here we select Silverlight 5.

Silverlight 5 is similar to flash and is an RIA solution on the Microsoft platform. On this platform, we can use XAML to develop brilliant applications.

Although its glory has been quickly covered by HTML5, and Microsoft has stopped its subsequent version updates and made full efforts to invest in HTML5, it is still early to develop HTML5, silverlight is still attractive and advantageous, as well as powerful development tool support.

Of course, if your project is ASP. NET or another type of project, you can also refer to this article for operations. In fact, this article is of little significance to an experienced front-end developer, because it mainly describes how to call a web service. But as a series, considering that each person's focus is different, in order to make an overall introduction, we will also detail each step of the operation here.

 

Through the third article, you can see how to use a client program to access the service interface and perform mining, prediction, and query to implement the product recommendation function.

 

 

 

The fourth article is independent of the first three articles. It mainly introduces how to use Excel data mining to independently implement the commodity recommendation function. It will be demonstrated through the sample data provided by Microsoft.

Excel is a familiar tool, and it is one of the components in Microsoft Office. In the Baidu encyclopedia, you can find the following description:

Excel is an important part of Microsoft Office software. It can process, analyze, and assist in decision-making operations on various data, it is widely used in management, statistics, finance, and other fields.

Data processing and statistical analysis are what we often use Excel to do, while assisting decision-making corresponds to the highest level in business intelligence, that is, the Excel Data Mining function provided by Microsoft.

For data mining in Excel, you need to install a plug-in: SQL server data mining tools add-ins for office.

 

Through the fourth article, you can see how to use Excel to implement the product recommendation function.

 

 

The fifth article undertakes the previous article, which mainly describes how to organize data and use Excel data mining to independently implement the product recommendation function.

In the previous article, we demonstrated how to use the sample data in Data Mining for add-ins provided by Microsoft to implement the function of mining, prediction, and analysis, this article uses adventure works to demonstrate how to organize data for mining and prediction in Excel.

The examples of adventure works provided by Microsoft are divided into OLTP and OLAP databases. The former records business data and the business data in a system is usually operated at this layer, the business data layer enters the Data Warehouse through ETL cleaning, loading, and conversion for the analysis system to perform statistical analysis.

For how to obtain and install these sample libraries, refer to my article.

 

Through the fifth article, you can learn how to organize sample data for data mining and import it in Excel.

 

 

The sixth article is an independent article. It mainly demonstrates how to create a mining model through DMX.

In the previous articles, we briefly introduced how to establish a mining model and compile a program to use a simple DMX query Mining Model to implement prediction. Then we briefly introduced how to perform independent Prediction and Analysis in Excel. This article briefly introduces how to use DMX statements to create mining structures and models, train models, and perform prediction and query.

Many query languages are involved in the Analysis Service. First, we are most familiar with the SQL language. In the analysis service, you can use SQL to create partitions for multi-dimensional datasets, or specify training data or prediction data in data mining. Secondly, MDX can be used to query multi-dimensional datasets. Next, we will introduce the DMX, which is mainly used for data mining in the Analysis Service. It can be used to establish mining structures and mining models, as well as training data and prediction queries.

There are still many books in Microsoft documents and examples. The DMX method is usually divided into three steps: 1. create a mining structure 2. create a mining model 3. training data 4. prediction Query

 

Through the sixth article, you can understand how to organize sample data for data mining and import it in Excel.

 

 

 

I hope this small series will help you in the project.

 

 

Download article-related files:

Download an analytic Service Project

Analytic Service Database Backup download

Download services and frontend programs

 

 

Resources used:

How to get the official Sample Library and deploy them

Microsoft official data mining tutorial

 

 

Download related plug-ins:

Microsoft SQL Server 2012 data mining add-ins for Microsoft Office 2010