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

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.

The previous article describes how to use DMX to create a mining model. This article describes how to create a mining model through programming.

By programming, you can use AMO to analyze all architecture-related implementations of the Service, including multi-dimensional data sets and data mining.

The AMO object tree contains all object models that support multi-dimensional data sets and data mining. You can see its structure in detail in this article.

The object structure is a little more complex, but we focus on three parts:

Connect the two objects to the analytic Service database and operate the analytic Service database instance.

Then, you can use datasource and performanceview to create a data source connection and Data Source view.

Then, the object system and miningmodel under miningstructrue are used to create the mining structure and mining model. We can note that column is a self-contained structure, which mainly takes into account the nested model in the mining structure.

Through the above object model, we can see that these objects correspond to the creation of a data mining project in Visual Studio, in a normal process, we will perform the following operations:

• Create a project (a database is used after deployment)
• Create a data source
• Create a data source view
• Create a mining structure (define a data structure)
• Create a mining model (mining algorithms, etc)

The following describes how to use AMO to create a data mining project.

Open Visual Studio. The version used in this article is 2010, and the corresponding database is SQL Server 2012. Create a console project in Visual Studio, right-click the references folder, and select Add reference...

Click Browse to find the DLL file corresponding to the AMO object.

This file is usually located at the following location:

C: \ Program Files (x86) \ Microsoft SQL Server \ 110 \ SDK \ assemblies

 

Next, return to the console code and add the reference of the AMO object.

Using Microsoft. analysisservices;

 

Then, use the following code to connect to the analysis service instance.

Server ssasserver = new server ();

Ssasserver. Connect (@".");

 

Create an analytic Service database.

# Region create database

String strdatabasename = "dmamo ";

Console. writeline ("Check Database:" + strdatabasename );

If (ssasserver. Databases. Contains (strdatabasename ))

{

Ssasserver. Databases. Remove (strdatabasename );

Console. writeline ("Database" + strdatabasename + "already exists, droped .");

}

Database DB = new database (strdatabasename, strdatabasename );

Ssasserver. Databases. Add (db );

DB. Update ();

# Endregion

After the above Code is run, an empty database has been created under the corresponding analysis service instance.

 

Create a data source.

# Region datasource

Relationaldatasource RDS = new relationaldatasource ("dmdatasource", utils. getsyntacticallyvalidid ("dmdatasource", typeof (database )));

RDS. connectionstring = @ "Data Source =.; initial catalog = adventureworksdw2012; provider = sqlncli11.1; Integrated Security = sspi; Application name = ssasdemo ;";

RDS. impersonationinfo = new impersonationinfo ("Wade", "iamfromcnblogs ");

DB. datasources. Add (RDS );

DB. Update (updateoptions. expandfull );

# Endregion

Note that the impersonationinfo attribute of the data source object corresponds to the settings shown in figure. During local development, we usually set it as an administrator account for convenience.

 

Create a data source view.

# Region Data Source view

Dataset dset = new dataset ();

Sqlconnection sqlconn = new sqlconnection (@ "Data Source =.; initial catalog = adventureworksdw2012; Integrated Security = sspi; Application name = ssasdemo ;");

// Line items

Sqldataadapter dalineitems = new sqldataadapter ("select [ordernumber], [linenumber], [model] from [vassocseqlineitems]", sqlconn );

Dalineitems. fillschema (dset, schematype. mapped, "vassocseqlineitems ");

Dset. Tables ["vassocseqlineitems"]. extendedproperties. Add ("tabletype", "View ");

// Orders

Sqldataadapter daorders = new sqldataadapter ("select [ordernumber], [customerkey], [region], [incomegroup] from [vassocseqorders]", sqlconn );

Daorders. fillschema (dset, schematype. mapped, "vassocseqorders ");

Dset. Tables ["vassocseqorders"]. extendedproperties. Add ("tabletype", "View ");

// Relationship

Datarelation relateproductsubcate = new datarelation ("rel", dset. tables ["vassocseqorders"]. columns ["ordernumber"], dset. tables ["vassocseqlineitems"]. columns ["ordernumber"]);

Dset. relations. Add (relateproductsubcate );

Performanceview dsv = new performanceview ("dmdsv", "dmdsv ");

Dsv. performanceid = "dmdatasource ";

Dsv. schema = dset. Clone ();

DB. performanceviews. Add (dsv );

DB. Update (updateoptions. expandfull );

# Endregion

Creating a data source view is a complicated process. tables need to correspond to objects one by one, and the relationships need to be created one by one. Finally, all the tables used must be loaded through the DataSet object, then, set the clone method to the data source view architecture.

 

Create a mining structure.

# Region mining structure

Miningstructure MS = new miningstructure ("msorder", "msorder ");

DB. miningstructures. Add (MS );

Ms. Source = new performanceviewbinding ("dmdsv ");

Scalarminingstructurecolumn scordernumber = Ms. Columns. Add ("ordernumber", "ordernumber ");

Scordernumber. iskey = true;

Scordernumber. type = miningstructurecolumntypes. text;

Scordernumber. content = miningstructurecolumncontents. Key;

Scordernumber. keycolumns. Add ("vassocseqorders", "ordernumber", oledbtype. wchar );

Scalarminingstructurecolumn scmodel = Ms. Columns. Add ("model", "model ");

Scmodel. iskey = true;

Scmodel. type = miningstructurecolumntypes. text;

Scmodel. content = miningstructurecolumncontents. Key;

Scmodel. keycolumns. Add ("vassocseqlineitems", "model", oledbtype. wchar );

Tableminingstructurecolumn tclineitems = new tableminingstructurecolumn ("seqlineitems", "seqlineitems ");

Tclineitems. foreignkeycolumns. Add ("vassocseqlineitems", "ordernumber ");

Tclineitems. Columns. Add (scmodel );

Ms. Columns. Add (tclineitems );

Ms. Update ();

# Endregion

Creating a mining structure is actually a process of ing the schema defined in the data source view into a data mining structure. Use the corresponding miningcolumn object to map fields to be used according to the data source view.

 

Create a mining model. The enumerated attribute algorithm is used to set the mining algorithm used by the Mining Model Based on the mining structure.

# Region Mining Model

Miningmodel Mm = Ms. createminingmodel (true, "basket Forecasting Model ");

Mm. algorithm = miningmodelalgorithms. microsoftassociationrules;

Mm. Columns ["seqlineitems"]. Usage = miningmodelcolumnusages. predict;

Mm. Update ();

# Endregion

 

 

 

 

 

 

 

 

 

A mining model corresponds to a mining structure, so many structures directly inherit the mining structure. The only thing that needs to be done here is to set a column as a prediction field based on the created mining model.

 

Finally, we use AMO to process the mining model.

# Region Process

Ssasserver. capturexml = true;

DB. Process ();

Ssasserver. capturexml = false;

Console. writeline ("processing ...");

Ssasserver. executecapturelog (True, true );

# Endregion

 

Open the Mining Model to view the rules generated by the mining model.

 

Of course, in fact, it is not recommended to create a mining model in the Code. Some amo deep object models currently only provide interface descriptions in the SQL server documentation, but do not provide an introduction or explanation of the model, I mainly follow the process of creating a project and then go back to understand the meaning of the object model. Even if there are few nested structures in the mining structure and model on foreign websites, so this article is really a long time to explore, and the compilation debugging and comparison will continue to get through all the details. In any case, through this process, we can gain a deeper understanding of the object model at the bottom of the Analysis Service to better understand and design our mining model, you can also use AMO to automatically adjust the model. It is hoped that this article will allow more friends to understand the AMO object model.

 

Appendix: Example project.