R Script Visual in Power BI

2019-05-28 1 Comments

Dave Mason - Power BI

Power BI desktop provides an option to visualize data with R. I decided to give this a try after exhausting the limits of my knowledge with the other built-in Power BI visualizations. My experience with both R and Power BI is fairly limited, so I covered a lot of new territory with this exercise. There may have been other, better options to reach the goal I was aiming for. But so be it--I learned a lot in the process. Hopefully you get something of value from this recap.

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.

Power BI - R script options

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:

Power BI - 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:

Power BI - R script visual

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, 
 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.

Power BI - R script visual

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:

Power BI - Hierarchy

The fields in the R script editor are updated accordingly:

Power BI - R script editor

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.

Power BI - Edit script in external IDE

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(
    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:


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", 
        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:

Power BI - R script editor, run script

Power BI - R script visual

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.

1 comment:

  1. I think there is a need to look for some more crucial aspects of SQL and REST API.

    Powerbi Read Rest