A Day of Data Science with SQL Server, Azure ML, Spark, Hadoop, and R

Hello Dear Reader!  Next week in Jacksonville, Friday August 11th,  I will be presenting a Day of Data Science: With SQL Server 2016/2017, Azure ML, Spark, Hadoop, & R.  We will also be working in some Power BI, and may even be building something like what you see on the page.  Here's the abstract.

The world of data is changing.  It used to be enough to collect data and report off of it.  ‘Business’ people would make ‘business’ decisions based off of reports, historical experience, and their gut. ‘Business’ people may go into meetings and say things like, “We will change our business and stop doing A.  We used to do A, and we got profit!  Now we do A, and profit is down.  Therefore we stop doing A, and now we will do B!”  They may have data to back up their decision, they may not have data.  Sometimes they got it wrong, sometime they got it right.  One day someone figured out that they could use math and science to calculate the odds of making a good decision.  In Las Vegas they call it gambling, in the business world we call it Data Science.  Data Science is not some mysterious solution that fixes everything.  It is the process of using a series of calculations to make many micro-informed-decision.  This leads to informed decision making.  Those calculations are what we would call models, and they store the algorithm that we will use to calculate our odds.  There are many different ways to create, store, and utilize models.  In this pre-con we will discuss some of the architectures and ways this can be accomplished.  We will then focus on the newest advanced analytics capabilities in SQL Server and in R to discuss on premise architectures.  Including the advantages to storing data and models in SQL Server as well as how to use Predictive and Prescriptive analytics.  We will then end the day shifting our focus to the cloud with an end to end Lab focusing on Azure ML, Spark, Hadoop, Power BI, and Modern Applications.

*Note to participate in the Lab you will need access to Microsoft Azure and an Azure account.

What does the agenda look like?

8:30 AM – Speaker & Course Introductions
8:45 AM – Data Science concepts
9:45 AM – Begin Introduction to R – Using Baseball Analytics
10:00 AM – Break
10:10 AM – Continue Introduction To R
11:00 AM – R and SQL Server 2016/2017
12:00 PM – Lunch
1:00 PM – Azure ML Overview
2:00 PM – Begin Hands on Lab Walk Through
4:30 PM – End of Class

Will I live blog it like last year?  Only time will tell Dear Reader, only time will tell.

As always Thanks for stopping by, and I hope to see you there!!

Thanks,

Brad

How Do I Add an R Package to SQL Server from In-Database R?

Hello Dear Reader!  I'm working on some really fun stuff with in-database R using SQL Server 2016.
I needed to add a couple packages to my R engine so I could use them and call them from an R script. I realized I had not added any external packages to my R instance so this was a perfect opportunity to write a blog!  So here is a quick blog on how you add an R Package to SQL Server.  This entire tutorial is if you have internet.  If you need to do this for a server, which probably doesn't have access to internet look at this MSDN article, Install Additional R Packages on SQL Server.


First we need to find our instance default install path for SQL Server.  In this case I'm a comic book geek and my named instances are JARVIS and STARK.  STARK is my 2016 instance so we'll need to find that path.

In this case my path is C:\Program Files\Microsoft SQL Server\MSSQL13.STARK\R_SERVICES\bin\x64 , then I want to hold down SHIFT and right click on the Rgui.exe, then select Run as administrator.

After this opens we need to create a variable to hold the location were we would like the package to be installed.  Then we can install the package we want to use, in this case the plyr package.

lib.SQL <- "C:\\Program Files\\Microsoft SQL Server\\MSSQL13.STARK\\R_SERVICES\\library"
install.packages("plyr", lib=lib.SQL) 

Notice I adjusted the path for my named instance.

After it installs our window should look like this.

Now I can run a simple test script loading the plyr package.

execute sp_execute_external_script

@language=N'R'

,@script =N'

       library(plyr);

       OutputDataSet<-InputDataSet'

,@input_data_1=N'select 1'

WITH RESULT SETS undefined;

If I get this error the package did not load.

Msg 39004, Level 16, State 20, Line 4
A 'R' script error occurred during execution of 'sp_execute_external_script' with HRESULT 0x80004004.
Msg 39019, Level 16, State 1, Line 4
An external script error occurred: 
Error in library(plyr) : there is no package called 'plyr'
Calls: source -> withVisible -> eval -> eval -> library

Error in ScaleR.  Check the output for more information.
Error in eval(expr, envir, enclos) : 
  Error in ScaleR.  Check the output for more information.
Calls: source -> withVisible -> eval -> eval -> .Call
Execution halted
Msg 11536, Level 16, State 1, Line 4
EXECUTE statement failed because its WITH RESULT SETS clause specified 1 result set(s), but the statement only sent 0 result set(s) at run time.

If I the statement compiles, everything is fine and I get a result set.

As always Thanks for stopping by.

Thanks,

Brad