SQL Server Data Mining with Microsoft Office Excel

Data Mining with Microsoft Office Excel


A key issue in data mining is not only generating the results but also interpreting them. Unfortunately it is often difficult to interpret the results of data mining and many users are turning to Excel and its data mining add-in tool.


You will need to have permission to access SQL Server Analysis Services. You will need to have Excel installed (preferably 2007 or later).  The Microsoft SQL Server  Data Mining Add-ins for Microsoft Office 2007 can be downloaded from:

http://www.microsoft.com/downloads/details.aspx?FamilyId=7c76e8df-8674-4c3b-a99b-55b17f3c4c51&displaylang=en .

This package includes two add-ins for Microsoft Office Excel 2007 (Table Analysis Tools and Data Mining Client) and one add-in for Microsoft Office Visio 2007 (Data Mining Templates). In this article, will focus on the Table Analysis Tool for Excel 2007.

Unfortunately,  this add-in does not work in a 64 bit environment and Microsoft has even said we won’t get 64bit support version in the near future.


After the standard dialog box in the installation, you will get the following dialog requesting you to select your installation options.

By default, the Table Analysis Tools for Excel is selected and if you wish, you can select the other two options.


In the start menu under Microsoft SQL 2008 Data Mining Add-ins, you will see a short cut named Server Configuration Utility which is the tool you to configure the add-in.

At the first dialog, you need to enter the analysis server name, the authentication method should be Windows Authentication as analysis services does not support SQL Server Authentication.

You need to allow the creation of the temporary mining model in SQL Server Analysis Service(SSAS). You can enable this from SQL Server Analysis Service by selecting the properties of the Analysis Service  logging in to the SQL Server Analysis Services from SQL Server Management Studio.

Also, you can configure this from the installation wizard.

In the wizard, the next step is to create a SSAS database or else you can allow temporary mining models in the  SSAS database.

With the installation, you will get a sample Excel file named DMAddings_SampleData.xlsx to test with. When you open the excel file, in the Analyze ribbon you will see that available for table analyze:

The below table lists the data mining algorithms used by each analysis option.  

Menu Option

Data Mining Algorithm

Analyze Key Influencers

Naïve Bayes

Detect Categories


Fill form Example

Logistic Regression


Time Series

Highlight Exceptions


Scenario Analysis (Goal Seek)

Logistic Regression

Scenario Analysis (What If)

Logistic Regression

Prediction Calculator

Logistic Regression

Shopping Basket Analysis

Association Rule

Analyze Key Influences

When you have a data set there can be several attributes. For example, in case of a customer dataset, there can be several attributes like Marital Status, Gender, Income and Occupation etc.  Analyze Key Influences examines what are the influencing factors for a given attribute. In the Table Analysis Tools Sample sheet, you can find a table with customer attributes.

Let us assume that we wish to analyse the Occupation column for key factors.

1.       First select Analyze Key Influences from the Analyze ribbon.

2.       Next select the Occupation column for analyze:



Leave a comment

Your email address will not be published.