I have my work cut out for me…

October 28, 2009

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


SharePoint Conf 2009: Five Observations

October 28, 2009

Allow me to let you in on a little secret:  when Microsoft employees attend Microsoft conferences, we are often learning just as much as everyone else.  The company is just too big, and we’re all just too busy, to really keep tabs on everything that’s going on, even with our immediate partner teams.

Last week’s SharePoint conference in Vegas was no exception, and was in fact the most eye-opening yet for me.  The moments where I sat/stood there thinking “Really?  We have that now?” really stood out this time.

So…  5 Things the ‘Pro Wants You to Know (About the Show)

#1 – SharePoint is suddenly HUGE

Where did this come from?  It wasn’t long ago that Microsoft teams were skeptical of betting on SharePoint – “it’s still new, are customers really adopting it,” etc.  Even while working on a team that was betting heavily on SharePoint, I must have fallen asleep, because WOW!

I’ve heard attendance numbers ranging from 6,000 to 9,000, and I’d believe anything in that range.  Wall to wall people (think:  techie Mardi Gras).  Quarter-mile hikes between sessions.  Sold out – even I had to show up without a reserved spot, and pull a major Houdini to get in just to staff the PowerPivot booth.  Row after row of booths in the exhibit hall, some of which had no immediately obvious connection to SharePoint.  That told me something:  SharePoint is now a platform and a center of gravity in the business world.  This felt a lot like the PDC I attended in 1998, when Windows was still the focus of basically all commercial software.

Oh, and there’s even a real-life SharePoint fairy now!

SharePoint Fairy

(I didn’t meet her – SharePoint fairies are intimidating! – but I hear she’s the marketing director for one of the vendors at the show.  Different fairy outfit each day, and walking around in those shoes can’t be comfy.  My feet and legs were hurting each night, and I wore Pumas most days.  Plus she had the guts to carry this off for a full week, smiling and outgoing all the time.  SharePoint Fairy, we salute you!)

Anyway, if you’re like me and have been snoozing on this one, here’s some advice:  investigate SharePoint.  Think about what it would mean to translate your apps/solutions/etc.  The customers are ahead of us this time.

Coming up:  Items Two thru Five


US Meteorological Data

October 28, 2009

Kasper asked me to share a pointer to the US weather data I used in the mashup example I posted last night (when I revised the “What is PowerPivot” post – sorry if you only saw v1).  I figure others may be curious too, so I’ll share here rather than in comments.

I did quite a bit of cleaning/shaping (in Excel) on that data before I was able to use it.  Rather than be cruel and say “that’s an exercise for the reader, here’s the website, good luck,” I’ll share the workbook I used for cleaning it, and include some explanatory comments so you can follow what I did.

You’ll have to rename this file to be .xlsx, because WordPress doesn’t let me upload Excel files… which is just incredibly poor judgment on their part, as I’m sure you will all agree :)

Click here to download the data


What is PowerPivot?

October 28, 2009

I think a lot of people arriving at this site for the first time already know quite a bit about what PowerPivot is, but a few people have expressed curiosity:  it would be easier to understand why the site is called PowerPivotPro if we knew what PowerPivot was in the first place.

There are a lot of good places to read about PowerPivot, so I won’t go super deep here.  Instead I’ll give you a summary in my own words, and then point you elsewhere for the details and the snazzy demos.

First, the (previously missing) sizzle

I realized the first version of this post contained ZERO picture, just a wall of text.  I have higher standards than that.  So quickly, here:

powerpivot report

This is the sort of thing that PowerPivot produces.  That’s a screenshot taken straight out of Excel, believe it or not.

OK, now imagine…

  1. That same report, published as an interactive web app, with a single click
  2. The consumers of that web app never needing to know it was produced in Excel
  3. The report itself literally taking less than 5 mins to produce (and they can look much better – this is actually one of my “zero effort invested” samples)
  4. Prepping the data behind that report taking less than 30 minutes
  5. The report being based on your company’s sales data, even up to hundreds of millions of rows’ worth (!), but still very fast
  6. The report automatically refreshing with new data every night, hands free
  7. IT loving this flavor of Excel reporting, rather than worrying about the risks

…and you’ve got the spirit of what PowerPivot can do.

Filtering by Temperature???

I can’t resist pointing out that this report takes a company’s sales data (in this case, from the sample db AdventureWorks) and “mashes that up” with real data I pulled down from the US Meteorological Site.

In about ten minutes’ time, now I had the ability to filter my sales data by the temperature that was recorded on the date of the sale, in the location that the sale was made.

If that doesn’t excite you, perhaps you have not understood it :)  Which I understand – it deserves more explanation really.

(Now I’m tempted to dive in and just show everyone that example, it’s so much fun.  I’ll save it for later.  Tomorrow, I’m back to the football stuff.)

“You still haven’t answered the “what” PowerPivot is, Rob!”

Yeah I know.  Short and sweet, PowerPivot is two things:

1) An addon for Excel 2010 that helps you design reports in Excel, based on killer data models that get built as you build the report

2) An addon for SharePoint 2010 that enables the reports to render interactively (in conjunction with Excel Services), as well as some other nifty server-side capabilities

…what follows, from my original post, could almost be titled “Why is PowerPivot?”

Quick Refresher:  What is Business Intelligence (BI)?

Broadly defined, BI encompasses any data-driven analysis or reporting for business purposes.  If you’re looking at data in an attempt to make better business decisions, or doing something as preparation for looking at data for that purpose, well, that’s BI.

But traditionally, when someone has said “BI,” typically they’ve been referring to those BI activities that are executed by the IT department:  the construction and delivery of standard reports, scorecards, certain flavors of applications, and the plumbing that makes it all possible.  Things that require developers and database pros, in other words.

Of course, the IT department only has so much time and resources.  Of all the needs for digestable data in the company, they maybe have time to address 5%.  (Some people might say less than that).  It’s the most important, most central 5%, but there’s still a lot of unmet need.

What happens to the other 95%?

If IT can’t do it, what happens?  The short answer is that the business units do it themselves, without IT involvement.  There are a number of tools that they use, but by far the most common is Excel.

Excel gets the job done.  A tremendous percentage of the world’s business decisions are brought to you by Excel.  (And the letter Q).  It’s a great tool – flexible, powerful, end-user friendly.  It’s even taught in business schools.  It’s sometimes called the “language” of business.

There are, however, some problems that arise.  Here are a few:

  1. Usually, people share their Excel reports and models by simply emailing the files around.  This carries risk, since the data is often sensitive.
  2. When the author of the Excel report updates it (or fixes it!) there’s no good way to make sure everyone is using the latest.
  3. Keeping the report up to date as conditions change is often a lot of work, especially as the number of reports grows over time.
  4. If the report author leaves the company, or is even just out sick on the wrong day, key business processes can grind to a halt.
  5. These reports often get their data through very clever methods that the IT department is unaware of.  So IT often unknowingly breaks them by making a change to backend systems (and then IT often gets blamed for it).

As a result, there has been a longstanding but low-grade tension between IT (who sometimes wish that Excel would just disappear) and the business units’ Excel power users (who sometimes wish that IT would stop complaining).  Neither side is right or wrong really – it’s just a natural, inevitable tension, given the state of things.

PowerPivot magically fixes all of this, right?

Why, of course it does :)  OK, it’s not going to fix everything, and the things it does fix, will take some adaptation on the part of both sides of the business.  But PowerPivot does indeed bring a number of things to the table that have never existed before, and that will certainly improve the situation dramatically when properly deployed.  Here are a few:

  1. More powerful tools for the Excel authors.  Things that used to take hours will now take minutes (or seconds), and some things that used to simply be impossible become possible.
  2. A secure and “instant update” method for sharing the reports.  No more sharing the files directly – now you can publish the workbook to SharePoint, where it becomes an interactive web application.  The consumers get all of the benefit, but they never download the file.  Less risk to sensitive data, and when I publish a new version, everyone gets it the next time they visit the site.
  3. Scheduled, automatic report refresh.  You can configure the reports to automatically refresh nightly (or on any other interval) without human intervention.  Less hassle for the publisher, and less risk of an outage.
  4. Transparency for IT.  Since these reports are all stored and executing on centralized servers, IT has the ability to see what apps are out there “in the wild,” which they cannot do today.  They furthermore can see which apps are being used most frequently, who is using them, and inspect them to see what systems they rely on.  Again, less risk of outage.

That list of benefits typically only come with a “real” BI solution.  This is why PowerPivot is often referred to as “Self-Service BI.”

Lastly, it’s crucially important to note that PowerPivot delivers all of those benefits without requiring the business users to adopt some new IT-approved tool.  PowerPivot is merely an extension to Excel, and an extension to paradigms that are already well-known by those users.  …Which is particularly exciting for me, because, hey, now even I can use it :)

Rob, you said it was going to be brief!

OK, yeah, it ran a little longer than I wanted.  But there’s a lot to cover here, and I want to make sure everyone has the chance to understand – Excel users, BI pros, and SharePoint pros alike.

As always, questions welcome.

For more info…

A few links here, but you can search on “Microsoft PowerPivot” or “Microsoft Gemini” (the old codename) and find a lot of great stuff.

Official site:  http://powerpivot.com/
PowerPivot Team Blog:  http://blogs.msdn.com/gemini/
Twitter:  powerpivot
Facebook:  http://www.facebook.com/PowerPivot
Donald Farmer’s Video Series: Geminute