R Script And Power BI: Analytical Harmony
Why is R Script Important in Power BI?
There are many key measures in the realm of data analytics and visualization. Some of which are very well known, e.g. mean, correlation, variance, standard deviations. While some of these are very straight forward and can be done quickly in Power BI, there are some that just need the extra computational power. This is where R Script comes in. R is a statistical computation power-house, and it is completely available at your fingertips in Power BI.
What exactly is R outside of Power BI?
R is one of the most commonly used programming languages for statistics and data science and has been widely used since its inception. It is an open source program, and henceforth it has had many people build and develop packages. This has resulted in a mass of flexible and reliable sources for you to complete your task with ease.
R Script is nearly available right from the get-go in Power BI. All you need to do is install R onto computer that Power BI Desktop is installed and you are off to the races. R is free, so there is no cost for you to begin to learn R.
What tools do I need to enable the use of R Script?
Beyond installing R, no other tools are needed. What will be necessary is practice: R is a language like every other and requires learning its syntax and functionality. The practice is well worth the effort however, since R will open many doors that are otherwise are to access. Best of all is that there are may online resources for you to engage while you are learning R. From advanced courses to YouTube, you will assuredly find the information you are looking for.
Here Are a Few More Utilities That R Provides:
Solution Mobility
R Script in Power BI enables a view of your data like none other. With Microsoft's support of R in their online service, you will experience a unique mobility in your analytics. Utilizing R in your Power BI reports enables you to share your R insights with your company just like any other report.
Fast Solution Deployment
Once you have your script written. Creating the script in Power BI is as simple as copy and paste. The R interface is the perfect place to develop your script. After you have fine tuned it and tested it for your purposes. You can copy your entire script and place it into the Power BI R Script editor.
A Whole New Bank of Custom Visualizations
R is well known for its utility in creating useful visualizations. Working with R Script enables a new layer of development in working with more statistically flavored visuals. This allows for deeper insights and a more enjoyable experience.
Familiar Cross Filtering
R Script visuals update based on cross filtering just as you are familiar. It behaves the same as other Power BI visuals. Your R Script visuals will fit into your report just the same as any other.
Unified Technology
The open source nature of R, along with the vast collective on knowledge available on the internet create a powerful resource. There is a power located in utilizing common tools: and R is among the top when it comes to the most powerful analytic languages.
Solution Re-Usability
Building visuals in Power BI using R Script allows for a versatile report back end. R Scripts can become quite complex, but having the ability to save your scripts and reuse them when you need them is a massive bonus to using R.
What Steps are Required to Work with R Script in Power BI?
Okay, so how can I go about adding R Script to my Power BI reports? We'll answer that by going through a very simple example using R Script.
The most salient unknowns as far as I can tell are:
How do I install R on my computer?
How do I add R visuals to my report?
What kind of functionality is available?
All of the above will be answered in the 9 steps below:
In order to answer these questions, we will go through an example of using R Script for linear regression. Linear regression is one of the most basic machine learning algorithms. The idea behind it, is that, given a plot of points we can find a line of best fit: A line that best represents our data. Once we have this line we can predict further data points using our line of best fit. Now while linear regression is quite a simple use of R I think it helps to start that way. R helps tremendously in stream lining the process. Here are the maths behind linear regression:
These are the equations that build the line of best fit for our regression model. And while they are relatively simple in the way of machine learning models, it is still much easier to use R. Just try and write these using Dax and you will see.
Step-by-Step Instructions
1) Getting Started
Okay, so the first step you are going to nee to do is to download R. Get R here. The rest is a very standard procedure of following install procedures. This only needs to be done once. After you have R installed you can begin to script and include R visuals in your Power BI reports.
2) Create a Simple Dataset
Now that we've gotten R installed, we need a simple data set to implement our regression on. I've just gone a head and created this data by hand to appear somewhat correlated. Note the column labels are important. I will explain shortly, in step 7).
3) Make A Parameter
One of the main reasons for regression is its predictive capabilities. So lets set up a parameter to test our regression. To create a parameter, click the "new parameter" button, under the modeling tab. I changed the range to have a maximum of 10 since that matches my data. Click "Okay" Once you are done.
4) Rename Your Parameter Value
Again, I am emphasizing that it is important that I rename my parameter value. In your fields pane you should see a new table created by your parameter. In that table you will a "Parameter" field, and a "Parameter Value" field. I am renaming "Parameter Value" to NewX. This is to emphasize that this is the value that I will be predicting on. Rename the field by right clicking it in the Fields Pane.
5) Create an R Visual
Next you will need to create an R visual, to do this click the R Script visual in the "Visualizations" pane. Notice how this opens a new blank visual and an R Script editor at the bottom of the report canvas.
6) Add Your Data
Every visual needs data. So lets drag our data into our R visuals fields and observe that our R Script is creating a data-frame for us (more on this soon). It is very important that we don't let Power BI summarize our data before it goes into R. To do this we must right click the ellipses by our data fields in R and choose "Don't Summarize".
7) Paste R Script
Next we've got to give our visual some script to work off of. I created the following for you to copy and paste into your R Script editor. Note how my script refers to specific data names (recall my emphasis on your field names before this step). The R Script below will only work if the field names are matching. This is important: you may need to change the script below if your field names do not match mine (Xaxis, Yaxis, NewX). Copy the sript below into the R Script Editor area while you have the R visual selected. The lines following the '#' are comments so you can try and follow what this script is doing.
#First we attach our data set so we don't have to identify the dataframe before the column name in the frame.
attach(dataset)
#Next we create our model. This creates the linear model Yaxis depending on Xaxis.
model <- lm(Yaxis ~ Xaxis)
#Next we are going to grab our coefficients.
intercept <- summary(model)$coefficients[1,1]
slope <- summary(model)$coefficients[2,1]
#We can manually predict the Y value by utilizing the equation of a line.
predictedY <- slope*NewX[1] + intercept
#Next we create our scatter plot.
plot(Xaxis,Yaxis)
#Add our line for our linear model to our plot.
abline(model, lwd = 0.1)
#Here we add our new predicted point.
points(NewX[1],predictedY, pch = 23, col = "blue", lwd = 10)
#Lastly, we can extract the variance and t value and display them in Power BI through the title.
variance <- summary(model)$coefficients[1,2]
tvalue <- summary(model)$coefficients[1,3]
title <- paste(paste("Scatter Plot with Predicted Value (blue): Variance",variance),paste(" t-Value",tvalue))
title(title)
8) Now We Need to Run Our Script
Now that you have your script pasted into Power BI, all you need to do is run your script by clicking the run button at the top right of the R Script window. Check out our visual!
9) Experiment
Now have a look at our plot. Play around with the parameter. You should notice that your predicted point will move along your line of best fit. Now that is cool! As an additional piece of this script, I've added the variance and t-Value to the title, which are both statistical measures to help validate how well your model works.
What Value Can R Script Add to my Business?
In addition to all of the topics briefly covered earlier, R Script in Power BI can open doors and add value to your business in the following ways:
TIME: When comparing the time to deploy your own complex reports when using R Script, versus forgoing its use. It is clear that the time saved in utilizing R's
COST: In addition to reducing cost as a consequence of saving time. Learning to utilize R Script in your Power BI reports allows you to access techniques and methods that are often only available by contracting analysts to work with you. While this may still be necessary, your preemptive work can save costs in this.
ANALYTICS AVAILABLE: The library of visuals available through R Script in Power BI is truly awesome. While the visuals library on the Power BI Marketplace is quite extensive, the visuals tailored for statistical use with built in analytics are beyond worth the effort. Keep your eyes peeled for more blogs here at FreshBI, where we will go a little deeper into R Script uses in PowerBI.
Our Latest Blogs
About FreshBI
Based in Canada, South Africa and in the United Kingdom, we have helped hundreds of businesses achieve excellence & success through business intelligence.
Power BI runs in our blood, and we are ready to take your business to next level.