If you’re just reading this site for the first time, you should definitely check out The Great Football Project – this is where I spend the most time and energy, and where you can see the most in-depth “how to” on my site… and perhaps anywhere
Below you will find the introductory post, which explains what the project is all about. And each post then links to the next post in the series.
This is what I plan to do for my first series of meaty posts: develop a real, non-demoware PowerPivot solution, from start to finish, and walk you through it as I go. I’m going to take a “hardcore” BI project from my past and re-implement it using PowerPivot, and give you detailed recaps of each step.
So, what’s the project in question?
First, check out these screenshots (click for larger versions).
“What is that,” you ask? “A football web page written in Latin???” (Indeed. No one loves their football as much as the Vatican, a burgeoning demographic for the NFL.)
Actually, it’s a mockup from a real project I led in MSN/Windows Live back in 2006, for the now-disbanded Fantasy Sports team. This page was our equivalent of today’s PowerPivot Report Gallery – a fun, engaging jumping off point for browsing reports. I even dug this out as inspiration when we were starting the Report Gallery work… and then the folks working on Report Gallery built something about 10x as cool
Speaking of reports, check out Screenshot #2:
That one isn’t even a mockup. It’s a real Excel report that I built at the time using Excel 2007. A lot of non-Excel people are probably surprised that Excel can look that good. C’mon, admit it. And look at all those things you can filter by! Wanna know how all QB’s perform, on the road, outdoors, in the 4th quarter, in close games? No problem! (MSN purchased NFL data from Stats, Inc. – one record per play from every game between 1992 and 2006. I was a kid in a candy store.)
I built more than 30 such reports. Two more quick examples. First, the Team Playcalling Tendencies report:
“Put the spreadsheet down, sir, and slowly back away”
With this, I learned things that I’m pretty sure no one else in the world knew… or at least, not NFL announcers. They’d say something from the gut like “It’s third down and 3 yards to go, I’m pretty sure the Chargers are gonna pass here.” And I’m jumping off the couch screaming “Oh no they’re not!” Being a data guy is fun, but it’s a lonely business sometimes
And lastly, one of my personal favorites, the “Wide Receiver Performance by Height” report:
The (Unnoticed) Rise of the Short Wide Receiver
2006 was a crazy year for shorter WR’s, by the way. In the end, two Five-foot-eleven guys led the league in receiving yards. Both of them got a disproportionate number of their yards “after the catch.” Incredibly interesting, given that the NFL has for many years now strictly been coveting WR’s who were at least Six-foot-three. I showed this to an NFL sportswriter, who surmised that defenses had been forced to recruit bigger players to defend against this new breed of gargantuan receiver, and those bigger defenders were now suddenly vulnerable to smaller, quicker receivers, who could catch the ball in the open field and then elude defenders for extra yards. He and I were the only ones talking about that though, as far as I could tell.
It’s amazing what a little data can do – if you pay attention. Most NFL teams are still strictly recruiting big receivers, passing up what I believe is a huge opportunity – pick up a number of undervalued (shorter) WR’s out of college each year and I’m pretty sure you could build a dangerous passing attack at a fraction of the cost.
(For multiple reasons, I am reminded of big dinosaurs being replaced almost overnight by smaller mammals).
OK, I know most of you don’t share my eccentric passion for sports stats, so I will rein myself in and simply list a handful of the other 30 or so reports to give you a flavor:
|- Overall NFL scoring and Playcalling trends by Year|
|- Running Back Performance by Run Direction (Left/Right/Middle)|
|- Running Back Performance by Field Type (Grass/Fake Turf)|
|- Quarterback Performance by Distance Thrown|
|- Team and Player Tendencies/Performance by Temperature (!)|
Microsoft Really Built This???
Yes, we did :). The idea was to post these as public, interactive web reports, using SharePoint and Excel Services, as a means of differentiating our fantasy sports offering from the established providers like ESPN, Yahoo, and CBS.
And we did that, as a beta. A full, public implementation of the Excel/SharePoint/BI stack, more than three years ago (I should call Guinness).
Alas, the political landscape shifted. The fantasy sports team was disbanded and parceled out to other teams. And no, not because of the stats reports project :) A sad demise for sure. But along the way, we learned a lot of valuable lessons that helped inform some of the decisions we later made on PowerPivot.
Built on a Cube (And why this is relevant to PowerPivot)
This mothballed football project is particularly relevant to PowerPivot because it was built on an OLAP Database, also known as a “cube.” BI pros will know what this means, but Excel and SharePoint folks might not. Don’t worry, I will summarize that in a brief, separate post that the BI crowd can skip.
For now, suffice it to say that a ton of time, money, and effort went into developing the cube. But once it was done, I could build virtually any report in minutes using Excel. This flexibility and speed is part of what makes cubes such a coveted commodity in the business world – there really is no substitute.
But it’s that “time, money, and effort” part that gives people pause. And it’s also the impetus behind the PowerPivot project – the goal is to give you the benefits of a cube, at a fraction of the cost and in a fraction of the time.
When you are using PowerPivot for Excel, you don’t realize it, but you are building a cube behind the scenes. You’re just an Excel user employing a natural extension to the toolset you already know.
So, taking a formal cube that was built “the hard way” and re-implementing it from scratch using PowerPivot is a perfect test of its core value proposition, and along the way I hope to demonstrate the “how” as well as the “why.”
A lot of fun stuff above, at least for me. But getting serious at the end here, let’s summarize the important points:
- Yes, you can build very attractive reports in Excel – even more so now that we have Slicers in 2010
- Cubes are very powerful, flexible, and re-useable databases for reporting and exploration of your data
- Cubes are, however, traditionally expensive and time-consuming to build
- PowerPivot aims to deliver many cube benefits, as well as other benefits of traditional “hardcore” BI, in a fraction of the time and cost
- While the MSN project was about something casual, it very closely paralleled real-world business BI projects, both in the methodology used and in the kinds of actionable insights it delivered
- The MSN project had to implement much of the “glue” that PowerPivot delivers out of the box, such as the Report Gallery
- My goal is to repeat the entire MSN project, in PowerPivot, walk you through my progress at each step, and compare the results and costs at various points along the way