Creating Facets in SQL Server 2017 Using R

MarlonRibunal, 2018-04-11

SQL Server 2017 In-Database Machine learning has brought the analytics closer to the data. It is now convenient to do data analytics either by using the programming language R or Python with the database engine.

In my previous post, I have demonstrated how easy it is to create a bar graph in SQL Server 2017 In-Database Machine Learning using  R.

We’re going to build upon that basic graph.

Sometimes doing data analysis would require us to look at an overview of our data across specific partitions, say a year. For example, we want to see how our product groups fare on month-to-month basis across the last 4 years.

In a data analytics perspective, there are quite a handful of data points in this requirement – data aggregate (quantity), monthly periods, and year partitions.

One of the approaches to handle such requirement is by using a facet. Faceting is a way of plotting subsets of data into a matrix of panels based on one or more variables – or facets.

Again, let’s begin with the end in mind. Here’s what faceting looks like:

Dataset

The quantity aggregates based on monthly period per product group are laid into the year partition facet in the following query. You can also set up your data like this in the R engine.

SELECT DISTINCT
SUM(il.Quantity) OVER (PARTITION BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate),
sg.StockGroupName
ORDER BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate)
) AS Quantity,
DATEPART(YEAR, i.InvoiceDate) AS InvoiceYear,
DATEPART(WEEK, i.InvoiceDate) AS InVoiceWeek,
DATEPART(MONTH, i.InvoiceDate) AS InvoiceMonthNo,
DATENAME(MONTH, i.InvoiceDate) AS InvoiceMonth,
sg.StockGroupName
FROM Sales.Invoices i
JOIN Sales.InvoiceLines il
ON i.InvoiceID = il.InvoiceID
JOIN Warehouse.StockItems si
ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
ON sis.StockGroupID = sg.StockGroupID
ORDER BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate);

Setup

Please refer to Creating Bar Graph In SQL Server 2017 Using R for the setup. We need two (2) libraries for this demonstration:

  • ggplot2()
  • scales

Again, if you get “missing package” error, just install it. If you get a “no function x” error, chances are you have not declared the function’s parent package. The data is based on the WideWorldImporters database.

Properties

The x-axis is ordered by DATEPART(MONTH, i.InvoiceDate) AS InvoiceMonthNo.The substring function in R is more intuitive than other programming languages. This expression is used as the label for x-axis: substr(InvoiceMonth, start = 1 , stop = 3). The theme setting is simply set by theme_dark() function.

Chart

The whole chart creation code looks like this:

quantitytschart <- ggplot(groupqty , aes(x = reorder(substr(InvoiceMonth, start = 1 , stop = 3), InvoiceMonthNo), y = Quantity, group = StockGroupName, colour = StockGroupName)) +
geom_line(size=1) + geom_point(size=3) +
facet_wrap( ~ InvoiceYear) + theme_dark() +
labs( x= "Month", y="Stock Group Quantity") +
scale_y_continuous(labels = comma)

That’s it.

Facets*

The above matrix is of type facet_wrap. Another option is facet_grid.

  • facet_grid(. ~ InvoiceYear) 
  • facet_wrap( ~ InvoiceYear)

facet_grid(row ~ col): this is what they call “bivariate”, which creates 2-D matrix of panels based on two factor.

facet_wrap(~cell): “univariate”, creates 1-D strip of panels, based on one factor, and wrap the strip into a 2-D matrix.

*Source: ggplot2 Quick Reference: facet

Here’s the facet_grid (See the screenshot of facet_wrap above):

Complete Code

Here’s the rest of the code:

EXEC sp_execute_external_script @language = N'R',
@script = N'
#Library
library(ggplot2);
library(scales);

chartfile <- "C:\\Marlon_test\\Groupqty.png"
png(filename=chartfile, width=1800, height=900)

#Data Frame
groupqty <- InputDataSet

quantitytschart <- ggplot(groupqty , aes(x = reorder(substr(InvoiceMonth, start = 1 , stop = 3), InvoiceMonthNo), y = Quantity, group = StockGroupName, colour = StockGroupName)) +
geom_line(size=1) + geom_point(size=3) +
facet_grid(. ~ InvoiceYear) + theme_dark() +
#facet_wrap( ~ InvoiceYear) + theme_dark() +
labs( x= "Month", y="Stock Group Quantity") +
scale_y_continuous(labels = comma)

print(quantitytschart)',
@input_data_1 = N'
SELECT DISTINCT
SUM(il.Quantity) OVER (PARTITION BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate),
sg.StockGroupName
ORDER BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate)
) AS Quantity,
DATEPART(YEAR, i.InvoiceDate) AS InvoiceYear,
DATEPART(WEEK, i.InvoiceDate) AS InvoiceWeek,
DATEPART(MONTH, i.InvoiceDate) AS InvoiceMonthNo,
DATENAME(MONTH, i.InvoiceDate) AS InvoiceMonth,
sg.StockGroupName
FROM Sales.Invoices i
JOIN Sales.InvoiceLines il
ON i.InvoiceID = il.InvoiceID
JOIN Warehouse.StockItems si
ON il.StockItemID = si.StockItemID
JOIN Warehouse.StockItemStockGroups sis
ON si.StockItemID = sis.StockItemID
JOIN Warehouse.StockGroups sg
ON sis.StockGroupID = sg.StockGroupID
ORDER BY DATEPART(YEAR, i.InvoiceDate),
DATEPART(MONTH, i.InvoiceDate);
';

Have fun and happy faceting!

The post Creating Facets in SQL Server 2017 Using R appeared first on SQL, Code, Coffee, Etc..

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads