How To Predict Values From A
Custom R Model in Power BI
Luca Zavarella Follow
Apr 10 · 7 min read
If you usually develop predictive models in R for your customers, you might
need to provide them with a practical GUI to test their model. A really simple
and convenient way would be to provide the users with as many sliders
and/or combo boxes as the input features of the model, and a simple label
showing the predicted value. The rst obvious choice for a R developer would
be a Shiny App. But if your customer IT infrastructure is Microsoft-centric, a
Power BI report could be the best choice.
Our Test Predictive Model
First of all, let’s create an R model to test with the Power BI report. We’ll use
the mtcars dataset, which is a dataset that was extracted from the 1974
Motor Trend US magazine, and comprises fuel consumption and 10 aspects of
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
automobile design and performance for 32 automobiles (1973–74 models).
After an in-depth analysis you can nd here, the nal model to t will be the
following:
finalfit <- lm(mpg ~ wt+qsec+factor(am), data = mtcars)
We could load the training data into the Power BI model to do the training
phase into the report. But in this way every time an input parameter is
changed, a model training is needed, losing the real-time e ect of the
prediction. The best thing to do is using the already trained model into the
Power BI report. So, let’s serialize it on the le system:
1 project_folder <- "Z:/<your_folder>/"
2
3 finalfit <- lm(mpg ~ wt+qsec+factor(am), data = mtcars)
4
5 saveRDS(finalfit, file = paste0(project_folder, "[Link]"))
mtcars_model_fitting.R hosted with ❤ by GitHub view raw
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
Let’s Set The Power BI Report
In order to execute the R code from Power BI Desktop, you need to have an R
engine installation. If you already use RStudio with CRAN R, your Power BI
Desktop options for R scripting would be something like this:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 1 — Power BI Options for R Scripting
If you want to use the Microsoft R Open engine, make sure to have it installed
on your machine and then change the R home directory properly.
The integration of the R language into Power BI Desktop is really e ective.
You can use an R script to:
• Import Data
• Transform Data
• Create Visualizations
You can also import R-based Custom Visuals. For more information, you can
read this blog post.
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
For our goal we will surely need an R Script Visualization to predict the target
value from the model and then show it as text. Numerical feature can be
entered via What-If parameters. Categorical ones can be entered via the OK
Viz Smart Filter (just download the pbiviz le and import it).
Load the R model
Since we serialized our model in a RDS le, we need to load it rst and then
unserialize it into our report. If we did both these steps into the R Script
Visualization, we would have an I/O operation (the load step) every time we
modify an input value through a slicer or a combo box. In order to minimize
the I/O operations and therefore have better prediction performance, it is
better to load our predictive model from disk once (in an import data phase at
the opening of the Power BI report) and then make just our predictions into
the R Script Visualization.
Power BI can load data via R scripts only if it is of [Link] or [Link]
types (look at here for details). So, how can we load a serialized model into
Power BI?
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
The trick is to serialize the model in a string of bytes and
then store it in a dataframe.
So, after clicking on Get Data → More…, just search for “script”, select “R
script” and click on “Connect”:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 2 — Get data from an R script
Now just enter the following code into the popped-out R script window:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
Notice that an absolute path is needed for the project_folder variable.
After clicking “OK” in the previous window, you have to select the model_df
data frame and then click “Load”:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 3 — Load the R dataframe into Power BI
That’s it! The R model is now into our Power BI report.
Adding Slicers and Combo boxes
Now it’s the time to add the visuals to our report. Since the input features are
numerical (wt and qsec) and categorical (am), let’s add two What If
Parameters and one Smart Filter. After clicking on New Parameter in the
Modeling tab:
g. 4 — Create a new What If Parameter
a new dialog window pops up, asking for parameter details. After checking
the min, mean and max values for wt, here the entered values:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 5 — Details for the Weight What If Parameter
When OK is clicked, a new calculated table is added. Rename its objects as it
appears in the following picture:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 6 — Calculated table added with the Weight What If Parameter
The associated slicer will be as following:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 7 — The slicer for the new What If Parameter
Do the same for the qsec parameter:
g. 8 — Details for the qsec What If Parameter
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 9 — Calculated table added with the qsec What If Parameter
In order to create the lter for the am categorical value, rst of all we need to
create a new table entering manual data:
g. 10 — Create a manual table
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 11 — Entering manual data in a table
We can then add a Smart Filter choosing the Transmission value as eld:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 12 — Adding the Smart Filter
g. 13 — The Transmission Smart Filter
Now we have all the controls needed to enter the input parameters. We just
miss the “core” R code to predict values from the model.
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
Adding the R Script Visual
We can use the R Script Visual to unserialize the model and then show the
predicted value as a string in a label. Let’s add it, dragging the following
values in the component:
• model_id
• model_str
• weight
• qsec
• am
The custom visual will be as following:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 14 — Adding the R Script Visual
The code to add to the associated R script editor is the following:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
The resulting label with the prediction is the following:
g. 15 — The predicted value shown in the visual
That’s it! Here the complete report shown during few interactions with it:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 16 — Interacting with the report
A complete pbix le and the model saved as a rds le are available at this link.
Publishing the Report
What if this tool has to be shared between stakeholders? One solution could
be sharing the two les (pbix and model rds les) so that the users could use
them in their laptop. The main issue of this solution is that the path pointing
to the rds le has to be manually changed according to the new position
where the user has downloaded it. So the user has to edit the pbix le (not so
obvious for a common user).
A simpler solution is to publish the report to the Power BI service. The
predictive model is already serialized in the Power BI model, so the
dependency with the external le is removed. The only foresight is to make
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
sure the R packages used in the report are already installed on the Power BI
service. The supported R packages on the service are the ones at this link.
Since we used CRAN standard packages, our report can be easily published.
Just a couple of clicks (pushing the Publish button on the Home tab and
selecting the report destination) and that’s it:
g. 17 — Interacting with the report published on the Power BI service
Obviously, if we try to refresh the underlying report dataset:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 18 — Trying to refresh the dataset
we’ll get an error, since there is no gateway connected to the model le to
retrieve the serialized model:
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
g. 19 — Error after trying to refresh the dataset
You could bypass the error installing a Personal Gateway on the machine
where the RDS le is located and where R is installed.
Just keep in mind that still today (April 2019) R visuals aren’t supported by
the Publish to web feature:
g. 20 — R Visuals are not allowed by Publish to Web
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
If you want Microsoft will take into account this feature in future releases, you
could vote this feature request.
Conclusion
After a Data Scientist has trained a predictive model to solve a business
problem, he has to provide stakeholders the tools to test it. The best tool to do
that is a dynamic one that let them drag sliders and choose categorical values
through lters to see the predicted value in real-time. Power BI reports can
manage all this requirements and they can be easily shared through the Power
BI service too.
Data Science Power Bi Rstats Machine Learning
80 claps 1
Luca Zavarella Follow Towards Data Follow
Medium member since Science
Mar 2019
Sharing concepts, ideas,
Mentor & Technical and codes.
Director @ SolidQ.
Classical pianist in the
free time.
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD
Create PDF in your applications with the Pdfcrowd HTML to PDF API PDFCROWD