In this article I will describe a way to couple SQL Server together with R, and show how we can get a good set of data mining possibilities out of this fusion. First I will introduce R as a statistical / analytical language, then I will show how to get data from and to SQL Server, and lastly I will give a simple example of a data analysis with R.

What is R and what noticeable features does it have

R is an open source software environment which is used for statistical data analysis. All operations are performed in memory, which means that it is very fast and flexible as long as there is enough memory available.

R does not have a storage engine of its own other than the file system, however it uses libraries of drivers to get data from, and send data to, different databases.

It is very modular in that there are many libraries which can be downloaded and used for different purposes. Also, there is a rapidly growing community of developers and data scientists which contribute to the library development and to the methods for exploring data and getting value from it.

Another great feature is that it has built-in graphical capabilities. With R it takes couple of lines of code to import data from a data source and only one line of code to display a plot graph of the data distribution. An example of this graphical representation will be given shortly. Of course, aside from the built-in graphics, there are libraries which are more advanced in data presentation (ggplot2, for example) and there are even libraries which enable interactive data exploration.

For more details on R features and on how to install it, refer to the R Basics article, which was recently published on Simple-talk.

Connecting to SQL Server from R

This part assumes that the reader has already gained some familiarity with the R environment and has the R and RStudio installed.

As mentioned, R does not have its own storage engine, but it relies on other systems to store the analyzed data. In this section we will go through some simple examples on how to couple R with SQL Server’s storage engine and thereby read data from, and write data to, SQL Server.

There are several options to connect to SQL Server from R and several libraries we can use: RODBC, RJDBC, rsqlserver for example. For the purpose of this article, however, we will just use the RODBC package

Let’s get busy and setup our R environment.

In order to get the connectivity to SQL Server working, first we need to install the packages for the connection method and then we need to load the libraries.

To install and load the RODBC package, do the following:

• Open the RStudio console (make sure the R version is at least 3.1.3: If it isn’t, then use the updateR() function)
• Run the following command: install.packages(“RODBC”)
• Run the following command: library(RODBC)

Note: the R packages are usually available from the CRAN site, and depending on the server setup, they may not be directly accessible from the R environment, but instead it may be needed to be downloaded manually and installed manually. Here is the link to the package page: RODBC: http://cran.r-project.org/web/packages/RODBC/index.html

Exploring the functions in a package

R provides useful ways of exploring the functions of the R packages, If, for example, we wanted to list all functions in a specific package we would use a function similar to this:

And then we would call it like this:

Typing ??RODBC at the command prompt will bring out some help topics about the RODBC package.

Further, typing ? before any of the functions will bring out the help information about a function. For example,

Getting connected

For the purpose of this exercise, we will be using the AdventureWorksDW database (it can be downloaded from here).

Let’s say we are interested in calculating of the correlation coefficient between the annual sales and the actual reseller sales for each reseller. First we will create the following view:

I have divided the Sales and the Annual Sales amounts by a 1000, so it is easier to work with the numbers later on. We will go in details in the statistical analysis later on; let’s start by getting connected.

First we need to create a variable with our connection string (assuming we have already loaded the library by running library(RODBC) ):

RODBC connection

And now we can use this variable as a parameter in the different calls to our database. In the RODBC package there are two different ways to connect to the SQL Server: there are two methods sqlFetch and sqlQuery.

Here is how we use sqlFetch:

and then we check the contents of the frame with the following command:

The data looks like this:

Another way to get the data is to use sqlQuery like this:

As you may have guessed, this is quite flexible if we want to get a subset of the data by using the WHERE clause.

Benchmarking of RODBC

To benchmark the performance of the RODBC library, I have written a script which will read data from SQL Server to R.

Here is the script which will be used:

The script above creates a connection to a database called TestData. The database has two types of tables – a narrow table with 6 columns and a wide table with 31 columns.

The X in the table name represents the different amount of data there is in each table. All tables in the database are as follows:

The tables are populated with Redgate’s Data Generator, and then the R script above is used to get all data from the respective table and measure the time it took in seconds.

Here is how long it took (in seconds) to read the 10k, 100k, 1M, 10M and 30M rows:

Exploring the data

Now that we have loaded the data into memory, it is time to explore it. First, let’s see the density and the data distribution. R has great facilities to visualize data almost effortlessly. As we will see shortly, we can call a plot a graph with only a few clicks.

First, let’s set the properties of our environment to display two plot graphs in one row. In R this is easily done by using the par() function like this:

This will create a plot matrix with two columns and one row, which in this case will be filled in by rows. Now, let’s run the two commands which will fill in the graphs in the plot matrix:

This will give us the following graph (found in the Plots section in the RStudio environment):

From here we already can extract some valuable knowledge – and we did this with a few clicks! We can see that:

• there are generally two segments of resellers by AnnualSales – ones that peak at around 100K, and the other ones that are above 2.5M
• there are three different segments of resellers by SalesAmount – under 50k, around 150k and around 300k

Remember that this was the data we loaded for the sales in Euros. Let’s load the data from USD sales, and compare the histograms.

We will be using the following view in our database:

And then we will use similar commands to load the data in R:

And then we get the histograms like this:

The histograms look like this:

This gives us some more insight into how the resellers perform in the USD market.

Let’s go a bit further ad get a summary of our datasets. Very conveniently there is a built-in function in R, which does exactly that: summarizes our dataset (also called dataframe in R language). If we simply type:

We will get the following summary info:

And respectively for the USD resellers we will type

And we will get the summary:

Now let’s do some clustering to dig a bit deeper in our data.

Cluster Analysis

There are countless ways to do Cluster Analysis, and R provides many libraries which do exactly that. There are no best solutions, and it all depends on the purpose of the clustering.

Let’s suppose that we want to help our resellers do better in the Euro region, and we have decided to provide them with different marketing tools for doing that, based on their Annual Sales amount. So for this example, the marketing department needs to have the resellers grouped in three groups based on their AnnualSales and the SalesAmount needs to be displayed for each reseller.

In the example below, we will take the dataFetchEUR dataframe and will divide the resellers in three groups, based on their AnnualSales. Then we will write the data back into our SQL Server Data Warehouse, from where the marketing team will get a report.

By looking at the data summary for the AnnualSales column, we can decide to slice the data by the boundaries of 1,000K and 1,600K. These are imaginary boundaries, which in reality will be set by the data scientist after discussions with the team who will be using the data analysis.

Just to verify, we can group the resellers count per their AnnualSales:

We get the following result:

So, this seems right. Our three segments will be:

Segment 1 will be < 1,000,000
Segment 2 will be >= 1,000,000 and < 1,600,000
Segment 3 will be > 1,600,000

For this we can use a simple ifelse function to define the clusters:

AnnualSalesHigh.cluster <- ifelse(dataFetchEUR$AnnualSales >= 1600, 3, 0)
AnnualSalesMedium.cluster <- ifelse(dataFetchEUR$AnnualSales >= 1000 & dataFetchEUR$AnnualSales < 1600, 2, 0)
AnnualSalesLow.cluster <- ifelse(dataFetchEUR$AnnualSales < 1000, 1, 0)

Now we have three different datasets, and we need to combine them into one dataset (dataframe). We can use the cbind function like this:

Now we can look at the segments with the View function:

The data looks like this:

Now we need to sum up the values in each row and output another dataframe, which has only one column. We can do this with the following function:

And finally, we need to bind together the original dataframe and add the categorization per reseller:

The data looks like this:

And finally, we will save this dataset into our data warehouse by using the sqlSave function in the RODBC package:

And now we have a table called [dbo].[Marketing_EURAnnualSalesCluster] in our [AdventureWorksDW2012] database, which is ready for use by the marketing department.

This process can be automated and a batch file can be created with the R scripts – the entire flow from getting the data to writing it back to the data warehouse – and it can be scheduled to be run regularly.

Conclusion:

In this article we have seen how easy it is to connect to SQL Server from R and do an exploratory data analysis. This brings great value to business, especially because of the time savings of data modelling and visualization techniques which can be very time consuming with other technologies.

We have explored a way to connect to SQL Server (by using the RODBC library) and we have created a simple Cluster Analysis and segmentation, which provides immediate value to the end-users of the data.