Witnessing the power of BI and SQL Analytics

So, a very fun story has dawned on me in the past week or so. Amazon.com 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 Amazon.com clearly associated a group of products together. Witness what happens when you do an Amazon.com 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.com. 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.

Why 64 Bit SQL Server is faster

So this morning we had an interesting talk about whether or not Microsoft SQL Server x64 was any faster than x32. You better believe it is. Since 2005 Microsoft has been presenting videos like crazy on the speed advantages of x64. They are pretty dramatic. You can view a nice webcast on this very subject here.

Whilst certain lazy cretins on StackOverflow.com continue to question whether there is anything to be gained out an upgrade, let's understand why there is a tremendous speed boost in going to 64bit SQL Server.

1. You move 8 bytes of data around per operation or cycle, not 4. That is twice as much raw binary moving per cycle. A lot of SQL Server actions simply move bytes from point A to point B. A lot of things SQL does involves picking up an arbitrary bucket of bits and throwing it down somewhere. In the simplest scenario, given a fixed number of cycles, moving twice the number of bits per cycle automatically doubles the speed of movement, cutting movement time in half. Of course, other bottlenecks--such as IO bottlenecks--can express themselves, reducing this gain.
2. Your pathetic 32 bit chip and software still works like an x87. Consult any good CPU historians on the subject of the Intel 8087 math coprocessor. The bomb blast you will get from them will border on hysteria. The Intel 8087 is quite possibly the worst floating-point engine ever to make it to market. Most will tell you it is the slam-dunk worst, full-stop. Inside the body of your nice shiny 32bit CPU still v-fibs the heart of a wretched x87 coprocessor. In the 32 bit world, this is the only floating point unit guaranteed to be there for you, if you presume a i80486 processor or better. What does this mean? It means all your nice money, interest, passing percentages, quarterback efficiency indexes, Loan-to-Value calcs, etc. run slower than they might otherwise. I don't even want to talk about something real-number intensive like 3d.
   
3. AMD64 has a nice new shiny math coprocessor. When they created the x64 instruction set, AMD decided to dispense with the x87 architecture completely. They replaced it with a beautiful architecture entirely predicated on the SSE2 instruction set. Everybody likes SSE2/SSE3. It is pretty straight-forward, and you get kick-ass performance. I don't know of a better way to do Floating Point. This means all your loan-to-value, interest, index rate, real profit, liability calcs execute faster; a hell of a lot faster.
4. You can use a ton of RAM, and you better. This is the target Microsoft has pounded with countless artillery shots. Every last time you hit the RAM cache for data rather than the hard disk, you get a speed-up of 100:1. This is no joke. High-speed electronic RAM is several orders of magnitude faster than a mechanical disk drive. When you go over 8GB of RAM, which is cheap and easy to do, it is possible for many corporations to cache entire mission critical databases in RAM. It is the easiest way in the world to speed up your transactions 100 fold and save a ton of wear and tear on your disk array. Everything gets written through to your RAID array in a lazy fashion, after the sparks fly.
5. Your price per tpmC drops through the floor. If you consult the Transaction Processing Performance Counsil, you will discover the lowest cost solutions always involve x64 code. I don't give a damn whether it is Windows, Linux, Oracle, or Microsoft: The solution is always 64 bit, and not 32 bit. Have a look here and prove me wrong.

So there you have it friends. If you haven't gone 64 bit, you are just plain wrong. Don't give me any shit about Visual Fox Pro either.