Use Association Rules of SQL Server Analysis Services data mining to implement commodity recommendation function (2)

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

 

 

 

This 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.

Note: If you use winform and ASP. you can also directly access the data of the Mining Model in the code, but if it is another platform, such as win 8 app, Silverlight, and Flash, they cannot communicate directly with the data source, so they can only be accessed through the service method.

This article is to compile such a service interface, which accepts the list of items purchased by the customer, and then recommends other items to the user based on the information.

The core logic of the service is to dynamically spell DMX statements based on the sent commodity list. The DMX statement mentioned in the previous article mainly uses it as a "template ".

Select flattened

Predictassociation ([Association]. [V assoc seq line items], include_statistics, 3)

From

[Association]

Natural prediction join

(

Select (select 'touring tire' as [model]

Union

Select 'touring Tire Tube 'as [model]

) As [V assoc seq line items]) as t

According to the previous article, we know that the part that needs to be changed is the select Union select section. A product corresponds to a select.

Another thing you need to introduce is adomd. net. You may feel familiar with this name. Yes, you must have used ADO. net. Do you still remember the steps for operating the database in the Classic mode? Create a connection, command, and then datareader or other methods to receive data. Adomd. net is used to access the Analysis Service, because it is used with ADO.. NET is integrated with the same interface, so you can find the familiar connection and command objects, while the receiving objects include datareader and cellset objects corresponding to dataset.

Traditional analysis services include multidimensional models and data mining. Therefore, in adomd. net, MDX and DMX can be discarded ). The data access interface we use here is adomd. net.

 

After confirming the above logic, we start to build Web Services.

Here we use Visual Studio 2012 to write services. Different from the previous article, the previous article used SQL server data tools to compile an Analysis Service Project of SQL Server 2012, which is actually a shell of Visual Studio 2010. Why is the version used in that place 2010 instead of 2012? There is a ing between SQL Server and Visual Studio.

SQL Server version	Corresponding Visual Studio shell version
2000	None
2005	2005
2008	2008
2012	2010
Next version 2012	2012

 

It can be seen that it is mainly related to the release of the two product series. Therefore, you must note that if Visual Studio 2012 and SQL Server 2012 are installed on your machine at the same time, when you open an analysis service project similar to the one created in the previous article, do not double-click the project file by default, because the system uses Visual Studio 2012 (not the corresponding 2010) to open your project, and then prompt you to upgrade the project to 2012. You don't have to think about it. the SQL Server version corresponding to Visual Studio 2012 shell has not yet been released, so even after the upgrade, it must fail.

In the following example, open Visual Studio 2012. On the menu bar, choose File> new project.

Select ASP. NET empty web application as the project type.

Click OK to create a new ASP. NET project.

In Solution Explorer, right-click the project name and click Add-> new item.

In add new item, select Web service. Click Add.

Then we can see the service code automatically generated by the system for testing.

Add a reference to adomd. net. Right-click the project directory and choose add reference.

The adomd. NET component does not appear here by default. You need to click browse to manually find it.

Usually the position of the adomd. NET component is located in this directory:

C: \ Program Files \ Microsoft. NET \ adomd. Net \ 110

Find Microsoft. analysisservices. adomdclient. dll and click OK.

You can see that the adomd. NET component has been added to the reference directory.

Return to the Web Service Code interface and reference the using library in the code.

Then write code for the service. First define a class, and the service result will be returned in the structure of this class.

Public class modelresult

{

Public String modelname {Get; set ;}

Public double support {Get; set ;}

Public double probility {Get; set ;}

}

After defining this class, write the service code, and paste it here:

[Webmethod]

Public list <modelresult> getmodelresult (list <string> modellist)

{

List <modelresult> result = new list <modelresult> ();

 

Bool firstone = true;

 

// Build DMX Query

Stringbuilder sb = new stringbuilder ();

SB. appendline ("select flattened ");

SB. appendline ("predictassociation ([Association]. [V assoc seq line items], include_statistics, 3 )");

SB. appendline ("from ");

SB. appendline ("[Association]");

SB. appendline ("natural prediction join ");

SB. appendline ("(");

 

Foreach (string item in modellist)

{

If (firstone = true)

{

SB. appendline ("select (select '" + item + "' as [model]");

Firstone = false;

}

Else

{

SB. appendline ("union ");

SB. appendline ("select" + item + "'as [model]");

}

}

 

 

SB. appendline (") as [V assoc seq line items]) as t ");

 

Adomdconnection conn = new adomdconnection ();

Conn. connectionstring = "provider = msolap.3; Integrated Security = sspi; persist Security info = true; initial catalog = multidimensionalproject1; Data Source = .";

Conn. open ();

 

Adomdcommand comm = new adomdcommand (sb. tostring (), Conn );

 

Adomddatareader DR = comm. executereader ();

 

// Read result

While (dr. Read ())

{

Modelresult mr = new modelresult ();

 

Mr. modelname = Dr [0]. tostring ();

Mr. Support = convert. todouble (Dr [1]);

Mr. probility = convert. todouble (Dr [2]);

 

Result. Add (MR );

}

 

Conn. Close ();

 

Return result;

}

Pay attention to the first line of this Code. There is a [webmethod]. The difference between the Web service method and the traditional method of a class is here, only when this mark is set before the method is used will the system regard it as a Web service method release.

The code first queries the "template" based on the DMX statement mentioned in the previous article, and dynamically constructs the select Union Select part through a foreach loop. Finally, read the results through the connection and command objects and the datareader method, and load them into the class.

Then, to facilitate the test, add a test service method:

[Webmethod]

Public list <modelresult> getmodelresulttest (string modelsingle)

{

List <string> modellist = new list <string> ();

Modellist. Add (modelsingle );

Return getmodelresult (modellist );

}

This method actually calls the service method just created, but it passes only one item. Here it is packaged into a list and then the method is discarded.

In this way, we can test the method we just compiled on the service browser interface provided by ASP. NET. Right-click the asmx file and choose View in browser.

Then you can see the simple test page automatically created for the service.

Click the getmodelresulttest method.

Enter 'touring tire' In the parameter value and click "call ".

Finally, you can see the results returned by the Service. The information format is XML. You do not need to parse it. Through the SOAP protocol mentioned above, the system will create a local proxy class for you.

 

 

Now, the service-Layer Code for the mining model has been compiled. The next article will create an application to call this service to implement a simple commodity recommendation function.