THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

BI Beginner: Using R Forecasting Visualization

There are new HTML 5 enabled custom visualization for making interactive R visualizations in Power BI Desktop. Read the documentation and find the download links here. Because of the dependency on R packages that are loaded with the R library command. The end result is that your required R packages end up installed in your machine’s R library. Some R experts, the people who can help you, may have a tendency to be pedantic about exactly what is a library and what is a package. I point out the terminology in the interest of facilitating communication after reading various posts on this topic. Fortunately, Power BI executes the library command for you and simplifies the installation process. However, problems can occur. After the discussion on installation and configuration problems, this post shows you how to use the new forecasting visualization. A cautionary note: It can take several seconds before the R visualization is rendered. Patience is a virtue.

I originally had my Power BI Desktop point to the version of R installed with SQL Server 2016.

image

Figure 1. Navigation to Power BI Desktop Options.

I decided that I wanted the full version of Microsoft’s R distribution and that’s what I recommend you use. After all, I wanted to have the exact same R installation that you would have if you only have Power BI desktop and no installation of SQL Server 2016.

image

Figure 2. Verifying your R home directory.

I went to the official download site for Microsoft R Open. After installing R, I changed the home directory in Power BI Desktop to point to it instead of the version that came with SQL Server 2016. It may not have been necessary, but again, I wanted to have the exact same environment as a business user would have. Most business users will not have SQL Server installed. I did have errors as shown below. I’ve compared the contents of R that comes with SQL Server 2016 to the Microsoft R Open that I installed. It looks like either one should work, but that isn’t what I experienced. I’m going to do further testing. Keep in mind that the primary purpose of this blog post is to show you the capabilities of the Forecasting Visualization and how to use it.

image

Figure 3. R home directory set to the Microsoft R Open download.

Even with all of the Microsoft R Open packages installed, you will need more R packages installed in your library. As you import custom visuals, you may see notices about needing more R packages. All you need to do is click Install to have the R library command issued for you.

image

Figure 4. What you see when a custom visualization has dependencies on R package you don’t have.

Once Power BI finishes installing the required packages for you, it shows you a box telling you what was installed.

image

Figure 5. R packages installed.

If you are still lacking some packages, you’ll see an error message similar to the following when you attempt to use the custom visualization.

image

Figure 6. Missing R packages preventing a custom visualization from working.

For the benefit of search engines. I’ve included the text of some of the error message.

Error Message:
R script error.
Loading required package: XML
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'XML'
2: In libraryRequireInstall("XML") :
  *** The package: 'XML' was not installed ***
Loading required package: htmlwidgets
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'htmlwidgets'
2: In libraryRequireInstall("htmlwidgets") :
  *** The package: 'htmlwidgets' was not installed ***
Loading required package: ggplot2
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'ggplot2'
2: In libraryRequireInstall("ggplot2") :
  *** The package: 'ggplot2' was not installed ***
Loading required package: plotly
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'plotly'
2: In libraryRequireInstall("plotly") :
  *** The package: 'plotly' was not installed ***
Loading required package: scales
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'scales'
2: In libraryRequireInstall("scales") :
  *** The package: 'scales' was not installed ***
Loading required package: forecast
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'forecast'
2: In libraryRequireInstall("forecast") :
  *** The package: 'forecast' was not installed ***
Loading required package: zoo
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'zoo'
2: In libraryRequireInstall("zoo") :
  *** The package: 'zoo' was not installed ***
Loading required package: ggplot2
Warning messages:
1: In library(package, lib.loc = lib.loc, character.only = TRUE, logical.return = TRUE,  :
  there is no package called 'ggplot2'
2: In libraryRequireInstall("ggplot2") :
  *** The package: 'ggplot2' was not installed ***
Error in ets(timeSeries, model = deModel, damped = damped) :
  could not find function "ets"
Execution halted

Once I imported all four custom interactive R visualizations, I was able to get all of them to work. I needed some time series data, so I downloaded gasoline sales data from the U.S. Census Bureau. Specifically, I downloaded this file. It wasn’t formatted and arranged like I needed, so I shaped the data and made it into a two column Excel file that I’ve shared with you as an attachment.

To import a custom visualization for Power BI desktop, follow the steps shown below.

image

Figure 7. Import a custom visual by clicking the three dots as shown.

image

Figure 8. Make sure that the author of the custom visualization is trustworthy!

image

Figure 9. Select your pbviz file.

image

Figure 10. Visualization is installed. Click it to use it.

image

Figure 11. Enable your script visuals.

image

Figure 12. Using the Time Series Forecasting Chart custom visualization.

image

Figure 13. Output from the interactive visualization.

image

Figure 14. Move your mouse cursor to see the interactivity.

image

Figure 15. From that same position, the left mouse button was clicked and held down while selecting a portion of the visualization.

image

Figure 16. Result of zooming in to the selected area.

You are able to configure your visualization by altering the parameters.

image

Figure 17. Forecasting settings are configurable.

Published Sunday, June 25, 2017 4:51 PM by John Paul Cook

Attachment(s): PowerBI20170625.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is a database and Azure specialist who works in Houston. He previously worked as a Data Platform Solution Architect in Microsoft's Houston office. Prior to joining Microsoft, he was a SQL Server MVP. He is experienced in SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. John is also a Registered Nurse who graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. He volunteers as a nurse at a clinic that treats low income and uninsured patients. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2. Connect on LinkedIn

This Blog

Syndication

Privacy Statement