Microsoft SQL Server PowerPivot Planning and Deployment

SQL Server Technical Article

Writer: Kay Unkroth

Technical Reviewers: Daniel Yu, Dave Wickert, Denny Lee, Donald Farmer, James Howey, John Hancock

Published: April 2010

Applies to: SQL Server 2008 R2

Summary: Microsoft® SQL Server® PowerPivot technology redefines how IT departments deliver and succeed with BI. This technology gives users the power to drill into any aspect of their business and compress decision cycles to gain deeper insight, and it enables them to share their findings with others effortlessly and securely. At the same time, the IT department gains effective new management tools to increase operational efficiency in a Microsoft SharePoint® Server 2010 farm environment.Establishing a PowerPivot environment requires deploying the PowerPivot for Excel® 2010 add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers. Those tasks are independent of each other, but planning and coordination are required to ensure the overall success of the deployment efforts.

Executive Summary

Microsoft® SQL Server® PowerPivot is an innovative data analysis technology that redefines how organizations of all kinds deliver and succeed with business intelligence (BI). The focus shifts from IT delivering corporate BI solutions toa managed BI collaboration environment that gives users the power to get timely and reliable information to make more relevant decisions. PowerPivot does not replace corporate BI, but complements it with managed, self-service solutions.

Providing business insights to all employees means giving producers of intelligence access to the best data analysis tools and reliable access to trustworthy data, as well as facilitating knowledge-sharing and collaboration within teamsof producers and consumers of intelligence and across departmental boundaries. It means leveraging the network of power users in each department, the ones who create departmental and team solutions, assist colleagues in ad-hoc analysis, and, when a centralized solution is best, communicate BI requirements back to the IT department. It also means implementing reliable BI monitoring and management processes to ensure availability and performance for mission-critical, self-service BI applications. PowerPivot enables organizations to extend the reach of BI in the enterprise from corporate to team and individual spaces, while at the same time increasing IT management and operations efficiency.

PowerPivot integrates with Microsoft Office Excel® 2010 to give users unmatched computational power for advanced data analysis with a familiar user interface. PowerPivot also integrates with Microsoft SharePoint® Server 2010 to establish a managed, self-service BI environment that takes advantage of all the standard SharePoint features, such as role-based security, compliance policies, workflows, and versioning, and introduces new features and capabilities, such as PowerPivot Gallery and automatic data refreshing for shared workbook applications. And PowerPivot features a management dashboard that lets users monitor shared applications, track usage patterns over time, drill down to reveal hidden details, discover mission-critical solutions, and make sure appropriate server resources are provisioned.

Establishing a managed, self-service BI environment entails deploying the PowerPivot for Excel add-in on workstations running Microsoft Office 2010 and PowerPivot for SharePoint on SharePoint application servers. These deployments can be performed independently. The PowerPivot for Excel add-in does not require a SharePoint environment and the SharePoint environment does not require PowerPivot for Excel on all workstations. The Excel add-in is a requirement only for those creating and publishing workbook applications. Other users can access published workbook applications in SharePoint via a Web browser, with the same performance and most of the features as the Excel client. However, planning and coordination are required to determine the best deployment sequence and configuration options, provision adequate storage capacities and system resources, and optimize the managed BI collaboration environment for high availability and performance.

This white paper contains information for technical decision makers, IT administrators, and system architects who are planning to deploy PowerPivot technology in an enterprise environment. This paper assumes the audience is already familiar with the Windows Server® operating system, Microsoft Office, SharePoint, SQL Server, online analytical processing (OLAP), and self-service BI. A high-level understanding of the new features and technologies in Microsoft SQL Server 2008 R2 is also helpful. Detailed product information is available in the MSDN® Library for SQL Server 2008 R2 at https://go.microsoft.com/fwlink/?LinkId=181772.

Introduction

Microsoft Office Excel has long been the dominant data analysis tool in the enterprise. Despite the availability of alternative solutions, employees and decision makers continue to favor Excel because the user interface is familiar and the analysis features are comprehensive and intuitive. Workbooks and spreadsheets are easy to use. Without having to wait weeks or months for IT to deliver BI solutions, users can import data from virtually any source directly into a workbook, process the data using Excel formulas and other data manipulation capabilities, and analyze the data in a variety of ways using PivotTables, PivotCharts, and SQL Server Data Mining add-ins, and so forth. In this way, Excel has served as the basis for self-service BI for more than a decade.

“The way in which Excel particularly can serve as a front end to data warehouses has been dramatically enhanced, and I think you'll find that particularly interesting,” Steve Ballmer, Launch of Microsoft SQL Server 7.0 at COMDEX/Fall ’98, November 16, 1998.

Now, PowerPivot for Excel takesthe self-service BI capabilities of Excel to an unprecedented level. As a separate add-in, PowerPivot exploits the familiarity of Excel while adding an in-memory BI engine and new compression algorithms to load even the biggest data sets into memory. Users can process enormous quantities of data with incredible speed. Processing millions of rows takes about the same time as processing thousands, and by using Data Analysis Expressions (DAX) in addition to standard Excel features, power users can easily create advanced workbook applications that rely on data relationships between tables as in a database, include calculated columns and measures, and aggregate over billions of rows. In many cases, PowerPivot for Excel can establish the table relationshipsautomatically. Workbooks become more powerful and more mission-critical than ever.

However, bigger, more powerful, mission-critical workbook applications pose significant challenges for the IT department. It is vital to avoid the proliferation of spreadmarts in the enterprise, and to ensure data consistency, integrity, security, and compliance across the myriad of user-generated workbooks. Perhaps even more fundamental, it is crucial to be able to discover mission-critical workbook applications and keep an eye on performance, availability, and quality of service. It is not uncommon for user-generated spreadsheets to evolve into mission-critical business applications, and for IT departments to remain unaware of their existence until an update or change in the underlying data sources breaks these spreadsheets and users turn to IT for troubleshooting with mission-critical urgency. Another key issue is maintenance, as conflicting and outdated data in spreadsheets contributes to misleading analysis, confusing results, and complications in decision-making. Clearly, a managed BI collaboration environment is required to meet the self-service BI challenges with operational efficiency.

PowerPivot integrates with SharePoint Server 2010 as a reliable platform for building the managed BI collaboration environment. Among other capabilities, SharePoint facilitates seamless and secure sharing and collaboration on user-generated workbook applications, while PowerPivot provides the management tools and usage data that put IT in control of spreadmarts. Now the IT department can determine who is using shared workbook applications, when, how often, and with what client application. Perhaps even more importantly, the IT department can also analyze the data source information to see where users are importing their data from. By supporting automatic data refreshing for shared workbooks, PowerPivot helps to ensure consistent and accurate information in self-service BI applications. It is even possible to use shared workbooks as data sources for further analysis, similar to a SQL Server Analysis Services cube. By delivering SharePoint-based BI management tools, PowerPivot makes it possible for IT departments to establish efficient monitoring processes, discover mission-critical workbook applications proactively, and apply appropriate maintenance procedures.

<span id="_Toc251353878"><span id="_Toc246474322">

The success of BI in the enterprise depends on the ability of the BI environment to satisfy user needs, IT needs, and business requirements. Users want to work with familiar tools and need clear and accurate answers to their questions. IT must ensure security and compliance with reliable, scalable, and fast-performing systems that deliver accurate data to users, facilitate information and analysis sharing, and make it easy to collaborate on user-generated BI solutions. Business requirements, on the other hand, are concerned with achieving a positive return on investments (ROI) quickly, reducing operations costs, and increasing business agility and productivity. PowerPivot—unlike any other technology—enables organizations to address these needs and requirements with familiar tools and efficient processes.

Table 1 summarizes the benefits and advantages of PowerPivot technology.

Table 1: PowerPivot Benefits and Advantages

User-Related IT-Related Business-Related

Maximized utilization of familiar Excel features, such as the Office Fluent™ user interface, PivotTables, PivotCharts, and the new Slicers feature.

Fast calculations and advanced analysiscapabilities, such as through automatically established data relationships and DAX expressions, which make actionable insight readily accessible to everyone.

More and faster answers by combining massive amounts of data from a multitude of sources, including relational databases, multidimensional sources, cloud services, data feeds, Excel files, and text files, in the corporate network and on the Internet.

Access to relevant information virtually anytime and from any location through PowerPivot galleries in SharePoint.

Increased IT efficiency associated with monitoring and managing mission-critical self-service BI applications in the enterprise.

Increased consistency, integrity, security, and compliance, reliability, and scalability for self-service BI applications based on standard SharePoint-based features.

High degree of data accuracy for decision-making through automatic data refreshing.

Reduced IT backlog by delegating BI support responsibilities to power users in each department.

Increased business agility through better, faster, more relevant decisions.

Increased employee and team productivity through shared self-service BI applications.

Maximized ROI into SQL Server 2008 R2, SharePoint Server 2010, and Office 2010.

Reduced operations costs associated with maintaining and supporting self-service BI applications in the enterprise.

PowerPivot for Excel: Architecture and Design

SQL Server PowerPivot comprises client and server technology. On the client side, power users work with PowerPivot for Excel to create and work with spreadsheets and workbooks.

PowerPivot for Excel is an application-level Excel add-in that implements advanced data analysis features, enhances the user experience through ribbon customizations and spreadsheet templates, and overrides the default PivotTable field list to implement its own task pane, thereby enabling the functionality of OLAP PivotTables and PivotCharts without requiring SQL Server Analysis Services cubes.

Application Architecture

Figure 1 shows the PowerPivot for Excel architecture, which relies on an add-in assembly, a VertiPaq engine, and a VertiPaq database module. The add-in assembly loads PowerPivot into the Excel application process. The VertiPaq engine performs query processing and implements a column-based data store with efficient compression algorithms to get massive amounts of data directly into memory. With all the data in memory, PowerPivot can perform its query processing, data scans, calculations, and aggregations without having to go to disk. As a consequence of working with column-based data and avoiding the overhead of disk I/O operations, PowerPivot for Excel achieves a very high analysis performance.

Figure 1: PowerPivot for Excel Architecture

The PowerPivot application architecture includes the following main components:

PowerPivot add-in assembly   Loads PowerPivot into Excel. PowerPivot then interacts with Excel through the Component Object Model (COM) interop assembly of Excel, calls into the Excel object model, and responds to events and callbacks raised in Excel.

Registry settings and manifest files Enable Excel 2010 to load PowerPivot at run time. The registry settings under HKEY_CURRENT_USER\Software\Microsoft\Office\Excel\Addins point to the deployment and application manifests, which are Extensible Markup Language (XML) files that describe how to load the add-in assembly.

VertiPaq engine   Processes PivotTable and PivotChart queries and DAX expressions. The VertiPaq engine leverages the richness of Analysis Services object models internally, but without imposing dimensional modeling concepts on users.

VertiPaq database   Stores the PowerPivot data. The VertiPaq module maintains its database in a custom XML part within the Excel workbook. Note that you can locate the VertiPaq database in an Excel file by renaming the workbook using the .zip file extension, opening the .zip file in Windows® Explorer, and then navigating to the xl\customData folder. For technical details, refer to the blog post “For Excel PowerPivot, the Database is IN the Workbook” at https://go.microsoft.com/fwlink/?LinkId=181771.

Data cache   Stores temporary files during save operations in the %TEMP% directory. With the exception of this temporary data cache, PowerPivot maintains no data outside the Excel workbook. For details, refer to the blog post “Understanding why an Excel PowerPivot Workbook is so Large” at https://go.microsoft.com/fwlink/?LinkId=181773.

Microsoft OLE DB for Online Analytical Processing (OLAP)   Exposes a set of COM objects and interfaces to access multidimensional data stores. The OLE DB for OLAP provider can connect to SQL Server Analysis Services as well as PowerPivot workbooks on the local computer or published in a SharePoint farm.

Analysis Management Objects (AMO) and ADOMD.NET   Communicate with a running instance of SQL Server Analysis Services or PowerPivot by using XML for Analysis (XMLA). AMO exposes management objects and ADOMD.NET enables client applications to retrieve data and metadata from Analysis Services and PowerPivot data sources.

Note: PowerPivot features a document-centric view of the data, meaning all imported data and connection information for external data sources reside in the Excel workbook.

Hardware and Software Requirements

PowerPivot benefits from multi-core processors, large memory and storage capacities, and a 64-bit operating system on the client computer. The x64 platform removes the 4 GB limitation for addressable memory. Disk storage is important because PowerPivot workbooks with hundreds of millions of rows can quickly exceed several hundred megabytes in size,even though column-based compression helps to reduce the data volume in comparison to the source data. Users also benefit from a fast computer network when importing data and uploading workbooks to PowerPivot galleries and libraries in SharePoint.

Note: The maximum file size of a PowerPivot workbook is 2 GB. There are no restrictions on the amount of data users can import into a workbook, but workbooks exceeding the maximum file size can’t be saved. A 2-GB workbook typically corresponds to a 4-GB dataset, considering a 2:1 compression ratio.

Table 2 summarizes minimum and recommended system configurations for PowerPivot for Excel on client computers.

Table 2: PowerPivot for Excel System Requirements

Component Minimum Recommended

Processor

500 megahertz (MHz) processor

Dual-core processors faster than 2.6 gigahertz (GHz)

Random Access Memory (RAM)

1 GB

4 GB or more

Disk

NTFS file system–formatted partition with a minimum of 3 GB of free space

NTFS file system–formatted partition with 40 GB of free space for workbook files

Display

1024 × 768

1024 × 768 or higher resolution monitor

Network

56 kilobits per second (Kbps)

1 gigabit per second (Gbps)

Operating System

Microsoft Windows XP with Service Pack 3 (32 bit)

Microsoft Windows Vista® or Microsoft Windows 7 Business Edition or Ultimate Edition (64-bit)

Software

Microsoft Excel 2010

Microsoft .NET Framework 3.5 SP1

Microsoft Office 2010 Microsoft .NET Framework 4.0

Note: Performance tests show that PowerPivot for Excel can load more than 100 million rows and maintain adequate processing performance with 2 GB of memory. However, test results vary depending on the compressibility of the data. For fastest processing performance, Microsoft recommends multi-core processors and more than 4 GB of RAM.

PowerPivot for SharePoint: Architecture and Design

On the server side, PowerPivot for SharePoint adds content types, administration and application pages, dashboards, timer jobs, and Web services, and integrates with Excel Services to establish a full-featured BI collaboration environment. Excel Services gives business users the ability to view shared PowerPivot workbooks in a Web browser, any time and at any place with network connectivity.

Just as PowerPivot for Excel extends the capabilities of Excel on the desktop, PowerPivot for SharePoint complements the capabilities of Excel Services in a SharePoint farm. Excel Services provides the foundation to share workbooks as interactive, Web-based reporting tools. Excel Services also supports integration with other BI features and technologies, such as SharePoint Report Center, SQL Server Reporting Services, and Microsoft PerformancePoint® Services 2010.PowerPivot also adds its own front-end Web service so that PowerPivot for Excel and other client applications can access PowerPivot data in SharePoint directly.

System Architecture

Figure 2 shows the system architecture of PowerPivot for SharePoint in relationship to Excel Services and typical client applications. As a service application, PowerPivot is fully integrated into the SharePoint infrastructure for hosting services.

Figure 2: PowerPivot for SharePoint Architecture

The PowerPivot for SharePoint architecture relies on the following main components:

Analysis Services Client Libraries   Enable client applications to communicate with the PowerPivot Web service. Clients must use the SQL Server 2008 R2 version of the client libraries to support PowerPivot connections.

Analysis Services Service   Loads the VertiPaq engine to manage the data within PowerPivot workbooks retrieved from SharePoint content databases. The PowerPivot system service extracts the database from the Excel workbook, selects an appropriate SharePoint application server, which runs the Analysis Services service as well as an instance of the PowerPivot system service, and then attaches the database to the Analysis Services instance without requiring Excel 2010 on the server.

Note: On a SharePoint server, an Analysis Services instance can only load VertiPaq databases for in-memory BI processing. Analysis Services in VertiPaq mode does not support traditional OLAP processing modes. Although it is possible to install additional Analysis Services instances on the same server to support traditional OLAP processing, Microsoft does not recommend this configuration because of the differences in workload patterns and resource consumption.

Content Database   Stores the data, documents, and workbooks associated with SharePoint lists, libraries, and sites. SharePoint maintains all PowerPivot workbooks in content databases. Analysis Services instances can retrieve the workbooks by using the SharePoint object model. The workbook URL identifies the SharePoint content item.

Data Cache Maintains workbooks in memory and on disk in the %PROGRAMFILES%\Microsoft SQL Server\MSAS10_50.Powerpivot\OLAP\Backup folder to increase system performance by eliminating roundtrips to the SharePoint content database.

Excel Calculation Services   Loads PowerPivot workbooks in much the same way as other Excel workbooks, calls user-defined functions if necessary, and refreshes external data as needed, such as when a user interacts with PivotTables or PivotCharts via slicers. The connection information for PowerPivot data points to the PowerPivot OLE DB provider, which enables Excel Calculation Services to request the data from the PowerPivot system service. The DataSource property for VertiPaq databases is listed as $EMBEDDED$.

Excel Web Access   Renders workbooks on a Web page and allows the user to interact with those workbooks and explore them in Microsoft Internet Explorer®. Excel Web Access does not require Excel on the client computer.The Excel Web Access Web part renders the workbook using Excel Calculation Services.

Excel Web Services   Provides programmatic access to workbooks. Business applications can calculate, set, and extract values from workbooks, refresh external data connections, and create application-specific user interfaces around shared workbooks.

PowerPivot Application Database   Stores configuration settings, connection information, server status, and schedule information for data refresh operations.

PowerPivot Gallery   Implements custom content types and Microsoft Silverlight™-enabled application pages for document libraries to visualize shared workbooks and Reporting Services applications interactively.

PowerPivot Management Dashboard   Facilitates centralized BI monitoring and management in a PowerPivot for SharePoint environment.

OLE DB Provider Enables Excel Calculation Services and other processes, such as Reporting Services and PerformancePoint Services, to locate and establish connections to a PowerPivot system service in the local farm or a PowerPivot Web service in a remote farm. The PowerPivot transport and connection logic is directly integrated into the Analysis Services OLE DB provider of SQL Server 2008 R2, which also provides connectivity to SQL Server Analysis Services (SSAS). The OLE DB provider uses the IsHosted flag to distinguish between SSAS and PowerPivot clients and relies on Windows Communication Foundation (WCF) to communicate with a PowerPivot system service in the local farm or Hypertext Transfer Protocol (HTTP) to communicate with PowerPivot Web services in remote farms.

PowerPivot System Service   Uses Microsoft ADOMD.NET to communicate with an Analysis Services instance. The PowerPivot system service also manages the application database, monitors server health, coordinates client requests for load balancing, collects usage data, and performs automatic data refreshing for PowerPivot workbooks.

PowerPivot Web Service   Exposes shared PowerPivot workbooks via XML for Analysis (XML/A) as data sources to external applications, such as Excel or Microsoft Report Builder 3.0. Support of XML/A enables any client application that uses Multidimensional Expressions (MDX) and the Analysis Services OLE DB provider to query PowerPivot data in workbook applications hosted in a SharePoint farm.

Service Applications Provide backend services for Web applications. SharePoint 2010 no longer uses Shared Services Providers (SSP). Instead, SharePoint 2010 uses an Internet Information Services (IIS) Web site called SharePoint Web Services, which hosts all service applications in one or multiple application pools for security and process isolation. Default and custom service application groups associate service applications with Web applications.

Web Applications Provide IIS sites and application pools, security identities, and isolation for front-end processes on a SharePoint server. A single Web application can host multiple site collections and each site collection can have the PowerPivot feature activated or deactivated individually.

SharePoint Timer Jobs Run scheduled processes to collect and process usage information, refresh data in shared workbooks, gather health statistics, and update the Management Dashboard workbook with data from the PowerPivot application database. Table 3 summarizes the timer jobs that PowerPivot adds to the SharePoint configuration.

Table 3: PowerPivot-Specific Timer Jobs

Timer Job Default Schedule Description

Usage Data Import timer job

Hourly

Collects usage information from PowerPivot components and imports the data into the centralized SharePoint Usage Collection database.

Usage Data Processing timer job

Daily

Processes the usage information from the centralized SharePoint Usage Collection database and imports the normalized data into the PowerPivot application database.

PowerPivot Data Refresh timer job

Minutely

Determines if a data refresh job needs to be scheduled and executed.

PowerPivot Health Statistics Collector timer job

Every 15 minutes

Collects statistics regarding the CPU and memory utilization of PowerPivot components and imports the data into the PowerPivot application database.

PowerPivot Management Dashboard Processing timer job

Daily

Updates the Management Dashboard workbook based on the data in the PowerPivot application database. This timer job must be scheduled to run after the Usage Data Processing timer job.

PowerPivot Setup Extension timer job

Hourly

Verifies that all installed PowerPivot components are up-to-date and running and checks whether there are PowerPivot components that need to be patched after SQL Server patching runs.

Note: Microsoft SQL Server Management Studio can connect to an Analysis Services instance on a SharePoint server and list the VertiPaq databases loaded on demand when users interact with the workbooks. However, Microsoft does not support the use of SQL Server Management Studio for Analysis Servicesadministration on a SharePoint server. Use SharePoint Central Administration and PowerPivot Management Dashboard instead.

PowerPivot Farm Topologies

SQL Server PowerPivot supports all SharePoint farm topologies, ranging from standalone server deployments to large multi-server installations. A standalone server hosts all Web applications, service applications, and SQL Server databases on a single computer. This configuration is suitable for evaluation purposes and small workgroup environments. In most environments, however, it is more advantageous to deploy separate front-end servers and application servers. Figure 3 illustrates this concept.

Figure 3: PowerPivot for SharePoint in a Multi-Server Farm Environment

By scaling out with multiple application servers, an organization can support SharePoint, SQL Server, and PowerPivot with higher availability, increased performance, better manageability, and greater security. PowerPivot can run together with other components and services on the same SharePoint server as long as the computer is not configured as a dedicated front-end server for Web applications or SharePoint Central Administration. It is also possible to deploy dedicated PowerPivot application servers that do not run other service applications, which is especially useful in environments with a large number of workbooks and long-running queries.

In farms with multiple PowerPivot servers, PowerPivot automatically performs load-balancing in a round-robin fashion or based on server health, depending on the system configuration. Each PowerPivot service application maintains an internal list of available servers and datasets in the farm, which PowerPivot uses as follows:

  1. Data is already loaded. The PowerPivot service application checks its internal list of active PowerPivot datasets to determine an Analysis Services instance in the farm that already has the data loaded into memory. If this is the case, PowerPivot passes the requests to the PowerPivot system service responsible for the selected server. This PowerPivot system service then passes the request to its local Analysis Services instance. This mechanism ensures that only one copy of a workbook is loaded across the entire farm.
  2. Data is not loaded but it is cached. The PowerPivot service application checks its internal list of cached PowerPivot datasets that are already on the file system. If the same PowerPivot dataset is cached on multiple servers, the PowerPivot service application will check server health and build a list of servers that have sufficient resources to accept the request. From that list, a server is picked at random.
  3. Data is neither loaded nor cached. If the data must be retrieved from the content library, PowerPivot uses an allocation scheme to select the server for the request. The PowerPivot service application then issues a load request to the selected Analysis Services instance, passing the SharePoint URL of the PowerPivot workbook.

Note: You can create multiple PowerPivot service applications in a farm to isolate service processing and configuration settings for different Web applications, but you cannot restrict a PowerPivot service application to a specific subset of PowerPivot servers. A PowerPivot service application always includes all PowerPivot servers in the local farm. Deploying additional PowerPivot servers increases the processing capacity of the entire farm.

PowerPivot Service Associations

As a service application, PowerPivot takes advantage of the SharePoint 2010 infrastructure for hosting services. This provides administrative flexibility in centralized SharePoint environments that host site collections and sites for multiple departments and teamswith different processing and configuration requirements.It is even possible to configure Web applications to use services from different farm environments. Services that can be shared across server farms include the Secure Store Service, User Profile, Managed Metadata, Business Data Connectivity, Web Analytics, and Search. Note, however, that Excel Services and PowerPivot for SharePoint are single-farm services. These services cannot be shared across farms. They must reside in the farm that hosts the content.

Figure 4: PowerPivot for SharePoint and Service Associations

Figure 4 illustrates how Web applications in a SharePoint farm can share the same PowerPivot service application, while another Web application uses a custom service association that excludes the PowerPivot service application. This configuration can be useful for Web applications of departments that have not yet completed their onboarding to the managed BI collaboration environment. As soon as a department completes the onboarding process, a farm administrator can change the service associations to include the PowerPivot service application so the department can use PowerPivot in the local farm. The section “PowerPivot Deployment Planning” later in this white paper covers BI onboarding processes in more detail.

Note: PowerPivot Management Dashboard requires a PowerPivot service application in the default service connection group. Configurations that exclude PowerPivot for SharePoint from the default group are not supported.

System Requirements

Table 4 lists general PowerPivot for SharePoint system requirements. These requirements can serve as a starting point to build a reference system for evaluation purposes. Microsoft recommends performance testing on reference systems prior to any production deployment.

Table 4: PowerPivot for SharePoint System Requirements.

Component Minimum Recommended

Processor

2 dual-core processors

2 dual-core processors for commodity enterprise servers or 4 quad-core processors if the organization standardizes on quad-core servers.

Random Access Memory (RAM)

8 GB

64 GB (depending on the number of currently used workbooks and their average size)

Network

100 megabit per second (Mbps)

Multiple 1-gigabit-per-second (Gbps) network interface cards (NICs)

Disk

NTFS–formatted partition with a minimum of 80 GB of free space

NTFS–formatted partitions with more than 80 GB of free space on dedicated direct attached storage (DAS)-based or storage area network (SAN)-based drives.

Database Engine
(for the SharePoint farm; not the PowerPivot app servers)

SQL Server 2005 Service Pack 2 (Cumulative Update 12) or Service Pack 3 (Cumulative Update 2) or SQL Server 2008 (Cumulative Update 4)

SQL Server 2008 R2

Operating System

Windows Server 2008 SP2 (64-bit) or Windows Server 2008 R2

Software

SQL Server 2008 R2 Enterprise Edition

SharePoint 2010, with Excel Services

SQL Server Native Client

Windows PowerShell™ V2

Microsoft .NET Framework 3.5 SP1

Microsoft "Geneva" Framework Runtime

Microsoft Sync Framework Runtime v1.0 (x64)

Microsoft Chart Controls for Microsoft .NET Framework 3.5

Microsoft SQL Server 2008 Analysis Services ADOMD.NET

Application Servers without PowerPivot for SharePoint

Microsoft OLE DB Provider for Analysis Services 10 (MSOLAP100.DLL)

SharePoint Central Administration servers without PowerPivot

Microsoft SQL Server 2008 R2 Analysis Services ADOMD.NET

Security Accounts

Separate Windows domain accounts for:

  • Analysis Services instance
  • PowerPivot service application
  • Unattended PowerPivot data refresh

Note: The general recommendations are sufficient for only a relatively small number of users and workbooks in a workgroup or team environment with fewer than 100 concurrent PowerPivot connections. Large PowerPivot deployments benefit from higher memory capacities to load more data and extra processors to support long running scans of raw, unaggregated data.Note also that PowerPivot performance depends on the net available bandwidth between application and database servers. Consider a connection speed of 1 Gbps or faster and multiple NICs.

Database Storage Requirements

SharePoint stores all user data in content databases, including PowerPivot workbooks, which can be as large as the maximum upload size. In conjunction with the estimated number of PowerPivot workbooks in all site collections and sites, the maximum upload size provides a good indicator of database storage requirements. Microsoft recommends a maximum upload size of 2 GB for PowerPivot-enabled Web applications.

It is important to note that SharePoint versioning and recycle policies can increase storage requirements substantially. By default, SharePoint retains deleted workbooks in the recycle bin, which contributes to storage requirements. Furthermore, with versioning enabled, SharePoint creates a new item copy each time a user checks in a changed document or workbook. The system retains the old copy as part of the versioning history. Because of the increased demand for database storage, Microsoft does not recommend enabling versioning for Web applications that use PowerPivot for SharePoint unless there are compelling business reasons. Table 5 lists general factors that contribute to database storage requirements.

Table 5: PowerPivot Database Storage Considerations

Content Database for Site Collections Content Database for SharePoint Central Administration Content Database for PowerPivot Service Application

PowerPivot workbooks and other documents up to the maximum upload size.

Reporting Services reports that include charts, maps, and other visual elements.

Versioning history, which retains previous versions of the same large files many times over.

Recycle policies, which retain deleted items in the recycle bin.

Customized versions of site pages.

Excel worksheets and a PowerPivot data files used in the PowerPivot Management Dashboard.

Internal reporting database that tracks usage data and data refresh history for 365 days, by default.

Lists of cached or loaded PowerPivot data files.

Data refresh schedules.

PowerPivot usage data copied from the central usage data collection database.

Note:   SharePoint Server 2010 supports SQL Server 2008 R2 Remote BLOB Storage (RBS) to move the storage of binary large objects (BLOBs) from database servers to commodity storage solutions. In conjunction with PowerPivot workbooks, RBS can help to avoid wasting expensive database storage capacities.For details about using RBS in a SharePoint environment, refer to the Microsoft TechNet article “Install and Configure Remote BLOB Storage (SharePoint Server 2010)” at https://go.microsoft.com/fwlink/?LinkId=181774.

Local Disk Space Requirements

On application servers, PowerPivot for SharePoint caches files to eliminate roundtrips to the content database. This increases performance because the Analysis Services instance can load files cached on disk immediately rather than retrieving the workbooks from the content database each time a load request is received. By default, PowerPivot caches workbooks in the %PROGRAMFILES%\Microsoft SQL Server\MSAS10_50.Powerpivot\OLAP\Backup folder on the local system drive.

Scale-out deployments based on multiple load-balanced servers can help to reduce the number of cached workbooks on each individual server because PowerPivot prefers to direct new requests to a server that already has the requested workbook loaded or cached, as mentioned earlier in this white paper. However, multiple application servers might cache the same file, depending on downtime and other reasons that prevent a server with a loaded or cached copy from handling the current request. Although it is not possible to predict or restrict caching servers on a per-workbook basis in a multi-server topology, the scale-out deployment can help to distribute cached files across a larger number of servers, thereby reducing individual local disk space requirements.

Note: The PowerPivot system service on each server performs a daily synchronization check to remove cached files if the original file in the content database is modified or deleted or if the cached files are older than fourteen days. You can’t configure the retention time. PowerPivot for SharePoint caches workbooks on application servers for up to fourteen days.

PowerPivot Deployment Planning

The deployment of a managed BI collaboration environment based on SQL Server PowerPivot opens new opportunities to strengthen the relationship between information workers, decision makers, power users, solution developers, and IT professionals in the enterprise. As Figure 5 suggests, it is an opportunity to implement a BI strategy that lets IT focus on its core technology functions and BI developers on creating corporate BI solutions, while power users across the organization take an active role creating shared, self-service BI applications to give information workers and decision makers a comprehensive set of analysis tools that deliver the information they need to make decisions. It is important to engage all of these players in the deployment planning process.

Figure 5: Managed BI Collaboration in the Enterprise

Note: PowerPivot technology and managed, self-service BI applications do not replace SQL Server Analysis Services and corporate BI solutions. The technologies and solutions complement each other.

Deployment Planning and Management Process

SQL Server PowerPivot provides flexibility to accommodate waterfall, spiral, and agile development methods as documented in the Microsoft Solutions Framework (MSF) Process Model, which describes a high-level sequence of activities for building and deploying IT solutions. These activities are applicable whether an organization plans a rapid deployment in a single effort or prefers a more gradual approach in a sequence of deployment steps. Detailed information about the MSF, including an MSF Resource Library and case studies, is available on Microsoft TechNet. Figure 6 shows typical phases and milestones.

Figure 6: MSF Planning, Design, and Deployment Process

Table 6 outlines typical tasks and activities for planning and deploying a managed BI collaboration environment based on SQL Server PowerPivot.

Table 6: General Deployment Planning Tasks and Activities

Project Phase Activity Comments

Assessment and Scoping

Assemble the core team and define project vision and scope.

To ensure project success, MSF recommends assembling a multidisciplinary project team of peers who share responsibilities and balance each other’s competencies. In planning sessions with information workers, decision makers, power users, BI solution developers, and IT administrators, the team members must identify high-level business and technical requirements and translate these requirements into a project vision and scope that provide the context for all deployment actions and investments. For example, an important aspect concerns the decision to centralize or decentralize the BI environment.

Deployment Planning Exercises

Create the architectures and designs of all BI-related technologies and decide how to deploy PowerPivot for Excel and PowerPivot for SharePoint.

The objective of the deployment planning exercises is to formulate the overall project plans. This entails selecting the required hardware and storage technologies, system architectures, and farm designs based on typical productivity scenarios, performance, scalability, availability, security needs, and other requirements determined during the assessment and scoping phase. This phase also requires the project team to determine how to deploy PowerPivot for Excel.Power users need PowerPivot for Excel to create self-service BI solutions, but information workers and decision makers can access these solutions in a Web browser.

Proof of Concept

Perform lab deployments to evaluate PowerPivot for Excel and PowerPivot for SharePoint and validate the deployment concepts.

Prior to engaging in preproduction and production deployments, it is a good idea to verify the deployment concepts in a lab environment that simulates the future production environment in terms of hardware, software, and topology, but without production users. The lab environment provides the evaluation and testing ground for the project team to validate, benchmark, and optimize the designs for specific productivity scenarios, test deployment approaches, and verify operational readiness. For example, if the chosen service and administrative accounts do not satisfy the security requirements, the project team can alter the designs and deployment plans to make sure the BI environment functions as expected.

Pilot Projects

Conduct user acceptance testing in preproduction environments.

Preproduction and pilot deployments serve to verify the enterprise readiness of the deployment plans and designs, identify issues that might otherwise not be found prior to the production deployment, and collect valuable user feedback. The difference in comparison to lab deployments is that preproduction and pilot deployments include power users as early adopters. An important task is to document all designs and create build documents and checklists with detailed deployment instructions for the full-scale production rollout. These are the final specifications for acceptance and implementation.

Production Rollout

Deploy the core technology, provision site collections and sites, and stabilize the deployment.

The design documents, specifications for each server type, and deployment checklists coordinate the procurement and installation of server hardware and software in the data center. This typically includes the installation of operating systems, joining the new servers to the Active Directory® environment, installing SharePoint and adding the servers to the desired farms, provisioning Web applications with site collections and sites if necessary, and configuring the service applications and associations. To achieve a rapid deployment, the tasks can be automated using batch files and Windows PowerShell scripts. Independently, the deployment of the PowerPivot for Excel add-in can commence as part of the Office 2010 rollout, or separately after the Office 2010 installation.

Planning a Decentralized BI Collaboration Environment

In a decentralized environment, individual departments, workgroups, or teams have the knowledge and the ability to deploy and manage their own SharePoint farms and BI technologies, as illustrated in Figure 7. Although it is difficult to achieve broad sharing of information while maintaining security, compliance, and operational efficiency in a decentralized environment, decentralization is a valid choice if administrative autonomy is a priority, if there is no common Active Directory forest, or if connectivity issues prevent regional users from accessing SharePoint servers in a central location with acceptable performance.

Figure 7: Decentralized BI Collaboration Environment

PowerPivot for SharePoint supports decentralized deployments through new server installations and farm deployments of varying sizes and complexity. In particular, workgroups and teams might find the New Server option advantageous because of its simplicity. With the New Server installation option, SQL Server Setup automatically applies the required configuration of SharePoint features, thresholds, and security settings, activates and provisions Excel Services and Secure Store Service, and increases the maximum file size to support larger workbooks. However, this type of deployment places the entire workload on a single server. For this reason, it is a good idea to choose a high-end server model that has the memory and processing resources to support the significant processing demands of PowerPivot. Microsoft does not recommend using an existing server that is already running other data-intensive applications.

Note: If the processing demand exceeds the capabilities of a standalone server, you can add more PowerPivot servers, provided SharePoint was installed using the Server Farm option. The New Server installation option in SQL Server Setup does not prevent a scale-out with additional servers.

Planning a Centralized BI Collaboration Environment

Most enterprise environments can benefit from centralization of the BI infrastructure. With SQL Server PowerPivot, it is no longer necessary to keep BI solution developers and IT resources close to the "customer" because power users in each department can create and share self-service BI solutions on their own. The benefits range from reduced deployment, maintenance, and support costs to increased security, compliance, consistency, reliability, and scalability. The centralized environment enables efficient utilization of server and storage technologies; consistent monitoring and management processes; and seamless analysis sharing and collaboration within teams and across organizational boundaries, as illustrated in Figure 8.

Figure 8: Centralized BI Collaboration Environment

The basic concepts and techniques for deploying PowerPivot for SharePoint are the same in centralized and decentralized environments. However, the centralized approach provides the following advantages:

Unambiguous security account administration   PowerPivot for SharePoint requires service and administrative accounts for the Analysis Services instance and the PowerPivot service application. To support automatic data refresh in SharePoint, the configuration might also require an unattended data refresh account. All of these accounts must be Windows domain accounts in the same Active Directory forest. Built-in accounts, such as Network Service or Local Service, are not supported. Because all PowerPivot servers in a farm use the same set of service accounts, the administrative overhead for maintaining these system accounts and passwords decreases in a consolidated BI environment.

Streamlined security and compliance management   SharePoint provides the infrastructure for access-based security, authentication, and compliance policies. There is no need to log on to multiple applications. SharePoint identifies each user based on the Windows account, and shows only those resources the user can access.

Resourceful use of database storage   SharePoint stores user data, such as PowerPivot workbooks, in content databases. Factors that influence the database storage requirements include the maximum upload size, which determines the maximum item size, as well as versioning and recycle policies. PowerPivot workbooks typically contain more data than standard Excel files (up to a maximum of 2 GB), and thus typically require more storage capacity than other service applications. In a centralized environment, a high-end storage area network (SAN) solution can provide the required capacity with higher availability levels than direct attached storage (DAS) solutions in departmental farm environments.

Increased availability, performance, and scalability   For SharePoint relational database uses in SAN-based environments, SQL Server supports the deployment of failover clusters to achieve a very high resilience against single-point failures. It is also possible to deploy multiple front-end and application servers for load-balancing and failover redundancy. Having multiple PowerPivot servers provides for high availability, better performance, and scale-out capabilities. These advanced system configurations are usually cost-prohibitive in decentralized environments with small SharePoint farms.

Efficient enterprise search capabilities   SharePoint Server 2010 includes an Enterprise Search Center to locate business documents and PowerPivot workbooks quickly in order to accelerate decision-making based on the latest information. Centralized environments require less administrative overhead to implement and maintain enterprise search.

Reduced operations and development costs   The centralized BI collaboration environment reduces the need to deploy, maintain, and support isolated BI environments. It also helps to coordinate development processes; share workflows and BI effectively across organizational boundaries; eliminate duplicated efforts; and achieve a high level of consistency across all BI solutions.

Focused delivery of training and support   Providing training, technical documentation, and support to both power users and business users is critical to the success of BI initiatives. Users need to understand the advantages of the new environment and the benefits of learning how to use PowerPivot workbooks in Excel and Internet Explorer. The better the training, help system, and technical documentation, the lower the support requirements and operational costs. The BI collaboration environment can be the central location to publish these materials.

Planning a Global BI Collaboration Infrastructure

While the centralized deployment approach provides many benefits and advantages over decentralized topologies, it is important to keep in mind that PowerPivot performance depends on network bandwidth in a variety of scenarios, such as when importing massive quantities of data into a workbook or uploading a workbook into a PowerPivot gallery. The PowerPivot processing performance in a SharePoint farm also depends on the connection speed between PowerPivot servers and database servers. Although data caching mitigates the impact of network issues on PowerPivot performance, it is a good idea to avoid PowerPivot traffic over connections with low bandwidth and high latencies, such as wide area network (WAN) links.

Figure 9 shows a sample BI infrastructure with regionally centralized collaboration environments and data warehouses. This design avoids latencies and other issues that can impact the user experience by placing the servers geographically close to the business users. In many enterprise environments, this hybrid approach between local centralization and global decentralization provides a better performance and user experience than a strictly centralized deployment. Global decentralization effectively distributes the load while providing faster access to the shared, self-service BI solutions in the same region with high network bandwidth.

Figure 9: A Global BI Environment with Regional Collaboration Centers

Note: PowerPivot for SharePoint users do not require high-bandwidth network connections because these users retrieve only HTML-rendered processing results when accessing workbook applications published in a SharePoint farm. Excel Services performs the calculations and analytical processing directly on the SharePoint servers. However, PowerPivot for Excel users do not benefit from server-based processing when uploading or downloading workbook applications.

Implementing a BI Onboarding Process

Establishing a managed BI collaboration environment is a strategic effort that goes beyond hardware and software deployment activities because it must also include the transition of departments and business units to the BI collaboration environment in a consistent and coordinated way. This requires executive support, approved onboarding processes, and clearly defined roles and responsibilities. Among other things, it entails identifying the power users in each department, who will play an important role as early adopters and stakeholders in user acceptance testing prior to the final transitioning steps.

As mentioned earlier in this white paper, PowerPivot integrates with the SharePoint infrastructure for hosting services, which can help to facilitate an agile transitioning of departments and business units. The advantage of this approach is that the IT department can determine PowerPivot performance and database storage requirements in incremental steps during each onboarding cycle. Instead of determining, deploying, and provisioning all required resources upfront, the collaboration environment grows organically according to the number of departments and users. Figure 10 illustrates this agile deployment approach.

Figure 10: An Agile BI Onboarding Process

An agile deployment of a managed BI collaboration environment includes the following characteristic phases:

  1. Core deployment.  In a first step, the IT project team plans and deploys the core environment with a small number of PowerPivot application servers that can handle the anticipated workload of a limited number of users from preselected departments.
  2. Onboarding.  After testing and stabilizing the deployment, the IT team transitions the initially selected departments to the collaboration environment while monitoring system reliability, PowerPivot performance, and database storage consumption. If necessary, the IT team also provisions Web applications and site collections for the departments. The IT team can delegate administrative permissions at the site-collection level so that each individual department can subsequently provision its own site hierarchies and define user permissions.
  3. Stabilizing.  Following the successful transition, the IT department can start the user acceptance phase. During this process, power users must confirm that the PowerPivot environment works as expected. The IT team keeps track of any issues and coordinates the required solution steps, such as adding further application servers and storage resources to ensure scalability, availability, performance, and quality of service.
  4. Onboarding.  Whenever a new department agrees to onboard to the managed BI collaboration environment, the overall deployment cycle begins again with an initial assessment of requirements, proactive deployment of additional resources, and the final transition of the selected department.
  5. Stabilizing.  After each onboarding phase, it is essential to stabilize system performance and track storage consumption. Keep in mind that the workload increases on all PowerPivot servers in the farm with each onboarding cycle, and this affects all Web applications in the farm. It is vital to grow the managed BI collaboration environment in close alignment with the availability and performance needs of all departments.

Note: The managed BI collaboration environment is an ideal place to publish online documentation about the onboarding process. Moreover, it can be useful to publish an onboarding questionnaire to give IT details about departments that intend to come on board, such as the number of power users and business users, primary contacts who will work with IT during the onboarding process, and the desired onboarding timeline. This information helps the IT team estimate onboarding requirements, communicate status, and manage capacities.

Deploy PowerPivot for Excel

Power users are important BI players in the managed BI collaboration environment because they assume the role of publishers, creating and sharing self-service BI applications in each department. Regular users, on the other hand, are consumers, who primarily view existing PowerPivot applications to obtain information and key performance indicators.

Deployment Planning

PowerPivot for Excel deployment planning should include an evaluation of the requirements of power users and business users based on the following assumptions:

Power users.  Need Excel 2010 and PowerPivot for Excel on their workstations to create PowerPivot workbooks and use all of the features of PivotTables and PivotCharts. Power users also benefit from an upgrade of client hardware to the x64 architecture and the installation of a 64-bit Windows operating system because 32-bit workstations are subject to 4 GB memory constraints. Note, however, that PowerPivot workbook files are limited to 2 GB in size, which helps to avoid memory exhaustion even on a 32-bit system. It is not necessary to upgrade a client computer in order to run PowerPivot for Excel.

Power users also play an important role regarding PowerPivot for SharePoint. Power users can:

  • Decide whether to publish workbooks in a PowerPivot Gallery.
  • Determine the security roles specifying who can access the uploaded workbooks and who can modify the workbooks.
  • Specify that a workbook can be rendered only online and that the file can’t be downloaded (view-only permission).
  • Manage data refresh settings, including the refresh schedule, user credentials for the update process, and the portions of the dataset that should be updated.

Business users.  Can work with a Web browser to view published PowerPivot workbooks in SharePoint without modifying PivotTables or PivotCharts. Excel Services renders the workbook. Users can sort, filter, and expand or collapse areas of the PivotTable or PivotChart. Business users can also work with earlier versions of Excel to view PowerPivot workbooks, but earlier versions cannot pivot the data.

Manual Deployment

PowerPivot for Excel is available for download free of charge at www.powerpivot.com. Users with administrative permissions on their client computers can use the following procedure to install PowerPivot for Excel:

  1. Verify that Office 2010 is installed, including Office Shared Tools. The PowerPivot installation will fail if Office Shared Tools is not present on the client computer.
  2. Visit the PowerPivot site and download either the x86 (32-bit) or the x64 (64-bit) version of PowerPivot_for_Excel.msi.
  3. Double-click the PowerPivot_for_Excel.msi file to start the Setup wizard.
  4. On the Welcome screen, click Next.
  5. Accept the license agreement and click Next.
  6. Enter their name, and then click Next.
  7. Click Install, and then click Finish.
  8. Verify the installation by starting Excel, clicking the PowerPivot tab on the ribbon, and then clicking PowerPivot Add-in to open PowerPivot.

Note: Microsoft strongly recommends using the 64-bit versions of Microsoft Office 2010 and PowerPivot for Excel on workstations running a 64-bit Windows operating system.

Automated Deployment

In the first release of SQL Server PowerPivot, Microsoft does not support automated deployments of PowerPivot for Excel. Typically, the options for automated deployment include creating an unattended installation that uses an answer file for configuration options; using Group Policy capabilities to deploy across domains; and slipstreaming the installation package into the main installation routine of Office 2010. There is another option for automated installation: use a software distribution solution such as Microsoft System Center Configuration Manager 2007.

With System Center Configuration Manager 2007, it is possible to push installations of PowerPivot for Excel to client computers. This process is similar to distributing other software with System Center Configuration Manager 2007, as explained in https://go.microsoft.com/fwlink/?LinkId=181775:

  1. Create the deployment package and an associated program for the package.
  2. Advertise the package to the selected target group computers.
  3. Create a new distribution point for the package.

Deploying PowerPivot for SharePoint

While PowerPivot for Excel is available as a separately downloadable .msi file, PowerPivot for SharePoint is directly included in the SQL Server 2008 R2 Enterprise, Developer, and Data Center editions, and is deployed using the SQL Server Setup program. Regarding PowerPivot for SharePoint, SQL Server Setup offers two installation options: New Server and Existing Farm.

Figure 10 highlights the main installation options and core activities in the PowerPivot for SharePoint deployment path. The installation of a “New Server” is recommended for workgroup deployments and lab environments that place SharePoint, Excel Services, Analysis Services, and other PowerPivot server components on the same computer. Administrators and system architects new to PowerPivot should choose this deployment path because the New Standalone Server option automatically configures all services and features.

The Existing Farm option is the better choice for SharePoint administrations that need greater flexibility. Farm administrators can initially configure the SharePoint environment without PowerPivot servers. After the configuration of core database servers, application servers, and front-end servers, the farm is ready for PowerPivot preparation and installation. Among other things, it is a good idea to deploy the SQL Server 2008 R2 Client Libraries for Analysis Services on all SharePoint servers in the farm to ensure consistency in system configurations. Strictly speaking, only SharePoint Central Administration must have the Microsoft ADOMD.NET client library and only application servers running services that access PowerPivot data, such as Excel Calculation Services, require the newest Analysis Services OLE DB Provider, but these selective deployments can create operational difficulties.

Figure 11: PowerPivot for SharePoint Deployment Paths

The first installation of a PowerPivot server in an existing SharePoint farm involves the most deployment and configuration activities. No further configuration steps are required during subsequent server deployments to scale out the farm. Existing Web applications and service applications discover and use new PowerPivot servers automatically. The first server installation requires the following farm configuration tasks to support PowerPivot:

  1. Deploy the PowerPivot Solution Package.   SQL Server adds the solution packages Powerpivot.wsp and Powerpivotwebapp.wsp to the farm configuration, but deploys the Powerpivotwebapp.wsp only to the SharePoint Central Administration web application. Powerpivot.wsp is a solution package that is applied globally. It adds templates, content types, and Web parts to the farm. PowerPivotwebapp.wsp is a solution package to add the PowerPivot feature to SharePoint Web applications that run on a Web front-end server. The Powerpivotwebapp.wsp solution must be deployed manually to each of the SharePoint Web applications that will support PowerPivot data access in the farm.
  2. Create a PowerPivot Service Application.   Web applications require a PowerPivot service application to access the PowerPivot servers in the farm, which provides an endpoint to the PowerPivot system service. It is possible to create multiple PowerPivot service applications and add the corresponding service application connections to different service connection groups. However, make sure that the default service connection group includes a PowerPivot service application. Otherwise, PowerPivot Management Dashboard will not work properly.
  3. Activate Excel Calculation Services and Secure Store Service.   By default, SharePoint 2010 does not enable Excel Services, but PowerPivot for SharePoint requires this feature to support PowerPivot data access in the farm. PowerPivot for SharePoint also requires the Secure Store Service feature to store credentials and the unattended execution account for data refresh. By enabling Secure Store, workbook authors and document owners can access a broader range of data source connection options when scheduling data refresh for their published workbooks.
  4. Configure Automatic Data Refresh.   Workbook users can refresh data on demand in PowerPivot for Excel, but PowerPivot for SharePoint also supports data refresh as a scheduled operation to re-import data from external sources; this runs as an Analysis Services process on an application server in the farm.
  5. Increase the Maximum Upload Size.   Because PowerPivot workbooks can be large, Microsoft recommends increasing the maximum upload size to 2 GB for Web applications with access to PowerPivot service applications.
  6. Enable Usage Data Collection.   PowerPivot for SharePoint uses the SharePoint usage data collection infrastructure to gather information about PowerPivot usage throughout the farm. Although usage data is always part of a SharePoint installation, it must be enabled before it can be used.
  7. Enable PowerPivot Integration for Site Collections.   Features facilitate site customizations and ensure versioning consistency across all front-end servers. In the course of deploying the Powerpivotwebapp.wsp solution to a Web application, SharePoint adds the PowerPivot feature to the corresponding site collections. Features make it easy for site collection administrators to activate or deactivate PowerPivot at the site collection level.

Note: In a multi-server farm, all PowerPivot instances must be the same version. If you applied service packs or updates to other PowerPivot servers that are already in the farm, the new instance you are deploying must be updated to the same version after setup is complete. After installation, the new instance will be unavailable until any necessary updates have been applied.

Recommendations and Best Practices

Developing and deploying self-service BI solutions that deliver relevant answers to users, yet do not burden IT with complex administrative overhead, is a complex organizational and technological challenge. Among other things, it requires establishing a reliable collaboration infrastructure, defining and sanitizing business data, managing change, and sound project management. The following general best practices are useful for implementing PowerPivot:

  1. Have a successful project kickoff.   By the time the implementation project begins, it helps to have some support available, and to very clearly define project details such as roles, responsibilities, timeframes, phases, and so on. With a methodology in place, it is easier to take corrective action and incorporate feedback, and also ensure that the relevant stakeholders are part of the initiative.
  2. Involve the relevant contributors and stakeholders.   Implementing a PowerPivot BI solution requires participation from application developers, information workers, executive and other decision makers, and IT professionals. Although workers can create individual BI applications, the IT organization must design and deploy the environment, provision data sources, and assign permissions to provide the underlying platform. Gathering those requirements and obtaining input from leadership is crucial to project success.
  3. Engage power users and business users.   Managed, self-service BI is about providing business insights to all employees. It is therefore a good idea to include power users and business users even in the earliest stages. Heavy involvement and actual face time is critical to successful PowerPivot engagement.
  4. Focus resource time on key competencies.   With key decisions that need to be made about topology, server sizing, logical design, and rollout phases, it is a best practice to delegate responsibilities according to expertise. By defining roles and responsibilities from the onset, it is straightforward to identify gaps in resources and tasks, and to ensure that people focus on completing tasks for which they are qualified.
  5. Get executive buy-in.    Making a change to the existing BI environment or driving adoption of a new one is easier if the strategic direction of the organization recognizes the importance of self-service BI. Project success for BI often depends not as much on technical completion as on demonstrating the ease of use and business benefits a new platform provides.
  6. Onboard gradually and stabilize.   Because establishing a managed BI collaboration environment is a strategic effort that has the potential to influence all areas of an organization, it is important to drive adoption in stages. Onboarding in phases has the advantage of enabling users to become familiar over time and providing IT enough time to respond to issues and fine-tune configuration settings according to individual department needs.
  7. Use clear documentation and training.   As in any project that implements new systems and processes, it is vital for users to have clear steps for onboarding and usage. Consider establishing a knowledge base for common questions and include training videos and details about common data sources that can be used for many types of BI applications.
  8. Right-size storage and connectivity.   The client and server components of PowerPivot provide fast processing capabilities with features such as the in-memory engine, but that speed and reliability depend on the underlying components performing as expected. The two most common performance issues relate to disk size or speed on the back-end storage subsystem or to connectivity when transferring large amounts of data. It is important to plan for appropriately sized servers and bandwidth for the organization’s needs.
  9. Plan and design with security and scalability in mind.   This is a best practice for any project, and it is vital for a multi-tier solution that contains sensitive business information. Designing with security in mind means using standard security best practices, such as least-privileges, and proactively monitoring and auditing. Designing with scalability in mind means ensuring the possibility of scaling up and out to accommodate additional users or user load.

Conclusion

In classic BI solutions that do not incorporate self-managed capabilities, users rely on IT staff for many functions, such as report definition and data updating. PowerPivot helps to break through these barriers by offering business users the ability to help themselves while providing visibility and oversight to IT. With PowerPivot, users can create BI applications without having advanced technical knowledge by using the familiar Excel interface. The features and capabilities that PowerPivot enables you to realize for BI insight depend on the investments you make. For example, you can implement just the free PowerPivot for Excel add-in right away and plan to complete the BI offering for collaboration and management later through PowerPivot for SharePoint.

Planning for and designing a BI environment that incorporates PowerPivot requires careful attention to many details, and ensuring that user performance and availability expectations are met. The details to consider include the existing SharePoint environment, how PowerPivot for SharePoint is integrated in the overall application server design, storage speed and size, bandwidth, department needs, data source reliability, and leadership expectations. By planning to onboard gradually and stabilizing performance for each new group of users, you can minimize IT support needs. With PowerPivot, insight into business details is not limited to experts, but expanded to all users who can quickly create BI solutions and share their findings with others.

For more information:

https://www.microsoft.com/sqlserver/: SQL Server Web site

https://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter

https://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter 

Did this paper help you? Please give us your feedback. Tell us on a scale of 1 (poor) to 5 (excellent), how would you rate this paper and why have you given it this rating? For example:

  • Are you rating it high due to having good examples, excellent screen shots, clear writing, or another reason?
  • Are you rating it low due to poor examples, fuzzy screen shots, or unclear writing?

This feedback will help us improve the quality of white papers we release.

Send feedback.