Linear regression in “The Man who counted”

Recently, I got a book by Brasilian writer  Júlio César de Mello e Souza (published under pen name Malba Tahan), titled The Man who counted. Book is a collection of mathematical stories very similar to Scheherazada’s 1001 Nights, where mathematical story-telling is the center of book.

                                               2017-03-25 19_38_40-the man who counted - Google Search

In story 5“In so many words”, Malba describes a simple algebraic problem of proportion between price for lodging offered and price of jewel sold.

This man,” old Salim said pointing to the jeweler “came from Syria to sell 
precious stones in Baghdad. He promised he would pay 20 dinars for his 
lodgings if he sold all of his jewels for 100 dinars and 35 dinars if he 
sold them for 200. After several days of wandering about, he ended up selling 
all of them for 140 dinars. How much does he owe me according to our agreement?”

Both, jeweler and lodge owner,  calculate the result each using percent proportion problem, both ending with wrong results, that was each to favor each:

  1. Jeweler:
200 : 35 :: 140 : x

x = (35 x 140) / 200 = 24.5

2. Lodge Owner:

100 : 20 :: 140 : x

x = (20 x 140) / 100 = 28


With two different results, both would end up in argument, so the third needed to be calculated:

Sale Price        Price of Lodgings
200               35
-100             -20
-----            -------
100               15


So the difference between both calculations forms a proportion to calculate the new case, when Sale price for jewel is 140, the price of lodging would be 26.

100: 15:: 40: x

x = (15 x 40) / 100 = 6


Mathematically speaking, problem is very interesting to be solved also using Linear Regression, since the two pair of points [200, 35] and [100, 20] form a linear prediction function and we would need to predict what would be the price of lodging, when sale price for jewel is 140.

diamond <- c(100, 200)
sleep   <- c(20, 35)

# regression
sleep_model <- lm(sleep ~ diamond)

plot(x=diamond, y=sleep)
abline(lm(sleep ~ diamond))

2017-03-25 21_07_29-Plot Zoom

Now, we can call this a prediction, what actually Beremiz does by heart.

predict_data <- data.frame(diamond=140)
fit <- predict(sleep_model, predict_data, interval = "predict")

#new value for diamond=140

Result is 26, which is strictly algebraic and R-prediction speaking correct result.

In this case, linear regression does same as proportion calculation, but what strikes me is which calculation – not mathematically speaking – does make more sense? 26 or 24,5 or 28 ? And which method for calculating next price lodge should satisfy both jeweler and lodge owner.

Happy reading!



First thoughs on R integration in SQL Server 2016

This blog post is a very personal confession, might be delicate but it must be understood as a  proposal or plan for improvement.

Update #1: Typing error in title: correct title is First thoughts on R integration in SQL Server 2016 (update: June 24, 2016)

Before the start, let me put some background and context. I have been working with statistics and database for quite some time now and used different tools for statistical purposes. Starting with SPSS, Lisrel, Pajek, continuing with SPSS Modeler and then going to Weka and R together, very heavly used SQL Server SSAS  and working with Orange and in past 2 years working with SAS  and SAP Hana Analytics. With all the products I have used – and I am not here rank or make comparison which one is better or which one I prefer – some things remain the same. Getting to know your data to a detail is the most important one, where as  applying the statistical knowledge to it the result of it.

So I have had opportunity to work with different data sets, for different companies, on different projects and with different tools. And I am – looking back – very happy to have done it. So this thoughts are not me complaining about any of the products or solutions, but merely looking on improvements based on my year long experiences.

Working with R in SQL Server has been my personal thing for couple of years. The story goes back 4 years ago, when I started working on building a web based engine for creating marketing campaigns based on input parameters and desired goals. Once the business/marketing department decided on their new campaign, using a web based (in house build) program, they selected product groups and customer segment and some minor additional information (time frame, validity of campaign, legal stuff,..) they clicked okey and that was it. But in the background the stored procedure generated long T-SQL that has pushed (1) data into SQL Server engine and (2) selected proper model that feed the data to R engine.  So going back to year 2012, I have build framework for communication SQL Server with R engine. Most of the framework is available at SQLServerCentral web page.  The framework had some security flaws and issues (calling external procedure, storing data in external files) and mostly performance issues (scaling out wasn’t possible, R was solemnly dependent on RAM of the server), but overall the framework was working with putting it into production and project put into production.

Back then I wrote my framework in a way that I would be used as easily as possible, storing and handling R code and T-SQL code in SQL Server database. Most of the problems I had was, that SSMS couldn’t debug or validate R code and to some extent, converting R results back to table was also proven not to be as easy as one might think. But overall, I was still in the middle – between the platform and putting the solution into production – mainly to control the input data and results (outputted data in form of recommendations or campaign lists).

After working with R integration in SQL Server 2016 for the past couple of months, I have to admit, I was happy that security issues were solved. And performance issues were solved with RevoScale library. Both are very very welcoming features. Come to think of it, two main issues did Microsoft solved. And looking from my aspect of building my own framework, security issues were really and badly needed to be solved. Same logic applies to workload and working with large datasets. Performance has proven really great. In addition, you have to keep in mind that with R Integration Microsoft is – rather then keeping them separated – bringing different roles of people even closer. So DBA, Analysts and developers are now and can now be all part of one data process. Integration with Azure is also very positive and good thing, come to think of it as an extension to your flavor of landscape. Support to different databases is also what will in future be very helpful – but admitting, I haven’t test it yet.

What I expect in the future for R integration that Microsoft will do is:

  • support R in Analysis Services
  • better support R in Integration Services
  • stronger R integration with DQS and MDM
  • native integration of R into transact SQL (calling SP every time for every kind of statistics might be tiring)
  • have some DMV created for R profiler, for R debugging and for R performance monitoring
  • bring support for HTML files and JS / JQuery scripts into SSRS or PowerBI
  • improve usage of R exports with file table
  • open server Endpoints Objects for R in order to use full extension of R.
  • improve Resource Governor for R in Standard edition
  • questions on auditing, profiling of R code from T-SQL and
  • security (couple of questions as well)

I have most probably forgot on couple of points for improvement. But this was more technical, now let’s talk about user experience point of view:

  • think about the support of R debugger / validation of code in SSMS
  • in fact, bring R into SSMS (!) instead of VS or create RTMS (R Tools for SQL Server Management Studio)
  • not limit R exports only on data frames or images but support also vectors, lists, etc

Of course, not to hasten myself too much, I forgot to ask myself, what actually the purpose of integration was.

2016-06-23 11_48_40-sql-server-2016-r-services-7-638.jpg (JPEG Image, 656 × 333 pixels)

Is it really only about making the super predictive analytics or is it making statistical tool as well? If latter, than I would strongly suggest to recap my open suggestions.

So, for the end, I will serve you with a hint 🙂 I am preparing and building bunch of scripts (from my previous experience and from the framework that I have built) to help all the data scientists and developers to use R much easier. But (!) – mathematical and statistical background still is very much a something I would put as highly recommended to have when digging into R.

Follow the blog where I will keep you updated on the scripts and where the code will be available.


Happy R-SQLing 🙂