Commodity recommendation using association rules of SQL Server Analysis Services data mining (6)

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.

 

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 generally divided into three steps:

1. Create a mining structure
2. Create a Mining Model
3. Training data
4. Prediction Query

Before performing the preceding operations on an existing Analysis Service database, you must manually create an Analysis Service database and manually establish a data source connection. in SQL, we know that the database is created, however, it is not allowed under the analysis service instance (only through xmla), so you need to manually create it here. Specific Method:

First, open SQL Server Management studio. Connect to the Analysis Service.

Right-click the databases node and choose new database.

Name the database dmtest, and select the specified user name and password for identity simulation. In this example, we recommend that you enter the administrator password for the login machine. Of course, this is not recommended in the actual production environment. You need to assign the specified user here according to the specific permissions.

After the database is created, you can view the database you just created in the Object Browser.

Next, open SQL data tools and click File> open> Analysis Services database...

This function enables a running analytics Service database.

Enter the local localhost or a "point" on the server, and select the SSAS database dmtest created under the analysis service instance.

Click OK. The structure of the analytic Service database is loaded into the project. It should be noted that, unlike offline open, this method is to open online. Any modifications made here are directly synchronized to the server after being saved.

The following steps are similar to the first step.

Create a data source connection.

Specifies the simulation information.

Name the data source dsaw.

You can see the new data source connection in the solution browser.

The above SSAs database data source has been connected to the home. It should be noted that, unlike the previous one, you only need to create a data source connection and do not need to create a data source view.

Disable SQL data tools and return to management studio. Select the DMX icon in the toolbar to create a DMX query.

We can see that the DMX query interface is similar to that of MDX.

Next, enter the following statement. This statement is used to create a mining structure.

Create mining structure [Market Basket] (

Ordernumber text key,

[Products] Table (

[Model] Text key

)

)

After running, you can see the newly created mining structure.

Then, add a Mining Model for the mining structure. Each model must specify a mining algorithm. For example, the association rules are used for commodity recommendation. When creating a mining model, you also need to use predict to specify the elements to be predicted.

Alter mining structure [Market Basket]

Add Mining Model [Default Association]

(

Ordernumber,

[Products] predict (

[Model]

)

)

Using microsoft_association_rules

Pay attention to the part after using. Here we specify that the model is an association rule model.

After running, you can create a model in the mining structure.

The mining model is located under the mining structure. The same mining structure can have different mining models. The benefit of having multiple mining models is that for the same problem, different mining models can be applied to evaluate which mining model has the most accurate prediction.

The next step is to train the model and use the insert statement. Because orders and order items are nested, pay attention to the shape and append of the following statement, and the relate in the statement to associate the case table with the nested table:

Insert into mining structure [Market Basket]

(

[Ordernumber], [products] (Skip, [model])

)

Shape {

Openquery ([dsaw], 'select ordernumber

From vassocseqorders order by ordernumber ')}

Append

(

{Openquery ([dsaw], 'select ordernumber, model from

DBO. vassocseqlineitems order by ordernumber, model ')

}

Relate ordernumber to ordernumber

) As [products]

After you click "run", the system will mine data based on the specified training data processing structure. The processing duration is usually determined by the sample data size and algorithm parameters.

After training, right-click the mining structure to view the created mining model.

Under the Rules tab, you can see the number of rules generated by the system.

You can view the data item set in itemsets.

As well as dependency networks, this is a very intuitive tool, the association between items can be seen intuitively, and the degree of association between items can be viewed through the slider on the left.

As shown in the preceding figure, the data mining structure is created. You can see that the structure is the same as that described in the first article through the tool wizard. That is to say, you can create various objects in the Data Engine without passing through the graphic interface.

So far, those who are familiar with SQL statements may feel more friendly when looking at DMX. In addition to a few keywords and parameters related to algorithm models, the basic structure of DMX seems to have been familiar.

The example DMX used in this article is used to query all the tutorials from Microsoft. For more details about DMX, refer to the tutorial address:

Http://technet.microsoft.com/zh-cn/library/bb895168 (V = SQL .100). aspx

For more details about the DMX statements mentioned in this article, you can refer to the above address.

This DMX tutorial includes bicycle buyers, shopping baskets, and timing forecasting. Although not all mining algorithms are covered, it is sufficient for your reference.

For more information about how to apply data mining to projects, see the second and third articles in this series.

For more information about data mining algorithms, see the following link:

Http://technet.microsoft.com/zh-cn/library/ms175595 (V = SQL .100). aspx

DMX reference:

Http://technet.microsoft.com/zh-cn/library/ms132058 (V = SQL .100). aspx

 

 

 

The following are several additional test queries.

Test query 1:

Select flattened

Predictassociation ([Default Association]. [products], include_statistics, 3)

From

[Default Association]

Natural prediction join

(Select 'touring tire' as [model]

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

Result:

 

Test query 2:

Select

Predict ([Default Association]. [products], include_statistics, 3)

From

[Default Association]

Natural prediction join

(Select 'mountain bottle cage' as [model]

Union select 'mountain Tire Tube 'as [model]

Union select 'mountain-200 'as [model]) as [products]) as t

Result: