Extranet Analytics Products Using PowerPivot!

 Marvin-Martian-Pivotstream 

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

PivotStream PowerPivot Online Service 1

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.

PowerPivot Hosted Analytics 2
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:

PivotStream PowerPivot Online Service 4

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

7 Responses to Extranet Analytics Products Using PowerPivot!

  1. Tom Gleeson says:

    The ability to produce web-enabled dashboards quickly (& cheaply) is a major plus of PowerPivot, but that could perhaps be achieved by other means with a trade-off between longer development time v lower licensing costs.

    But what’s really invaluable about PowerPivot, and I think your use of the product validates this, is the ability to directly use domain experts in the production of cubes.

    As they say on the credit card ads: Priceless!

    Tom

  2. sam says:

    Prior to 2007 when you build PivotTables on external dialog we could save the query as a cube.
    For some mysterious regions this was removed in 2007 onwards…any ideas why

  3. dennyglee says:

    Great stuff like always Rob! By any chance do you have any information on the ROI and/or how fast you guys were able to build these reports using PowerPivot vs. not, eh?! Thanks!

  4. [...] 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 [...]

  5. Tony Bailey says:

    I’ve only been looking into PowerPivot for the last couple months, so maybe you have already covered this….

    How are you dealing with PowerPivot’s lack of “click through”/”drill through” capability? It seems like this would be a capability you would need especially in an implementation like this where the client is seeing a “web application” and click through navigation would be natural and expected.

    Do you use a report menuing structure before they get into PowerPivot? Or do you do something more unique within the excel workbooks itself?

    • Hi Tony. Great questions.

      I agree that contextual report navigation is a natural “want” here, and I pressure MS about it all the time. I mean, just think of the demos we could offer – you’re looking at a particular product’s sales performance and could click thru immediately to its public-facing product page. That would be hot, for sure.

      Honestly though, our clients are still digesting the capabilities offered by slicers. There’s a culture of digesting fixed, static reports that takes some time to unlearn. I think we are still very early in that process, both as a company and as an industry. So we are not pressured by our clients (yet) for these navigation capabilities.

      Of course, organizations with a history of using BI tools (like yours) will be much more demanding today than most.

      Yes, we do present them with a tree-view menu of reports before they ever see Excel Services. And then within Excel Services, we use hyperlinks to let them navigate to the particular report worksheet they want to see.

  6. Subash Thakkar says:

    I believe the drill through capability is coming with Denali. Is that right?

Leave a Comment or Question