powerobjects: Enabling R Scripts in Power BI Desktop

[Blog bot]

Источник: https://www.powerobjects.com/2018/11...ipts-power-bi/
==============


Since the September 2016 release, Power BI has enabled users to extend Power BI’s out-of-the box functionality utilizing R Scripts. But what is R? It is an open-source statistical data programming language that allows you to manipulate and display complex data sets on the fly. R is an extremely powerful tool, but because of its level of complexity, it can also be time consuming.

In today’s blog, we’ll be describing the capabilities of R visuals and begin exploring ways in which using R can enhance a user’s experience in Power BI. To do this, we need to:

1. Discuss the benefits of R

2. Enable R Scripting

3. Install R and R Studio

4. Configure Power BI to use an external IDE

5. Customize your R Script to use Power BI Redmonder

Let’s begin…

Discuss the benefits of R


The benefits of R can be divided into 2 main categories: Analytics and Reporting.

Analytics: For Dynamics 365 CRM and ERP users, R scripts can be incredibly useful in solving real-world problems through statistical analysis. Microsoft MVP, Tomaz Kastrun, describes a situation where he has used R and Power BI to comb through 500 million historical invoices taken from Dynamics NAV to identify fraudulent records. Another application of R in D365 is being able to test your forecasts against a mean-squared error (MSE). One thing to keep in mind is that scripts can be re-used with little to no modification. If either of the above examples are of interest, please reference this link to see how you can apply R scripts within your organization and D365 environment.

Reporting: In terms of reporting, one of the big questions is when to use R versus the countless built-in visualizations already available within the Power BI Desktop. Use cases for R visualizations:

• Data mining techniques – Data mining is the process of finding anomalies, patterns, and correlations within large data sets to predict outcomes.
• Hierarchal clustering – An analysis technique that groups similar objects into groups called clusters.
• Transforming data post-load – The standard process of extracting data from source systems is Extraction, Transformation, and Loading (ETL). Often, there’s a need for transforming the data after it has ben loaded into the data warehouse. R allows you to make data transformations on the fly, such as converting a data type.
• Facets to add extra dimensions to a base plot – Facet grids allow you to split a plot into a matrix of panels that all share a set value. See below for an example:

To find out more about the above use cases, please see the Additional R Resources section at the bottom of this post.

Enable R Scripting


Upon opening Power BI Desktop, select R script visual under the VISUALIZATIONS section:



The following message will appear asking you to “Enable script visuals.” Review the terms and select Enable.



Install R and R Studio


At its current state, Power BI does not have built-in R scripting. To utilize the functionality, we need to install the R library, any R packages we wish to use in development, and an IDE for development.

Installing R

1. Navigate to either Microsoft R Application Network or CRAN Repository

2. Download the latest version for your OS (version 3.5.1 at the writing of this post)

3. Save the downloaded file to your computer and run “microsoft-r-open-3.5.1.exe”

4. Choose a destination folder and ensure Microsoft R Open, MRO, MKL, and Visual C/C++ Runtime 2015 are all selected

5. Select Continue and complete the installation



Installing R Studio

1. Navigate to R Studio

2. Download the latest version for your OS (version 1.1.456 at the writing of this post)

3. Save the downloaded file to your computer and run “RStudio-1.1.456.exe”

4. Choose a destination folder

5. Select Next and complete the installation



Configure Power BI to use an external IDE


Now that we have R and an IDE installed, we can configure Power BI to launch R Studio when scripting.

Configuring BI:

1. Launch Power BI Desktop

2. Go to: File > Options and Settings > Options > R scripting

3. Under “Detected R IDEs”, select R Studio



When creating a custom dashboard, it’s best practice to have a consistent theme throughout. When introducing R visuals, this gets a bit tricky, since R does not have built-in PBI files, which means that for each visual, we need to enter custom RBG values to get it to match up with our existing color pallet. We will be leveraging Microsoft, which recognizes people in the community through publishing exceptional R scripts that leverage Power BI. They can be found under the R Script Showcase section of the Microsoft Community site.

Customize your R Script to use Power BI Redmonder

1. Navigate to R Studio

2. In the command line, enter install.packages(c(“gdata”,”ggmap”, “ggplot2”, “Redmonder”)) to install the following packages:

• gdata – Various R programming tools for data manipulation
• Ggmap – Makes it easy to retrieve raster map tiles from popular online mapping services like Google Maps, OpenStreetMap, Stamen Maps, and plot them using the ggplot2 framework
• ggplot2 – A system for ‘declaratively’ creating graphics, based on “The Grammar of Graphics.” You provide the data, tell ‘ggplot2’ how to map variables to aesthetics and what graphical primitives to use, and it takes care of the details.
• Redmonder – Provides color schemes for maps (and other graphics) based on the color palettes of several Microsoft products. Forked from ‘RColorBrewer’ v1.1-2.

3. Once all packages have been installed, you’re ready to create your first R Script

4. First, we need some data:



5. Create your dataset with the following values and name it anything



6. Create a new tile and select the R visual



7. Select the data we just created



8. Type the following R Script and select Run:




You will see that that the bar graph is gray scale and may not go well with the rest of your visuals. Rather than color coding each individual bar, recall that we have downloaded the R package, “Redmonder.” This allows us to select from various default Microsoft Power BI color templates from directly within our R Script.



9. Type the following and select Run:



Note that the bar graph is now themed to the standard Power BI color schema:



Now that you’ve made your first R tile within Power BI and learned how to import additional R packages, check out what else the Power BI community has to offer at Microsoft’s R Script Showcase.

Additional R Resources

• Run R Scripts in Power BI: https://docs.microsoft.com/en-us/pow...ktop-r-scripts
• Showcase Visuals: https://community.powerbi.com/t5/R-S.../bd-p/RVisuals
• Hierchical Clustering in R: https://www.r-bloggers.com/hierarchi...tering-in-r-2/
• R Data Mining: http://www.rdatamining.com/
• ERP and CRM applications of R: https://msdynamicsworld.com/story/r-...take-advantage
• Redmonder Manual: https://cran.microsoft.com/web/packa.../Redmonder.pdf
• Littler Command Line: http://dirk.eddelbuettel.com/code/littler.html
• ggmap: https://cran.r-project.org/web/packa...ap/README.html
• gdata: https://cran.r-project.org/web/packa...ata/index.html
• ggplot2: https://cran.r-project.org/web/packa...ot2/index.html
• Facets: https://ggplot2.tidyverse.org/reference/facet_grid.html
• Redmonder: https://cran.r-project.org/web/packa...der/index.html
• Example R Datasets: http://www.sthda.com/english/wiki/r-built-in-data-sets
• ERP and CRM users utilizing R: https://msdynamicsworld.com/story/r-...take-advantage
• Regression prediction and MSE measure: https://tomaztsql.wordpress.com/2016...d-mse-measure/

We hope this was helpful for you. Get in there and try it out! For more Dynamics 365 tips – be sure to subscribe to our blog!

Happy D365’ing!



Источник: https://www.powerobjects.com/2018/11...ipts-power-bi/