The Data Mining Client for Excel brings the data mining applications of SQL Server Analysis Services to the desktop. You can work with the same data mining algorithms, structures, and viewers that are available in an instance of SQL Server Analysis Services, but you can store source or test data in Excel tables. By using the Data Mining Client, you can analyze complex data sets and make predictions as easily as opening a table in Excel. Moreover, because your data and results can be stored in Excel, you can quickly leverage and present patterns discovered by the model.
The easiest way to install the add-ins is to use the Getting Started wizard, which walks you through the process of setting up a database and configuring it to enable data mining.
The Data Mining Client for Excel maintains an active connection to the server, so you can identify patterns in data stored in Excel data tables, and then save your data mining model to the server if you wish, to use in further testing or prediction. You can also apply the data in Excel to existing data mining models, and then reprocess the model to improve its accuracy, or try different mining models on the same data to develop more in-depth analyses.
After you have created a mining model, you can assess its effectiveness by using standard methods such as a lift chart or a classification matrix, or you can measure the return on profit for a data mining model that makes predictions.
The Data Mining Client for Excel also provides management tools that let you create, rename, delete or reprocess data mining models and structures that are stored either on the server or in temporary session files.
Click the Connection button to select an existing connection, or to create a new connection to an instance of SQL Server 2008 Analysis on the Analyze or Data Mining ribbon.
To create a new connection to Analysis Services
The Model Usage and Management groups provide wizards to help you browse, modify, and manage existing mining models that are stored on an instance of SQL Server 2008 Analysis Services. If you have the necessary permissions, you can delete, modify, rename, or process existing mining models and structures without leaving Excel.
To use the Browse Model wizard
The wizard opens a Browse window that is appropriate for the type of model that you selected.
Depending on the data mining algorithm that you used when you created the model, the Browse window may include graphs to help interpret the results, legends that contain additional detail, and controls for interacting with the data.
You may use the data for the mailing processing.
The Query wizard helps you interact with existing mining models to make predictions based on data in an Excel table, an Excel range, or another data source. You can apply the patterns contained in the existing mining model to input data. First, you have to choose the model from the list of models available on the server, then choose the data to be evaluated and finally define the mappings between the input data columns and the model columns.
The wizard presents a list of data mining models on the server to which you are connected.
You have now selected an algorithm that will be applied to a dataset.
Then, you must specify a source for the dataset to use as input. You can use data in an existing Excel table or range, or you can specify a SQL statement to retrieve the data.
Susan wants to use the actual customer dataset.
Next, you have to create a query to select a table available in the current data source and select the columns to include in your dataset.
An SQL-query will be generated. You can see the query in the lower pane of the query wizard (1).
Depending on the type of model that you choose, you must now specify the column that contains the data to be evaluated, and define mappings between the input data columns and the model columns. Depending on the algorithm you choose, you can set other properties on the model.
Note, that the table column Age Group, which is a column of the Mining Model has no corresponding Table Column because the Age Group was introduced as a calculated field in the data source.
We have two choices:
In order to add a new column,
After we have added the new column Age Group to our data source, the relationships between data model columns and the input table columns should look like this:
Finally, the wizard also gives you the ability to choose one or more predictions, and specify an output column in which to store the results.
Repeat the above step to choose some more output columns:
You can now use the data-set for further processing.