Dynamic Forecasting with Power BI and R

It's been a while since I put together a blog post, and I figured I'd get back into the groove with a quick post highlighting a simple, but very powerful use case for R in Power BI.

Microsoft recently announced (in preview) the ability to execute R scripts in Power BI to create visualizations.  You can find details of the announcement here and here

Quick Functionality Overview

I'll provide a more detailed overview of the R with Power BI functionality in another post, but to give you the a quick summary, when you select the R visual in Power BI Desktop (1), you can drag and drop fields into a R script editor (2).  The fields then become part of a data frame object that you can then use in an R script to output a R visualization (3).

Using R in Power BI

Using R in Power BI

Being able to use the expansive visualization capabilities from the R language is an obvious win for this new functionality, but there's also a less obvious, and even more intriguing capability here.  In addition to all of the crazy-cool visualizations you can create with R, you can also utilize all of the other statistical, forecasting, and machine learning stuff that R does really well.  You can then output the results of those analytic functions as meaningful visuals right in Power BI.  To make it even more of a game-changer, you can use Power BI's native slicing and cross-filtering capabilities to dynamically feed data into these analytic functions. 

Example

Let's look at a quick example.  Here we will use just a few lines of R code to create a simple forecasting script that can be used in Power BI.  In keeping up with what everyone is most excited about this month (and I'm not talking about what's under the tree), I've used Bing Ads Intelligence to get search volume data for the last 6 weeks for the search terms: Chewbacca, Yoda, and Darth Vader.  You can get a quick look at the data in the screenshot below.

Search data

Search data

Next, I used RStudio to write a quick script that uses a simple forecasting technique involving seasonality and trend decomposition along with exponential smoothing.  The code (with comments) can be seen below. 

##########

library(forecast)

# group searches by date
dataset <- aggregate(Search ~ Date, data = dataset, FUN= sum)

# create a time series based on day of week
ts <- ts(dataset$Search, frequency=7)

# pull out the seasonal, trend, and irregular components from the time series (train the forecast model)
decom <- stl(ts, s.window = "periodic")

# predict the next 7 days of searches
pred <- forecast(decom, h = 7)

# plot the predicted searches
plot(pred)

##########

Now lets jump over to Power BI.  Here, I've loaded the search data into the Power BI model, selected the R visual from the visualizations list, and I dragged all 3 fields from the searchData table into the visual.  You can see the 3 fields shown in the data.frame function in the header of the R script.

Selecting data for the R script

Selecting data for the R script

Next, we will copy-and-paste the code from above into the R script editor over the text "Paste or type your R-script code here". Then we will hit the run button to execute the R script.

Run the R script in Power BI

Run the R script in Power BI

After clicking run, the R script will run using the data in the Power BI model under the current filter context.  Since this is the only visualization I've created and there aren't any additional filters being applied, the forecast was run for all 3 search terms combined.  You can see the output of the forecast along with confidence intervals below.

We can also show the seasonality and trend from the forecast model by modifying the R script from above to plot the "decom" object, and then we can create a second R visualization on the Power BI palette using the modified code.  The code and screenshot of the updated visual is below.

##########

library(forecast)

# group searches by date
dataset <- aggregate(Search ~ Date, data = dataset, FUN= sum)

# create a time series based on day of week
ts <- ts(dataset$Search, frequency=7)

# pull out the seasonal, trend, and irregular components from the time series (train the forecast model)
decom <- stl(ts, s.window = "periodic")

# plot the forecast model
plot(decom)

##########

Forecast along with decomposition

Forecast along with decomposition

Finally, all we have to do to make our forecasting visual dynamic is to add a slicer.  Below, you see the Character field has been added to the palette as a slicer, and I've selected Chewbacca.  You'll notice that both the forecast and decomposition visuals have been updated to reflect a model and prediction based only on Chewbacca searches.

Forecast and decomposition for Chewbacca searches

Forecast and decomposition for Chewbacca searches

Drops Mic

Hopefully this example has illustrated how with just a few lines or R code, you can create a simple, yet powerful, interactive forecasting tool in Power BI.  I'm really excited about the potential of R integration with Power BI as well as R integration across the entire Microsoft data platform.  Look for upcoming posts where I'll explore different use cases and examples of R integration with tools like Azure Machine Learning, SQL Server 2016, and Revolution R Enterprise.