Three quick news updates

A few quick items for this Friday, then I’m off to Cedar Point for some much-needed coaster thrills:

1) HostedPowerPivot Interest – Wow!

We’re blown away by the response to HostedPowerPivot.com.  I’ve spent multiple hours on the phone today with longtime blog readers – an added benefit of this is getting to know some of you better.  Very cool, seeing all the different usage cases people have in mind.

2) Webinar with Rackspace June 8th

I’m going to be doing a PowerPivot webinar with Jeff DeVerter, SharePoint guru at Rackspace, on June 8th.  It’s going to be more of an intro to PowerPivot, so longtime readers here may find it elementary.  But there will be some fun demos, and Jeff is a really dynamic personality, so I am looking forward to it.

Click here to view the agenda, and to reserve a spot

3) Part two of “portable formulas” is live on the Excel blog

I’m 100% serious that formula portability is one of the top 3 benefits of PowerPivot over normal Excel, it’s just taken me awhile to figure out how to explain it.

Click here for part two

…and here if you missed part one

HostedPowerPivot.com from Pivotstream & Rackspace

“At last we will reveal ourselves to the Jedi Bee-I.  At last we will have shared intelligence.”

A secret long kept, finally revealed

It’s a recurring theme – I see it in my training/consulting practice, in my inbox, in the survey results, and at events: 

“We LOVE PowerPivot.  It’s a perfect fit for our analysis and reporting needs.  But our company has not yet adopted SharePoint, and we don’t have the in-house expertise to stand up and support PowerPivot for SharePoint.    We just want the simple beauty of PowerPivot, we want it now, and it’s frustrating that we can’t have the full system yet.”

I hear you.  That is precisely where we found ourselves when I joined Pivotstream.  The lack of a “turnkey” solution to that problem meant we had to go build it ourselves.  And our core business has been running on our internet-based PowerPivot infrastructure since last summer.

But now, one year and two hosting providers later, we are finally able to share what we’ve built with the community.

A Long, Long Time Ago, In a Conference Room Far, Far Away…

OK, it was February, in San Antonio.  John Casey and I were at Rackspace headquarters for two full days to pitch an idea:  that an all-in-one, customized-to-your-needs, zero-hassle PowerPivot for SharePoint infrastructure would be a very valuable thing to the world at large.

We’d chosen Rackspace based partly on their reputation for support, but primarily because they had the most SharePoint expertise in the hosting business.  SharePoint, after all, is probably the most complicated part of running a PowerPivot server farm.  We were already moving our own server farm over to Rackspace at that point, but now we were pitching them on a partnership.

It’s a dicey proposition, walking into someone else’s offices knowing that you have to start from scratch.  We planned to cover the dynamics of the BI market, past and present, Excel’s place in it, Microsoft’s first-ever total alignment on a strategy, and why PowerPivot was going to change the world.  That’s a tall order for anyone to digest or believe in a short two days, no matter how fervently I believed in the message myself.

I underestimated them.  They understood perfectly.  We ended up meeting with 10-12 members of their leadership team over those two days, transitioning from “here’s a cool idea” to “here’s how we can execute.”

Keeping this a secret has been the longest three months of my life.  I am stoked that the waiting is over.

Want the short version?

Being that this is a blog – my blog, specifically – and that I love telling stories, my aim here is to describe how we got here – motivations, steps along the way, etc.

But if you just want to get to the “meat” of this, and/or request more information, go ahead and visit HostedPowerPivot.com:

Ok, back to the story.

Thanking Rackspace

I’m pretty sure we could not have done this HostedPowerPivot thing with anyone else, although I did not fully understand that going in.

We’ve been running our core PowerPivot platform in a Rackspace data center for four months, and the level of support we get from them is night and day different from what we had in our last data center.  They advertise “fanatical support,” and I’m a believer now.

As fantastic as that is, though, the word “support” doesn’t capture what continually impresses me.  I keep coming back to the human element – the real people on the other end who are acting like human beings and not cogs in a machine.  I’m not accustomed to big established companies, especially infrastructure companies, maintaining a nimble, entrepreneurial vibe, but that’s what I’ve found here.

For instance, does this sound like “support” to you?

Me:  “Hey Rackspace, we’ve found some unexpected PowerPivot performance results on this hardware set.  We’re now running some tests on every hardware platform we’ve got.”
Rackspace:  “Are there some other hardware options we can try out for you?  We’ve got access to a bunch of stuff here you know.”
Me:  “YES.  You’d need to install PowerPivot and run a bunch of tests on each machine, do you have time for that?”
Rackspace:  “No problem.  Send us the instructions and we’ll try it on 10 different machine types.”
…
Rackspace:  “OK, here’s a detailed spreadsheet of our results.  Three test runs for each unique config, reported separately and then averaged.”
Me:  “Did you say spreadsheet?  I think I’m in love.  We’ll correlate that with our other results.”
…
Me:  “OK based on all results, the best query performance would be achieved on a non-standard config, one with the following properties…  is that machine something that can be built out in your datacenters as THE standard PowerPivot server?”
Rackspace:  “Hmmm…  we’ll look into it and get back to you.”
…
Rackspace:  “Yes, we have approval to build that out.  Should we order one up for testing purposes?  We’ll have to have some new equipment delivered from the hardware vendor, might take a few days.”
Me:  “Yes please.”
…
Rackspace:  “Test machine racked and running.  And uh, I think you will be pleased.  It’s blowing the socks off of everything else we tested!”
Me:  “I love it when a plan comes together.  Gentlemen, we have ourselves a PowerPivot server.”

Thanks guys.  Too many of you to name specifically, but you know who you are

Step 1:  Register Domain.  Step 2:  Submerge in PowerPivot for a year

True story:  Jeff Elderton, our CEO at Pivotstream, registered the domain HostedPowerPivot.com before we even decided I was going to sign on.  It’s been in our plans from the beginning.  But before we could credibly do such a thing, we first had to apply the technology ourselves, for our own core business.  We dug into that while PowerPivot was still in beta, as our sole focus.

Along the way we had to solve all of the common problems everyone will hit.  We’ve written software to plug the gaps and provide a professional aesthetic.  We know how to “capacity plan” specifically for PowerPivot.  We’ve even figured out that certain hardware configurations can dramatically outperform the most commonly-used server configs.  We learned a lot more than we expected to.

All of that was expensive and time-consuming of course.  But it was absolutely worth it.  The things we deliver to our customers simply were not possible before PowerPivot.

Today, I’m pretty sure no one in the world runs a PowerPivot infrastructure of the depth and breadth of what we run at Pivotstream.  Our entire core business (subscription analytics for dozens of clients) runs on our PowerPivot infrastructure.  There’s no substitute for just doing something – I learned much more about PowerPivot from the outside, as an adopter, than I did as an insider, working on the team at MS.  That was surprising, although it makes sense in hindsight.

I’m really happy to see it all come full circle.  At his core, Amir Netz describes himself as an inventor.  I like that, I think it fits him quite well.  I’m similar in some ways, but it’s not like I will ever come up with something like the VertiPaq engine, so “Inventor” would be an overly generous description of me.  I like to think of myself as a creator.  I love creating useful things.  I love filling voids.  And this one has had me jazzed for a very long time.

1997:  Alabama 20, Vanderbilt 0

There’s one more story I’d like to tell, and it’s a bit of a cliffhanger because it deserves its own post.  Like so many other things around this blog, it all comes back to football:  there’s a connection between us hooking up with Rackspace, and the 1997 Alabama routine thrashing of Vanderbilt.

Just one of those fun little wrinkles in life.
 

Click here for THRILLING highlights. I wonder who posted these? Hmmm…

Buying a PowerPivot Server? Talk to Me First!

PowerPivot *IS* Fast… but takes some tuning

Most of us have seen the demos of 100 million rows in PowerPivot, and slicer clicks taking well under a second against those sources.  Super slick.

And whenever I first load a large data set like that and start working with it, I do indeed experience sub-second response times.  But once I go and build sophisticated measures, and put, say, 5-6 slicers on the report, things start slowing down, sometimes by a lot.

When you think about everything PowerPivot is DOING in those cases, it’s staggering.  But the report consumers don’t care – they want speed, not explanations.

So at Pivotstream, we spend as much effort tuning our models and reports for speed as we do creating them in the first place.  We’ve gotten much better at that over time, so it’s almost second nature to us now.

Multiple Times Faster.  AND More Cost Effective.

The quest for speed didn’t end with our modeling and reporting techniques though.  Remember, we’ve been at this for over a year now, and eventually we started to brainstorm and experiment on hardware platforms.  The results have been surprising – in a very good way.

Being a relatively small company, we needed help in this quest, so we approached a leading technology provider for assistance.  We weren’t sure what sort of response to expect – would they understand / take it seriously / appreciate the opportunity?  And even if so…  would our collaborative investigation yield results?

Yes, yes, yes, and BIG YES.

As a technology professional, this has been one of the most stimulating experiences of my entire career.  Real science fiction, cutting edge experimentation…  which is why for now, I’m going to refer to our partner as Cyberdyne.  (This is all so fresh that there are still some legal details to tidy up before I can disclose their true identity).

Anyway, bottom line:  what we’ve developed is both multiple times faster than off-the-shelf server hardware…  AND at the same time, almost certainly more cost effective.

Our Version of the Pepsi Challenge :)

I’m quite confident that our Black Box (as we are calling it today) will blow the doors off of literally ANYTHING you can get your hands on through any other channel.

So, before you sink big money into PowerPivot server hardware, talk to us.  There’s a good chance that we can save you a lot of time and money.

If you are somewhere in that process today, from early evaluation to the final stages, drop us a note – info@pivotstream.com

Beta program update

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

What beta, you ask?  Click here to see last week’s announcement.

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

Customized SharePoint Report Portal

 

 

“I can’t believe it’s not SharePoint, er, Excel, er, wait… it IS Excel and SharePoint…  so confused…”

-Me

 

 

 
A couple weeks back I posted screenshots of our custom SharePoint master pages.  The functionality was great, but the aesthetics were still a little bland.

We’ve had some plastic surgery done since then.  Here’s the new home page:

Redesigned Customer Report Portal

We kept all of the functionality from before (no Ribbon or Site Actions menu unless you are an admin, etc.) but there’s a new component now as well:  the treeview.  There are five reports under each node of that tree, so our customers can quickly find any of the 100+ reports they are looking for.

(No, we don’t use the built-in Report Gallery – it really doesn’t handle more than 2-3 reports in practice).

And here is one of the redesigned report pages:

 
Redesigned Customer Report Page

As I said before, if you need advice or help doing something similar, drop us a note, we’ll see if we can work something out so that you don’t have to start from scratch:  info@pivotstream.com

Cleaner SharePoint pages, and an offer to share

 

 
 
“Dr. A. Heller. Weapons designer.
Innovator. Inventor. World changer.”

“…why does he live in a deserted amusement park?”

 

Building an Arsenal of PowerPivot Components and Tools

In the past six months assembling the core PowerPivot-fueled platform at Pivotstream, we’ve been developing some components and tools to supplement the core PowerPivot v1 product, both in terms of what our customers see as well as “behind the scenes” tools for us.

You might expect this from an ex-Microsoftie whose prior job was designing software – I really can’t stay out of that game completely.  (Doubly so given my background on the PowerPivot and Excel teams).  I’m always starting sentences out like “You know, I bet we could…”

One small example of this that I shared last week:  VBA macros that we’ve been using and modifying to make workbook creation/editing go faster.  That’s just the tip of the iceberg though really, so let’s take a look at the first in a series of larger components.

First Example:  Cleaner SharePoint Pages

First, consider what the top of the default SharePoint page looks like:

Default SharePoint Home Page Etc. 

And what the default Excel Services report page looks like:

 
Default Excel Services Report Page

Not bad for out of the box, but there is definitely room for improvement.  Let’s look at those again with some elements highlighted:

 
Extraneous Elements Highlighted

Things that many BI sites would rather NOT have:

1. Site Actions Menu – This is fine for a standard SharePoint experience, where users are often pseudo-admins of the site.  But for a BI site, where you want as little noise as possible, and don’t want users to have anything resembling admin control, this is a liability.
2. SharePoint Ribbon – Don’t get me wrong, I actually am LOVING the SharePoint Ribbon.  Way more than I expected.  But again, on a BI site, the user’s task is to navigate as quickly as possible to actionable information.  And you will be surprised how often someone will call you and say that they can’t find the option they are looking for on the Page ribbon tab, when the button they want is right there in the report.  Again, it’s just noise in the BI scenario.

Now let’s take a look at the Excel Services page again, with the same level of focus:

 
Extraneous Elements Highlighted Again

Things that many BI sites would rather NOT have:

1. Excel File Tab and Toolbar – Just like above, this is a great element for many scenarios.  If you view Excel Services as “Excel in the browser,” which is precisely its mission in most cases, then yes, you need a File menu for Saving, Downloading, Checking Out, etc.  But in a BI site, again, it’s noise, it’s confusing because it’s read only, etc…  plus it distracts from the well crafted perception that this is NOT Excel but a Web Application.  Most people just feel GOOD about web apps, and they feel kinda icky about Excel.  People are more likely to use something that they feel good about.
2. File Name – Same thing.  The feel-good vibe of the app is disrupted by the presence of an Excel file name.  “Are you saying that’s a file on my desktop?  Where is it?”  Subtle little things like this are easy for techies like us to dismiss, but ignore them at your own peril.
3. Unsupported Features Bar – This one requires little explanation, it looks horrible.  And it suggests to the user that something is broken, when really, it’s just those slicer parent controls.  In the VBA post I gave you a macro for removing all of those right before you save, but in practice that isn’t a viable solution.

The Simplified/Modified Versions

None of those problems was going to fly for us at Pivotstream, so we developed our own modified set of page templates.

First, a really simple, sample home page as a customer would see it:

Note also the little copyright notice at the bottom, that’s built into all of our pages now as a result of the template.

These templates are also themeable, so we can change the color scheme, page content, and images from within SharePoint itself with a few clicks:

(No one has ever accused me of being an artist, so at time I choose intentionally ugly colors – you can do much better I am sure.  Bet you can’t whip up a PowerPivotYoda like I can though).

Improvements to Note:

1. No SharePoint ribbon to distract
2. No Site Action menu (ditto)
3. No Search box (we can turn that back on once we have it hooked up to work properly)
4. No QuickLaunch menu telling me about all those doclibs and such on this site that I don’t want the users to concern themselves with.
5. Lots of real estate for adding content and links.

Customized Report Page Templates

Next, here’s a report page we have in production, with a very minimalist style:

Zooming in on the top, we see:

Improvements to Note:

1. Still no SharePoint Ribbon or Site Actions menu
2. No Excel “File” ribbon
3. No “Unsupported Features” warning
4. “Download Snapshot” appears as a link in the header, separate from the File ribbon
5. No Excel Icon on the browser tab – actually it is usually our Pivotstream logo, but IE forgets those icons all the time and puts the little IE logo in instead.  Sigh.  Firefox is much better about that I’m told, but I’m still running IE.
6. No Excel filename in the title bar
7. “Breadcrumbs” nav bar in the header, for nav back to the site home

In other words, everything extraneous removed, and only functionality remains.

Fear not, admins still see the suppressed controls!

Of course, when *I* log in to these sites, I often need to make changes, like add a new page, update a setting, etc.

So, I need a lot of the things that are suppressed in the screens above, like the Site Actions menu, and the SharePoint ribbon.

The screenshots above were all taken when I was logged in under my User account.  Here’s what I see when I sign in as my Admin account:

Neat huh?  It detects I am an admin, and gives me back all the stuff like the SharePoint ribbon, Site Actions, the notification I have the page checked out, etc.

Under the hood

OK, so what all is going on here, in order to make this work?  Here’s a rough list:

1. A new SharePoint Master Page, from which all user-facing pages are derived
2. Admin vs. User detection logic in that Master Page, and subsequent suppression of certain page components when User
3. A new Layout for the report page, that includes the Excel Services web part with default settings already applied
4. A new Control (part server-side and part Javascript) that implements Download Snapshot functionality without having to expose the Excel toolbar (whose options are confusing in a pure BI environment).

That’s about all I am qualified to convey – I supervised the creation of all of this, but I did not code it.  (Similarly, I can tell you about how Excel’s calc chain involves a separate linked list per processor, but I couldn’t tell you how to write something similar now could I?).

That Excel Services Toolbar – Isn’t that part (hiding it) simple?

Yes and no, and then no again :)  On one hand, it IS easy to create a new web part page, slap an Excel Services web part on there, and then customize its settings to your liking.

What I did NOT find easy, was repeating that process every time I wanted to publish a new report.

With this new system, I just instantiate the right template, and change one setting to point the template at the right workbook.  Done.

Also, the Download Snapshot feature turns out to be pretty important.  For us, and most locked-down BI deployments of PowerPivot, the snapshot feature is the ONLY way to print something that looks decent.  And it’s the only way to take a fixed view of the entire workbook offline.  So, we didn’t want the File menu, but we needed that feature to stay.

So we re-implemented it :)

What about the Sharing thing you mentioned?

It cost us well over $20,000 in billable time to build all of this, in addition to my time designing and supervising it.  In my opinion, there is no sense in others having to sink the same cost (and their own time) rebuilding the wheel.

If you are interested in getting your hands on any of the components I described here, or if you are interested in whether they can be customized to your specific needs, drop a note to:

     software@pivotstream.com

Unlike the VBA Macros and other small stuff, I can’t give away modules like this that the company paid for.  But I am free to explore the idea of sharing with the community at a fraction of what it cost us, as a means of recouping some of our development budget.

And if I can make this sort of project pay for itself, that just means I can spin up other such projects :)  This is very much an experiment at this point.

Anyway, drop us a note if you’re interested, and we’ll see if we can figure something out.