Excel at Data Mining – How to Manage Models
- How To Data Mining Add In For Excel 2016 Mac Add Ins
- How To Install Data Mining Add In For Excel 2016
Today, I’m going to show you how to browse, document, and manage models in 5 minutes with the Microsoft Excel data mining add-in. The assumption of this post is that you have already installed the data mining add-in for Excel and configured the add-in to be pointed at an instance of SQL Server with Analysis Services to which you have access rights.
For this example, we will be using a tutorial spreadsheet that can be found on Codeplex here. You also need to attach the AdventureworksDW2012 data file to SQL Server. The file can be downloaded here.
Our goal is to create Browse, Document, and Manage one of the models that we have previously created.
Read below if you are unable to view the video walkthrough.
. For Excel® 2007, always download SolverSetup. In Excel® 2010, choose File Help and look in the lower right. In Excel® 2013 and Excel® 2016, choose File Account About Excel® and look at the top of the dialog. Download SolverSetup64 ONLY if you see ‘‘64-bit’’ displayed. Since the release of Excel 2016 for Mac, customers have given great feedback about its ease of use and the benefits of the new features. We want to highlight the Excel 2016 for Mac features that customers rave about and share some tips and tricks that could save you a ton of time using the most powerful spreadsheet and data analysis tool on the Mac platform. By using a data mining add-in to Excel, provided by Microsoft, you can start planning for future growth. Add to that, a PDF to Excel converter to help you collect all of that data from the various sources and convert the information to a spreadsheet, and you are ready to go. I can't find the Analysis ToolPak in Excel for Mac 2011. There are a few third-party add-ins that provide Analysis ToolPak functionality for Excel 2011. Option 1: Download the XLSTAT add-on statistical software for Mac and use it in Excel 2011. XLSTAT contains more than 200 basic and advanced statistical tools that include all of the Analysis.
Step 1: Browsing the Models
We go to the DATA MINING tab, the Model Usage group, and select the Browse icon. From here, we can see our models organized by mining structure used to create the models. Notice that when we click on each of the models, we can see the Model Name, Algorithm, Description, Output, and Input for a particular model on the right hand side.
Clicking Next takes us to the Data Mining Model Viewer that we usually see after building a model. This allows us to explore the model by filtering on different attributes or comparing different results for the output. In our case, we only have a binary output (1 or 0), but this would allow you to be able to compare 2 out of N possible results for an output attribute.
Step 2: Documenting a Model
We go to the DATA MINING tab, the Accuracy and Validation group, and select the Documents Model icon. From here, we can see our models organized by mining structure used to create the models as they were when we browsed them. Like before, we can see the Model Name, Algorithm, Description, Output, and Input for a particular model on the right hand side.
Select and model, and click Next. Then select Complete information. Now, Excel will create a new worksheet with information about the model from model performance to different ranges tested in the data.
This will be very useful for evaluating the validity of a model for future applications. For example, we can see in our above model that we only considered folks who were 33 and older. This model would need to be re-run with new data if we wanted information on the bike buying habits of people in their 20s.
Step 3: Managing Models
We go to the DATA MINING tab, the Management group, and select the Manage Models icon. From here, we can see our models organized by mining structure used to create the models as they were when we browsed them. Again, we can see the Model Name, Algorithm, Description, Output, and Input for a particular model on the bottom right hand side.
However, we see a new window pane with tasks that we can perform on different models. This allows us to manage all of our models from a central location and do things such as rename, delete, clear, process, export, or import for a particular model.
And that is how you can browse, document, and manage your data mining models in less than five minutes using the Excel Data Mining Add in.
How To Data Mining Add In For Excel 2016 Mac Add Ins
Happy digging!
Billy Decker
Billy Decker is a consultant at StatSlice Systems. He graduated with a dual degree in Physics and Mathematics from the University of Texas at Austin and received his Masters Degree in Physics from the University of Texas at Arlington. He previously worked for Global Technical Services as a Senior Training Analyst and Bell Hellicopter as an Instructional Designer. His technical experience includes, but is not limited to, SQL, SAP, Business Objects, QlikView, and Sharepoint.
Subscribe
You can subscribe to our RSS feed.
- Notice: JavaScript is required for this content.
- Categories
- Blog (97)
- Big Data (13)
- Big SQL (4)
- Redshift (2)
- Blog Authors (49)
- Bernard Wehbe – Blog (8)
- Billy Decker – Blog (13)
- Brett Neuman – Blog (7)
- James Vogel – Blog (5)
- Jared Decker – Blog (6)
- Justin Taylor – Blog (10)
- Business Analytics (36)
- Business Intelligence (35)
- Dashboarding (11)
- Data Mining (10)
- Data Warehousing (11)
- ETL (6)
- IT Management (8)
- Mobile BI (1)
- OLAP (2)
- Products (24)
- Microsoft (6)
- QlikView (5)
- Roambi (3)
- Tableau (11)
- News (14)
- Jobs (5)
- Partnerships (2)
- Our Work (41)
- Case Studies (6)
- Newsletters (9)
- Publications (14)
- Webinars (7)
- White Papers (7)
- Training (8)
- information (4)
- Jared Decker (4)
- registration (4)
- Uncategorized (1)
- Blog (97)
Edit: This has also been confirmed working in Excel 2019 / Office 365.
Ever since the release of Excel 2016 a compatible SQL Server Data Mining Add-in has been missing. There has been numerous requests for Microsoft to release an official update but so far this hasn’t happened.
Having Excel 2013 and Excel 2016 installed side-by-side has been used as a workaround to get the Excel 2013 DM Add-In semi-working also for Excel 2016. The following registry hack is allowing the otherwise blocked installation to proceed for Excel 2016 without the need for an actual Excel 2013 installation.
Download Microsoft® SQL Server® 2012 SP4 Data Mining Add-ins for Microsoft® Office® (Latest official version)
Insert the following entry in the Windows registry (.reg file here but use at your own risk)
Proceed with installation which now shouldn’t complain about missing Excel 2010/2013