Learn more about SQL Server tools

 

Tutorials          DBA          Dev          BI          Career          Categories          Videos          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Events

DBA    Dev    BI    Categories

 

Introduction to the SQL Server Analysis Services Neural Network Data Mining Algorithm


By:   |   Read Comments (3)   |   Related Tips: > Analysis Services Development

Problem

In data mining and machine learning circles, the neural network is one of the most difficult algorithms to explain. Fortunately, SQL Server Analysis Services allows for a simple implementation of the algorithm for data analytics.  Check out this tip to learn more.

Solution

In this tip, we show how to create a simple data mining model using the Neural Network algorithm in SQL Server Analysis Services 2012.

In Visual Studio (also known from the start menu as SQL Server Data Tools), create a new Analysis Services Multidimensional and Data Mining Project.

Introduction to the SQL Server Analysis Services Neural Network Data Mining Algorithm

In this tip, we will name the project NeuralNetworkExample. Click on OK when finished with the New Project window.

name the project NeuralNetworkExample

In the Solution Explorer window, right-click on the Data Sources folder and choose "New Data Source..." to initiate the Data Source Wizard.

right-click on the Data Sources folder

Click on "Next >".

initiate the Data Source Wizard

Choose your data connection, if one exists. If a data connection does not exist, click on "New..." to create a new data connection.

If a data connection does not exist, click on "New...

In this example, we are using a connection to the AdventureWorksDW2012 database on the localhost.

we are using a connection to the AdventureWorksDW2012 database

Click on "Next >".

Click on "Next >".

On the Impersonation Information screen, click on "Use a specific Windows user name and password." Enter your username and password. Click on "Next >".

Use a specific Windows user name and password

On the Completing the Wizard screen, the data source name can be changed if desired. Click on "Finish".

On the Completing the Wizard screen, the data source name can be changed if desired

The new data source will appear in the Solution Explorer.

The new data source will appear in the Solution Explorer.

In the Solution Explorer window, right-click on the Data Source Views folder and choose "New Data Source View..." to launch the Data Source View Wizard.

right-click on the Data Source Views folder and choose "New Data Source View...

Click on "Next >".

launch the Data Source View Wizard.

On the Select a Data Source page in the Relational data sources window, select the data source we created in the above step. Click on "Next >".

select the data source we created in the above step. Click on "Next >".

On the Select Tables and Views page, move the view vTargetMail from the Available Objects box to the Included object box by selecting vTargetMail in the Available objects box and then clicking on the ">" box. Click on "Next >".

the Select Tables and Views page

On the Completing the Wizard page, give the Data Source View a name and click on "Finish".

give the Data Source View a name and click on "Finish".

The data source view now appears in the Solution Explorer window. In the center of the image below, we see the columns listed in the view vTargetMail. Many of these columns such as First Name and Name Style are not useful for data mining. Also, the Spanish and French columns will be redundant because there is an English equivalent. We can select the specific columns we would like for input to the neural network algorithm when we create our data mining model, or we can create a new Named Query to restrict the available columns within the data source view.

The data source view now appears in the Solution Explorer window.

To create a new Named Query, right-click next to the vTargetMail object and choose "New Named Query" as shown below.

create a new Named Query

Enter the query as shown below, give the named query a name and then click "OK".

give the named query a name and then click "OK".

The new Named Query, ColumnsForDataMining, now appears next to the view vTargetMail. Right-click on the Mining Structures folder and select "New Mining Structure..." to launch the Data Mining Wizard.

The new Named Query, ColumnsForDataMining, now appears next to the view vTargetMail.

Click on "Next >".

Right-click on the Mining Structures folder and select "New Mining Structure..."

Press the "From existing relational database or data warehouse" radio button and then click "Next >".

"From existing relational database or data warehouse" radio button and then click "Next >".

Select the Microsoft Neural Network as the data mining technique.

the Microsoft Neural Network

On the Select Data Source View page, we will use our previously defined objects. Click on "Next >".

On the Select Data Source View page

Next, check the Case box on the ColumnsForDataMining line. Click on "Next >".

check the Case box on the ColumnsForDataMining line

On the Specify the Training Data page, check the box in the Key column that corresponds with the CustomerKey column. The remainder of the columns will be used as input. The BikeBuyer column will be our class label, so we check the Predictable box for the BikeBuyer column. Click on "Next >".

On the Specify the Training Data page, check the box in the Key column that corresponds with the CustomerKey column.

The default values are shown below on the Specify Columns' Content and Data Type page. Click on the Detect button.

The default values are shown below on the Specify Columns' Content and Data Type page.

Notice how the content type for Bike Buyer, Number Cars Owned, Number Children At Home, and Total Children changed from Continuous to Discrete. Also, the data type for Bike Buyer can be changed from Long to Boolean because the distinct values for the Bike Buyer column are zero and one. Click on "Next >".

the data type for Bike Buyer can be changed from Long to Boolean because the distinct values for the Bike Buyer column are zero and one. Click on "Next >".

We will use 30% of our data for testing the mining model's accuracy. Click on "Next >".

We will use 30% of our data for testing the mining model's accuracy

On the Completing the Wizard screen, we can rename the mining structure name and the mining model name. Click on "Finish".

On the Completing the Wizard screen, we can rename the mining structure name and the mining model name.

Our mining structure now appears in the Solution Explorer.

Our mining structure now appears in the Solution Explorer.

The Mining Structure tab is selected by default. At this point the Analysis Services objects reside in the Visual Studio project. Click on the Mining Model Viewer tab.

At this point the Analysis Services objects reside in the Visual Studio project

Visual Studio will attempt to deploy the SSAS objects to the server specified in the project properties. When asked if "Would you like to build and deploy the project first?", choose "Yes".

Visual Studio will attempt to deploy the SSAS objects to the server specified in the project properties

When given the warning about the time it could take to process the mining model and asked "Do you wish to continue?", choose "Yes". The number of records in the view is not a large amount, so it should not take more than a minute to process.

"Do you wish to continue?", choose "Yes".

When the Process Mining Model window appears, press the "Run..." button.

When the Process Mining Model window appears, press the "Run..." button.

The Process Progress window will appear. When the process completes successfully select "Close" in the Process Progress window and "Close" again in the Process Mining Model window.

When the process completes successfully select "Close" in the Process Progress window and "Close" again

Depending on your hardware configuration, the Load Mining Model Content window might appear stating to "Please wait...".

he Load Mining Model Content window might appear stating to "Please wait...".

The Deployment Progress window will appear also stating that the SSAS objects were successfully deployed to the Analysis Services server.

The Deployment Progress window will appear also stating that the SSAS objects were successfully deployed to the Analysis Services server.

In the Mining Model Viewer tab, we can see which attributes and their values favor the False (non-buyer) classification and which attributes and their values favor the True (buyer) classification. The wider the blue bar the more that specific key-value pair will favor a particular classification. In the example shown here, when there are 3 children in the customer's home, then the customer tends to be a non-buyer of bikes. When the customer's yearly income is between approximately 79,000 and 154,000, then the customer tends to be a bike buyer.

the Mining Model Viewer tab

When we click on the Mining Accuracy Chart and then click on the Classification Matrix page, we can see the confusion matrix for the Neural Network algorithm. This displays the count of true positives, true negatives, false positives and false negatives.

click on the Mining Accuracy Chart and then click on the Classification Matrix page

Summary

In this tip, we have provided an introduction to the Neural Network data mining algorithm in SQL Server 2012 Analysis Services.

Next Steps

Explore changes to the results by adding or removing columns to the mining model. Also, check out these other tips on data mining in SQL Server 2012 Analysis Services.



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips


 









Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Sunday, November 15, 2015 - 10:11:30 AM - Adam Chandley Back To Top

Hi, I'm an MS student at USF and we're in the midst of a DM class that leverages SAS, Weka, and R for the various data mining algorithms. I've decided to do my term project via SSAS due in large part to the uniqueness of the challenge as well as my lengthy background in SQL Server (but with no history in mining). I wanted to drop a note and encourage you to check out SQL Server 2016 (latest CTP) as well as SQL Server Data Tools (SSDT, latest). There have been a few updates as well as the new VS IDE that makes things more interesting. Another note I would add is the ability to build a mining structure with multiple models hanging off of it. Makes things very convenient to perform multiple analysis against your formatted data. I plan on producing a 'how-to' from this experience to encourage local academia to take advantage of their MSDN-AA and leverage these services. The upcoming PowerBI platform on Azure and embedded R support should create some new buzz for data semi-science - good skills to have when the demand hits!


Friday, July 11, 2014 - 5:10:27 PM - MarkD Back To Top

Just found that the Classification Matrix is available for view on the deployed artifact, but still not in VS.


Friday, July 11, 2014 - 4:59:56 PM - MarkD Back To Top

Thank you :)

I've tried to follow this walkthrough and when trying to examine the accuracy chart, am met with the message "The predictable column has not been mapped. Open the column mapping editor and connect a column in the input table to the 'Bike Buyer' mining structure column.

Everything else works spot on; any idea?


Learn more about SQL Server tools