StatSlice Business Intelligence and Analytics | Business Intelligence and Analytics Dallas | Business Intelligence Training Dallas

Excel at Data Mining – Connecting to a SQL Server Data Source

Today, I’m going to show you how to connect to a SQL Server data source in less than 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 the example, we will be using a tutorial spreadsheet that can be found on Codeplex here. You will also need to attach the AdventureworksDW2012 data file to SQL Server which can be downloaded here.

Our goal is to connect to a SQL Server data source and then create a Neural Network Model on the data.

 

Read below if you are unable to view the video walkthrough.

Step 1: Connecting to the vTargetmail view

First, we go to the DATA MINING tab, the Data Modeling group, and select the Advanced drop down. From here select Create Mining Structure to open the Mining Structure Wizard. Click Next to get past the title screen on the wizard. Change the radio button to External Data Source. Click the Data Source Query Editor button to the right of the Data Source Name text box.

External Data Source

Next, click the New Data Source button next to the empty drop down.

Naming the data source

Now, we are going to name our datasource srcAdventureworksDW2012. My AdventureworksDW2012 database is saved locally, so I will be connecting to the localhost and using my windows Authentication. This step might vary for some of you depending on the server to which you attach your database. Finally, we select AdventureworksDW2012 from the Catalog Name drop down. Then click OK.

new analysis services data source

The Data Source Query Editor has now listed every available tables and columns from the AdventureworksDW2012. We are going to select all the columns from vTargetMail by selecting vTargetMail on the left hand side and then selecting the Add Columns to Query. The columns in the query are then populated on the right hand side, and a query is automatically generated on the bottom. Click  OK.

data source query editor

Now that we have created our new datasource, finish out the Mining Structure Wizard by clicking Next multiple times.

Step 2: Creating a Lift Chart

Now, go back to the Advanced drop down and select Add Model to Structure. Click Next to get past the wizard title screen.  Select the Table Structure that you just created and click Next. From the drop down, select Naïve Bayes Model and click Next. For Purchased Bike, changed the drop down to Input and Predict. Now, some of the columns that we included in the structure are not useful for predictions. So let’s set AddressLine1, CustomerAlternateKey, EmailAddress, FrenchEducation, FrenchOccupation, NameStyle, Phone, SpanishEducation, and SpanishOccupation to Do Not Use. Click Next and then Finished.

Creating a Lift Chart

And that is how you connect to a SQL Server data source in less than five minutes using the Excel Data Mining Add in.

Happy digging!

Billy Decker

Billy Decker_border2Billy 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.