Couldn’t Resist… Parameterized PowerPivot Report!

In the intro to David’s guest post below I mentioned some new applications we are building.  Check out a PowerPivot report where, in the browser, you can type in a SKU number and get a full dashboard for just that product!

Click for Larger Version

Much respect to our resident Data Junkie Monkey, aka DJ Monkey, for pulling this together.

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…

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

Hardware/Capacity Planning: From the Trenches

So How Many Servers Do I Need in My PowerPivot SharePoint Farm?

As people increasingly move up from just dabbling with the addin, and decide to start leveraging the publish/schedule/share/secure benefits of the PowerPivot for SharePoint infrastructure, I am getting this question more frequently.

How many servers?  How much RAM?  And less often but just as important…  how many CPU’s?

Time for an old joke:  “Ever hear the one about the statistician who drowned in a river that was, on average, only 3 inches deep?”

That’s the whole joke.  And as time goes on, I only find it funnier.  And funnier.  But first, some basics.

The Three Primary Server Roles

For the vast majority of PowerPivot deployments, you will mostly need to concern yourself with three server types:  Excel Services, PowerPivot Engine, and Data Source DB Servers:

 
(Click for Larger Version)

A few notes on the diagram up front:

1. I recommend viewing the larger version – there is information on the diagram (in the notes) that I will not repeat below.
2. Don’t read too much into the 3/4/2 ratio of Excel Services/PowerPivot/SQL – I included different numbers of servers in each tier intentionally, to illustrate that you can scale each tier out at independently.  But, for instance, I do NOT expect you will need 3 Excel Services boxes for every PowerPivot box (you will likely need less).
3. Although separated above, server roles CAN be combined onto single servers – for instance, many folks can get by with an “all in one” server where everything above lives on a single box.  Also, even in a multi-server farm, one of the MS-recommended configs is to combine Excel Services and PowerPivot onto a single box, and then deploy as many of those combined boxes as needed.
4. There are some elements missing from the diagram – for instance, there is a Web Front End (WFE) role, and a SharePoint Configuration Database role, but in my experience, PowerPivot does not put unique strain on those elements (except for config db disk space).  Your mileage may vary of course.

Early Planning Efforts

Back in the Spring, I set down to the very serious task of “how much hardware will we need on our SharePoint servers in order to handle user loads?”  And naturally, I made a very serious spreadsheet to model it out:

Wow, what a spreadsheet.  I mean, I even used the =POISSON.DIST() & =BINOM.DIST() functions – clearly, such spreadsheet horsepower indicates accurate results!

Turns out, that spreadsheet was nothing more than an amusing theoretical exercise with little bearing on the real world.

There were multiple problems with that spreadsheet:

1. It relied on outright guesses as to how often/how intensely consumers would utilize the published reports.
2. I was not yet aware of the CPU-gobbling power of slicers, a power that makes real-world queries many times more CPU-intensive.
3. Most importantly, it assumed peak usage would occur in the morning, when consumers are most in need of fresh information.

Solution for 1) and 2)

These are in some sense the simplest to address.  Quite simply, put up a pilot solution and observe the usage characteristics.  You will learn a lot about usage patterns as well as what that does to CPU and RAM.

But even better:  you may choose to simply ignore these factors for now and focus on problem 3 instead.  Here is why:

Peak Load is Probably Experienced During Scheduled Refresh

If you’ve looked at the larger version of the diagram above, you have seen a hint as to what I’m about to say here: 

At Pivotstream, our nightly refresh process puts far more strain on our servers than our users do, and is what we now plan our hardware around.

I will explain further, so you can evaluate whether your situation will be similar.  We receive new data on a nightly basis (typically late at night).  That data gets ingested into SQL server, and then our PowerPivot refresh process begins.

Once PowerPivot refresh begins, we have about 6 hours, tops, to get all models and reports refreshed, so that when business opens in the morning, everyone has access to fresh insights.  6 hours sounds like a lot…  until you discover that it isn’t.

A lot happens during scheduled refresh!

Keep in mind that the PowerPivot model (the embedded database living inside your PowerPivot workbooks) must first be refreshed – let’s call this phase “Model Refresh.”  This can put a lot of strain on your SQL servers just in terms of supplying the data, since PowerPivot v1 pulls a fresh copy of all tables (no incremental refresh).

As the data flows into the PowerPivot servers, a LOT of CPU power goes into compressing that data into the storage format.  During the refresh process, RAM usage steadily climbs as well.

As model refresh nears completion, RAM usage spikes upward quite a bit, sometimes by as much as 50-100% the size of the resulting workbook.  CPU usage also spikes during this phase.

Once the model is done refreshing, PowerPivot for SharePoint then triggers a refresh (via Excel Services) of the Pivots and Cube Formulas in the workbook, so that when thumbnail screenshots are “snapshotted” for the report gallery, those reflect the latest data (let’s call this phase “Pivot Refresh”). 

Yes, that process initiates via Excel Services, but that refresh immediately results in a meaty set of queries sent back to the PowerPivot servers for processing.  So, CPU usage spikes again.  And the model in question is held in RAM while that happens, so that RAM can’t be recycled into the pool to be used for model refresh.

Remember, this all happens for a single workbook.  And if you hang a bunch of report-only workbooks off of a single “hub” model workbook (as we do all the time), the amount of time it takes to finish Pivot Refresh can actually exceed the time for Model Refresh.

If you have to cram all of this into a narrow nightly window, chances are that you will need more hardware to pull it off than you ever will need for normal daily usage!

Rough Guidelines for Your Own Situation

This post is running a bit long, so I will try to be succinct in closing:

1. If a significant percentage of your PowerPivot models and reports will be refreshed nightly, refresh is likely going to be a larger peak strain than normal interactive usage
2. Even if you only refresh weekly (or less often), if the execution window between “data is ready” and “reports must be ready” is basically still a single night, then refresh is still likely going to drive your peak hardware need.
3. If you think “Peak Load = Refresh Process” is likely the case for your organization, I suggest ignoring interactive usage projections during your hardware planning process, and instead developing some prototype workbooks (with rich data sets and mutliple report sheets with 100% realistic slicer sets, measure complexity, etc.), and then putting those workbooks through the refresh process to get a baseline.

Illustrated guide to Excel Services Chart Rendering

 
COMMON QUESTION:  “Hey, will this chart render unchanged in Excel Services, or will it look different?”

MY STOCK ANSWER:  “Hmm, I forget, let’s try it out”

I hate giving that answer.  So today, I ran a little test.  Simple workbook, 12 charts that are all flavors of fancy.  Not an exhaustive test, but a reasonable sample.  Here are the 12 charts in Excel client (desktop).  Click for a larger version.

 
12 Charts in Desktop Excel

Green Circle = Renders 100% identical in Excel Services
Red X = Renders less than 100% identical (sometimes small differences, sometimes large)

Now, here are the same 12 charts rendered in Excel Services:

Same 12 Charts, As Displayed on SharePoint (Excel Services)

Again, note that being flagged with an X isn’t the kiss of death – I just didn’t want to bog down in “grading” the differences.  Sometimes the changes are big (top left chart) and sometimes the changes are astoundingly small (bottom middle chart).

Either way, having an illustrated reference is going to be helpful for us, so I thought I’d share it.  Feel free to submit questions/comments/other examples.  I may expand this reference over time.

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.

Extranet Analytics Products Using PowerPivot!

  

“I claim this planet milestone in the name of Mars Pivotstream, isn’t that lovely?”

-Me (always looking for Marvin Martian tie-ins)

(Our poor CEO is in agony right now at the sight of Marvin adorning this announcement.  It’s a blog, it’s meant to be fun, but be assured I take my day job very seriously.  Like earth-shattering kaboom seriously).

May 2011 Update: We can now host PowerPivot for SharePoint for You

When I first wrote this post in August 2010, we weren’t in a position to take on general-purpose PowerPivot hosting for the community at large.

But now, we are.  In a big way.  Please see this post for more detail if you are interested in us running your PowerPivot for SharePoint infrastructure for you, via a collaboration between Pivotstream and Rackspace.

Ok, back to the original post…

Rolling Along  (…and Pretty Sure We Are First)

A Production Pivotstream Application
(All Clients’ Identities Redacted)

I’ve been meaning to blog about this for a long time now actually – I planned to make a big splashy post the moment we went live with a PowerPivot-fueled version of one of our existing Retail/Pharma Analytics products.  (Pivotstream has been in business for a couple of years using a variety of technologies, so PowerPivot is a new angle for us, since the time I signed on).

That first “go live” moment passed months ago, actually, and instead of calling it Miller Time and firing up the blog editor, it was then time to start building the next product.  And the next.  Etc.  Good problems to have.

Our clients are making better business decisions already, and are doing so with zero investment in infrastructure or training.  A URL, a browser, and login credentials is all they need to start using business applications like the one above.  We’ve heard customers respond with gems like “Reports are dead, now we have Pivotstream!” which makes me smile for multiple obvious reasons.

I believe we are the first in the world to be doing this (extranet-provided PowerPivot apps), so I finally got around to a post, both to brag about it a little (I’m really proud of the results) and to explain a bit of how it works :)

PowerPivot = powerful enabler in the background

The fact that PowerPivot is a key component of these products is why I am blogging about it here – I figure it is interesting proof of what can be done.

But our customers/clients don’t have much much day to day reason to think about PowerPivot as part of those products, for one key reason:

Customers of our products are NOT building the applications/reports/workbooks in PowerPivot for Excel – WE ARE.  All our customers see is what’s important to them:  a suite of interactive reports in their browser.  Reports that span vast quantities of data, return some very targeted and intelligent metrics, and can be sliced in seconds.

But still follows/validates the core value proposition of PowerPivot

The only real difference between our PowerPivot-fueled app suites vs. a prototypical intranet deployment of PowerPivot is simply that the author and consumer roles are split across companies.

If you ignore that boundary, well, our system becomes very similar to any other deployment.  The end result for the consumers is the same, it’s just that the address bar in the browser points to pivotstream.com versus your internal SharePoint sites.  Will consumers in your organization know it’s PowerPivot fueling the web apps on your intranet?  Some will, and some won’t, depending on their level of curiosity, just like with us.

Beta application for another of our clients, about to go into production this month
(One of about 50 reports in that suite)

Why that works:  Pivotstream knows the clients’ business needs

Here’s a crucial difference between Pivotstream and the average BI consulting firm:  except for me, most of our people didn’t come up through the BI or technology ranks.  Each of their many years of experience is rooted in the industries that we serve.  In other words, our PowerPivot modelers/authors used to work in roles that would benefit from the types of applications that we offer today.

Now, that’s not to say these folks are techno-phobes, or afraid of learning new things.  They were willing to invest in PowerPivot, and hungry to learn it because it empowered them to do things that were previously impossible.

Interesting people for sure – they could all be very superstars working in their former roles in retail, pharma, etc., but they’ve opted to essentially broadcast their expertise instead.  They just needed a transmitter powerful enough.  PowerPivot, SharePoint, and Excel Services, with SQL Server behind the scenes, fills that need.

“Self Service BI” becomes “Full Service BI,” at least in our verticals

As I’ve emphasized before, the biggest time savings of using PowerPivot is that it no longer requires the business unit workers to communicate requirements to BI pros, and then iterate semi-endlessly until the right results emerge.  When the toolset, the business problem, and the business rules all live within one person’s head, sophisticated and robust applications can be built to address complex needs at a fraction of the cost of using traditional methods.

And that’s how it works with us, too.  Those savings are then passed on to our clients in the form of very reasonable subscription rates.

But we also provide our clients with everything else:  we run all of the servers, ingest and shape all of the data, build the applications, keep them up to date…  all they need are web browsers and the logins we provide.  Protection from the chart police is also included, heh heh:

Did Someone Say “Pie?”

Benefits of using SharePoint

We choose to deliver those products over the web using SharePoint, because:

1. It gives our customers the quickest possible lead times – no installation requirements
2. It gives us a means to keep everything up to date, nightly
3. It eliminates user data download requirements – a web app is a lot better than downloading a 2 GB workbook every day.
4. It allows us to protect our core IP – some of our measures and views are truly firsts in the target industries, and the ways in which we implement those, from the backend up through pixels, are something we can lock down via in-browser delivery.

And sure, if you have a proposal about how we might be able to build or host a different kind of app for you, drop us a note at info@pivotstream.com – in fact, we’re already discussing several cooperative opportunities currently.

What’s my role in all of this?

Why, I’m the PowerPivotPro, of course :)  This entails things like:

1. Training my colleagues on PowerPivot – the basics, best practices, etc. 
2. Responsible for our server farm – selecting data center and hardware, planning server roles and topologies, spinning up SharePoint and PowerPivot for SharePoint, customizing it to our needs, extranet security, monitoring server health, etc.
3. Adviser to our SQL Integration Services team – we use SQL Server Integration Services (SSIS) to digest data from our clients and partners (typically flat files over FTP), and then land the data in SQL Server.  From there, it’s imported into PowerPivot models.  I don’t know SQL or SSIS all that well myself, but I do know what sorts of data structures make for the best PowerPivot modeling and performance, so I spend a lot of time working closely with that team.  Longtime readers of this blog will know that sort of cooperation sounds familiar.
4. High Priest of What’s Possible – I do a lot of application prototyping in PowerPivot based on the needs of our business team.  “Can we build something that does X?”  is a common starting point, and off I go.  I frequently create DAX measures, data structures, and slicer sets that are then used as templates.  But even that is a highly collaborative process, as we tradeoff application requirements against performance, PowerPivot limitations, etc.
5. Soaking it all in – in the process, I’m learning quite a bit about the retail, pharma, and alcoholic beverage industries and what makes them tick.  As I learn from my colleagues and vice versa, the line between our roles is naturally blurring over time.

In short, I’m basically a fulltime PowerPivot consultant to the rest of Pivotstream :)

And now that things are rolling along nicely, I’m thinking its time to start assisting others with their on-premise deployments.

Consulting – interested in assistance from the ‘Pro and the team?

I know most people reading this are doing so in order to apply PowerPivot on their own, and we are increasingly in a position to share our expertise.  So if you are interested in training/consulting to support your own internal deployments, or just want to know more about our core applications, please click here, or contact info@pivotstream.com

Questions on how we did this?

This post is getting kinda long so I’ll cut it short.  Lots left to talk about, but I’ll start with wherever there is the most curiosity.

Anything you want to know about how we employ PowerPivot for this, please ask in comments.  I can’t give away certain secrets of the core apps, but as always, will try to be as helpful as possible.

Some questions may end up warranting entire posts to answer, which is good too :)