Ryan Robitaille

Let's be honest - I'm a data guy. "Geek" to be more exact. But, I'm also a sports guy - and if there is ANY sport out there that prides itself as being massively data-centric... its Baseball. Data goes with baseball like Barry Bonds and... um, Home Runs (yeah, that's it). Data collecting is an integral part of baseball culture and has been going on way before the internet or relational databases were even invented.

"Business Intelligence" is a term that's been kicked around around for some time now, and basically just means "analyzing data from your past, in order to BETTER make better decisions in the future (or to better steer it in realtime)". Its all about strategy, learning from mistakes (and successes), and being able to actively monitor / measure the health of your business. We might use this data to ask important business related questions: "Is Suzi Salesperson performing up to par this quarter?" or "What line of products have the biggest margin this time of year?".

However, its not too far of a stretch to imagine someone who works for a Major League Baseball team sitting in front of a computer at the head office thinking: "What is the ROI of our pitching staff this year? Are they performing to expectations?" Its really no different then the business and reporting scenarios many of us encounter every single day. The exciting part is not only being able to read what has happened out on the ball field yesterday - but what might happen tomorrow. Boom. "Bizball Intelligence" anyone?

In this multi-part post, I'll be taking you through all the steps needed to get up and running with your own historical AND current Major League Baseball statistics database (an operational / transactional DB), staging it out in a more "reportable" fashion (a data warehouse DB), and then finally building some cubes, calculated measures, and choice "Player Key Performance Indicators" (KPIs) based on the data. Who knows, maybe you'll be so good at it that you'll get hired by the San Diego Padres front-office, like this guy did, but more on him later.

So grab some sunflower seeds and a calculator - adjust your jock straps and get ready, 'cause here we go.

[ Part I: DATA ]

First up. We need some data (actually, a TON of data), and (more importantly) we need a place to store and easily access it. Its database creation time.

Here at C&C, we are big fans of the Microsoft SQL Server 2008 stack of products (Database, Analysis Services, & Reporting Services), and I'll be using SQL Server 2008 R2 for this tutorial. You can get a 6 month eval copy, (you can also install the free SQL Server Express product to get up and running with a database at no cost - but you'll be missing out on the Analysis Services and Data Mining pieces).

There are lots of places with baseball data on the web, but sometimes its incomplete, unofficial, not granular enough, and organized badly for what we need it for. Besides, we can't do as much with stats that are ALREADY calculated - we need to calculate them ourselves in order to Data Mine properly (although those "other" data sources come in handy to verify that our own calculation formulas are correct). So in my mind, the grand-poobah of MLB data collection is (shockingly enough) MLB.com.

MLB.com's "GameDay" application uses a XML structure to read info about every game, inning, pitch, hit and player. The "full-monty" of data only goes back to about 2006 or so (pitch location, coordinates, speeds, etc also known as "Pitch/FX Data"), but less granular data is available for prior years (and even more from other methods, I'll post about later).

Browse around their backend, and you'll see what I mean: http://gd2.mlb.com/components/game/mlb/. From there it goes into year/ month/ day/ game/ inning, linescore, batters, pitchers, etc. etc. all packaged up into XML files for the reading. Now before you think this is some kind of illegal backdoor in MLB's servers, its not - well, not really. MLB has been aware that people have been using this data for about 4 years now. If they REALLY wanted to lock people out of it, they would have done it long ago. So feel free to browse it 100% guilt-free. The data is near realtime too! Pretty slick, MLB.

linescore.xml Example

So how do we get this mountain of XML files into our database?

Well, that's easy. In 2010 Wells Oliver created a python script and associated libraries that reads the XML Gameday data and inserts it into a relational database format. Wells wrote the script to work with MySQL and (also) hasn't updated it in some time (the MLB data format is always changing, and expanding, especially in 2011). So I took his fine work, rewrote it to use Microsoft SQL Server and added / changed / enhanced portions of its logic and the database schema. Anyways, I have "forked" his project, as they say in software development circles.

Download the Python code here: gameday_mssql.zip

Download the SQL Server Create table script here: gameday_mssql.sql

It requires:

• Python 2.6 (probably works with any 2.X ABOVE 2.6, but haven't tried yet)
• Pymssql Python Library
• BeautifulSoup Python Library

Download it, expand it - (I use "C:\Gameday" myself), and then modify the 'db.ini' file in the root folder to configure your database instance.

I'm working on a development SQL instance, so my db.ini looks like this:

(If you need to use Windows Authentication / Trusted Auth, stay tuned because I'll be adding that shortly)

Obviously you're going to want to create a 'mlbgameday' (or whatever you desire to call it) database on the server first AND you're going to have to create the schema frist.

Once you're all set, the script is used like this: (make sure that the python directory is in your PATH)

The only required argument is "year", but I would shy away from trying to fetch a whole year at one time for now. The multiple threads tend to overwhelm the pymssql library and max out some internal max connections setting (the TDS lib MAX_CONNECTIONS). For "type" you can fetch 'mlb' data or 'aaa' data, which might be useful in our analysis, but not a neccessity.

In order to gather a couple years worth of data painlessly, there is ANOTHER script I put together in the package called build_all.py - simply open this file with a text editor and modify these variables near the top:

Now, running "python build_all.py" will crawl through each date in your specified date range gathering data from all games on each day. Crawling through 6 years of MLB data might take a day or so - but once you have it, you have it - and updating it each day to get the previous days games should only take a minute or so (more on that in the next post).

Sample Output:

Etc... etc...

Fire it up and you'll have more data then you know what to do with - what DO we do with it? That's next...