Rated R (A Teaser)

How many lines of TSQL codes and Window Functions you need to come up with this?

With R, it takes about 2 commands:

[sql]
> order > summary(order)
[/sql]

To give you an idea, I prepared the dataset into a view like this:

[sql]
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
[/sql]

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.