<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/working-on-data-warehouse-sql-with-r/"></div></div><div><a data-url="http://www.r-bloggers.com/working-on-data-warehouse-sql-with-r/" data-counturl="http://www.r-bloggers.com/working-on-data-warehouse-sql-with-r/" data-count="horizontal" data-text="Working on Data-Warehouse (SQL) with R" 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://datascienceplus.com/working-on-data-warehouse-sql-with-r/"> DataScience+</a></strong>, and kindly contributed to <a rel="nofollow" href="http://www.r-bloggers.com/">R-bloggers)</a> </div>
<p>SQL being the most popular used query language for deep diving from small data to so called big data. However, there are many other languages like R which are growing in the user community for stats and graphical methodology models and plot data to match the data scientists mind. </p>
<h2>R as an Analytic Application </h2>
<p>R can be used for Data Warehousing and BI applications but R is not commonly being used as a platform for Data Warehouse; instead it works as a mediator for performing focused analysis with database managements.<br>
Experts say that R handles everything in memory, which implies that it might be difficult to use it for expansive volumes of data.</p>
<p>R offers a large number of interfaces to apps such as Excel, SAS etc. which enable users to have a rich experience of the app integrated with the interface. Citing an experts comment on this – “Typically, I see people running their data warehouses in other packages and then using R to perform high-quality, focused analysis”. </p>
<p>R has compatibility with many packages like PL/R, Dplyr, plyr etc. Below we will see how Dplyr works. </p>
<h2>Dplyr – what is Dplyr? (Just for the crazy minds)</h2>
<p>Dplyr is a package which provides tools for manipulating datasets in R. Dplyr is the next iteration of plyr. Dplyr differs greatly from plyr for its easy, intuitive syntax. The major difference in Dplyr is the usage of the operator (%.%), that allows the code to be read in the opposite direction (left to right). If your usage is more towards explorations or if you want to collaborate your data with other data structures you could choose Dplyr. </p>
<h3>Trying data with dplyr – Github</h3>
<pre>install.packages(&quot;dplyr&quot;)</pre>
<p>For examples have the data packages installed </p>
<pre>install.packages(c(&quot;nycflights13&quot;, &quot;Lahman&quot;))</pre>
<p>To learn dyplr please refer to link </p>
<pre>vignette(&quot;introduction&quot;, package = &quot;dplyr&quot;)</pre>
<p>— Source github</p>
<h3>Working on R with PostgreSQL</h3>
<p>In terms of Postgresql it’s commonly called PL/R or madlib as a language for many databases. </p>
<p>To install packages (postgres SQL) for R</p>
<pre>install.packages(&quot;RPostgreSQL&quot;)
library(RPostgreSQL)
<em> package ‘RPostgreSQL’ successfully unpacked and MD5 sums checked </em></pre>
<p>Connect to DB (DW): </p>
<pre>
drv &lt;- dbDriver(&quot;PostgreSQL&quot;)
con &lt;- dbConnect(drv, dbname=&quot;postgres&quot;, host=&quot;10.172.88.12&quot;, user=&quot;gpadmin&quot;,password=&quot;gpadmin&quot;,port=&quot;5432&quot;)
</pre>
<p>Experimenting with Sample retrieval </p>
<pre>
data &lt;- dbGetQuery(con, &quot;select datid,datname from pg_catalog.pg_stat_activity;&quot;)
data[1,1]
<em>
[1] 94888776
</em></pre>
<p>Clearing the result</p>
<pre>dbClearResult(rs)
<em>Function: dbClearResult (package DBI)
res=&quot;PostgreSQLResult&quot;</em>
dbDisconnect(con)
dbUnloadDriver(drv)</pre>
<h2>What is the Future of R?</h2>
<p>When it comes to general data warehousing and BI tasks R as a solution makes it through many companies. Organizations prefer to take R as it’s easy to add existing IT environments or to add them on the current codes which can be used for special analytics in intensive projects. R lacks in state-of-the-art GUI. Hence, users prefer to use more interactive and easy-to-use front ends for R packages.</p>
<p>R has growth potential with more number of packages that help statisticians and analysts use these packages in more interactive ways hence enabling R to answer their research and business goals. </p>
<ul></ul>
<p><strong>
<p>Related Post</p>
<p>
<ol>
<li><a title="Permanent Link toImplementing Apriori Algorithm in R" target="_blank" rel="bookmark" href="http://datascienceplus.com/implementing-apriori-algorithm-in-r/">Implementing Apriori Algorithm in R</a></li>
<li><a title="Permanent Link toHandling missing data with MICE package; a simple approach" target="_blank" rel="bookmark" href="http://datascienceplus.com/handling-missing-data-with-mice-package-a-simple-approach/">Handling missing data with MICE package; a simple approach</a></li>
<li><a title="Permanent Link toBest packages for data manipulation in R" target="_blank" rel="bookmark" href="http://datascienceplus.com/best-packages-for-data-manipulation-in-r/">Best packages for data manipulation in R</a></li>
<li><a title="Permanent Link toIdentify, describe, plot, and remove the outliers from the dataset" target="_blank" rel="bookmark" href="http://datascienceplus.com/identify-describe-plot-and-removing-the-outliers-from-the-dataset/">Identify, describe, plot, and remove the outliers from the dataset</a></li>
<li><a title="Permanent Link toLearn R By Intensive Practice – Part 2" target="_blank" rel="bookmark" href="http://datascienceplus.com/learn-r-by-intensive-practice-part-2/">Learn R By Intensive Practice – Part 2</a></li>
</ol></strong>
<div>
<a target="_blank" href="http://feeds.feedburner.com/~ff/datascienceplus?a=k5frjHhrwsc:MexppE8bGvE:D7DqB2pKExk"><img src="http://feeds.feedburner.com/~ff/datascienceplus?i=k5frjHhrwsc:MexppE8bGvE:D7DqB2pKExk" border="0"></a> <a target="_blank" href="http://feeds.feedburner.com/~ff/datascienceplus?a=k5frjHhrwsc:MexppE8bGvE:qj6IDK7rITs"><img src="http://feeds.feedburner.com/~ff/datascienceplus?d=qj6IDK7rITs" border="0"></a> <a target="_blank" href="http://feeds.feedburner.com/~ff/datascienceplus?a=k5frjHhrwsc:MexppE8bGvE:yIl2AUoC8zA"><img src="http://feeds.feedburner.com/~ff/datascienceplus?d=yIl2AUoC8zA" border="0"></a>
</div>
<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/working-on-data-warehouse-sql-with-r/"></div></div><div><a data-url="http://www.r-bloggers.com/working-on-data-warehouse-sql-with-r/" data-counturl="http://www.r-bloggers.com/working-on-data-warehouse-sql-with-r/" data-count="vertical" data-text="Working on Data-Warehouse (SQL) with R" 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://datascienceplus.com/working-on-data-warehouse-sql-with-r/"> DataScience+</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/1u3AAUx7ezs"></a></div>