Friday, June 4, 2010

Witnessing the power of BI and SQL Analytics

So, a very fun story has dawned on me in the past week or so. has been demonstrating the power of business intelligence tools and SQL Analytics packages.

As you all know, I recently became obsessed with perfecting Alton Brown's apple pie recipe. Evidently, I was not the only one. Enough of us jumped on this bandwaggon that clearly associated a group of products together. Witness what happens when you do an search for "Grains of Paradise"

Customers who bought this item also bought:
  1. A 10"x2" tart pan with a false bottom
  2. A pair of porcelain pie birds
  3. A jumbo stainless steel apple core slicer with 12 slots
  4. Tapioca flower
All of these are rare ingredients use in Alton Brown's apple pie recipe. I am surprised it didn't bring up the Apple Jack. Maybe they just can't sell the booze at Amazon. Interesting that these ingredients might completely overwhelm the conventional middle-eastern stuff you would normally associate with Mustapha's spices.

You can see that Alton has a large foot-print. He can make an impact on internet super-stores even as large as Amazon's completely automated analytical engine shows the power of this impact.

I will show you another example from the baking domain. The New York Times published a how-to video on Youtube some 4 years ago showing how to make no-knead bread. As the New York Times ambassador said, they got a huge response, and it became a cult.

Amazon clearly confirms that this is so. When you do an Amazon search for 'Dutch Ovens' guess what customers often buy with their Dutch Ovens? They buy book on how to make no-knead bread. Don't think so? Check it out! The products are linked in both directions. It takes purchasing power to make that happen.

I must say, that did look like pretty awesome bread. For those who are out of the house for some 12 hours a day, the recipe is easy. Just stir, walk out the door, fire up the car, and go to work. When you come back, heat up the dutch oven on your stove, and heat up the real oven. You toss them together and in about 1 hour you have your bread. The problem with the recipe is that house wives are impatient. They want to hurry the process. Time is the key ingredient.

Amazon's fully automated business intelligence solution has drawn a connection between the Dutch oven and this book regarding no-knead bread. It knows that people who buy one, often buy the other. It just so-happens that these products are related by a hit internet video which teaches busy people how to make great bread.

Folks, this is only the tip of the iceberg. I chose these examples because they are simple, clear-cut, and undeniable. There is far more to the story than that. We can explore all manner of natural phenomenon like this. We can study football in this manner. We can find out what is really important... like scoring points.

You have these lovely little mathematical techniques called ANOVA and ANCOVAR which allow us to analyze variance and co-variance. We can see how numerical values move together. We can see how much scoring impacts your victory total. We can see how much stopping the other team from scoring impacts your victory total. We can see that teams with great punting stats have lousy win totals. The Rams had a great punt team last year. They punted a lot from deep in their own territory. Donnie Jones could really cut loose. The Saints had fairly poor punter stats.

Believe me, the world is wide open to analysis by these marvelous SQL tools. Microsoft claims that they bundle the world's most sophisticated BI engine in the box with every copy of Microsoft SQL Server. This is very interesting to me. We happen to have a lot of SQL Server boxes laying around here at work. I happen to have the developer's edition at home.

In the past, I have used the common tools the NFL and ESPN provide us to gather basic statistical information I use to form my opinions. You can get a hell of a good grounding in the facts with these tools. The simulators, like John Madden Football, can also tell you a hell of a lot. This season, I think I am going to step it up. I am going to gather in this data, slap it into a SQL MDB and see what the BI engine can tell me.

This aught to be fun.