PowerPivot is going to be put to the test perhaps even more than I had thought.  And um, it looks like I will be, too.

I spent a good chunk of today taking inventory of what the MSN football cube could do, as well as what the underlying data tables look like.

Oh my goodness.  It looks daunting from the aerial view.  But I’m optimistic that it won’t be so bad when I start working on the individual pieces, akin to how this beautiful painting is really just a bunch of dots when you get close to it:

Georges Seurat - a PowerPivot artist before his time

(I’m going out of my way to use the word “akin” here, I’ll tell you why later – I promised someone I’d do it.  But I lost 15 minutes in the process of forcing the analogy, reading the wikipedia page on Seurat – fascinating stuff, his philosophy).

Anyway, the MSN cube contained well over 150 fields that were exposed to the user (and many more behind the scenes).  I’ll stick to the 150 here.

Numerical Measure Fields

Interceptions Team First Downs
Times Sacked Team Pass Attempts
2PT Conversions (Pass) Team Pass Yards
2PT Conversions (Rec) Team Points
2PT Conversions (Run) Team Rush Attempt
Catch % Team Rush Yards
Completions Team Total Yards
Completion % Team FG Att*
Fantasy Pts Team FG Att Allowed*
First Down % Team FG Att Allowed per game*
Fumbles Team FG Att per game*
Interceptions per Game Team FG Made*
Pass Yards (Air) Team FG Allowed*
Pass Yards (YAC) Team FG Allowed per game*
Pass Yards (Air %) Team FG per game*
Pass Yards (YAC %) Team INT thrown*
Completions per Game Team INT allowed*
Times Thrown To Team INT allowed per game*
Rec Yards (Air %) Team INT per game*
Rec Yards (YAC %) Team Move*
Rec TD Team Move Allowed*
Rec TD per game Team Move Net*
Rec Yards Team Pass Attempt*
Rec Yards (Air) Team Pass Attempt allowed*
Rec Yards (YAC) Team Pass Plays*
Rec Yards per game Team Pass Plays per game*
Receptions Team Pass Yards Allowed*
Rec per game Team Pass Yards Allowed per gm*
Rush First Down % Team Pass Yards per game*
Rush Attempts Team Points*
Rush Att per game Team Points Allowed*
Rush TD Team Points Allowed per game*
Rush Yards Team Points per game*
Rush Yards per game Team Rush Plays*
Rush TD per game Team Rush Plays per game*
Yards per Pass Att (Sack Adjusted) Yards per Rush
Sack Yards Lost Team Yds per Pass Att net*

And that isn’t even all of them.  Remember, we hired a consultant to build this for us, and when the consultant was gone, we had to be able to build any report we wanted – it was going to be very expensive to re-hire the consultant for incremental fields, akin (ahem) to death by a thousand cuts.  (Gee, if only we had possessed a tool that allowed us to add our own, without learning about cube design…)

The * fields are ones that I remember being particularly tricky, both for the consultant to build and for me to use properly, so I’ll be keeping my eye on those.  There are some other tricksy hobbits in there as well, like Catch %, Rec Yards (Air), and Rush First Down %.

Filtering and Grouping Fields

And these are the fields we could filter and group by (known as dimensions/attributes in cubespeak) – the fields you’d place on rows, columns, page filters, or slicers in a pivot table.

Conference Pass Distance in Air
Division Half
Team City Minute
Team Nickname Play Time
Down Quarter
First Down (Yes/No) Second
Day/Night Play Type
Roof Type Player Age
Field Type College
Year Height
Season Half Last Name
Season Segment Nick Name
Week Player Full Name
Stadium Position
Humidity (!) Weight Group
Home Team Weight in Pounds
Offensive Team Years Pro
Defensive Team Scoring Event
Home Team Win Loss Temperature
Home Team Offense Field Position
Leading vs. Trailing Yards to Go
Offense Win Lose  

Again, that isn’t quite a complete list.  I have simplified a bit.

Now imagine being able to mix and match any of these fields with any of the numerical fields above.  Boundless possibilities.  Which is why cubes are so cool to have.

Next Football Post:  Examining the Source Tables >>