skip to Main Content

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.

Filter/Grouping Tables

First, here are the tables that contribute to all of those Filter/Group fields from my last post:

powerpivot football dim tables

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.

Numerical/Play-by-Play Tables

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.

powerpivot football fact tables

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 🙂

Next Football Post:  Loading the Data >>

Rob Collie

One of the original engineering leaders behind Power BI and Power Pivot during his 14-year career at Microsoft, Rob Collie founded a consulting company in 2013 that is 100% devoted to “the new way forward” made possible by Power BI and its related technologies. Since 2013, PowerPivotPro has rapidly grown to become the leading firm in the industry, pioneering an agile, results-first methodology never before seen in the Business Intelligence space. A sought-after public speaker and author of the #1-selling Power BI book, Rob and his team would like to help you revolutionize your business and your career.

This Post Has One Comment
  1. I still use dim and fact to preface my power pivot data tables because I like easy hierarchy systems that I already know. can you think of any reason to stop using this nomenclature for power pivot?

Leave a Comment or Question