D.Roca D.Roca - 1 month ago 17
R Question

Connect R to a SQL Server database engine

At my work I have R-Studio (Microsoft R Open 3.2.5) installed and would like to connect to a Microsoft SQL Server database, to run scripts using the tables I have.

Is there any chance that I can connect to a SQL Server database using Pentaho and then using the object Execute R-Script to make an OLAP Cube?
Do I need a package to connect SQL engine?
What would be the steps to perform?

I already have the snowflake arquitectura of the data base. With the fact table and the state tables. But I do not know where to start.

Answer

You can connect to SQL Server directly from R using at least 4 libraries (RODBC, rsqlserver, RSQLServer, RJDBC).

As long as you have enough RAM, you can import your data into R and do your analysis there using for example amazing dplyr or data.table packages. On the other hand, you can just connect to SQL Server and send SQL queries to the server and do your data wrangling inside your database and then import results to R for further (statistical) analysis and visualization.