In-Depth

Planning a PowerPivot for SharePoint Deployment

Learn how PowerPivot for SharePoint expands the capabilities of the desktop client and SQL Server Analysis Services.

• 11/17/2010

by Chris Leiter

When deciding to use PowerPivot in your enterprise, you must weigh the costs, benefits, risks, and rewards of implementing a self-service business intelligence (BI) offering instead of a traditional BI solution. The same considerations must be given to scalability, supportability, security, and usability. PowerPivot can provide a robust and dynamic experience for information workers to quickly develop charts and reports that provide them with appropriate and necessary business insight, but like all applications (particularly SharePoint applications), you must ensure that PowerPivot for SharePoint follows the governance and compliance requirements of your organization.

When designing a PowerPivot for SharePoint solution, understand how PowerPivot for SharePoint will be used, especially in the context of a broader SharePoint offering. This will help you develop the right solution and at the appropriate scale for your organization. It is important to remember that PowerPivot is not designed to replace an enterprise data warehouse. Organizations that have invested in a traditional SQL Server Analysis Services solution should not expect to discard it and allow users to build their own versions of the truth. PowerPivot fills a need for ad hoc reporting and analysis but it should be considered as one of many available BI tools.

SharePoint Architecture

PowerPivot for SharePoint is designed to integrate with Microsoft SharePoint Server 2010. It takes advantage of the new application model that allows you to create one or more application instances which can be associated with one or more Web applications via an application proxy. To understand how PowerPivot for SharePoint works, become familiar with these components:

The PowerPivot Service: This service acts as the broker for Excel files that contain PowerPivot data. When a PowerPivot file is rendered in the browser by the Excel Services application, the PowerPivot service is instantiated. One of the tasks of this service is to maintain an instance map to identify which server, if any, may have the database already loaded. If the model already exists, the Excel Service can connect to the existing model. If not, the PowerPivot system service can use a selectable mechanism to determine where ((which instance) to load the model into memory.

The SQL Server Analysis Services (SSAS) Engine: The PowerPivot for SharePoint engine is a modified SQL Server Analysis Services engine that runs in Vertipaq mode, which means that the entire OLAP database is loaded into system memory, allowing much faster and more responsive read operations. Unlike a traditional SSAS instance, it must be installed on a SharePoint server in SharePoint integrated mode.

PowerPivot Solutions: There are two solution files that must be deployed to enable PowerPivot functionality. PowerPivotFarm.wsp registers the required libraries and components in the Global Assembly Cache (GAC), registers PowerPivot content types, and adds the required templates and pages for administration and usage. This is deployed during installation. The PowerPivotWebApp.wsp solution enables Web Front End (WFE) functionality such as thumbnail image generation. This solution will need to be deployed on each Web application that will use PowerPivot for SharePoint (including Central Administration).

Scalability

Because PowerPivot for SharePoint takes advantage of the application services model in SharePoint 2010, it is much easier to build applications that scale as your business grows. Although it is entirely possible to run all required services on a single server, this is not an optimal solution because the server itself becomes a single point of failure. The preferred architecture for a PowerPivot for SharePoint solution will invoke a three-tiered model, as seen in the figure below.

Illustration property of Hitachi Consulting

In this model, implement a minimum of two Web Front End Servers, placed behind a load balancer for fault tolerance and availability. This is the presentation tier that will allow users to access SharePoint services.

The application services tier is where services such as PowerPivot for SharePoint, PerformancePoint, Excel Services, and others should be running. Depending on which application services you will be using and the hardware resources available, you may be able consolidate one or more of these applications on as few servers as possible. A minimum of two servers is recommended for fault tolerance and for load balancing, which will vary by application. With applications designed for SharePoint 2010, it is typically not necessary to put application servers behind a load balancer.

Finally, the data services tier is used for data storage. This will include the databases for SharePoint farm configuration, application configuration, and content. One or more SQL Server clusters are recommended at this layer, and distribution of content and configurations databases may vary based on your specific usage requirements. You can further increase availability by using database mirroring across clusters.

Licensing Considerations

Unlike the PowerPivot for Excel add-in, PowerPivot for SharePoint is only available as part of SQL Server 2008 R2 Enterprise, Enterprise Evaluation, Data Center, or Developer editions. The licensing terms of those products will apply to PowerPivot for SharePoint the same as other BI components. These licenses cannot be "shared" across multiple physical servers (i.e., installing the Database Engine on server A and PowerPivot for SharePoint on server B under a single license). For each application server on which PowerPivot for SharePoint is installed, a valid license must be acquired.

Required Licensing

PowerPivot for SharePoint requires SharePoint Enterprise Client Access Licenses (CALs) for consuming PowerPivot for SharePoint features. PowerPivot for SharePoint will not work with SharePoint Foundation 2010. Additionally, you must have a SQL Server Enterprise Edition (or Datacenter Edition) license for each PowerPivot Application server in your farm. Remember, this is required only for the servers on which the SQL Server Analysis Services in SharePoint integrated mode engine is installed.

Cost Model Concerns

From a software licensing perspective, PowerPivot for SharePoint can be a costly investment. Although specific pricing will vary based on a number of factors, you will need to evaluate the best and most cost-effective solution for your organization. Consider the following scenario comparing the per-processor model with the server/CAL licensing model. The table below contains assumptions about SQL Server Enterprise Edition pricing that is used for illustrative purposes only.

Using these assumptions, let’s compare the per-processor cost model of implementing two application servers running PowerPivot for SharePoint versus the server/CAL Licensing option.

When you compare this against the cost of installing SQL Server Analysis Services on an existing SQL cluster (or even a new cluster), which may significantly reduce costs, the cost-benefit may not be obvious. However, the ability to provide a self-service model creation and reporting solution in SharePoint may provide a much-needed business advantage your information workers may not have otherwise. For more information on SQL licensing, visit http://www.microsoft.com/sqlserver/2008/en/us/licensing.aspx.

Summary

PowerPivot is a robust, information worker-based solution to bring business intelligence to the desktop. PowerPivot for SharePoint expands the capabilities of the desktop client and SQL Server Analysis Services that allows users to quickly build, deploy, and share OLAP solutions with minimal effort.

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 [email protected]