<p><div><div><div><div data-width="100" data-show-faces="false" data-send="true" data-layout="button_count" data-height="21" data-href="http://www.r-bloggers.com/microsoft-analytics-in-2016/"></div></div><div><a data-url="http://www.r-bloggers.com/microsoft-analytics-in-2016/" data-counturl="http://www.r-bloggers.com/microsoft-analytics-in-2016/" data-count="horizontal" data-text="Microsoft Analytics in 2016" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
(This article was first published on <strong><a href="http://feeds.cybaea.net/~r/CybaeaOnR/~3/Lm6qEZ2feAc/"> CYBAEA on R</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>If you had asked me two years ago if Microsoft was a serious vendor for data science and analytics infrastructure and tools, I would have laughed. At the time their offering seemed to me to consist of Excel against SQL Server. There is nothing really wrong (or exciting) about SQL Server, but friends don’t let friends use Excel for data analysis or indeed for anything that matters at all, so that whole proposition was a non-starter. But things have moved on, so how does Microsoft stack up now (mid-2016)? (You can skip right to our <a target="_blank" href="http://feeds.cybaea.net/~r/CybaeaOnR/~3/Lm6qEZ2feAc/#conclusions">conclusions</a> if you are impatient.)</p>
<p>In 2015 Microsoft acquired Revolution Analytics (<a target="_blank" href="https://blogs.technet.microsoft.com/machinelearning/2015/04/06/microsoft-closes-acquisition-of-revolution-analytics/">MSFT</a>, <a target="_blank" href="http://blog.revolutionanalytics.com/2015/04/revolution-analytics-microsoft.html">Revo</a>) which gave them a serious and scalable tool for data science, visualization, and statistical analysis. This is based on the <a target="_blank" href="http://www.r-project.org/">open source R</a> implementation which has massive mind-share making it easily the most popular serious data science tool, but adds easy scalability and enterprise features.</p>
<p>And they announced integration with Apache Spark (Microsoft Azure HDInsight), Azure Machine Learning, and SQL Server, which suddenly looks like a serious platform. Add a <a target="_blank" href="https://blogs.microsoft.com/blog/2015/01/27/microsoft-brings-business-intelligence-mainstream-enhancements-power-bi/">75% reduction in the price of Power BI</a> (and a reasonable free version) and various other enhancements, and it is looking really interesting.</p>
<p>So is it ready for prime-time? I had the opportunity this week to spend four days with the Microsoft teams to find out. We focused on the R environments and integrations and received plenty of hands-on experience.</p>
<h2>Solutions components</h2>
<p>We covered the following solutions components:</p>
<ol>
<li>The scalable <code>rx*</code> model and data processing functions using the XDF file format.</li>
<li>The integration with the <code>dplyr</code> package through <a target="_blank" href="https://github.com/RevolutionAnalytics/dplyrXdf"><code>dplyrXdf</code></a> for streaming data manipulation.</li>
<li>The Apache Spark / HDInsight integration: using essentially the same syntax, but automatically distributed over a Spark cluster.</li>
<li>The Microsoft SQL Server integration: Using the same <code>rx*</code> functions against database tables either from the client or running on the SQL Server.</li>
<li>Running R inside the SQL Server using stored procedures (and <code>sp_execute_external_script</code>).</li>
</ol>
<h3>1. Scalable model and data processing functions</h3>
<p>This is the venerable technology from Revolutions. It provides functions that work transparently on chunks of data which means you have no issues of memory limitations. For data processing the key function is <code>rxDataStep</code> which does all the <a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-data-transform">transformations</a> you would expect and is supported by <code>rxMerge</code> for merging data and <code>rxImport</code> for importing data files. This all works well and was the core offering from Revolutions, along with the XDF file storage format which allows efficient processing of large data.</p>
<p>For data exploration you have comprehensive summaries (including approximate median), contingency tables, and cross tabulations, and there is a small but useful selection of data visualization functions. Models are currently limited to</p>
<ul>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-linear-model">Linear Regression Models</a> (<code>rxLinMod</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-logistic-regression">Logistic Regression</a> (<code>rxLogit</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-generalized-linear-model">Generalized Linear Models</a> (<code>rxGlm</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-decision-tree">Classification and Regression Trees</a> (<code>rxDTree</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-boosting">Stochastic Gradient Boosted Decision Trees</a> (<code>rxBTrees</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-decision-forest">Classification and Regression Decision Forests</a> (<code>rxDForest</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-cluster">K-Means Clustering</a> (<code>rxKmeans</code>)</li>
<li><a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler-user-guide-naive-bayes">Naive Bayes Classifier</a> (<code>rxNaiveBayes</code>)</li>
</ul>
<p>More model types are apparently in the works. I hope to see association rules (the <a target="_blank" href="https://en.wikipedia.org/wiki/Apriori_algorithm">Apriori</a> and Eclat algorithms at least) and anomaly detection (eg one-class support vector machines) at least. I hear rumours that support vector machines (SVM) are coming soon, and of course they provide the infrastructure for you to implement your own models.</p>
<p>Nothing new here compared to the Revolutions offering when you are working on XDF files. What is new is the seamless integration with Spark clusters meaning that you can easily run any of these models on an efficient distributed environment.</p>
<p>The code we went through is here: <a target="_blank" href="https://github.com/Azure/Cortana-Intelligence-Gallery-Content/blob/master/Tutorials/MRS-for-SAS-Users/MRS%20for%20SAS%20Users.md">MRS for SAS Users</a></p>
<h3>2. Streaming data manipulation the <code>dplyr</code> way</h3>
<p>This is the <code>dplyrXdf</code> package which was <a target="_blank" href="https://blogs.technet.microsoft.com/machinelearning/2016/03/16/the-dplyrxdf-package-bringing-data-munging-with-pipes-to-xdf-files-2/">announced</a> about three months ago. At the moment you have to install it from the <a target="_blank" href="https://github.com/RevolutionAnalytics/dplyrXdf"><code>dplyrXdf</code> GitHub</a> page.</p>
<p>I was skeptical at the start but quickly convinced. If you want to quickly compare the syntax you can see the course material <a target="_blank" href="https://github.com/akzaidi/adv-r-tutorials/blob/master/dplyrXdf.ipynb">here</a>. So you get the <code>dplyr</code> syntax, that’s nice. But you get it distributed on a Spark cluster with (almost) no change of code: that’s awesome! (And you don’t have to keep track of your temporary files: that is sometimes useful.)</p>
<h3>3. Microsoft R with Spark on HDInsight</h3>
<p>It is easy to set up (though not <em>quite</em> as easy as Amazon EMR yet); the instructions are <a target="_blank" href="https://github.com/akzaidi/adv-r-tutorials/tree/master/Spark">here</a> but note that this is rapidly changing. The quick summary is that it works really well. The key function here, and for the SQL Server integration, is <code>rxSetComputeContext</code> which is a general function that defines where your computation is taking place. On Spark you have <a target="_blank" href="https://azure.microsoft.com/en-gb/documentation/articles/hdinsight-hadoop-r-server-compute-contexts/">many options</a> but you end up with code along the lines of:</p>
<figure>
<pre><code data-lang="r"><span>computeContext</span><span> </span><span>&lt;-</span><span> </span><span>RxSpark</span><span>(</span><span>consoleOutput</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>,</span><span>
</span><span>nameNode</span><span> </span><span>=</span><span> </span><span>myNameNode</span><span>,</span><span>
</span><span>port</span><span> </span><span>=</span><span> </span><span>myPort</span><span>,</span><span>
</span><span>executorCores</span><span> </span><span>=</span><span> </span><span>6</span><span>,</span><span> </span><span>executorMem</span><span> </span><span>=</span><span> </span><span>&quot;3g&quot;</span><span>,</span><span> </span><span>executorOverheadMem</span><span> </span><span>=</span><span> </span><span>&quot;3g&quot;</span><span>,</span><span> </span><span>persistentRun</span><span> </span><span>=</span><span> </span><span>TRUE</span><span>,</span><span> </span><span>extraSparkConfig</span><span> </span><span>=</span><span> </span><span>&quot;--conf spark.speculation=true&quot;</span><span>)</span><span>
</span><span>rxSetComputeContext</span><span>(</span><span>computeContext</span><span>)</span><span>
</span><span>linmod</span><span> </span><span>&lt;-</span><span> </span><span>rxLinMod</span><span>(</span><span>tip_pct</span><span> </span><span>~</span><span> </span><span>trip_distance</span><span>,</span><span> </span><span>data</span><span> </span><span>=</span><span> </span><span>taxi_xdf</span><span>,</span><span> </span><span>blocksPerRead</span><span> </span><span>=</span><span> </span><span>2</span><span>)</span></code></pre>
</figure>
<p>There is some good documentation here: here: <a target="_blank" href="https://azure.microsoft.com/en-gb/documentation/articles/hdinsight-hadoop-r-server-overview/">Overview of R Server on HDInsight </a></p>
<p>Bottom line: it is easy to use, it works, it scales. But you still have to write good R code, of course.</p>
<h3>4. Microsoft R and SQL Server</h3>
<p>For an overview and to really understand the workflow for this and the following component, this document is the best: <a target="_blank" href="https://msdn.microsoft.com/en-gb/library/mt612857.aspx">Data Science End-to-End Walkthrough</a>.</p>
<p>But briefly you can work in two modes. The first is a transparent use of SQL tables as yet another XDF compatible data source, all processed in chunks and on your local machine. Works well, with the obvious limitations of <a target="_blank" href="https://msdn.microsoft.com/en-us/library/mt590948.aspx">data type compatibility</a> etc. The downside is that all the data has to move from the server to the R instance. But the second mode will fix that: with a suitable invocation of our old friend <code>rxSetComputeContext</code> you can move the computation to the database server.</p>
<p>At the moment, at least, the compute context interface is rather clunky and you end up with lots of local files that created as part of the communications with the server. (See the <code><span>shareDir</span><span>=</span></code> argument of the <a target="_blank" href="https://msdn.microsoft.com/en-us/microsoft-r/scaler/rxinsqlserver"><code>RxInSqlServer</code></a> function.) This is ugly and error-prone.</p>
<p>Currently this only really works with the (Enterprise) SQL Server and not, for example, with the Azure SQL service. Very much work in progress, but give it time.</p>
<h3>5. Running R as stored procedure in SQL Server</h3>
<p>For its final trick, SQL Server is able to run R directly in the server as a stored procedure or similar. The key function is <a target="_blank" href="https://msdn.microsoft.com/en-GB/library/mt604368.aspx"><code>sp_execute_external_script</code></a> which you can include in stored procedures and similar.</p>
<p>In a typical use case, the analyst builds the model using the <code>rx*</code> model functions described above using one of the standard user interfaces and appropriate compute context. She then stores the (serialized) model in the database and the DBA sets up a stored procedure to calculate the scores for new (eg in a trigger) or changed (eg scheduling a stored procedure) data, or when the model changes. In other words, this is mainly a deployment option. It works well.</p>
<h2>Conclusions</h2>
<p>There is much to like here. Microsoft is building an infrastructure and services platform that is robust and scalable, and with a good or great partner ecosystem. They are one of the most interesting technology companies in this space, and I am not the only one who is excited for their vision; <a target="_blank" href="https://info.microsoft.com/CO-AAIoT-CNTNT-FY16-07Dec15-GartnerMQ-AdvancedAnalytics-Register.html">Gartner</a> says something similar.</p>
<p><strong>Who is it for?</strong> This is a technical platform for technically savvy data scientists. If you want something easy to use, go to <a target="_blank" href="http://www.oracle.com/technetwork/database/options/advanced-analytics/odm/overview/index.html">Oracle Data Miner</a> where everything is simple and works almost as if by magic, but you sacrifice some control for this convenience. The <a target="_blank" href="http://www.oracle.com/technetwork/database/enterprise-edition/odm-techniques-algorithms-097163.html">Oracle set of parallel algorithms</a> is more comprehensive than the <code>rx*</code> models described above which in the current implementation in particular lack association rules and integration into text mining, and the integration to R in the Oracle database appears more mature and complete.</p>
<p>But Oracle does not have a simple and consistent interface that works across files, databases, and Spark clusters. And in particular they do not have anything really comparable to Azure and the ecosystem of integrated services you find there to help you build your solution fast and cost-effective. In that sense the nearest competitor is Amazon Web Services which has even more integrated services, many of which are more mature. (For example, it is easy to have R Studio Server pre-configured on the EMR Spark service at AWS but a bit of a hack is needed on Azure.) But it does not have the same integration with Microsoft R Server for the large data set models so you will have some more work to use that.</p>
<p>Microsoft is by no means the only play in town, but they are certainly credible right now for your data and data science infrastructure and they are an interesting company to watch.</p>
<p><img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/CybaeaOnR/~4/Lm6qEZ2feAc"></p>
<p><div><div><div><div data-width="55" data-show-faces="false" data-send="true" data-layout="box_count" data-height="62" data-href="http://www.r-bloggers.com/microsoft-analytics-in-2016/"></div></div><div><a data-url="http://www.r-bloggers.com/microsoft-analytics-in-2016/" data-counturl="http://www.r-bloggers.com/microsoft-analytics-in-2016/" data-count="vertical" data-text="Microsoft Analytics in 2016" class="twitter-share-button" data-via="rbloggers" href="https://twitter.com/share"></a></div><div></div></div><div></div></div>
<div>
<div>To <strong>leave a comment</strong> for the author, please follow the link and comment on their blog: <strong><a href="http://feeds.cybaea.net/~r/CybaeaOnR/~3/Lm6qEZ2feAc/"> CYBAEA on R</a></strong>.</div>
<hr>
<a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers.com</a> offers <strong><a rel="nofollow" href="http://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="http://www.r-project.org/">R</a> news and <a title="R tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=tutorial">tutorials</a> on topics such as: <a title="Data science" rel="nofollow" href="http://www.r-bloggers.com/?s=data%20science">Data science</a>, <a title="Big Data" rel="nofollow" href="http://www.r-bloggers.com/?s=Big%20Data">Big Data, <a title="R jobs" rel="nofollow" href="http://www.r-users.com/">R jobs</a>, visualization (<a title="ggplot and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=ggplot2">ggplot2</a>, <a title="Boxplots using lattice and ggplot2 tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=boxplot">Boxplots</a>, <a title="Maps and gis" rel="nofollow" href="http://www.r-bloggers.com/?s=map">maps</a>, <a title="Animation in R" rel="nofollow" href="http://www.r-bloggers.com/?s=animation">animation</a>), programming (<a title="RStudio IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=RStudio">RStudio</a>, <a title="Sweave and literate programming" rel="nofollow" href="http://www.r-bloggers.com/?s=sweave">Sweave</a>, <a title="LaTeX in R" rel="nofollow" href="http://www.r-bloggers.com/?s=LaTeX">LaTeX</a>, <a title="SQL and databases" rel="nofollow" href="http://www.r-bloggers.com/?s=SQL">SQL</a>, <a title="Eclipse IDE for R" rel="nofollow" href="http://www.r-bloggers.com/?s=eclipse">Eclipse</a>, <a title="git and github, Version Control System" rel="nofollow" href="http://www.r-bloggers.com/?s=git">git</a>, <a title="Large data in R using Hadoop" rel="nofollow" href="http://www.r-bloggers.com/?s=hadoop">hadoop</a>, <a title="Web Scraping of google, facebook, yahoo, twitter and more using R" rel="nofollow" href="http://www.r-bloggers.com/?s=Web+Scraping">Web Scraping</a>) statistics (<a title="Regressions and ANOVA analysis tutorials" rel="nofollow" href="http://www.r-bloggers.com/?s=regression">regression</a>, <a title="principal component analysis tutorial" rel="nofollow" href="http://www.r-bloggers.com/?s=PCA">PCA</a>, <a title="Time series" rel="nofollow" href="http://www.r-bloggers.com/?s=time+series">time series</a>, <a title="finance trading" rel="nofollow" href="http://www.r-bloggers.com/?s=trading">trading</a>) and more...
<img height="1" alt="" width="1" src="http://feeds.feedburner.com/~r/RBloggers/~4/WWV6fZNJHNM"></a></div>