R Script Visual in Power BI

R Environment

The R engine isn't included with the installation of Power BI desktop. I won't go into detail on this, so just know you'd need to install that separately. I had already installed the R component as part of Machine Learning Services for SQL Server 2017. I also had RStudio installed. Within Power BI desktop, take a moment to click File | Options and settings | Options to open the Options page. Then click R scripting in the list of Global Options. Here you'll see options to set the R home directory and the desired R IDE.

In the Visualizations pane, the block "R" icon should be easy to spot. When clicked the first time in the Power BI report, you'll be prompted to enable script visuals:

Click Enable, and you'll see a placeholder for the R script visual on the canvas, with the R script editor below it:

Data Fields

In the images above, note the existing "Query1" with three data fields: Backup Finish Date, BackupType, and Database. The source of this data is a simple SQL Server query:

SELECT bs.database_name, 
 bs.backup_finish_date,
 CASE bs.type
  WHEN 'D' THEN 'Full'
  WHEN 'I' THEN 'Differential'
  WHEN 'L' THEN 'Log'
 END AS BackupType
FROM msdb.dbo.backupset bs;

With the R script visual selected on the canvas, tick the checkbox next to each data field you want to use for the visual. (You can also drag & drop fields into the Values pane, or on the R script visual on the canvas. This auto-populates the R script editor, where you'll see the names of the fields that were selected.

Note the hierarchy for Backup Finish Date. The individual hierarchy fields are referenced in the R script editor code. I want the Backup Finish Date as a whole, though. So I address this in the Values pane:

The fields in the R script editor are updated accordingly:

Next, I write the R code. The R script editor pane is small and doesn't look user-friendly for development activity. But Power BI gives us the option to use an external development environment. Click the arrow icon for this option.

For me, this opened RStudio with a few lines of code pre-written to create an R data frame. This mimics the dataset in Power BI. (Code is slightly formatted for readability.)

# Input load. Please do not change #
`dataset` = read.csv(
  'C:/Users/Dave/REditorWrapper_.../input_df_....csv',
    check.names = FALSE,
    encoding = "UTF-8",
    blank.lines.skip = FALSE);
# Original Script. Please update your script content here and once 
# completed copy below section back to the original editing window #
##############################

The rest of the R code is outside the scope of this blog post, but it's included for reference. The visual I create uses the ggplot function from the ggplot2 package:

library(ggplot2)
library(scales)

dataset$"Backup Finish Date" <- as.POSIXct(dataset$"Backup Finish Date", format = "%Y-%m-%dT%H:%M:%S")
dataset <- dataset[order(dataset$BackupType, decreasing = TRUE),]

colorValues <- c("Full" = "blue", "Differential" = "magenta", "Log" = "yellow")

ggplot(dataset, aes(
        dataset$"Backup Finish Date", 
        dataset$Database, 
        colour = dataset$BackupType)) + 
    geom_point(aes(size = BackupType != "Log")) + 
    scale_size_manual(values=c(1,3), guide = FALSE) +
    xlab("Backup Date") +
    ylab("Database Name") +
    labs(colour = "Backup Type") +
    scale_color_manual(values=colorValues) + 
    scale_y_discrete(limits = rev(levels(dataset$Database))) +
    scale_x_datetime(labels = date_format("%b-%d")) +
    theme(axis.text = element_text(size=16),
          axis.title = element_text(size=18),
          legend.title = element_text(size=18),
          legend.text = element_text(size=16))

The R code above is copied from RStudio and pasted into the R script editor in Power BI. To view the visual, click the Run Script icon in the R script editor:

Lessons Learned

I stubbed my toe a number of times learning how to make this work. Numerous times I clicked the Run Script icon only for Power BI to tell me there was a script error. Most of my issues were attributed to my inexperience with R. But there may have been a Power BI-related issue or two as well. I'll look to blog about this in another post.