OK, last post for the night. Turns out there were over 40 tables of data that went into the MSN football cube. These are the tables I will be importing into PowerPivot.
It was pretty difficult finding a way to list them all out in one place, with their column names, so everyone can see. I eventually settled on a db diagram from SQL Server Mgmt Studio.
First, here are the tables that contribute to all of those Filter/Group fields from my last post:
Note how they all start with “Dim” in their names? For the non-cube folks out there, that is because Filter/Grouping fields are often called “Dimensions” in cubespeak. PowerPivot does not use that lingo, which is a good thing, but I’m just telling you these things in case you encounter them elsewhere.
Normally, if you are presented with this number of tables and you have to do something with them in Excel, you block off your calendar for the rest of the week. It will be much, much faster with PowerPivot, as we will see.
And here are the tables that actually contain the data of what happened on individual plays. There will be more rows in these tables than in the tables above.
Just as the tables above are prefixed with “Dim,” these are prefixed with “Fact,” because that’s what they are called when you are building a traditional cube.
So, all I have to do is turn these tables into the fields from my previous post, and then into the reports from my first football post. Simple!
But it wouldn’t be as much fun if I had picked a simpler example. Remember, I intentionally chose an extremely realistic and complex project, because we are really gonna kick the tires 🙂