Plan a PowerPivot Deployment in a SharePoint Farm
This topic contains the following sections.
Adding PowerPivot to a Farm Topology
Server Components in a Farm
Plan for Service and Administrative Accounts
Plan for Database Storage and Disk Space
Plan for PowerPivot Data Transfer
Plan for Secure Storage of Sensitive Data
Plan for Creating and Viewing PowerPivot Data
Adding PowerPivot to a Farm Topology
SharePoint farm topologies range from standalone servers to large multi-server installations. Once you understand the basics of adding PowerPivot to smaller deployments, you can apply the same concepts and techniques to more complex topologies.
Adding PowerPivot to a Standalone Server
You can create a dedicated PowerPivot for SharePoint server that runs on a single SharePoint 2010 server. The advantage to using a standalone server is simplicity of deployment. SQL Server Setup provides an installation option that installs and configures a ready-to-use PowerPivot for SharePoint server. SQL Setup also configures SharePoint features, thresholds, and security settings that are either recommended or required for PowerPivot data and query processing on the server. It activates and provisions Excel Services, activates and provisions Secure Store Service, and increases the maximum file sizes to support larger files in a SharePoint environment. For a full description of how SQL Server Setup installs and configures the server, see Default Configuration for PowerPivot for SharePoint.
To take advantage of this approach, you must have SharePoint 2010 installation media, with SharePoint installed but not yet configured on the computer. "Installed but not configured" refers to installing SharePoint, but stopping before configuration by not running the SharePoint Configuration Wizard that starts at the end of the installation process.
Your choice of server for this type of deployment is important. Because there is only one server for all processing and hosting, you should use a higher end server. By definition, you are installing only one PowerPivot for SharePoint instance, so the entire load will be on the one server. For this reason, avoid using an existing server that is already running other data intensive applications, and choose a newer server that has the memory and processing resources to support a large number of datasets as well as the long-running queries that are characteristic of PowerPivot processing.
Important
Using the New Server installation option now does not limit your scale-out strategy later. As long as the underlying SharePoint server supports adding servers to the farm (that is, you installed SharePoint using the Server Farm option in SharePoint Setup), you can increase PowerPivot capacity by adding more SharePoint and PowerPivot servers.
For more information about system requirements and standalone server installation, see Hardware and Software Requirements (PowerPivot for SharePoint) and Install PowerPivot for SharePoint on a New SharePoint Server.
Adding PowerPivot to a Server Farm
If you have multiple SharePoint servers in a farm, you can install PowerPivot for SharePoint on a new or existing application server in the farm.
.gif "SharePoint farm topology with PowerPivot")
As with a standalone server, you use SQL Server Setup to perform the installation. However, in an existing farm deployment, SQL Server Setup requires that SharePoint 2010 is both installed and configured. When Setup runs, it will install just the PowerPivot server components. It will not configure PowerPivot for SharePoint, nor will it check the configuration of existing SharePoint web and service applications for feature availability, upper limits, or security settings. As such, this type of deployment will require post-installation configuration and more evaluation and assessment on your part to ensure that PowerPivot configuration is compatible with existing services.
You can add PowerPivot for SharePoint to any SharePoint server as long as it is not dedicated to running only Web front-end operations.
Farm topologies that include dedicated application servers for just Excel Services or dedicated Web front-end servers for just SharePoint Central Administration must be updated to use newer Analysis Services libraries. For more information, see How to: Install the Analysis Services OLE DB Provider on an Excel Services Computer and How to: Install ADOMD.NET on Web Front-End Servers Running Central Administration
For instructions on how to install and configure a PowerPivot for SharePoint instance in a farm, see Install PowerPivot for SharePoint on an Existing SharePoint Server and Configuration (PowerPivot for SharePoint).
PowerPivot Scale-out Deployment
A scale-out deployment consists of multiple installations of SQL Server PowerPivot for SharePoint in a farm that adds processing capacity to the entire farm. In a scale-out deployment, you install PowerPivot for SharePoint once on each application server that will perform PowerPivot query processing. For example, if you have five application servers and you want three of them to support PowerPivot query processing, you must install PowerPivot for SharePoint on those three servers.
Scale-out describes how physical service instances are used in the farm. Within a farm, a request for PowerPivot query and data processing can be allocated to any PowerPivot for SharePoint server that is available, where availability is determined by an allocation methodology. The default is round-robin that allocates requests in sequential order, but you can change the allocation method to use server health instead.
Adding more PowerPivot for SharePoint servers to a farm adds processing capacity for all web applications that use that feature. You cannot scale PowerPivot processing for some web applications and not others; you cannot create an affinity between physical service instances and specific Web applications. All web applications that share a connection to any PowerPivot service application can use the processing capability of any new server that you add.
As you add more PowerPivot for SharePoint servers to your farm, you might find that you have web applications that do not require PowerPivot query processing. For these applications, you can create a custom service associations list that excludes the PowerPivot service application, ensuring that those applications never send or receive requests for PowerPivot data.
If you have multiple web applications that have different processing or configuration requirements, you can vary PowerPivot service processing and configuration settings for different web applications by creating custom PowerPivot service applications. A service application sets a run time context for PowerPivot processing. You can create many PowerPivot service applications to isolate run time operations for different web applications.
For instructions on how to install multiple PowerPivot for SharePoint instances in a farm, see Add PowerPivot Servers to a SharePoint Farm.
Adding New Web Applications to a Farm that has PowerPivot for SharePoint
When you create new SharePoint web applications, you must deploy the PowerPivot Web application solution and activate the PowerPivot feature at the site collection level.
For more information, see Deploy PowerPivot Solutions and Activate PowerPivot Feature Integration for Site Collections.
Adding New Web Front-end Servers to a Farm that has PowerPivot for SharePoint
PowerPivot for SharePoint is unaffected by the addition of new Web front-end or application servers that you add to the farm. No specific configuration steps are required. Existing web applications that run on the new Web front-end servers should support PowerPivot data access if you deployed the solution for that application.
However, additional configuration is required if you configure a Web front-end server to run just SharePoint Central Administration web application. Because PowerPivot for SharePoint adds a management dashboard to that application, client libraries must be installed to ensure that reports and web parts in the dashboard can connect to the dashboard data store. For instructions on how to install the client libraries or how to deploy solutions, see How to: Install ADOMD.NET on Web Front-End Servers Running Central Administration.
Adding New Application Servers to a Farm that has PowerPivot for SharePoint
If you add a new application server to the farm, and you want the server to handle PowerPivot query processing, you must run SQL Server Setup to add PowerPivot for SharePoint to that server if you want that application server to handle PowerPivot server operations. SharePoint will not automatically copy PowerPivot program files from a solution or another application server to the new server.
Server Components in a Farm
A SharePoint farm consists of Web front end servers, application servers, with access to a backend database server that provides configuration and content databases. In a multi-server farm, you will often find that these components are distributed across multiple computers. Typically, the database server is the first component to be moved to a dedicated computer. Web front ends are also likely to run on dedicated computers, allowing system administrators to optimize Web front ends and application servers separately.
If you have a multi-server farm, you can install a SQL Server PowerPivot for SharePoint instance on one or more application servers in the farm. When you install PowerPivot for SharePoint, SQL Server Setup will deploy a solution package that copies program files to Web front-end servers in the farm.
As a first step, be sure that you understand the purpose and placement of each component in the farm.
PowerPivot component |
Purpose |
Placement |
|---|---|---|
PowerPivot System Service and Analysis Services service |
Queries and manages PowerPivot data in Excel workbooks. |
Runs on an application server, as a shared service application and Windows service, respectively. These services are installed through SQL Server setup and configured through Central Administration. |
PowerPivot Web service |
Intercepts and directs HTTP requests to PowerPivot servers in the farm. |
Runs on a web front end server in a SharePoint farm. If you have dedicated Web front end servers, SharePoint will deploy the PowerPivot solution package to the server. |
PowerPivot service application |
A configured instance of a PowerPivot System Service. |
Runs on application servers when a request for PowerPivot data is received. A service application is defined once for the farm. It will run on any application server that has a physical PowerPivot service instance. |
PowerPivot application database |
Stores internal data used only by the application in a SQL Server database. If you created multiple shared service applications, you will have a separate PowerPivot service application database for each one. |
Hosted on a database server used by the farm. The database is generated when you create a PowerPivot service application. |
PowerPivot content and libraries |
PowerPivot content includes Excel workbooks that contain embedded PowerPivot data. It also includes Atom data service documents and templates for data feed libraries and PowerPivot Gallery libraries. This content is stored in the SharePoint content databases created for a particular SharePoint Web application. |
Stored in content databases in a database server. Storing PowerPivot workbooks might significantly impact the disk space requirements of your content databases. Plan to monitor database growth closely while you determine the storage requirements for your organization. |
Plan for Service and Administrative Accounts
When planning a PowerPivot for SharePoint deployment, you must plan for the following service and administrative accounts.
Analysis Services service account. Analysis Services processes PowerPivot queries and data refresh jobs in the farm. This account is always specified during SQL Server Setup when you install PowerPivot for SharePoint.
PowerPivot Service Application Pool. A PowerPivot service application is associated with a PowerPivot System Service that provides SharePoint integration and infrastructure for PowerPivot query processing in a farm. The application pool that you specify for a PowerPivot service application is the service identity of the PowerPivot System Service. You can have multiple PowerPivot service applications in a farm. Each one that you create should run in its own application pool.
Unattended PowerPivot Data Refresh account. If you plan to enable PowerPivot data refresh on a SharePoint server, you will need to create an unattended PowerPivot data refresh account that will be used to run a data refresh job on the behalf of the person who owns PowerPivot workbook. This account must be a Windows domain user account. It should not be the personal account of an individual in your organization. The unattended PowerPivot data refresh account is defined and stored in Secure Store Service. For more information about this account and how to create it, see Configure and Use Stored Credentials for PowerPivot Data Refresh.
Note
If you are installing PowerPivot for SharePoint using the New Server option, you will be asked to specify additional accounts beyond those listed here. For more information, see Install PowerPivot for SharePoint on a New SharePoint Server.
Analysis Services Service Account
Requirement |
Description |
|---|---|
Provisioning requirement |
This account must be specified during SQL Server Setup. You can modify the user name or password using Central Administration. Using other tools to change accounts and passwords is not supported. |
Domain user account requirement |
This account must be a Windows domain user account. Built-in machine accounts (such as Network Service or Local Service) are prohibited. SQL Server Setup enforces the domain user account requirement by blocking installation whenever a machine account is specified. |
Permission requirements |
This account must be a member of the SQLServerMSASUser$<server>$PowerPivot security group and the WSS_WPG security groups on the local computer. These permissions should be granted automatically. For more information on how to check or grant permissions, see Change Service Accounts and Passwords (PowerPivot for SharePoint) and Install PowerPivot for SharePoint on an Existing SharePoint Server. |
Scale-out requirements |
If you install multiple PowerPivot for SharePoint server instances in a farm, all of the Analysis Services server instances must run under the same domain user account. For example, if you configure the first Analysis Services service instance to run as Contoso\ssas-srv01, then all additional Analysis Services service instances that you deploy thereafter in the same farm must also run as Contoso\ssas-srv01 (or whatever the current account happens to be). Configuring all service instances to run under the same account allows the PowerPivot System service to allocate query processing or data refresh jobs to any Analysis Services service instance in the farm. In addition, it enables the use of the Managed Account feature in Central Administration for Analysis Services server instances. By using the same account for all Analysis Services service instances, you can change account or password once, and all service instances that use those credentials are updated automatically. SQL Server Setup enforces the same-account requirement. In a scale-out deployment where a SharePoint farm already has an instance of PowerPivot for SharePoint installed, Setup will block the new installation if the Analysis Services service account you specified is different from the one already in use in the farm. |
PowerPivot Service Application Pool
Requirement |
Description |
|---|---|
Provisioning requirement |
The service application pool must be specified when the service application is created. New Server installation option: If you install PowerPivot for SharePoint using the New Server installation option, a default service application is created for you, with the application pool set to the same Windows domain user account that you specified as the farm account. You can change the service application pool in Central Administration after PowerPivot for SharePoint is installed. Existing Farm installation option: If you install PowerPivot for SharePoint using the Existing Farm installation option, you create the service application in Central Administration. You set the account when you create the service application. |
Domain user account requirement |
The application pool identity must be a Windows domain user account. Built-in machine accounts (such as Network Service or Local Service) are prohibited. SQL Server Setup enforces the domain user account requirement by blocking installation whenever a machine account is specified. |
Permission requirements |
This account does not need local system Administrator permissions on the computer. However, this account must have Analysis Services system administrator permissions on the local Analysis Services service that is installed on the same computer. These permissions are granted automatically by SQL Server Setup or when you set or change the application pool identity in Central Administration. Administrative permissions are required for forwarding queries to the Analysis Services service. They are also required for monitoring health, closing inactive sessions, and listening for trace events. The account must have connect, read, and write permissions to the PowerPivot service application database. These permissions are granted automatically when the application is created, and updated automatically when you change accounts or passwords in Central Administration. The PowerPivot service application will check that a SharePoint user is authorized to view data before retrieving the file, but it does not impersonate the user. There are no permission requirements for impersonation. |
Scale-out requirements |
None. |
Plan for Database Storage and Disk Space
When estimating disk space and database storage requirements, you should develop estimates using a test environment that mirrors your production environment.
Location |
Description |
|---|---|
Database storage |
A database server in a SharePoint farm will store PowerPivot user data in content databases. Additional document types related to a PowerPivot installation include:
.gif "Note") Note
Be aware that versioning and recycle policies that you set at the application level can result in storing the same large file many times over in the same database, significantly raising the rate of disk space consumption. If you enable versioning, a new copy of document will be created and stored each time a user checks in a changed document. Because of the negative effect on database storage, versioning is not recommended for web applications that use PowerPivot for SharePoint.
A database server will also host a content database for Central Administration. PowerPivot for SharePoint uses it to store the following files:
A database server will also host a PowerPivot application database for each service application you configure. This database will store the following:
|
Disk space on an application server |
PowerPivot for SharePoint caches files on an application server to eliminate the wait time for transferring the same file from a content database. Files are cached to disk so that the data source can be loaded immediately rather than retrieved from the content database each time a load request is received. All PowerPivot data files that are loaded in memory on a server will also be cached to disk on that same server. Depending on where the PowerPivot data file was last loaded, the same file might be cached on multiple application servers. Cached files are not stored indefinitely. Files that are not used are removed after fourteen days. A daily synchronization check by the PowerPivot service also removes cached files if the original file in the content database is modified or deleted. Deployment topology is a factor in how much disk space you will need to support PowerPivot file caching. On a single server deployment, all files are cached on one server. A multi-server deployment distributes cached files across a larger number of servers, possibly reducing the amount of disk space used on a per server basis. |
Plan for PowerPivot Data Transfer
Data transfer speed is a significant bottleneck to performance of PowerPivot query processing in the farm. Consider a 100 MB per second connection speed. At this transfer rate, a 2 GB Excel workbook might take 20 seconds or more to download from a SharePoint site to a client workstation. In addition, because SharePoint is optimized for download operations, the same file might take longer to upload to the server.
To minimize file transfers, PowerPivot servers use a file caching methodology that allows the server to retain and re-use data sources that were previously loaded on the server.
If a file is not cached, and the data source is very large, you should expect a longer load time as the file moves across the wire. For more information about file caching and data requests, see PowerPivot System Service.
Plan for Secure Storage of Sensitive Data
Using the built-in PowerPivot data refresh feature requires that you enable Secure Store Service for your farm. Secure Store Service is shared service built into the SharePoint infrastructure that stores credentials. PowerPivot for SharePoint uses Secure Store Service to define and store the unattended data refresh account, Windows credentials, or database credentials. For more information, see Configure and Use Stored Credentials for PowerPivot Data Refresh.
Plan for Creating and Viewing PowerPivot Data
Client applications for PowerPivot data include the following:
Web browser for viewing a published PowerPivot workbook that is published to a SharePoint library. Excel Services renders the workbook in a browser and supports some pivot operations. For example, users can sort, filter, and expand or collapse areas of the PivotTable or PivotChart. Users cannot modify the PivotTable or PivotChart, nor can they click links to drill down into detailed data. For full drilldown support, you must have the Excel desktop application.
Excel 2010 with the SQL Server PowerPivot for Excel add-in. Both Excel 2010 and the add-in are required to create PowerPivot workbooks and use all of the interaction features of PivotTable or PivotChart. You can use earlier versions of Excel to view static PowerPivot data, but you cannot pivot the data. For more information about the Excel add-in and support for different versions of Excel, see PowerPivot for Excel.
SQL Server Management Studio can be used as a diagnostic tool for checking connections or for browsing PowerPivot data structures. To connect to the data source, enter the SharePoint URL of a published PowerPivot workbook in the Connect to Server dialog box.
If you plan to support large scale data in Excel 2010, remember that information workers who use 32-bit workstations will be subject to the memory constraints of that architecture. For this reason, deployment planning for some organizations should include an evaluation of the capabilities of the client workstations. If you purchase and deploy resource-rich servers, you might also need to upgrade client workstations to a 64-bit architecture to assemble and enrich large data that is published to the farm.