R Visuals in Power BI: Beyond Plots

Reading Time: 5 minutes

One of the advantages of having R in Power BI is that it opens up opportunities to move beyond the default charts without having to build a TypeScript/JavaScript custom visual. The R output is not as interactive as D3 can be, but it is quick to develop, the script is more transparent to users, and it can be shared easily. Recently, however, I have also been exploring how to take advantage of the R visual in Power BI Desktop for use beyond plotting, such as I/O.

When you first create the R visual, Power BI directs the input that you have added into a data frame called “dataset”. In the case of the screenshot below, I added two fields called “X” and “Y” from my Power BI dataset, and I can now use those two fields in my R script.

 

If I were to click “Run” at this point, Power BI would throw an error. Why? For its final R output, Power BI requires a plot–or at least an image of some type–and not a data frame. Without a plot, you will receive the following message stating that “no image was created”.

 

With proper code that creates a plot as output, however, you can develop some great content using R. Ultimately though, having R also allows for more advanced scenarios besides simply generating plots. The following “Ship Sinker” prototype that I built in Power BI Desktop is a variation of a popular trademarked game. The Selection History chart as well as the Hit Counter by Ship are both generated using the ggplot2 package. Some people may object to the lack of an opponent, or having only one layout for the ships, but that’s probably due to their envy of my undefeated record and the fact that I usually hit a ship on every move. In any case, see the Power BI report in action in this video:

 

It’s great to make some fairly poor-looking charts using the built-in R plot, ggplot2, plotrix, or some other library (at least, mine always end up looking fairly bad as well as inconsistent with other Power BI charts, but that reflects my still-growing level of expeRtise). What if you could do more with the R visual?

Perhaps I/O?

The two R charts in the prototype game are unique in that they not only display data relevant to the current user selection, but also their historical selections. It would not be Battleship Ship Sinker without the ability to keep track of coordinates that you have already guessed. In Power BI, tracking this history would normally create a problem because users do not have access to telemetry data as they click around. With R, the inability to retain prior user selections is no longer a problem. Note that in most cases, retaining this type of data is not necessary. In the case of a game like Battleship Ship Sinker, however, it is required (unless you have the ability to recall up to one hundred moves).

Local File Input/Output

What makes this plot of historical moves possible? Looking at the following R script, it takes advantage of R functions for write.table and read.table. Clicking on the report’s 10×10 scatterplot grid to make a move triggers the R visual to write the X & Y coordinates as well as other data associated with the selection to a local text file. Because it is appending to the file, all historical selections are preserved. Reading from that same file then allows all of the historical selection data to be used in R. Since the final output is a plot of the selection history, Power BI does not give an error when the intermediary I/O code is used.

library(ggplot2)

#Local file path
filePath = "C:/temp/PBIShipSinker.txt"

#filter some unnecessary data (it's just a buggy prototype...)
dataset <- subset(dataset, dataset$CurrentColor != "#FFFFFF")

#write data associated with the current selection to the file path
write.table(dataset, file = filePath, append = TRUE, sep = ",", col.names = FALSE)

#read all historical data from the file path into a new data frame
df <- tryCatch(read.table(file = filePath, sep = ","), error=function(e) NULL)

#specify column names for the new data frame
colnames(df) <- c("ID", "SelectionXLabel", "SelectionX", "SelectionY", "HitMiss", "CurrentColor", "ShipType")

#Use ggplot2 to create the Selection History plot
ggplot(df, aes(x = SelectionX, y = SelectionY, label = HitMiss)) + 
 geom_point(aes(colour = CurrentColor), size = 3) +
 scale_colour_manual(values = c("#587ABC", "#EE0000", "#587ABC")) + 
 theme(legend.position="none", axis.ticks = element_blank()) +
 geom_text(vjust = 2) + 
 xlim(1,10) + ylim(1,10) +
 xlab("") + ylab("") + ggtitle("Selection History")

After the code runs, the final plot displays all historical X & Y coordinates as well as interprets some of the other data such as whether it was a hit or a miss. The second chart also takes advantage of this history by displaying what type of ship was hit.

 

Because it’s 1982, when you want to start a new game, simply delete the file on your local drive, or write additional R code to delete it. Wait…what?

SQL Server Input/Output

Local I/O is ultimately not a great option. What happens when R support comes to the Power BI service online, and you do not have access to your local file system? What if potential future multiplayer support will not involve physically passing your computer to another person? Everything is okay. The same logic can be applied to SQL Server or other database connections.

R has a few packages such as RODBC and RSQLServer that handle connections to SQL Server. With R Services in SQL Server 2016, it may even be possible to change your Power BI compute context from your local R environment to SQL Server in-database. Someone should try that…

For my current prototype, I used RODBC and adjusted the original code. Note how the following R script handles the SQL connection, appends new data (sqlSave), and selects all history from the table (sqlQuery) prior to the plot code. Using sqlSave, R creates the table if it does not already exist, and specifying append=TRUE handles the retention of historical moves. When it comes to the ggplot2 portion, nothing changes.

library(ggplot2)
library(RODBC)

#filter some unnecessary data (it's just a buggy prototype...)
dataset <- subset(dataset, dataset$CurrentColor != "#FFFFFF")

#ODBC connection to SQL Server, store new values and retrieve all history
connStr <- "Driver=SQL Server;Server=localhost;Database=Demo;Uid=[User];Pwd=[PW]"
connODBC <- odbcDriverConnect(connStr)
sqlSave(connODBC, dataset, tablename = "dbo.Ships", append = TRUE)
selectQuery <- "SELECT SelectionXLabel, SelectionX, SelectionY, HitMiss, CurrentColor, ShipType FROM dbo.Ships"
df <- data.frame(sqlQuery(connODBC, selectQuery))

#specify column names for the new data frame
colnames(df) <- c("SelectionXLabel", "SelectionX", "SelectionY", "HitMiss", "CurrentColor", "ShipType")

#Use ggplot2 to create the Selection History plot
ggplot(df, aes(x = SelectionX, y = SelectionY, label = HitMiss)) + 
 geom_point(aes(colour = CurrentColor), size = 3) +
 scale_colour_manual(values = c("#587ABC", "#EE0000", "#587ABC")) + 
 theme(legend.position="none", axis.ticks = element_blank()) +
 geom_text(vjust = 2) + 
 xlim(1,10) + ylim(1,10) +
 xlab("") + ylab("") + ggtitle("Selection History")

 

Exciting, eh? The plot is the same as it was when using a local file, but the data is now stored in and retrieved from a SQL Server table. This type of functionality represents a big advantage for the R visual in my opinion. In the real world, there may even be some creative uses besides a typical auditing scenario for retaining Power BI data like this. At the very least, maybe it’s time for Ship Sinker multiplayer…