PowerPivotPro

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

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:

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.