Demo: Predictive Modeling using R and SQL Server Machine Learning Services

Late last year, I wrote a series of articles about Predictive Modeling using R and SQL Server Machine Learning Services. At the time, I thought MLS was an underutilized feature of SQL Server. With a view to sharing my learning on the topic, I presented a session at the local SQL Server User Group earlier this month.

Presentation

This article will be focused on content presented at the User Group meeting. Below is the presentation I put together. If you are new to Machine Learning on premises, it will help you understand the capabilities of this powerful feature of SQL Server.

Demo

As a typical data nerd, I was more excited about the demo than the session itself. For the demo, I chose to predict heart disease risk using the popular heart disease data set from UCI.

https://archive.ics.uci.edu/ml/machine-learning-databases/heart-disease/processed.cleveland.data

https://archive.ics.uci.edu/ml/machine-learning-databases/heart-disease/heart-disease.names

1. Using R, a predictive model is trained and tested against known results.
2. After testing and comparing a few Machine Learning models, the R scripts were wrapped in SQL Server Stored Procedures letting us execute R scripts through Stored Procedures
3. The trained models were stored in a SQL Server table, and were used to perform Machine Learning predictions through Stored Procedure calls
4. Last step in the demo covered Native Scoring using the native C++ extension capabilities in SQL Server 2017

Demo solution is available for download at https://github.com/sqlroadie/PredictiveModelingUsingRandSQLServerMLS

Solution contains the following files:

• heart-disease.data – UCI dataset attached for reference heart-disease.names – Data description. Go through this file to understand what the variables mean
• PredictiveModelingUsingR.r – R script (with comments wherever applicable) that builds the predictive Model using RevoScaleR package. Go through this to understand how the models are created and used for prediction.
• PredictiveModelingUsingMLS.sql – SQL script that uses R code covered in the previous file to build a Machine Learning predictive model that is executed in the SQL on premises instance

If you need a hand with the demo, please drop me a note. LinkedIn is the best way to get in touch with me. Happy learning!

Thanks to Wardy IT and their Marketing Manager, Michaela Murray, for their continued efforts on organizing the user group meetings.