How many lines of TSQL codes and Window Functions you need to come up with this?
With R, it takes about 2 commands:
> order <- as.matrix(sqlFetch(ch,"SalesDetails") > summary(order)
To give you an idea, I prepared the dataset into a view like this:
CREATE VIEW SalesDetails AS SELECT soh.OrderDate AS [Date], soh.SalesOrderID, ppc.Name AS Category, pps.Name AS Subcat, pp.Name as Product, SUM(sd.OrderQty) AS Qty, SUM(sd.LineTotal) AS LineTotal FROM Sales.SalesPerson sp INNER JOIN Sales.SalesOrderHeader AS soh ON sp.BusinessEntityID = soh.SalesPersonID INNER JOIN Sales.SalesOrderDetail AS sd ON sd.SalesOrderID = soh.SalesOrderID INNER JOIN Production.Product AS pp ON sd.ProductID = pp.ProductID INNER JOIN Production.ProductSubcategory AS pps ON pp.ProductSubcategoryID = pps.ProductSubcategoryID INNER JOIN Production.ProductCategory AS ppc ON ppc.ProductCategoryID = pps.ProductCategoryID GROUP BY ppc.Name, soh.OrderDate, soh.SalesOrderID, ppc.name, pps.Name, pp.Name, soh.SalesPersonID
And, that’s it. I just fed that view to the R engine and it summarized the dataset just by using those two commands.
This is not the perfect dataset to test with R.
I’d love to dig deeper into R because of its powerful analytical features.
What is R?
“R is a language and environment for statistical computing and graphics.”
If you’re into statistics, linear/non-linear modelling, or simply want to try another tool to analyze your data warehouse, give R a shot.
If you want to start digging with R, Ted Malone ( b | t ) has a nice introduction to using R with SQL Server 2012. Get more information about R on the R Project website.
