Data Mining with Excel Q&A

Thank you to everyone who attended my free webinar on August 29, 2013 where I talked about using the Data Mining Add-in for Excel. If you were unable to attend you can find the recording here. You can also check out all the Pragmatic Works Free Training videos here. I discussed why data mining is useful to organizations, showed how to download and install the plug-in using Excel 2013 and then how to do a few basic things in each of the two tabs added by the installation.

Below are the questions I received during the webinar along with answers to each of them. If you have more questions please let me know and I’d be more than happy to post the answers here along with the others.

When selecting variables, the default has them all selected. Is there any way to deselect them all rather than having to do them one by one?
From everything I have seen the “Analyze” tab we looked at does not have a select/deselect all option. This option is however on the “Data Mining” tab. In the screenshot below on the left is Detect Categories from the Analyze tab without the option and the right is the Forecast Wizard from the Data Mining Tab with the option.


For the download, from, do I download DataMiningViewerClient to get started?
You actually want to choose the data mining add-in. The download links can be found on the right side of the page.


Can the data mining plug-in for Excel 2010 work with SSAS 2012?
Yes, the Excel 2010 plug-in will allow you to use an SSAS instance that is running 2008 or later. The Excel 2013 plug-in also uses SSAS 2008 or later. Just a side note: I have not tired using the Excel 2013 add-in against a non SQL 2012 SSAS server. The add-in wizard says it requires SQL 2008 or later, but the MSDN page says SQL 2012. I would need to setup a new environment to test other configurations, but I would assume the 2008 requirement is correct.

When we use the “Clean Data” option in Excel does it update the data in database?
No. When doing data mining in Excel the data is stored inside the workbook and isolated from the source. Any data changes only affect the table within the Excel workbook.

Does data mining support tabular models in addition to multidimensional models?
Data mining is currently only supported on an SSAS server running in multidimensional mode. In fact when installing SQL Server 2012 the choices are either “Tabular Mode” or “Multidimensional and Data Mining Mode”.


When will data mining on tabular models be available?
At this time there has been no announcement on when data mining will be available on tabular models.

How does the replace outliers with mean value option in the data cleanup cleanup menu work, does the mean include or exclude the outlier values?
Included. When changing my first two values the mean value for the entire data set is 56806. The mean value excluding those two records is 56117.22. The replaced value shown in the far right column of the screenshot is 56806.


What does the “outliers” result look like on the unmodified data set?


How do I get the sample data you used?
First open Excel and navigate to the Data Mining tab, click the Help icon on the far right side of the ribbon and select the Getting Started option.


On the first screen of the wizard select the option Use an existing instance of Microsoft SQL Server 2008 (or later) Analysis Services that I administer.


On the next screen click the Finish button.



Finally ensure the Open an example test table upon closing the wizard option is checked and click close in the bottom left. This will close the wizard and launch the sample workbook.




Bradley Schacht

Bradley Schacht is a Cloud Solution Architect on the state and local government team with Microsoft based in Jacksonville, FL. He has co-authored 3 SQL Server books including "SQL Server 2014 Professional Administration". As a former consultant and trainer, he uses his experience on many parts of the Microsoft BI and data platform to help customers deliver the best possible solutions. Bradley frequently presents at community events around the country. He is a contributor to sites such as and an active member of the Jacksonville SQL Server User Group (JSSUG).

You may also like...

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.