With the latest release of Power BI Desktop, you can now run R scripts directly in Power BI Desktop. To celebrate this new functionality, we’re creating a series of blog posts that show you how to use this new functionality, with some hands-on examples. By the end of this blog post, you learn how you can consume your R code in Power BI, and take full advantage of this built-in functionality in Power BI. To illustrate how this works, we look at a simple example where we create a forecast in R, and consume it in Power BI.
What does the “R script connector” do?
You can access the new R Script connector from the Get Data button in Power BI Desktop. You can simply paste existing R code in the dialog, and trigger an execution of your selected R script. The resulting datasets can be imported into a Power BI, just like any other data.
Why should you connect your R data to Power BI?
Simply put, you can do amazing things in R. But once you do those amazing things, it can sometimes be challenging to present the output, share and explain it with your peers, and make sure your results are kept up to date.
Using the R Script data connector in Power BI Desktop, you can now take your existing R code and use Power BI to visualize it, share it, and operationalize it.
- Visualize your R data – once your R script’s data is imported to Power BI, you can use all of the amazing Power BI tools to create visualizations, reports, and dashboards.
- Share the results – you can use Power BI to share the results computed in R with everyone in your organization, in the same place where all of the organizational data is displayed.
- Operationalize your R script – once the data source is uploaded to the Power BI service, you can configure a scheduled refresh, which triggers a periodic execution of the R script in its original environment, and refreshes the data if there are any changes. With scheduled refresh, the data displayed is always up to date.
Creating a forecast in R, and consuming it in Power BI
Let’s look at a simple scenario of forecasting.
Since I’m an R user, I already have a local R engine installed, and an existing R script. My R code reads time series data from a local CSV file that contains daily website traffic. It then creates a forecast (using Holt-Winters method) and 2 data frames are created – for actual and predicted data.
Here are the steps:
1) I first run the R script locally in my preferred R environment. I do this just to make sure it executes successfully, all packages are installed, and no interactivity is required from the user.
2) Now that I know my script runs successfully, I open Power BI Desktop and, select Get Data > Others > R Script from the ribbon.
3) The forecast dialog appears. I simply copy and paste my R script into the R script window, and select OK.
4) The script executes on the R which I have previously installed myself on my computer. The resulting data frames are loaded into the data model.
5) Once the data is loaded into Power BI Desktop, I can leverage its full power. For example – I created a line chart showing the prediction.
6) Now I have a report which is built on top of an R script data connection. I will publish it to the Power BI service, and share it with my peers.
Since I expect the input data to the forecast to update daily, I will schedule a refresh over this dataset.
Note: In order to enable refreshing “R Script” connections – confirm you have the latest version of the personal gateway is installed on the computer running the R script, and ensure the personal gateway is configured with the account that has access to the data being refreshed.
I first configure the connection type, as shown in the following image:
And then I schedule the refresh.
Once the refresh is scheduled, every morning at 10:00AM this R script will execute on my local R environment, and the refreshed output will be updated in the Power BI service, so my peers always see the updated results.
To summarize
In this post, you learned how you can now consume the results of R scripts in Power BI Desktop, then share them with your peers using the Power BI service.
You also saw the step-by-step process of how this is done, with an example of time series forecasting.
In future blog posts, we’ll get into more detail (and more cool things you do) with R and Power BI. There is just so much you can do with R – so stay tuned for more on this! If you want more information on R scripts in Power BI Desktop, take a look here.