This is the GitHub repo for the Best Practices for Creating Stunning R Visuals in Power BI* session given at PASS Summit 2020. The READE contains some instructions on how to configure your environment to use R in Power BI. Each configuration task is clearly identified with headings.
The recommended distribution for R is Microsoft R Open (MRO). This distribution of R is free and totally compatible with the distribution on CRAN. It comes with enhancements and performance improvements. Perform the following steps to download the MRO:
- Get the version of R that is being used in the Power BI service. You can find that information in the following Microsoft documentation: https://docs.microsoft.com/en-us/power-bi/visuals/service-r-visuals.
- Open up a browser in the DSVM and go to the following site: https://mran.microsoft.com/open. Two browsers are pre-installed in the DSVM. They are Microsoft Edge and Firefox.
- Click on the Download button on the right and you will be taken to the download page. Once on the download page, click on the Past Releases link which is located on the right section of the page. Clicking on the link will take you to a page that has links to all previous versions of Microsoft R Open. Click on the link for the version that the Power BI service is using.
- Choose the download for Windows.
- Execute the download
- Open R Studio in the DSVM
- Select Tools > Global Options. Verify that the MRO distribution you just installed is selected. If it is not, click on the Change… button and you should see it as one of the options. Select it then click Ok.
The R Studio IDE is the most popular IDE for R. It is equiped with many features that facilitates R development. R Studio is available as a free download at https://rstudio.com/products/rstudio/download/.
As of writing, Power BI gives you access to over 900 R packages in the Power BI service. The R packages in the service are updated irregularly and the versions of the packages are often not the most current. Given that, it is important to know what version of the package is being used in the service to ensure what you develop can be supported when deployed. Go to the following URL to get the list of R packages available in the services along with their version: https://docs.microsoft.com/en-us/power-bi/connect-data/service-r-packages-support#r-packages-that-are-supported-in-power-bi.
The install.packages() function is used to install packages in R. Use the following code to install a single packages such as tidyverse:
install.packages("tidyverse")You can also install multiple packages at once. Use the following code to install tidyverse and RODBC together:
pkgs <- c("tidyverse","RODBC")
install.packages(pkgs)Perform the following steps to install a specific version of a package:
- Go to the CRAN Package Archive at this URL: https://cran.r-project.org/src/contrib/Archive/
- Locate the package you want to download. Once you find it, click into the folder for that package to get the list of versions that are archived for that package. Copy the URL of the package you want to download.
- Assign the URL to a variable and used it with the install.package() to download the package. Below is the code you would use to download dplyr 1.0.0:
packageurl <- "https://cran.r-project.org/src/contrib/Archive/dplyr/dplyr_1.0.0.tar.gz"
install.packages(packageurl, repos=NULL, type="source")You need to do configure Power BI to use R. To configure Power BI to use R, go to File > Options and Settings > Options > R Scripting. Doing so will bring up the form you see below. Configure the form to pick the R distribution you want to use and the IDE you want to use. An example of what the form looks like filled out is pictured below:
As you can see, there is a considerable amount of configuration you need to do to setup your environment to use R in Power BI. The situation is made much easier if you leverage the Azure Data Science Virtual Machine. It comes pre-configured with many tools popular in data science including Power BI, R, and R Studio which is needed for this session. Perform the following steps to create an instance of Azure Data Science Virtual Machine:
Steps to create a DSVM in Azure
- Go to https://portal.azure.com. If prompted, sign in using the credentials created in step 1
- Click Create a resource in the upper left
- In the search box type Data Science Virtual Machine - Windows 2019
- Click the Create button. This action will cause a form to appear that you need to feel out to configure the DSVM. You will land on the Basics tab. The following steps will tell you how to feel out the form.
- Subscription: Select the subscription you want to use. It should default to the subscription that you set up in step 1.
- Resource group: If you already have a resource group in your tenant you want to use select it. Otherwise create a new one for your DSVM">
- Virtual machine name: The name you want your DSVM to have
- Region: An Azure region close to you
- Image: Make sure Data Science Virtual Machine – Windows 2016 is selected
- Size: I use B4ms because it is the cheaper option for the 16 gigs of ram options. RAM is important for R, Python, and Power BI.
- Username: <"Create a username">
- Password: <"Create password">
- Confirm password: <"Confirm password">
- Click Next: Disks >
- OS disk type: Select Standard SSD. This option is sufficient for what we are doing
- Click Next: Networking>
- Make sure all the required fields are filled out. You can identify the required fields with a *. They should be populated with a default value if not click the Create New link below them and accept the default settings.
- Click Next: Management >
- Accept defaults and click Next: Advanced >
- Accept defaults and click Next: Tags >
- Click Next: Review + create >.
- You will see a summary of the DSVM that you configured. You will also see the cost to run the DSVM. If you agree with the configuration click the Create button.