In-Depth
Installing and Maintaining PowerPivot for SharePoint
From farm options and security to data sources and maintenance, the options you choose are critical to using PowerPivot in your organization.
Chris Leiter, Senior Consultant, Hitachi Consulting
Life is about risk. When I get out of bed in the morning, I could stub my toe. I could slip in the shower. I could get in a car accident on the way to work. A lot of bad things can happen to me during the day. Of course, if I stay in bed, I could develop bed sores, blood clots, or gain several hundred pounds.
The payoff to getting up and going to work is pretty clear. I provide a service and earn a salary, and usually learn something new. The payoff to staying in bed might be my own reality show. As you can imagine, the risk-to-reward ratio favors going to work significantly over staying home and eating cheese puffs.
My previous article, Planning a PowerPivot for SharePoint Deployment, addressed PowerPivot for SharePoint's architecture, licensing, cost, and scaling. This article takes the next step by identifying key risks for installing and maintaining PowerPivot for SharePoint.
New/Existing Farm Options
Because installation is well documented on MSDN, this article does not provide step-by-step guidance through the processes. However, one decision you should carefully consider is whether to set PowerPivot up in a new farm or to install it in an existing farm. In my opinion, the "New Farm" option is useful for creating demo or test environments, but the majority of PowerPivot for SharePoint customers will likely already have an existing environment in which they will be installing the service. These might include a farm that has been upgraded from MOSS 2007, a new farm into which MOSS 2007 content will be migrated, or a brand new SharePoint 2010 installation as part of a planned SharePoint strategy. In each of these cases, PowerPivot should be installed on an existing farm.
Even if your intention is to set up a new farm for providing PowerPivot for SharePoint functionality to your users, I never recommend the New Farm option for one very simple reason: PowerPivot for SharePoint is a SQL Server component, not a Microsoft Office or SharePoint component. As such, it should be considered a tenant application. This means that it should behave as if it were simply renting space from the SharePoint environment.
As a best practice, you should install and configure SharePoint, and ensure all required features are working as expected before installing any tenant application. This allows you to ensure your building foundation and structure are stable before you allow renters to move in. Enabling tenant applications to configure the farm doesn't just put the cart before the horse -- it expects the cart to also feed and groom the horse.
Security
To provide a stable and secure environment for PowerPivot for SharePoint solutions, you will need to consider several key factors. PowerPivot for Excel allows users to import data from a robust list of data sources, and PowerPivot for SharePoint provides the ability to automatically refresh data from those sources. However, certain automatic data-refresh scenarios introduce potential security concerns.
PowerPivot for SharePoint uses the Access Connectivity Engine (ACE) data provider to connect to certain data sources, namely Excel and flat files such as comma-separated value files. Because the ACE provider was not designed for server environments, these refreshes will fail without additional configuration.
When accessing these files from a SharePoint document library, the ACE provider will attempt to connect to the library using a WebDAV client connection, which is not available on Windows Server 2008 and Windows Server 2008 R2 by default. You can install the WebClient service on Windows Server by enabling the Desktop Experience feature set, but this also includes a number of other desktop components, such as Windows Media Player. This potentially increases the attack surface of your application server; enabling the Desktop Experience may also increase your server downtime by installing additional components that will have to be patched or updated as necessary.
When these files are accessed from a standard file share, the ACE provider connects to the data source by impersonating the credentials configured by the user in the Automatic Data Refresh page. The provider needs to create a file in the TEMP directory of the service account, and if the user whose credentials were provided does not have permissions to create a file in that folder, the scheduled refresh will fail. Two potential workarounds include granting everyone WRITE permissions on the service account's TEMP folder (not recommended), or use the Unattended Data Refresh account, and grant that account read permissions to the files, which may not be optimal for security.
Data sources that require firewall traversal, such as an internet ODATA data feed or SQL Azure, may also introduce additional risks by allowing mid-tier applications to access the Internet. There are several options for configuring access to these data sources, including installing a required firewall client or configuring Web Proxy settings for the service account. From a security perspective, you should determine whether you want to support Internet data access. Because there are no whitelisting capabilities in PowerPivot, you must ensure that Internet data originates from a trusted provider.
Figure 1: Data refresh blocked by firewall.
When considering content security for PowerPivot, remember that PowerPivot files are Excel files. Features such as row-level security within Excel, although desirable, are not (currently) available. Anyone who has access to the Excel file has access to all the content of the PowerPivot sandbox contained within. Use the same strategies for protecting PowerPivot files as you do for other sensitive documents, such as using NTFS or SharePoint permissions to prevent unauthorized access. Use an information rights management system to prevent the file from being distributed or altered without approval. When publishing in SharePoint, set "view only" permissions for the file to allow a file snapshot to be downloaded to Excel, not the entire data set.
Maintaining PowerPivot for SharePoint
There are several tools available for monitoring PowerPivot for SharePoint. In addition to standard SQL Server tools such as SQL Management Studio and SQL Profiler, the PowerPivot Management Dashboard in Central Admin provides usage and health information for the PowerPivot Service. These reports can provide valuable insight to help identify potential bottlenecks and opportunities for increasing application responsiveness. Additionally, tools like the Windows Performance Monitor as well as the SQL Server Management Pack for System Center Operations Manager can allow you to view performance counters and configure alerts for PowerPivot instances.
Furthermore, keep your server up-to-date by installing recommended hotfixes or cumulative updates (CUs). At the time of this writing, CU4 is available. These updates include a number of significant fixes, including an improved mechanism for health-based model allocation to allow better load balancing across your application servers and an improved uninstall experience.
The Last Word
As with any technology, you should understand the impact of installing, configuring, managing, and deprecating PowerPivot for SharePoint in your organization. Implement sound data security practices. Use the same diligence for patching and maintaining your servers. Finally, understand the risks and rewards of a new service application to ensure a successful implementation. The decisions you make should not be based solely on technical constraints or the desire for the latest technology but based on the right solution for your organization.
Chris Leiter is a senior consultant at Hitachi Consulting with the Core Infrastructure Optimization team in the Microsoft practice. He has more than 15 years of information technology experience and is the primary author of Beginning Microsoft SQL Server 2008 Administration from Wrox Press. Chris can be contacted at cleiter@hitachiconsulting.com.