PowerPivot Accelerators: The Story (And Announcing: the Private Beta)

Sunday night update:  we’ve received a lot of exciting interest and have identified about 15 great beta sites so far, representing a broad cross section of the industry – BI and SQL pros, SharePoint pros, and Excel pros who are “growing up” into BI and SharePoint via PowerPivot.  Great international representation, too.  Still looking for a few more participants, so drop us an email – beta@pivotstream.com

Remove hardhat.  Pick up thinking cap.

Short version:  We have some more toys to share.  Skip to the end if you want in on the beta.

Long version:  In August 2009, while still a member of the PowerPivot team at MS, I took off my product design hat and put on my “user” hat.  From Cleveland, far removed from the internals of the product team in Redmond, I started this website, and embarked on The Great Football Project.    I was as curious as you how well the product would work.  Maybe that’s hard to believe, but I promise it is 100% true 🙂

I think it’s fair to say that applying a platform like Excel, SharePoint, PowerPivot, or SQL tends to foster a very different kind of expertise than that fostered by building or designing it.  You become much more familiar with the gaps, in particular, which is why those product teams listen so carefully to customer feedback (much more so than the Word team needs to, for example).

So I dove in, learning things at every turn.  About six months later, armed with the knowledge that PowerPivot performed VERY well in real scenarios, I dove in even deeper:  I left MS to join Pivotstream, where we started prototyping our PowerPivot infrastructure and models even prior to the product’s final release date.

“Gentlemen, we can rebuild it.  We have the technology…”

We started finding things in PowerPivot that we wished we could change.  Not earthshattering things.  “Last mile” type things – gaps in the feature set that made a big difference in practice.

That’s when my background as a software engineer became useful again.  Imagine working your whole career on MS products, where every day, your job is to identify flaws and opportunities to fix and fulfill.  And working right next to the people who actually reshape the products every day, as if the software were made of clay – stubborn clay, but clay nonetheless.

That breeds a certain optimism, a refusal to simply accept things as they are.  Instead of saying “crap I wish they had put X in the product,” my conditioned response is “hmm…  how hard can it REALLY be to fill that gap ourselves?”

Yes, it almost always turns out to be harder than it seems up front, sometimes MUCH harder.  But without that optimism, we probably wouldn’t have gotten started.  So it serves a purpose right?  Programmers everywhere are shaking their heads in disagreement, to which I reply, “Hey, why are you reading this?  Get back to coding!”  (Kidding.  Sorta.)

The Results:  A Complete System

I’ve already shared one of the projects with you:  the trimmed-down SharePoint pages optimized for the BI portal role.  And then their subsequent beautification.  Those have been in production for us for a long time now.

That’s just the tip of the iceberg though.  There are many other things we have done to improve our efficiency, or our customers’ experience, or both.  We are very proud of the results.  Here’s a glimpse:

1. Bulk Workbook Modification – when we started out, we would have to manually edit double-digit numbers of workbooks by hand whenever we wanted to make a change.  The same was true if we wanted to add or modify a lot of measures in bulk.  Not only was this inefficient, but also error-prone.  Today, our workbooks are very rarely touched by human hands.  We queue up a change and kick if off.
2. End to End Data Refresh – our nightly/weekly refresh process is completely automated, from FTP delivery of new data from clients, through SSIS, PowerPivot refresh of “Core” workbooks (triggered only when the underlying SQL sources are ready!), and automatic refresh of the “Thin” workbooks connected to them.  We can schedule Cores to refresh more than once per day.  In fact we can manually trigger a “right now” refresh of Cores in bulk, with basically one click.  In the Thins, we can even increment Date slicers to the latest value 🙂  Status updates are automatically emailed out to our team.  And the “refresh on open” problem is a thing of the past.
3. SharePoint Tools – there’s also a reasonable amount of drudge work involved with SharePoint stuff, particularly publishing and linking pages and workbooks.  You may not notice this, depending on how many workbooks you have, but for us, well, we have far too many to be clicking around in the SharePoint config UI all day, so we’ve built tools for that, too.

Even better:  these things will all remain valuable once SQL 11 / Denali ships, and we have things like BISM and Crescent to play with.  In fact I hear rumor that the SQL CTP2 beta release may be just around the corner…  are you pumped?  I’m pumped.

The Private Beta:  Now Taking Applications

All of that stuff is working great for us internally.  And just like with the SharePoint Pages, we’d like to share them with the community and recoup some of the development costs.  Getting these components ready for broader usage IS more work, however, and we’d like to recruit a small group of early beta testers to kick the tires.  The Accelerators won’t release until they are ready.

If you are interested, please send an email to beta@pivotstream.com and specify:

1. Your Name
2. Company Name
3. Current Usage of PowerPivot – personal or organizational, prototype/exploratory or already in production, and whether you are using PowerPivot for SharePoint or not
4. Which areas you are interested in testing (1-3 above)
5. Website(s) – your company website and/or blog URL if applicable

We’re going to keep this first round kinda small, maybe no larger than 10-15 participants.  So get your emails in, we’re anxious to start selecting the group 🙂