I’ve always found the best way to learn a new programming language is to start building stuff, solving problems, using the language. Even if you do things badly or inefficiently, you’re figuring out how to put the language to use. I tried the same thing with R.

Fair warning, there is no happy conclusion to this story. It’s a lesson about learning, not about solving a problem.

After poking at the R language for a little while, I decided I was ready to solve a problem. I have a fantastic idea for demonstrating the usefulness of the language specifically for DBAs. I won’t go into what it is here because I’m still hoping to solve this problem and it will provide a fantastic blog post. Anyway, I have a very good understanding of the problem (or so I thought) I’m looking to solve, so, what the heck, let’s throw R at it.

First thing I learned was how to connect R up to a SQL Server database. Easy. Then it’s query the database to create a data frame. Easy. Referencing columns and rows from the data frame, again, really simple. Spit out some interesting information about the data such as density or a histogram? A couple of Bingle searches later, easy. Here’s a starter (formatted as text, my code formatter in the blog doesn’t have an R language choice):

library(RODBC)

#set up queries
sqloutput <-"SELECT  rd.FirstVal,
        rd.SecondVal
FROM    dbo.Mytable AS rd;"

#connect to server
dbhandle <-
  odbcDriverConnect(
    'driver={SQL Server};server=WIN-3SRG45GBF97\\dojo;database=Testing;trusted_connection=true'
  )

#load tables
mydata <- sqlQuery(dbhandle,sqloutput)

#do something
density(sqloutput[,1])
density(sqloutput[,2])

Seriously, this really is an easy enough language to learn. It’s kind of quirky, but it’s easy to understand as long as you are quick to look up syntax(it really is quirky). So what’s the problem?

Algorithms.

But that’s not even true. For example, I can pretty quickly run a Chi Square Independence test against my data like this:

chisq.test(relateddata)

Then you get the output and all hell breaks loose…. or… more accurately, no hell breaks loose. You’re looking at some data and trying to figure out what it means. Suffice to say, I finally realized that I had the wrong algorithm. More web searches. Questions posted to various forums, defining my problem (as I saw the definition), which lead me to the fact that what I really needed to solve my problem was not to run standard comparison tests, but to compare continuous distributions. Awesome. Fixed that algorithm problem, right?

Wrong.

That’s because algorithms are not the problem… the only problem. The real problem is data preparation. A lot of the examples you’ll read online are very straight forward with nice neat data sets. That’s because they were carefully groomed and prepared. Here I am looking at the wooly wild real data and I’m utterly lost in how to properly prepare this so that it’s appropriately set up as a continuous distribution(or a distribution at all). WOOF! The reason this is so hard is because I actually don’t understand the data fundamentals of the problem I’m trying to solve in exactly the way needed to solve the problem. More cogitation is necessary.

My lesson. I’m going to continue learning R syntax. I’m absolutely going to keep studying the mathematics in order to better understand the algorithms. But I’ve got to also spend time figuring out how to groom the data. It’s pretty clear that is one of the biggest challenges to making this stuff work.

Oh, and make sure you close your connection to the database:

#cleanup
odbcClose(dbhandle)