I can’t recall if I have spoken about the many benefits of hosting your PowerPivot workbooks on SharePoint. Most any document is going to benefit from having a single source of “the truth” and avoiding “oh, you have the Tuesday version!? There was a new version Thursday” trauma.
But in the case of Power Pivot workbooks this gets amplified a bit. The reason is that while you may have 1 or 2 authors for your workbooks… You may have many many folks that want to consume your insights. And, there is a good chance they don’t have the right version of Excel, don’t have Power Pivot installed, or heck… maybe they are (Gasp!) on a mobile device!
Having your workbook hosted online solves these problems. Since the document is rendered in a web browser… you don’t even need excel installed, and the only notable version of the report is “the one on SharePoint”.
Which Host Should I Use?
Ya… Good question. There are no perfect answers here. Lemme run down a few options:
- On-Prem: If you are a larger corporation, and maybe you already have a SharePoint farm… Maybe you are just good to go! I’ll warn that getting Power Pivot correctly installed and configured isn’t trivial. If your IT guy pulls it off, high five him for me. You will need Enterprise SharePoint for Excel Services and one the higher levels of SQL for the Power Pivot components.
If you have the staff to support it, this is a great option. Main downside is (other than supporting it)… That Power View is rendered using Silverlight. Maybe one of the worst BI related decisions Microsoft made. I mean, not as bad as the total fiasco related to figuring out the versions and licenses to use Power Pivot in Excel 2013… But it’s pretty bad. If you don’t care about Power View or you don’t care about mobile, or… the Chrome browser… then no big deal.
- Office 365:
I guess this is the current “default” option. You have to buy your license of O365… then you have to pay another $10/mo for the Power BI option. The main upside here is that if you love Power View… Here it renders in HTML5 instead of the stupid Silverlight. The main downside? Well, just about everything else — at least in my opinion 🙂I’m annoyed as soon as I have to tell them what “flavor” of credentials I want to use. What other site does this!? I don’t find performance great. I find the max workbook size limiting. I find the ui a bit clunky.
I guess if you have small workbooks and already pay for O365 anyway… This is an okay option?
- 3rd Party Hosting:
There are a few companies that host SharePoint in the cloud, and are pretty flexible with building out surrounding infrastructure for you (sql servers, vm with excel, whatever). The main advantage here is… You aren’t administering SharePoint 🙂 And you can typically have a lot of flexibility on sizing of your servers and custom software.Here, I think you will have the best time with a company that has a decent amount of Power Pivot experience. I know of 3… And if you have experience with others, I would love to hear about them! I have had really good experiences with Plexhosted and PortalFront, and the third I’m legally not allowed to give an honest opinion.
I’ve helped a few clients navigate the requirements and deployments at these hosts (and processes for getting their data into the hosts and refreshing on good schedules, etc). I have no problem having a quick chat with anyone on my experiences with these hosts, vague sense of costs, pitfalls, etc Feel free to reach out.
- Power BI v2
This isn’t a real option yet. I mean, if you wanted to buy it today, I don’t think you can… It’s still in preview.The new kid is certainly interesting, but it’s also not like the others. Specifically, you cannot upload an excel workbook and have it render. You could use the data in the workbook to create fancy new visualizations… Uh, I think. Maybe you have to do that in Power BI Designer first. I really should play with that stuff more. 🙂
Moving forward, it’s definitely the one to watch. No silly Silverlight, of course, but they also have native apps (even iOS!). I suspect they will eventually render native excel workbooks (so we can get our conditional formatted pivot tables!), and the pricing is fantastic. Free for small data… and only $10/mo premium.
These dudes are iterating fast, making improvements every month — will be fun to watch.
Uh… That’s it. Go forth and host. Reach out if I can help steer you 
- The streak is alive! – August 10, 2017
- DAX KEEPFILTERS Plus Bonus Complaining! – July 20, 2017
- VAR: The best thing to happen to DAX since CALCULATE() – July 11, 2017
- Review: Analyzing Data with Power BI and Power Pivot for Excel – June 20, 2017
- Power BI Date Table – June 2, 2017
Scott Senkeresty here… Founder and Owner of Tiny Lizard. Yes, I have done some stuff: A Master’s Degree in Computer Science from California Polytechnic State University in beautiful San Luis Obispo, California. Over twelve years developing software for Microsoft, across Office, Windows and Anti-Malware teams… Read More >>
Great post Scott. Thanks for helping us to navigate the confusing waters of Microsoft BI.
Do you find that the on-prem support for Power Pivot is a wide skill set? Our SharePoint team doesn’t seem to be very, um, knowledgeable sometimes. I have reservations about whether or not they’d be able to implement SharePoint with Power Pivot properly.
So… I would say that it *is* a tricky beast to install and configure correctly, but I would also say that, in my experience, the folks at Microsoft Support were strangely good at helping me resolve issues. I don’t have tons of experience with other parts of Microsoft support, but in the excel services and power pivot world I was always super impressed.