SQL Server Data Mining with Microsoft Office Excel
Fill form is a prediction technique using sample or known
data. If you navigate to the next sheet, Fill Form Example, you will see something
In the above data set you will see High Value Customer for
the first 10 rows but the rest of the rows are not filled.
Fill Form will identify the pattern for the existing data
and it will fill the blank columns.
You need to click the Fill Form button from the add-in ribbon, following screen
will be displayed:
In the above dialog, you need to select the column that need to predict. By clicking the link Choose columns to be used for
analysis you can select the columns you want to take account for in making the
predictions. For example, you can ignore the ID column.
When you click Run button, the new column High Value
Customer_Extended is added to the sheet in which you will get the
predictions for the high value customer. Apart from this, the new sheet will be
added as follows.
The above sheet will tell you what the most and least
influencing factors are when predicting the selected column.
When collecting data, there can be
anomalies due to various reasons. Data mining techniques can be used to detect
those anomalies. Highlight Exceptions is a technique available to detect
After getting into the sheet and selecting the columns you
need to analyze, click the Highlight Exception button.
The exception rows will be highlighted, along with the column
which is the reason for the row to become exception.
Apart from highlighting the exception rows, there will be another sheet added to
the workbook which is summarization of anomalies:
This should get you started using Excel for SQL Server data mining, in future I
hope to write another articles explaining other techniques such as Forecast,
Scenario Analysis, Prediction Calculator and Shopping Basket Analysis