R and SQL Server articles

In past couple of months, I have prepared several articles on R and SQL Server that have been published on SQL Server Central.

The idea was, to have couple of articles covering the introduction to R, to basics on R Server, to some practical cases on R with SQL Server.

1) Using Microsoft R in Enterprise Environments

Article covers the concepts on Microsoft R Server, where and how to start with Microsoft R in enterprise environment and give answers to most common concerns people might have when introducing R language into corporation.

1

Link to article: http://www.sqlservercentral.com/articles/R+Language/140422/

 

2) Introduction to Microsoft R Services in SQL Server 2016

Integration and architecture on Microsoft R Services is main focus of this article. It outlinesdifferent flavors of R (Open, Client, Server, Services, Hadoop, etc.), how to deal with installation and basic overview and explanation on extended stored procedure SP_EXECUTE_EXTERNAL_SCRIPT.

2.png

Link to article: http://www.sqlservercentral.com/articles/Microsoft/145393/

 

3) Installing R packages in SQL Server R Services

Expand the functionality of R by adding new packages. Covers many ways how to install and add additional packages to your R environment.

3

Link to article: http://www.sqlservercentral.com/articles/R+Package/145571/

 

4) Using SQL Server and R Services for analyzing Sales data

Providing use cases on analyzing sales data was focus of this article with goal to show readers and users how to ope rationalize and bring R code into use in any enterprise (small or big) environment.

4

Link to article: http://www.sqlservercentral.com/articles/R+Services/145649/

 

5) Using Power BI and SSRS for visualizing SQL Server and R data

Visualizing the data for any use case, is also important aspect of understanding data insights. Article covers Power BI and SSRS visualization and how to embed R code in both tools.

5

Link to article: http://www.sqlservercentral.com/articles/R+Language/151358/

6) Using SQL Server and R Services for analyzing DBA Tasks

Broadening the use of Microsoft R for the DBA tasks was the main goal of this article. With simulation of  the disk usage, showing R example how to switch from monitoring the usage to predicting the usage of disk space. Clustering executed queries to narrow down performance issues and visualizing Query store information with heatmap were also introduced in article.

6

Link to article: http://www.sqlservercentral.com/articles/R+Language/151405/

 

More articles will follow, so stick around.

Happy R-SQLing!

 

 

R graphs and tables in Power BI Desktop

Power BI Desktop enable users to use R script visual for adding custom visualization generated with R language – regardless of R package used. Before using R script visual, you will need to enable it by setting path to R Engine on your client in the global options. Once this is done, you will be able to enhance your Power BI reports using R visualizations.

1450757873470

My personal favorite R packages I like to use in Power BI are shiny, plotly, ggplot2, googleVis, ggiraph, rCharts and d3js.  Apart from finding the package for data visualization where you feel home, one of the very great thing I find with Power BI + R is the ability to have visualizations created dynamically (more or less like if you would be using plotly or shiny)  with slicers and selectors. And this makes data exploration, data wrangling so much easier.

With October 2016 Power BI custom visuals got additional 7 R visuals to the family (check Power BI Visuals Gallery and fall in love with your favorite one 🙂 ).

2016-12-18-09_26_55-visuals-gallery-_-microsoft-power-bi

Upon using any of the additional R visuals, Power BI will – prior to importing the additional visualizations – ask for installing all the missing packages, as well as inform you of any incompatibility between R engine and package.

So how easy is to create R visualization in Power BI?  Let us first import data to Power BI from SQL Server. I have generated a sample data-set for this demo. Data and all the samples are available at Github.

2016-12-18-11_32_26-preparing-data-sql-sicn-kastrun-plan-spar_si01017988-69_-microsoft-sql-s

With sample data-set I have created a simple data visualization with slicers using Power BI.

2016-12-18-11_34_01-sample_powerbi_r-power-bi-desktop

Now, the same visualization using R.

2016-12-18-11_34_01-sample_powerbi_r-power-bi-desktop_2

Note that I have used same slicers (Year = 2016, Month = 2) and R graph (using ggplot2) was able to dynamically  visualize the numbers. So this mean, that any R visualized graph can be navigated and dynamically updated, which is absolutely great, especially in cases when you do data exploration. Following R code was used:

library(ggplot2)
ggplot(data=dataset, aes(x=Day, y=Value)) +  geom_line(aes(color="red")) + 
ggtitle("Value by day") + theme(legend.position = "none") + 
theme(panel.background = element_rect(fill = "white", color = "lightgray", size = 1))

with additional dataset conversion to data.frame done automatically.

2016-12-18-11_57_51-sample_powerbi_r-power-bi-desktop_code

Where it get’s tricky is if you want to return the R data.frame into your Power BI page in form of a table (imagine you want some results to your graph or that you are calculating some statistics). Well, Power BI does not allow returning tables from R script visual. Unless, table is an image (that is, in a “form” of a visualized “graph”).

So I manage to create a table as a graph using R and gplot function from ggplot2 package.

2016-12-18-12_40_32-sample_powerbi_r-power-bi-desktop

The best part of this table is, that is dynamically changed based on selection on graphs or slicers. The downside is, that it can not be scrolled within the object. So if you end up with bigger table, you have to tweak the font size or show table aggregated or grouped.  In my opinion, I would not change R created table with Power BI table, because Power BI table is simply better and easier to create and maintain. But I would definitely recommend using R tables to support your R visuals. For example: If you are generating clusters using R in power BI, use R generated table to expose additional statistics on clusters.

The code for generating this R table is:

library(gridExtra)
library(ggplot2)

qplot(x=1:5, y=1:5, geom = "blank") + 
theme(line = element_blank(), text = element_blank()) + 
annotation_custom(grob=tableGrob(dataset))

Script generated blank plot, where custom annotation is added with function grob. And this is our data.frame from dataset and is displayed on top of empty graph.In addition to “remove” the background blank graph, blank line and text is added:

theme(line = element_blank(), text = element_blank())

 

Update #1 (19.12.2016)

After reading the comment to this blog post from reader Baptiste Auguie  (with link to his post on displaying tables as grid graphs), that using grid will return the same result, but very much a simpler solution to plotting / drawing table. Thank you Baptiste! I must have missed this one.

R code is far less simpler without  qplot function.

library(gridExtra)
grid.table(dataset)

And the printscreen from Power BI:

2016-12-19-06_13_19-sample_powerbi_r-power-bi-desktop

As always, sample, code and data-set is available at Github.

Happy PowerBI-Rrrring!

#SQLSatDenmark 2016 wrap up

SQLSatDenmark 2016 took place in Lyngby at Microsoft Denmark headquarters. Apart from the fact that Lyngby is absolutely cute town, the Microsoft HQ is nice as well.

At Microsoft Denmark:2016-09-23-09_46_29-presentation1-pptx-powerpoint

Lyngy:2016-09-23-09_48_21-presentation1-pptx-powerpoint

On the evening before the precons day, dinner at MASH restaurant was prepared for all the precon speakers Tim Chapman, Andre Kamman, Kevin Kline and myself with hosts Regis Baccaro and Kenneth M. Nielsen.

2016-09-23-19_26_08-presentation1-pptx-powerpoint

After delicious steaks, pints of beer and interesting conversations, the precon day started.

My precon room was full and I had 30 attendees, lots of material and ending with demos on R and SQL Server integration from the fields. Feedback was great, obviously. The problem I had was that I prepared too much material, which was anyways handed over (all the code) for people to learn more when back at home and that I was focusing too much on statistics. But finishing at 16.30 and was available in the Microsoft HQ until 17.30 for any other questions.

Next day, SQLSaturday Denmark started early and in total 280 attendees showed up. It was easy going and well organized event, great sponsors, nice swag, raffle and overall good stuff one can find at the event – juice bar, good food and ending the event with hot dog stand and SQL Beer. Yes, traditional Danish SQL saturday beer 🙂

2016-09-23-19_43_41-presentation1-pptx-powerpoint

I delivered a session on Machine Learning algorithms in Microsoft Azure, explaining which algorithm to use with which dataset and what kind of statistical problem can be solved with. Great feedback from the crowd and very interesting questions – more or less very statistical and data mining questions. And I truly loved it.

Thanks to all the sponsors, organizers, attendees and the SQL family. It was great to see you.