PowerPivotPro

 
 
“What’s in it for me?”

-Average Excel Pro

OK, the average Excel pro has not caught on yet.  Heck, most of them haven’t even heard of PowerPivot yet – it did just release this week after all.

Let’s say you don’t have much need for handling large data volumes, your organization isn’t large enough to worry about spreadsheet robustness/business intelligence/one version of the truth, and you haven’t yet seen what DAX can do for you in pivots.

Or maybe you get some of that, but don’t really understand this whole SharePoint thing.

If you are in either of those boats, here’s an example from my work this weekend.

My First Production, Scheduled SharePoint PowerPivot Report!

You know that chart of PowerPivot visitors by industry that I posted earlier this week?  For months now, I’ve been logging that stuff in an Excel workbook:

Over time, it’s grown to 500+ rows.  No big deal. 

Well, OK, there are a few problems.

1. Workbook editing on many machines.  I have a desktop, a netbook, and two laptops.  Guess which one always has the latest version of the workbook?  Yep, that’s right, whichever computer I am NOT using at the moment I want to edit the file.  So I get workbooks on every machine.  That leads to merge fun, duplicate records, and sometimes even lost records.  It also makes the process not a lot of fun, so I tend to neglect it at times.
2. No time/date stamping.  I’m too lazy to enter a date every time, so the best approximation of “date first logged” is the sort order of the spreadsheet.  Not so precise, especially once I sort and accidentally save
3. Sharing with others.  I like to keep my colleagues at PivotStream apprised of the nature of the visitors I get on my sites.  We’re always evaluating different technologies and I want to make sure they are constantly reminded of what a good decision it’s been to adopt PowerPivot   And, well, emailing workbooks around is also clunky.  (I won’t bore you with why it’s clunky, but ask me for details and I will oblige.)

Ideally, what I want is a central, unified copy of the list, that is also centrally editable, constantly timestamped, backed up, as easy to view as a web page…  and all with the editing ease of Excel.  No webforms, please.

Tall order.  But like Inigo Montoya, I may know something you don’t know.

SharePoint List With DataSheet View!

Have you ever seen a SharePoint list?  If you’ve visited the FAQ, you have, because, well, that’s just a vanilla SharePoint list.

Well, I transferred the list from Excel to a SharePoint list on our new PivotStream (internal) SharePoint 2010 farm.  With a few clicks in the web UI, I get the list looking quite spiffy:

But how did I get the data out of Excel and into the list?  That’s where DataSheet View comes in.  Here’s the SharePoint ribbon button you click to switch into DataSheet View, at right:

Once you click that button, you get an editing experience that is VERY similar to Excel or Access, as seen below:
  

It’s no accident that it looks a lot like Excel or Access really, since this view was first built by a mixture of engineers from both of those teams about six years ago.  This is an ActiveX control that is installed with Office client, and it’s one of the hidden little gems of Office / SharePoint integration.

Anyway, using this view, I was able to do a bulk copy/paste of all 500+ rows from Excel, directly into the grid, and then they just streamed up to SharePoint in the background.  Took less than 30 seconds.

Now, when I want to add a new item, I come to this view, and start entering data as if I were in Excel.  I even get autocomplete   SharePoint adds a date/time stamp behind the scenes.

Now it Gets Fun:  Importing the List into PowerPivot

OK, great, now I have a place to edit my list that suits all of my requirements.  No more multi-machine, multi-workbook merge hassle.

But now that my list is stored on SharePoint, naturally, I want to show some charts in SharePoint!

Here’s another feature you may not have heard of:  every SharePoint 2010 list is now also a valid Data Feed source.  Why should you care?  Because PowerPivot LOVES Data Feeds.

Time to click another button in the SharePoint ribbon, “Export as Data Feed,” circled in green below:

I get the usual series of helpful security dialogs.  Yay, I feel safe!  Doubly safe, since there are two dialogs, heh heh.

Right now, PowerPivot is the only client app (at least on my computers) that is registered to handle data feeds, so after that, I get a dialog from the PowerPivot addin that asks me which workbook I’d like to add this data into, followed by the import wizard:

I can also preview and filter the incoming data in the import wizard if I want   But I want all records, so I just click Finish, and the data gets imported into the desired XLSX, as data in the PowerPivot window:

Building the chart at that point is of course pretty trivial.

Note also that I can refresh the source data by clicking the PowerPivot Refresh button at any time, since PowerPivot remembers where the data feed is located up on SharePoint.  So from time to time I can update my report to reflect the latest visitor statistics.

But why do this manually?  I’m too lazy for that.

Upload to SharePoint

Since our SharePoint 2010 farm is actually MOSS Enterprise with PowerPivot for SharePoint installed, I can upload my XLSX now and have it render on the server instead via Excel Services.

Here is the chart from the workbook, rendering as an embedded web part on our internal team SharePoint site’s home page:

As I said though, I don’t want that chart to be stale all the time.  I don’t want to have to upload a new file every time I update the data.

With PowerPivot, I can schedule the refresh.  Go to the report gallery view where the workbook was uploaded, and click the highlighted little calendar icon (circled in orange):

Now I get this page that lets me schedule data refresh to happen automatically, like for instance, once a night:

The first time you do this with a new workbook, I also recommend setting the “Also refresh as soon as possible” checkbox so you can verify that it worked:

Summary

So, there you have it.  My simple list didn’t have large data volumes, multiple tables, complex calc requirements, or any of those things we typically talk about when we discuss the strengths of PowerPivot.

But even so, the combination of PowerPivot and SharePoint turned out to be the best solution to my 100% real-life problem.  It even took me longer to write this blog post than to set it up