PowerPivotPro

Recall that in yesterday’s post I linked my FactEvent table to the CleanPlayers table via a relationship.  Time for the payoff.

But you know what?  I’m tired of calling it the FactEvent table, and then constantly having to tell everyone that’s where the plays are stored.  It’s the Plays table, damnit, so I’m just gonna right-click and rename it before moving on:

                 Becomes… 

Which does NOT impact the relationship I made.

Just in case you doubt the value of Beta releases…

I’ve actually hit a bug here where the relationship I created yesterday prevents me from saving and re-opening the file.  I’ve filed the bug and expect it has something to do with either the data cleaning I did or the fact that CleanPlayers is a linked table, because I have not seen this before.  As Dennis Miller used to say, “Well at least you know it’s live…”

Time for Multi-Table Pivots!

Showing this feature is going to take discipline, because there are a number of fun things along the way to distract me (ooh!  shiny!).  I’ll try to note things in passing and promise to return to them later.

I want a PivotTable that shows me some aggregate data.  So I go to the ribbon in the PowerPivot ribbon, click on the old reliable PivotTable gallery button, and…

…we see a bunch of intriguing options there!  But even though I designed that feature and love it like a child, I’m just gonna stick to the basics and go with a Single PivotTable   (I promise, we’ll come back later!)

I choose to place this PivotTable in a new sheet, and now I see this:

Couple things to note:

1. The PivotTable is on a normal Excel sheet – that’s important.  The PowerPivot addin does not render a single pixel of report or visualization.  That is ALL Excel, which is good, because we want everything to render to the browser via Excel Services later.
2. This is not your father’s field list – there are definitely some new things happening over there on the right side…

Let’s take a closer look at the top of the field list first:

Every Table from the PowerPivot Window Appears Here

Yes, all 40+ of them.  I’ve expanded a couple of the smaller ones so you can see their columns.

This is a big departure from normal Excel PivotTables, and their “one table at a time” restriction – if that seems limiting to the BI Pros out there, well, it is.  See what your Excel friends have labored with all these years?

Search Box

And since that means a whole lot more fields than your average Excel field list, we’ve got a Search control at the top (great work team, getting that in!)

The bottom of the field list also contains some new goodies:

The bottom four dropzones should be familiar, but the top two – Slicers – are new.  I can’t resist, so I’ll give a quick preview of those in a second.  For now, though, I want to build a quick, simple report.

First, I check the Yards field under the Plays table:

  which yields:

                                                            in the Excel grid.

BI Pro sez:  “OK, What Happened There?”

If you’re a BI Pro and you’ve been taking notes, you know that under the hood, PowerPivot is SQL Server Analysis Services, and SSAS doesn’t just fork over numbers.  It needs “measures” defined first.

Well, the addin defined one on the fly.  The user checked a checkbox, the addin sees that the field in question (Yards) is numeric, then tells the PowerPivot engine that it wants a Sum of Yards measure defined.  Once that is complete, the addin then tells the Excel OM to add that measure to the current PivotTable.

So… the Excel user gets the experience they are accustomed to (numeric fields default to Sum’s, in the data area of the pivot), and BI Pros can understand that a real OLAP measure has been created behind the scenes.

Moving on, I then scroll the field list down to the CleanPlayers table and check the FullName checkbox (recall that I added this field yesterday using DAX):

  which yields:

                                     in the Excel grid.

(Translation for BI Pros – in this case, FullName was just an attribute – all columns in the PowerPivot window are attributes actually.  And since the addin detected it was non-numeric, we just slapped it on rows without creating a measure.  Which again mimics Excel behavior).

(Note for football geeks:  Aaron Gibson, –272 yards lifetime?  And look at Aaron Rodgers, still riding the bench behind Favre back in 2006, the last year for which I have data.  BTW, don’t trust any of these numbers yet – I think I have a lot of work left to do).

What say you, Excel Pros?

You impressed yet?  One little 4-box relationship dialog from the last post, and suddenly you are pivoting one table by the fields in another?

Still letting it sink in, eh?  OK, let’s drive it home.  Every field in the CleanPlayers table is now a valid field for grouping/filtering/slicing the Plays table…

The Grand Finale (for today)

Remember those two mysterious extra dropzones for Slicers?

Now I’m going to grab the BirthStateName field from the CleanPlayers table, drag it into the Slicers Vertical zone, and I get this…

Yeah, that’s called a Slicer.  A new feature of Excel 2010.  Works a lot like a Page Filter (aka Report Filter), but more graphical and “Fisher Price,” to quote a former colleague.

Wanna see just players born in sunny states like FL, AZ, HI, and CA?  Single click to select one, ctrl-click to select multiple:

And then my personal favorite, single-clicking England:

I give you Osi Umenyiora, the only, ahem,  English Football Player in the NFL.

One little relationship suddenly tells us quite a bit doesn’t it?

(It even gives us cheesy jokes that equivocate on the term “English Football.”  Such power.)

Next Football Post:  My First Video Post >>

In the previous PowerPivot football project post, recall that I’d encountered some dirty data.  Even in the world of sports stats, data quality is a huge problem:

Some players have names, others are just numbers.  And we can’t edit/delete these records in-place, for the PowerPivot compression reason I outlined last time.  I mentioned three options to address this problem, so what are they?

Option #1a – Pre-filter the data during import (graphically)

In my previous post, I used the point-and-click method to grab my database tables out of SQL Server.  I performed no filtering whatsoever, even though there IS a really simple, Excel-style UI for filtering the data included in the import wizard:

In many cases, that will work for you – you can deselect certain values and set simple rules like “equals” and “does not equal.”

In my case, though, I don’t think that works.  I just have too many bad values to uncheck them individually.  And there isn’t a rule for “is text” or “is not numeric.”  (Something we should add next time I’d think).

So, moving on to:

Option #1b:  Pre-filter the data during import (by writing SQL)

That same import wizard also contains a tool that lets you write (or paste) you own SQL statements:

Which I’m pretty sure I could figure out how to do if I researched “filtering non-numeric values using SQL” on the web.

But for now, let’s stick with the “I’m just an Excel user” vibe. So…

Option #2:  IT Gives me Clean Data to Begin With

Does that sound lazy of me?    Not really.  Did you see the post earlier today on Enterprise BI Mashups?  One of the quotes in there is about how IT focuses on data sources and data quality, freeing business users to build better reports and analyses.  Specialization.  Division of labor.

And while PowerPivot does not require it, it’s still very much a best practice.

Option #3:  Clean the Data in Excel with a Linked Table

Let’s go with the most Excel-style solution.  PowerPivot has Clipboard support!

So…  I can select the entire DimPlayers table (ctrl-A), copy it (ctrl-C), switch over to the Excel window of the same workbook, and paste it.  Then it’s just a matter of sort/filter, and delete to get a table that only contains names:

OK, so how to get it back into PowerPivot?  Do you see the “Create Linked Table” button in the Excel ribbon above?  That’s a feature of our addin that copies the selected Table from Excel into PowerPivot, and then links the PowerPivot table back to the Excel table.

(Note that in order to do this, you have to format the Excel data as a real Table.  I use Ctrl-L)

Here’s the cleaned table back in PowerPivot:

See the little link icon on the sheet tab?  That means I can edit the data back in Excel, flip back over here, and the edits automatically flow through (actually, we replace the entire table, but the effect is the same).

Maintaining the Table in Excel

There are some lists that are just meant to be maintained by a human being.  For those, this linked table feature is perfect – open the spreadsheet every now and then, update the list, PowerPivot refreshes, and then you re-publish.

This local-editing-thru-Excel also works well in my case, since I am not getting any new data from Stats (although…  please please please, Stats, give me a free license – we will make beautiful music together).

The one case where this won’t work very well is if the Players table data changes all the time on the backend.  I don’t want to manually update my workbook all the time.  Remember, scheduled automatic report refresh is a big PowerPivot feature.  In those cases, options 1 and 2 are the ways to go.

Next Football Post: Formulas and Relationships! >>

No more messing around, let’s dive in. 

Rather than boiling the ocean up front, let’s set a modest goal for getting started:  I want to end up with a report that shows passing yards by player.  (PowerPivot lends itself quite well to this approach – no need to build an all-encompassing model up-front like with traditional BI tools.  This is more like…  Excel.)

First, I launch Excel 2010 (with the PowerPivot addin installed), and using the PowerPivot ribbon tab, I open the PowerPivot window:

(I’m not yet set up to do videos, but will be soon.  For now, let’s make do with pics).

Then, since my football tables live in a SQL db, I use the “From Database” button, type in my server and db name, and end up here:

As I showed yesterday, there are 40+ tables in there.  Some of which I probably won’t ever need.  And I certainly don’t need them all in order to deliver my first report.

But it’s just so much easier to grab all of the tables now.  That way I can browse them all, look at the data, and decide which tables I need for the report in a hands-on manner.

So I just click the little “select all” checkbox at the top-left.  Great feature…  that I argued against for awhile.  I was wrong.  I use it all the time.  Amir and Olivier, allow me to apologize publicly

Hit Finish, and I wind up with this:

A few things to note:

1. I’ve maximized the PowerPivot window.  Don’t worry, I can switch back and forth to Excel as much as I want, without closing this window.
2. There is one “sheet” per tab per table from the database.  Gives Excel users a comfortable way to browse the data.
3. I am actually seeing the data, just like in Excel.  No abstract schema browsers here.  We get real data, all of it, to work with.
4. I have expanded the sheet navigation “overflow” popup.  Given that PowerPivot will be working with a lot more “sheets,” on average, than Excel, it needed a revamped navigation concept.  (We stole this from OneNote – thanks folks!)

All good.  But there’s a problem here, too, that you may have noticed.

I have dirty players!

No, I’m not talking about Hines Ward.  (Leave the guy alone, ok, he just “plays hard.”)  I mean that my players table contains some bad data – players for which Stats Inc. did not record player names, and instead recorded numbers.

What do I do about this?  The PowerPivot window itself is read-only – I can add formulas, which I’ll show later, but I cannot edit or delete rows. 

Why Read-Only?

The reason for this is the remarkable compression employed by PowerPivot – some db’s shrink to 5% original size.  And that same compression is used, in-memory, to deliver faster query results at report and analysis time. 

Jamming individual edits into that compressed structure, while maintaining lightning-fast query speed – well, let’s not go there.  Outstanding compression and query perf.  It’s a good tradeoff, especially given the other methods available to me.

OK, so how *can* I clean the Players table?

I have three options, which I will cover in the next post.

Next Football Post

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:

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.

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 >>

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

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.

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 >>

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).

Screenshot #1:

“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).

Other Reports

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:

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.”

Key Takeaways

A lot of fun stuff above, at least for me.  But getting serious at the end here, let’s summarize the important points:

1. Yes, you can build very attractive reports in Excel – even more so now that we have Slicers in 2010
2. Cubes are very powerful, flexible, and re-useable databases for reporting and exploration of your data
3. Cubes are, however, traditionally expensive and time-consuming to build
4. PowerPivot aims to deliver many cube benefits, as well as other benefits of traditional “hardcore” BI, in a fraction of the time and cost
5. 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
6. The MSN project had to implement much of the “glue” that PowerPivot delivers out of the box, such as the Report Gallery
7. 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

Next Football Post:  I Have My Work Cut Out for Me >>