As the title implies, I won’t venture to call this a complete overview of PowerPivot for SharePoint. What follows is a review for those just beginning to contemplate leveraging PowerPivot for SharePoint, by a SharePoint Administrator with just enough experience and exposure to want to help. That said, this has been written for those with almost no exposure to Microsoft BI, general analysis services, dashboarding, or any of the other buzzwords dramatically increasing in popularity. If you have a comfortable familiarity with Excel and a working knowledge of SharePoint Service Applications, you can use this post as a guide for setting up your first development environment.
What is PowerPivot for SharePoint?
If SharePoint and Excel Services are your family sedan, installing components for PowerPivot for SharePoint is like adding a turbo, wings, and an airport. Your sedan handled a basic workload just fine, but now the possibilities and capacity are limited only by your imagination (and a few things listed below). More technically put, PowerPivot for SharePoint enables external data connections, greatly increases the computing capabilities and efficiency of PowerPivot-enabled Excel Workbooks, and enables new features and functions that make combining and visualizing data on SharePoint a realistic venture for any size organization.
Many people are initially confused when attempting to internalize their own definition of PowerPivot for SharePoint, due to the many ways that PowerPivot (as a collection of components) fits in to the Microsoft ecosystem. For our purposes, you will be working toward using the Power Pivot add-in for Excel to create an Excel Workbook to be accessed in an Excel Web Access web part served via Excel Web Services and PowerPivot for SharePoint, supported on the back-end by PowerPivot for SQL Analysis Services. These are all entirely different entities, which while directly and sequentially related, can be utilized and consumed in many different ways.
What do I need to know?
I’ve summarized a few key points for each component; however, when performing the installation, read the documentation. Make your service accounts first. Many known issues are covered, and user comments may help along the way.
- PowerPivot System Service and Analysis Services service:
- Installed First – dependent on new installation.
- Installed through SQL Server Setup – typically requires either a dedicated server or SQL instance.
- PowerPivot service application:
- Installed and Run on one or more APP servers in your farm.
- PowerPivot Web service:
- Installed and Runs on each WFE in your farm.
- PowerPivot application database:
- Created in SQL when the PowerPivot service application is created.
- PowerPivot content and libraries:
- Added by users with appropriate permissions to SharePoint Sites.
The Hard Limits
There are a number of System Requirements and configuration pre-requisites that must be met; the majority of these are superseded by the requirements of running Windows Server and SQL or SharePoint. A brief overview of some of the more “unexpected” points follows:
- SQL Edition
Be sure to pay attention to which edition of SQL you’re using. Regardless of what version you are using, only certain editions of SQL support PowerPivot services. For example, if you are running SQL 2012, you must have either the Business Intelligence or Enterprise edition.
- Classic-Mode Authentication
PowerPivot services can only be installed and run on web applications that are configured with Classic-Mode authentication. The most common workaround for showing your dashboards on a claims based web application is to host the workbooks and PowerPivot galleries on a dedicated web application in the same Farm, and then expose the dashboards through Excel web services. There’s a fair amount of configuration, some serious security considerations, and some capabilities lost with this method; however, this requirement isn’t going to change any time soon.
- Data Refresh Scheduling
It is important to set the expectations for the frequency at which any external data refresh or re-calculation can be handled. Out of the box, a single daily refresh is the highest available frequency. There are workarounds to defeat this schedule, but most require code-based solutions or un-supported “hacks.”
- Workbook limitations
Many administrators are already familiar with these limitations as they apply to Excel Web Access and SharePoint more specifically than PowerPivot for SharePoint. Before designing your dashboard’s appearance and functionality, I highly recommend reviewing “Differences between using a workbook in the browser and in Excel” for 2010 and 2013. A few notable limitations include:
- No data validation may be used
- Any form tool bar controls or custom ActiveX controls
- No macros (no macro-enabled workbooks)
The Soft Limits
When effectively used, PowerPivot workbooks can grow to be very large in size – almost always larger than the default workbook size permitted by Excel Web Services and in extreme cases, larger than the maximum file upload size permitted by your web application. In addition, there are (astronomical) limits to the number of rows and other capacity limitations supported by PowerPivot. Having a clearly outlined policy for how and when PowerPivot workbooks and dashboards can be deployed is a key component of any governance plan.
Be sure to consider that any changes or upgrades made to systems hosting external data sources leveraged by your workbooks will likely have an effect on your Dashboard, and potentially the external data connection itself. Ensure that any maintenance or upgrade to those external data sources is communicated to the person responsible for maintaining the Workbooks and Dashboards, as well as each PowerPivot component.
- Visual Design
Ultimately, you will be responsible for ensuring that the data is displayed in an effective and visually appealing way. If you’ll be displaying a dashboard on a web part page, one key consideration is to predetermine the width in pixels of the Excel Web Access web part. You can then adjust the size of your columns and page sizes in the workbook to ensure that your dashboard content fits as expected.
- Maintenance and Support
Depending on the size of your organization, you may have specific teams at different levels of your infrastructure. Make sure to clearly define who is responsible for each component of PowerPivot for SharePoint so that all components are maintained consistently.
As with anything SharePoint related, the community surrounding PowerPivot for SharePoint is expansive and very active – don’t hesitate to search for solutions on the web. While my experiences with formal training were very productive, I recommend drilling on an end-to-end configuration of a personal (non-production) dashboard before tackling any actual projects.