24 HOP 2016 – 5 Techniques to Beautiful Data Insights with R and SSRS – Recap

24 Hours of Pass – Evolution of data platform  webinar was going on on 25 and 26 of May, 2016. And the reviews, questions and recordings are now available.

Two weeks after the session, I am blogging my view on the event, view on our session and mostly I will try to answer all the questions and/or comments that have been gathered.

This has been my second 24HOP webinar (although the topic was similar to this one – R and SQL Server – 24HOP 2015 – Growing our community), but my first 24HOP webinar that I did as co-presenter. My co-presenter was Julie Koesmarno  (twitter|blog) and it was pure fun and 100% enjoyment. Thank you Julie for doing this and in four weeks preparing  on this session we have also learned many things from each other. Oh, and by the way, this was actually the second 24HOP webinar with Julie. With the first one, she was moderating my session 🙂

Doing webinar as co-presenter was somehow interesting from webinar tools point of view, also lovely and helpful Angie Horel from PASS organization was helping us to manage this part. So with Julie we ended up, she was navigating through the slide deck and through the VM machine where we had all our demos prepared. And prior to webinar, we practiced the slides so we knew when to do the transition. Also moderator Markus Ehremüller did great job keeping us together and managing and answering the board full of questions. Thank you Angie and thank you Markus.

We were scheduled on 25 May, 16:00 Hours GMT with around 373 attendees listening, participating and enjoying our sessions, who managed to be with us all the way until the end of the session. We had 10 minutes discussion board and I must say interesting questions were posted so Julie and myself had quite a discussion. Questions going from: “Is R (as R language) for Relational” to “Can R handle unstructured (big data) together with Polybase”. Here are all the comments from evaluation board:

 Overall was good, nice to see some good examples. Needed a little technical know-how on what you would actually do to make 
the visuals in R and use in PowerBI. Want to understand more about how to use R in SQL 2016.

 Thank you very much!!

 Great overview. Would have been nice to see more details of what went into the demos

 Sound quality was a bit spotty, but otherwise it was excellent.

 Need more material to read

 Thanks for the BIG PICTURE -- very elegant data representations!

 Hope to receive the slides! Sometimes technical content can be difficult to present but

 really liked the links to datavizcatalogue

 Great demonstration of how the new "R" feature relates to business use

 Great session! Very useful information as we are looking to provide better visualizations for

 Could address a little bit on the integration of R into SSRS display canvas. than just the demos. 
thought this was an excellent session! our company. Thank you!

Demo code and files can be downloaded from Github (https://github.com/tomaztk/24HOP_2016_5Techniques) where you will find all the code and reports.

Based on the comments, our goal of the session was the usage of R closer to broader community and to enhance the data visualization in SSRS, Power BI and any blob/html file export that R library supports.


So to webinar questions (I will answer it with from my point of view and Julie will post hers):

Question: Do you have to use SSRS to display output from R script call in SQL Server?

Answer: SSRS is used to display image (jpg, png,..) files that are created when report in SSRS is created. R output (up to this day and to my knowledge) can be displayed are: data.frames, jgp, png, list (prior you need to unlist it and save it as a data frame). HTML currently is supported and dynamic HTML can be stored from R (using sp_execute_external_script stored procedure) but displaying HTML file in SSRS is to this point still a bit tricky. I have tried and I could display HTML but without any external content (javascript, jquery, etc.).

Question: So prior to SQL2016, how did the R IDE communicate with a SQLServer?

Answer: Yes. You could establish connection in both ways using xp_cmdshell! I have wrote an extended article in 2014 on SQLServerCentral blog and forum on how to create a connection and push data back and forth and run any statistics and create beautiful visualizations.  Framework is also available with this article.

Question: Is it possible to create an interactive graph with R in SSRS? Not only image.?

Answer: In SSRS, up until today, I couldn’t do it due to limitations with calling javascript and jquery from SSRS. Otherwise, without this one, it is easy to implement it. I will not say No 🙂 because I still have not lost hope to find a workaround to do it. But there are plotly, Shiny available that can do just that.

Question: Can we call R script through a web service?

Answer: Yes. There are several ways. The fastest and most straightforward is using Microsoft Azure Machine Learning, where you upload your data, do a data model and call web service to evoke the R script. It can be done with Javascript, Json and localhost installation if you are running it from client.

Question:  Needed a little technical know-how on what you would actually do to make the visuals in R and use in PowerBI. Want to understand more about how to use R in SQL 2016.

Answer: Please post some questions on this blog and we will gladly help you. If you need more on R and SQL Server 2016, MSDN is a good place to start.

Question:How to show kurtosis and outliers in this platform? What code invokes the regression plots?

Answer: Both were prepared with R ggplot library, using boxplot and lines.

2016-05-24 11_36_43-24 Hours of PASS_ The Evolution of the Data Platform _ About

To sum up: this was one of the most fun co-presenting session, especially working with Julie it was very nice. In this webinar we showed the benefits of R visualization, the importance of knowing data insights and all the steps that lead to successful story telling. Most of all bringing the usage of R in SQL Server or SQL Server with R for better data insights and faster data analysis. And most importantly, it was fun! 🙂

Posted in Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Follow TomazTsql on WordPress.com

Tomaz doing BI and DEV with SQL Server and R


attaining enlightenment with sql server, .net, biztalk, windows and linux

SQL DBA with A Beard

He's a SQL DBA and he has a beard

DB NewsFeed

Matan Yungman's SQL Server blog

Reeves Smith's SQL & BI Blog

A blog about SQL Server and the Microsoft Business Intelligence stack with some random Non-Microsoft tools thrown in for good measure.

SQL Server

for Application Developers

Clocksmith Games

We make games we love to play

Business Analytics 3.0

Data Driven Business Models

SQL Database Engine Blog

Tomaz doing BI and DEV with SQL Server and R

Search Msdn

Tomaz doing BI and DEV with SQL Server and R


Tomaz doing BI and DEV with SQL Server and R

Ms SQL Girl

Julie Koesmarno's Journey In Data, BI and SQL World


R news and tutorials contributed by (750) R bloggers

Data Until I Die!

Data for Life :)

Paul Turley's SQL Server BI Blog

sharing my experiences with the Microsoft data platform, SQL Server BI, Data Modeling, SSAS Design, Power Pivot, Power BI, SSRS Advanced Design, Power BI, Dashboards & Visualization since 2009

Grant Fritchey

Intimidating Databases and Code

Madhivanan's SQL blog

A modern business theme

Alessandro Alpi's Blog

SQL Server, Azure and .net in a nutshell :D

Paul te Braak

Business Intelligence Blog

Sql Server Insane Asylum (A Blog by Pat Wright)

Information about SQL Server from the Asylum.

Gareth's Blog

A blog about Life, SQL & Everything ...

SQLPam's Blog

Life changes fast and this is where I occasionally take time to ponder what I have learned and experienced. A lot of focus will be on SQL and the SQL community – but life varies.

William Durkin

William Durkin a blog on SQL Server, Replication, Performance Tuning and whatever else.

$hell Your Experience !!!

As aventuras de um DBA usando o Poder do $hell

%d bloggers like this: