<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="https://www.r-bloggers.com/is-it-possible-to-use-revoscaler-package-in-power-bi/"></div></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/"> R – TomazTsql</a></strong>, and kindly contributed to <a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>I was invited to deliver a session for <a target="_blank" rel="nofollow" href="http://dataminds.be/events/event/sql-server-2016-and-microsoft-r-server/">Belgium User Group on SQL Server and R integration</a>. After the session – which we did online using web based Citrix  – I got an interesting question: “<em>Is it possible to use RevoScaleR performance computational functions within Power BI?</em>“. My first answer was,  a sceptical yes. But I said, that I haven’t used it in this manner yet and that there might be some limitations.</p>
<p>The idea of having the scalable environment and the parallel computational package with all the predictive analytical functions in Power BI is absolutely great. But something tells me, that it will not be that straight forward.</p>
<p>So let’s start by taking a large (500 MB) txt file and create XDF file:</p>
<pre>library(RevoScaleR)
file.name &lt;- &quot;YearPredictionMSD.txt&quot;
rxOptions(sampleDataDir = &quot;C:\\Files&quot;)
sampleDataDir</pre>
<p>File is available on-line at <a target="_blank" rel="nofollow" href="https://archive.ics.uci.edu/ml/datasets/YearPredictionMSD">this address</a> with the <a target="_blank" rel="nofollow" href="https://archive.ics.uci.edu/ml/machine-learning-databases/00203/">zip file</a>.</p>
<h2>Getting data with R script</h2>
<p>Open Power BI and choose Get Data -> R Script -> and copy/Paste the following slightly changed code:</p>
<pre>library(RevoScaleR)
file.name &lt;- &quot;YearPredictionMSD.txt&quot;;
rxOptions(sampleDataDir = &quot;C:\\Files&quot;);
sampleDataDir</pre>
<p>With copy pasting and clicking OK,</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/2017-03-20-18_56_17-untitled-power-bi-desktop.png?w=456" data-orig-size="933,601" data-image-title="2017-03-20 18_56_17-Untitled – Power BI Desktop" class="wp-image-3961" data-comments-opened="1" data-recalc-dims="1" data-attachment-id="3961" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/2017-03-20-18_56_17-untitled-power-bi-desktop/#main" alt="2017-03-20 18_56_17-Untitled - Power BI Desktop" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/2017-03-20-18_56_17-untitled-power-bi-desktop.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/2017-03-20-18_56_17-untitled-power-bi-desktop.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/2017-03-20-18_56_17-untitled-power-bi-desktop.png?w=456"></p>
<p>You will have to wait for the data to be read into the memory, the data models to be created and after monitoring the memory consumption and patiently waiting, you will notice, that this particular dataset (500 MB or 160 MB XDF), that minimum 3 GB of RAM will be consumed and you will end up with preview:</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/4-2017-03-20-19_01_53.png?w=456" data-orig-size="857,300" data-image-title="4 – 2017-03-20 19_01_53-" data-comments-opened="1" class="wp-image-3968" data-recalc-dims="1" data-attachment-id="3968" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/4-2017-03-20-19_01_53/#main" alt="4 - 2017-03-20 19_01_53-" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/4-2017-03-20-19_01_53.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/4-2017-03-20-19_01_53.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/4-2017-03-20-19_01_53.png?w=456"></p>
<p>By now, you will also notice that after saving this Power BI document, it will take somewhere up to 700 MB of your disk space and all the data visualization will consume additional RAM and time. After you will close the Power BI document, you will notice a lot of RAM being released.</p>
<h2>Using R Script in the visuals</h2>
<p>When you create a new Power BI document, I will create new dataset by Entering data. I will create three “dummy” variables.</p>
<p><img sizes="(max-width: 446px) 100vw, 446px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/7-2017-03-20-19_18_23.png?w=456" data-orig-size="446,158" data-image-title="7 – 2017-03-20 19_18_23-" data-comments-opened="1" class="wp-image-3978" data-recalc-dims="1" data-attachment-id="3978" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/7-2017-03-20-19_18_23/#main" alt="7 - 2017-03-20 19_18_23-" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/7-2017-03-20-19_18_23.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/7-2017-03-20-19_18_23.png?w=456?w=446" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/7-2017-03-20-19_18_23.png?w=456"></p>
<p>With these three variables I will try to inject the data returned from XDF data format and have data represented in Power BI.</p>
<p>After selecting the new visual and choosing R visual, I inserted following code:</p>
<pre>library(RevoScaleR)
file.name &lt;- &quot;YearPredictionMSD.txt&quot;;
rxOptions(sampleDataDir = &quot;C:\\Files&quot;);
sampleDataDir</pre>
<p>And this time, the result is fascinating. R is plotting histogram in a split of a second, simply meaning it takes advantage of XDF file and inject it to Power BI.</p>
<p><img sizes="(max-width: 598px) 100vw, 598px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/8-2017-03-20-19_26_47-untitled-power-bi-desktop.png?w=450#038;h=586" data-orig-size="846,829" data-image-title="8 – 2017-03-20 19_26_47-Untitled – Power BI Desktop" data-comments-opened="1" class="wp-image-3986" data-recalc-dims="1" data-attachment-id="3986" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/8-2017-03-20-19_26_47-untitled-power-bi-desktop/#main" alt="8 - 2017-03-20 19_26_47-Untitled - Power BI Desktop" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/8-2017-03-20-19_26_47-untitled-power-bi-desktop.png?w=450#038;h=586?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/8-2017-03-20-19_26_47-untitled-power-bi-desktop.png?w=450#038;h=586?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/8-2017-03-20-19_26_47-untitled-power-bi-desktop.png?w=450#038;h=586"></p>
<p>This is still – an outer file or dataset -, that Power BI does not have a clue about. Meaning, no slicers are available for dynamic change of the user selection.</p>
<p>Let’s try to insert the data into those three dummy variables, where the third one will be a factor that I have to pre-prepare. Since in this case factor is Year, it is relatively easy to do:</p>
<pre>library(RevoScaleR)
library(gridExtra)
library(dplyr)
Year % filter(year == c(&quot;2000&quot;,&quot;2001&quot;,&quot;2002&quot;)))
grid.table(df_f %>% filter(year == Year))</pre>
<p>Once I have this inserted in new R visualize, I just need to add a dummy slicer.</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/9-2017-03-20-20_52_52-revoscale_and_powerbi-power-bi-desktop.png?w=456" data-orig-size="871,579" data-image-title="9 – 2017-03-20 20_52_52-RevoScale_and_PowerBI – Power BI Desktop" data-comments-opened="1" class="wp-image-4001" data-recalc-dims="1" data-attachment-id="4001" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/9-2017-03-20-20_52_52-revoscale_and_powerbi-power-bi-desktop/#main" alt="9 - 2017-03-20 20_52_52-RevoScale_and_PowerBI - Power BI Desktop" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/9-2017-03-20-20_52_52-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/9-2017-03-20-20_52_52-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/9-2017-03-20-20_52_52-revoscale_and_powerbi-power-bi-desktop.png?w=456"></p>
<p>Now, I can easily change the years for my cross-tabulation (using rxCrosstab function). Since calculation is comprehended in the back on the whole dataset and using dplyr package just to omit or filter the results, it is also possible to use rxDatastep:</p>
<pre>rxDataStep(inData=outputFile, outFile=&quot;C:\\Files\\YearPredictMSD_Year.xdf&quot;, 
             overwrite=TRUE, transforms=list(LateYears = V1 > 1999))
rxCrossTabs(V2~F(LateYears), data = &quot;C:\\Files\\YearPredictMSD_Year.xdf&quot;)</pre>
<p>In this way, you will be creating new XDF file through PowerBI with the transformation. Bear in mind, that this step might take some extra seconds to create new variable or to make a subset, if you would need. Again, this is up to  you to decide, based on the file size.</p>
<h2>Using SQL Server procedure with R Script</h2>
<p>This approach is not that uncommon, because it has been proven that using Stored Procedures with T-SQL and R code is useful and powerful way to use SQL Server and R integration within SSRS.  Changing the computational context is sure another way to make a work around.</p>
<p>Creating Stored procedure:</p>
<pre>CREATE PROCEDURE [dbo].[SP_YearMSD_CrossTab]
AS
BEGIN
    DECLARE @RScript nvarchar(max)
        SET @RScript = N'
                library(RevoScaleR)
                sampleDataDir</pre>
<p>Or by copying the T-SQL Code into the SQL Server Data Source, the result is the same.</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/10-2017-03-20-21_51_04-revoscale_and_powerbi-power-bi-desktop.png?w=456" data-orig-size="802,729" data-image-title="10 — 2017-03-20 21_51_04-RevoScale_and_PowerBI – Power BI Desktop" data-comments-opened="1" class="wp-image-4037" data-recalc-dims="1" data-attachment-id="4037" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/10-2017-03-20-21_51_04-revoscale_and_powerbi-power-bi-desktop/#main" alt="10 -- 2017-03-20 21_51_04-RevoScale_and_PowerBI - Power BI Desktop" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/10-2017-03-20-21_51_04-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/10-2017-03-20-21_51_04-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/10-2017-03-20-21_51_04-revoscale_and_powerbi-power-bi-desktop.png?w=456"></p>
<p>In both cases, you should have a cross-tabulational  representation of XDF dataset within Power BI. And now you can really use all the advantages of Power BI visuals, Slicers and as well any additional R predictions.</p>
<p><img sizes="(max-width: 700px) 100vw, 700px" data-image-description="" data-orig-file="https://tomaztsql.files.wordpress.com/2017/03/12-2017-03-20-21_54_42-revoscale_and_powerbi-power-bi-desktop.png?w=456" data-orig-size="1111,488" data-image-title="12 — 2017-03-20 21_54_42-RevoScale_and_PowerBI – Power BI Desktop" data-comments-opened="1" class="wp-image-4043" data-recalc-dims="1" data-attachment-id="4043" data-permalink="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/12-2017-03-20-21_54_42-revoscale_and_powerbi-power-bi-desktop/#main" alt="12 --- 2017-03-20 21_54_42-RevoScale_and_PowerBI - Power BI Desktop" data-medium-file="https://tomaztsql.files.wordpress.com/2017/03/12-2017-03-20-21_54_42-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=300" data-large-file="https://tomaztsql.files.wordpress.com/2017/03/12-2017-03-20-21_54_42-revoscale_and_powerbi-power-bi-desktop.png?w=456?w=700" data-image-meta="{&quot;aperture&quot;:&quot;0&quot;,&quot;credit&quot;:&quot;&quot;,&quot;camera&quot;:&quot;&quot;,&quot;caption&quot;:&quot;&quot;,&quot;created_timestamp&quot;:&quot;0&quot;,&quot;copyright&quot;:&quot;&quot;,&quot;focal_length&quot;:&quot;0&quot;,&quot;iso&quot;:&quot;0&quot;,&quot;shutter_speed&quot;:&quot;0&quot;,&quot;title&quot;:&quot;&quot;,&quot;orientation&quot;:&quot;0&quot;}" src="https://tomaztsql.files.wordpress.com/2017/03/12-2017-03-20-21_54_42-revoscale_and_powerbi-power-bi-desktop.png?w=456"></p>
<p>There is a slight minus to this (if not all) approaches like this. You need to have many stored procedures or queries having generated like this. Also rxCube will help you to some extent, but repetitive work will not be avoided.</p>
<h2>Using HDInsight or Hadoop?</h2>
<p>Using XDF data files stored in HD-Insight or in Hadoop would generaly mean using same dataset and step as for SQL Server procedure. Just that you would need to – prior to executing T-SQL script, also change comptutational context:</p>
<pre><code># HD Insight - Spark - Azure
HDInsight mySshUsername <code>= USNM,</code>mySshHostname = HSTNM,
<code></code>mySshSwitches<code>= SWTCH) 
rxSetComputeContext(<span>&quot;</span>HDInsight<span>&quot;</span>)
## Hadoop
Hadoop mySshUsername <code>= USNM,</code>mySshHostname = HSTNM,
<code></code>mySshSwitches<code>= SWTCH)
rxSetComputeContext(<span>&quot;</span>Hadoop<span>&quot;</span>)</code></code></code></pre>
<h2>Verdict</h2>
<p>I have explored couple of ways how to use the Power BI visuals and environment with RevoScaleR XDF (eXternal Data Frame) datafiles. I have to admit, I was surprised that there will be a way to do it in a relatively easy way, but from data scientist perspective, it is still some additional load and work before you can start with actual data analysis. Last two approaches (R script in Visuals and SQL Server Procedures) are by far the fastest and also take the advantage of using parallel and distributed computations that RevoScaleR package brings.</p>
<p>I would very strongly advise Microsoft and Power BI development team to add XDF plug-in to Power BI. Plug-in would work with metadata presentation of the data each time the computations should be used, the metadata would push the code against R Server to have results returned. This would, for sure be a great way to bring Big Data concept to Power BI Desktop.</p>
<p>As always, code and samples are available at <a target="_blank" rel="nofollow" href="https://github.com/tomaztk/Using_RevoScaleR_in_PowerBI">GitHub</a>.</p>
<p>Happy coding!</p>
<p> <a target="_blank" rel="nofollow" href="http://feeds.wordpress.com/1.0/gocomments/tomaztsql.wordpress.com/3927/"><img alt="" src="http://feeds.wordpress.com/1.0/comments/tomaztsql.wordpress.com/3927/" border="0"></a> <img alt="" src="https://i1.wp.com/pixel.wp.com/b.gif?resize=1%2C1&ssl=1" border="0" data-recalc-dims="1"></p>
<p>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="https://tomaztsql.wordpress.com/2017/03/20/is-it-possible-to-use-revoscaler-package-in-power-bi/"> R – TomazTsql</a></strong>.</div>
<hr>
<a rel="nofollow" href="https://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="https://feedburner.google.com/fb/a/mailverify?uri=RBloggers">daily e-mail updates</a></strong> about <a title="The R Project for Statistical Computing" rel="nofollow" href="https://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="https://www.r-bloggers.com/search/data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="https://www.r-bloggers.com/search/Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="https://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="https://www.r-bloggers.com/search/map">maps</a>, <a title="Animation in R" rel="nofollow" href="https://www.r-bloggers.com/search/animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="https://www.r-bloggers.com/search/sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="https://www.r-bloggers.com/search/LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="https://www.r-bloggers.com/search/SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="https://www.r-bloggers.com/search/eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="https://www.r-bloggers.com/search/git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="https://www.r-bloggers.com/search/hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="https://www.r-bloggers.com/search/Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="https://www.r-bloggers.com/search/regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="https://www.r-bloggers.com/search/PCA">PCA</a>, <a title="Time series" rel="nofollow" href="https://www.r-bloggers.com/search/time+series">time series</a>, <a title="finance trading" rel="nofollow" href="https://www.r-bloggers.com/search/trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/kB94niffqns"></a></div>