Microsoft SQl Server ML Services: RevoScaleR Package

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail

 

RevoScaleR Package

RevoScaleR is packages created by Revolution Analytics (own by Microsoft) with the aim of importing, transforming, and analyzing data at scale. There are different categories of functions for the data store, imports and save as, data transformation, draw some charts such as histogram, line and so forth, descriptive analysis, predictive analysis, package management and so forth [3].

In this section, I will introduce some of the function that may use. The example will be shown in SQL Server 2017.

rxLinMod

rxLinMode, fits a linear model to data. Is a regression model for linear data. In below example, I am going to show you how I use this function for creating a model for predicting the strength of the concrete regarding input variables such as the amount of water, cement, ash, and age. Calculates predictions for fitted models. This function creates a model based on the formula and training data set.

As you can you see in below code, the first line is “EXECUTE sp_execute_external_script @language=N’R’” which is calling a stored procedure that allows the user to write an R script.

From second to the fifth line, is the relevant R code that stores in @script parameter. The code contains referring to RevoScaler package by using function require. There is no need to install this package as t exists in ML/ R services. The second R code line is about creating a formula. Our aim is to predict the strength of concert regarding some other variables, the formula that able to create this is formula=strength~cement+ash+age+water which stores the formula in “formula” variable. The third line, create a model using rxLinMod function to create a linear model from the formula. Finally, the last line stores the formula in an output variable named “Model”.

@input_data_1 stores the data collected from SQL database using SQL query “N’Select strength,cement,ash,age,water from [dbo].[concrete]”. Finally, the output variable “Model” will store the linear model.

After creating the model, I want to store the model for future to reuse it for predicting the new data. To store the model, I use the below codes:

Then, we need to Insert the model into the “LinearModel” table.

The model already stored in the database. Now in another store procedure we are going to use the model.

 

There is another input for store procedure that is not just dataset, it contains an input for models that we already stored in the database.

By running the stored procedure the below prediction result has been shown.

 

There are other models for the aim regression analysis such as rxLogit that use for logistic regression, rxGlm that generalized the linear model to data and so forth. Moreover, for other analysis such as prediction of the group (classification), there are some algorithms such as rxDTree, rxBTree,rxDForest, and rxNaiveBayes [3].

[1] https://docs.microsoft.com/en-us/sql/advanced-analytics/install/sql-r-services-windows-install

[2] https://docs.microsoft.com/en-nz/sql/ssms/download-sql-server-management-studio-ssms

[3] https://docs.microsoft.com/en-us/machine-learning-server/r-reference/revoscaler/revoscaler

 

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
rssyoutuberssyoutube
Leila Etaati
Dr. Leila Etaati is Principal Data Scientist, BI Consultant, and Speaker. She has over 10 years’ experience working with databases and software systems. She was involved in many large-scale projects for big sized companies. Leila has PhD of Information System department, University of Auckland, MS and BS in computer science. Leila is Microsoft Data Platform MVP.

Leave a Reply

Your email address will not be published. Required fields are marked *