Data mining and SQL Server 2005

To get started in immediate mode, then do the following steps,

1. Launch BI Dev Studio. All Programs --> Microsoft SQL Server 2005 --> SQL Server Business Intelligence Development Studio.
2. In the File-->Open-->Analysis Service Database
3. In the Connect to Database dialog, enter the name of the server and database, and then create the underlying solution.

Offline mode.
When working in Offline mode, your project contains files that are stored on your client machine. As you make modifications to objects in this environment, the changes are stored in XML format on your hard drive. The models and other object are not created on the server until you decide to deploy them to the destination server. This provides you as the data mining developer/analyst with the ability to design and test your models on a test server before deploying them to a live server.

To get started in offline mode, then do the following steps as shown in Figure 1.

1. Launch BI Dev Studio. All Programs --> Microsoft SQL Server 2005 --> SQL Server Business Intelligence Development Studio
2. In the menu File--> New Project --> Analysis Services Project

Figure 1.

Creating data mining objects.
Let's define the data source to be analyzed as well as the underlying structure and the data mining model.

First of all, let's define the data source object which comprises the connection string to the data management system and additional information about the connection.

Right-click the Data Sources folder in the Solution Explorer, and select New Data Source to launch the Data Source Wizard. In the wizard, we may choose different data source provider, in our example we choose Native OLE/DB, SQL Native Client and then enter the name of the server, the authentication mode (Windows Authentication) and select the example database AdventureWorks provided by Microsoft SQL Server 2005, the next page of wizard is for impersonation information for accessing the database, we choose "Use the service account" option, and finally select a descriptive name for this data source.

Creating a data source view object.
This is the actual view of your data for selecting, organizing, and exploring the data source. In this step, we choose the tables that contain the cases.

Right-click the Data Source Views folder in the Solution Explorer and select New Data Source View to launch the Data Source View Wizard. In the first step, we choose the data source created before. On the Select Tables and Views wizard page click the >> button to move all tables from the Available Objects list to the Included objects list, select the Sales.Customer table to be analyzed, and finally we set a descriptive name for this object such as Sales_Customer Views. You may right-click on the view object, and select explore data for getting knowledge about this data.

You may create a customized view of the data source. Right-click the data source view, and select "New Named Query", and create query for selecting important attributes to be analyzed as shown in Figure 2.

Figure 2.

Using the Data Mining Wizard.
Let's create the major objects in the data mining project: data mining structure and data mining models.

A data mining structure defines the domain of a mining problem, whereas the mining model is the application of a mining algorithm to the structure. A mining structure is composed by a list of structure columns that have data and data types, and a binding to the associated data source as well as additional information about how the data is modeled. The data mining model is the realization of a data mining structure as well as an algorithm with the underlying parameters. For each column of the structure, it is assigned how it will be used in the model and the underlying algorithm specific mapping. The use of the algorithm depends on the business problems and capabilities of your business intelligence server.

You can use Data Mining Wizard to create in one step the data mining structure and the data mining model objects. Right-click the Mining Structure Folder in the Solution Explorer and then choose New Mining Structure. On the first page, you choose whether you are creating a model from a relational or multidimensional source (an OLAP cube). Some mining algorithm doesn't support the creation of model from a cube. In our case, select "From existing relational database or data warehouse", as shown in Figure 3.

Figure 3.

The next page asks you which algorithm to use to create your initial mining model. Choosing which algorithm you are going to use is dependant on the business problem you are trying to solve. In our case, we're going to select Microsoft Clustering algorithm for grouping cases of self-similarity, as shown in Figure 4.

Microsoft Clustering algorithm uses K-means algorithm. The main idea is to initialize randomly k points as the center of the k clusters, and then it is assigned each case to the nearest central point according to a measure function, and so forming new clusters. Later, a mean point for each cluster is calculated, and this is the new central point for each cluster. And then it is again assigned each case to nearest central point, and so on, until no new clusters are created. That is, the algorithm has converged. It is remarkable to say, that the convergence of the algorithm depends strongly of the randomly initialized central points.

Figure 4.

Next pages allow us to select the case table from some defined views, as well as whether the table is nested or not. In that case, the created named query Sales_Customer_Territory_CustomerType, as shown in Figure 5.

Figure 5.

Now let's specify how the attributes are interpreted by the mining algorithm whether they are key, input or predictable. You may click on the suggest button to perform a quick entropy-based analysis to indicate which columns are likely to provide information towards the predictable column.

In our case, as shown in Figure 6, CustomerID attribute is the key for identifying each case, the CustomerType and TerritoryID attributes are the inputs for providing information to the data mining algorithm. In the case of the cluster algorithm, there is no need for a predictable attribute.

Figure 6.

Next page allows us to select for each attribute the respective data and content types. It is very important to select the appropriate content type (discrete or continuous) for the performance and accuracy of the model. Clicking the Detect button causes to sample and analyze the source data for choosing the appropriate content type. In our case, we have described the CustomerID and TerritoryID attributes´content type as discrete for helping the data mining algorithm, as show Figure 7.

Figure 7.

And finally the wizard ends after setting a descriptive name for the data mining structure and data mining model container.

Processing.
Processing is the action to train models on the server. Data mining has the ability to process all the models in a structure in parallel on a single data read. Before processing a newly created structure or model, you must deploy the objects in the server. In the Build menu, select the option Deploy solution.

Interpreting the created model.
Each data mining algorithm is associated with a content viewer. The viewer is specially designed to display patterns discovered with the underlying algorithm.

The cluster viewer.
For discovering information of the cluster, we must navigate for the each tab of the cluster, as shown in Figure 8.

The first tab shows the found cluster and its relationships, that is, how close a given cluster is from any other interested cluster. In this example, we can see that the model found seven clusters and the relationship between them. Each cluster has different color intensity showing the number of cases that belongs to the former cluster.

The Cluster Profiles view, accessed through the second tab, contains a column for each cluster in your model and a row for each attribute. This setup makes it easy to see interesting differences across the cluster space. Using this view, you can choose an attribute of interest and visibly scan horizontally to see its distribution across all clusters. When an item catches your interest, you can look at neighboring cells or other cells of the same cluster to learn more about what that cluster means.

The Cluster Characteristics view allows comparing cluster one by one. Through this process you will refine your view of your chosen cluster.

The last tab is Cluster Discrimination. It comprises variables, values of the variables and the favors or complements from a selected cluster to each cluster or others, indicating which cluster the attribute favors.

Figure 8.

After you have understood the created model, you may rename the cluster for understanding or future use of the model. It is very easy to rename a cluster, just select the cluster to be renamed, right-click and select Rename Cluster action.

Conclusion.

My real intention in this article is to introduce the Data Mining concepts and principles and how to be applied in common business problem, as well as, how we can develop a Data Mining project using good tools provided by Microsoft such as Microsoft Visual Studio 2005 and Microsoft Analysis Service shipped with Microsoft SQL Server 2005. Nowadays, it is very important to embed this kind of components in Information System for decision making.

Web Links.

• ² Referenced at http://www.crisp-dm.org/, April 2006

References.

• ¹ ZhaoHui Tan, Jamie MacLennan, Data Mining with SQL Server 2005, Wiley Publishing Inc, 2005
• ³ Tom M. Mitchell, Machine Learning, McGraw-Hill, 1997