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:
(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|
|Team Nickname||Play Time|
|First Down (Yes/No)||Second|
|Roof Type||Player Age|
|Season Half||Last Name|
|Season Segment||Nick Name|
|Week||Player Full Name|
|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.