IntroductionThe landscape for Microsoft Business Intelligence products in the enterprise is frankly cluttered. There are several great tools that run primarily in Excel – after all, 90% of the world’s data analysis currently happens in dimly lit cubicles using Excel. But Microsoft has been trying to get into the big leagues by bringing their BI offerings to SharePoint. SharePoint is, in effect, Microsoft’s version of a “wiki” platform. It’s their non-developer-centric web-based collaboration and publishing tool. It makes sense that when data analysts create complex Excel reports that they should be able to share them using a technology more modern than the F: drive – and that’s why SharePoint was built.
Power View for Excel in itself is not simple to install. It doesn’t come pre-activated from Microsoft. It requires a person dig deep inside the bowels of Excel’s labyrinthine options to activate a COM add in. Then, in most cases, the user needs to install Silverlight since it isn’t part of the operating system. Can you imagine the system administrator’s frustration when everyone in the organization suddenly wants Excel to do more? Beyond that, Power View requires you to essentially understand PowerPivot. PowerPivot is a data modeling tool – a very, very powerful one. Both of these pieces of software are easy to use, but getting maximum value out of them is – like any other software – a learning process.
But that’s a minor inconvenience compared to the system administration nightmare that is SharePoint. SharePoint is dauntingly complex all by itself and then trying to add a bunch of services that are supposed to interact with other services and servers and software. Add to that complexity the Microsoft licensing model – you should be aware at this point right now that your deployment isn’t going to be cheap. SharePoint Enterprise edition is not cheap, nor is SQL Server Enterprise edition (or Business Intelligence edition). Let’s be honest – if you were planning on running Microsoft’s BI platform for less than $25,000 in licensing fees, you’re going to be sticker shocked to say the least.
In any case, I’m going to try to break this down into the simplest terms and be thorough at the same time. This post is about all the actual requirements to get Power View for SharePoint working. I will also try to explain the software linkages and basic terminology. If you don't want basic explanations and want to get to the nuts and bolts of this post, jump down a few sections.
What is SharePoint Server?
There are four different services in SharePoint that you'll also need. The jumble of services gets tangled so I'm going to try to detangle them below.
What is SQL Server?
The Nuts and Bolts
That's a lot of software and moving parts just to get one data exploration tool up and running. Unfortunately, there's no way around it if you want to share your Power View sheets within your organization.
- Windows. You will most likely need a server edition but possibly not -- I've always used the server editions for SharePoint, but check the system requirements to see if it'll run on desktop editions.
- Internet Information Services. This web server software is packaged with just about any modern version of Windows you can think of. But no matter which version of Windows you settle on, SharePoint needs it.
- SharePoint Enterprise Edition. Yes, Enterprise. You cannot run the BI tools on Standard.
- SQL Server Enterprise Edition or Business Intelligence Edition. I've never tried using Developer edition, but according to Microsoft, it is exactly the same as Enterprise Edition -- so in theory it should work.
- When you install SQL Server (usually on a machine separate from SharePoint), make sure you do not install any Reporting Services of any kind.
- Feel free to install Multi-Dimensional Analysis Services on your default instance.
- After all that's done, you will go back to your SharePoint server and install Reporting Services for SharePoint and Reporting Services for SharePoint plugin.
- Finally, you will install PowerPivot for SharePoint. This is one of the options when installing SQL Server. If you don't see the option, you might not be installing from Enterprise or Business Intelligence Edition disks. It will automatically install a new instance of Analysis Services called [ServerName]\POWERPIVOT. This is a special version of the Tabular model of Analysis Services. It's fairly well locked down to only service SharePoint so you won't be able to use this to do backups and restores of other, non-PowerPivot related databases.
- What's really important here is that your Excel Services configuration must point to the POWERPIVOT instance of analysis services. If it does not, the chances of getting it to work are very very low.