Quick R Christmas tree

Dear readers,

Wish you all best in year 2017!

Here is a little R code:


dataset <- c(9,8,7,6,5,4,4.5,4,3,2,1,0.8,0.2)

df <- data.frame(group = rep(c("l","d"), 
                 x = 1:length(dataset),
                 y = c(dataset, dataset*-1))

ggplot() +
  geom_bar(data = df, aes(x=x, y=y),stat = "identity", fill = '#00A650',width=.8) +
  coord_flip() + ggtitle("Happy New Year 2017") + theme_void()

And the result….



See you in 2017!

SQL Saturday Vienna 2017 #sqlsatVienna

SQL Saturday Vienna 2017 is just around the corner. On Friday, January 20, 2017, a lot of local and international speakers will gather to deliver sessions relating to SQL Server and all related services. With great agenda – available  here, the attendees will surely enjoy different topics as well as have the opportunity to talk to Austrian PASS and SQL community as well as speakers, as well as SQL Server MVP.



My session at SQL Sat Vienna 2017 will be focused on what can database administrators gain from R integration with SQL Server 2016. Said that, we will look how statistics  from main DBA tasks can be gathered, stored and later analyzed for better prediction, for uncovering patters in baseline that might be overlooked and of course how to play with information gathered from Query store and DMV query plans. Session will also serve with field examples each enterprise can have.

This year, I will have the pleasure to do the pre-con on Thursday, January 19, 2017 at the JUFA Hotel in Vienna. A full day pre-con workshop on SQL Server and R integration with all the major topics covered, where and how to start using R, how the R integration works, deep dive into R package for high performance work and dive into statistics – from uni-variate to multi-variate as well as methods for data mining and machine learning. Everybody welcome to join, it will be a great day for a workshop! 🙂


2016-12-29 19_01_20-BI and Analytics with SQL Server and R - Tomaz Kastrun Tickets, Thu, 19 Jan 2017.png

Tickets are available here via Eventbrite.


Falco is already playing Vienna Calling   🙂



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.


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 🙂 ).


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.


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


Now, the same visualization using R.


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:

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.


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.


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:


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

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.


And the printscreen from Power BI:


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

Happy PowerBI-Rrrring!

SQLSatSlovenia 2016

SQL Saturday Slovenia 2016 took place on 10th of December 2016 at the Faculty of Computer Science in Ljubljana, Slovenija.

Event turned out –  as always – very great, with almost all of the international speakers and two Slovenian speakers. Agenda was  very very tempting with very different topics on BI, DEV and DBA.


With great line-up of good sessions came also great lunch and after the very “random of them random” raffle came also wine and schnapps tasting.


Day ended with lots of great discussions on BI relevant themes, PowerShell ideas and enchantments of DBA jobs.

Overall, with more than 270 attendees, great organizers and very good international speakers, it was again another of Slovenian SQL event that will be remembered!

Great work, everyone!