The 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.
Nonetheless, trying to figure out what’s what is a bit mind boggling. This is fairly evident if you’ve ever tried to install or administer these various different products. Microsoft’s documentation in this arena is a scattershot collection of chaos. This is evident in the fact that there are 10 different documents explaining how to install PowerPivot for SharePoint and they all have different nuances and characteristics, not a single one of which “gets it right” – or in more concise terms, not a single one is authoritative.
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 Power View?
Power View is an interactive data exploration tool. It allows you to explore data and relationships in order to find insight and trends. It's similar to other software like Tableau. It's really neat because you can filter data in one chart by choosing a data point in another chart. It allows you to see relationships visually. If you've never tried it, give it a whirl. You'll need Microsoft Excel 2013.
Power View comes in two flavors. It exists in Microsoft Excel and also in SharePoint. In SharePoint, people can look at your Power View worksheets and interact with them and they can also use your data model to create their own Power View worksheets.
What is PowerPivot?
PowerPivot is the data modeling engine that sits behind
Power View. If Power View is the car, then PowerPivot is what's sitting under the hood. This metaphor isn't great, because you still need to know how to be a mechanic to make this motor run. Granted, it's still 1910 and it's just a couple of spark plugs and a distributor (i.e., simple), but you will have to get under the hood to make this motor run. PowerPivot is not a database although it does store a lot of data in a tabular format. Tabular format data stores are beyond the scope of this post, but if you'd like more information, check out xVelocity and Analysis Services
PowerPivot comes in two flavors just like Power View. The Micrsoft Excel version and the SharePoint version. The SharePoint version actually doesn't stand alone -- its actually a "frontman" for Microsoft Analysis Services running in Tabular mode (a component of Microsoft SQL Server).
What is SharePoint Server?
SharePoint Server is a beast. It's a big piece of server software from Microsoft. Its purpose is to unify the universe. No, seriously! Okay, it's a collaboration engine. It's also gigantic and amorphous. You can do anything with it. The basic premise is this: anything that can be shared or collaborated on or published can be part of SharePoint. Think of it as a gigantic wiki page that allows people to share documents, collaborate on projects, build web pages, publish dashboards, and so on. The problem with describing SharePoint is that it's so big and complex and undefined. It's like a big ball of play dough -- you can quite actually mold it into any shape you want. Want a web page? Okay. Want a project management site? Okay. Want a blog? Okay. Remember: sharing, collaborating, publishing. That's key.
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.
Technically, Reporting Services is part of SQL Server. However, it can be installed in SharePoint mode and it's one of the many pieces of the puzzle necessary to get Power View working.
PowerPivot, as described above, is the tabular data engine inside Excel that stores the data that Power View uses as its data source. In SharePoint, this service must be installed, configured, and enabled to essentially connect to SQL Server Analysis Services running in PowerPivot mode (basically, a special form of Tabular mode).
Excel Services at their most basic level allow SharePoint users to open and use Excel spreadsheets. On a deeper level, these services are necessary to open PowerPivot models and Power View sheets that have been created in Excel on the desktop.
PerformancePoint Services are simply a dashboarding suite. They allow you to connect to various data sources (primarily SSAS cubes) and create meaningful and easy-to-use dashboards. Dashboards are typically one-page collections of informative charts, graphs, or KPIs that give a user a quick overview of what's happening with a system or a process.
What is SQL Server?
SQL Server is Microsoft's relational database product. The core database engine is fairly easy to understand. It's basically just a very advanced SQL engine. However, there are many, many components tacked on to SQL Server that are necessary to perform business intelligence using Microsoft products. For instance, two key components that must be installed are Reporting Services and Analysis Services. In addition, these services cannot simply be installed in their native modes -- they must be installed in SharePoint mode.
The Nuts and Bolts
That's a clever title, I reckon. (Insert smiley face)
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.
You will need this software:
- 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.
Installing #1 - #3 above is outside the scope of this article. But let's say you get that far and you're going to install SQL Server. There are several things you need to keep in mind:
- 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.
Getting Power View to work in SharePoint is not easy or simple or straight foward. There are a number of guides on the web and you might have to go through more than one of them. I can pretty much guarantee this: there are a number of important details that Microsoft doesn't emphasize well. It's these small details that make the difference between a successful configuration and one that's not.