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 like this:
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 anomalies.
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