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.
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.
Deploying PowerPivot for SharePoint
Important, see article about Analysis Services error before installing
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 using SQL CD. 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.
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:
http://www.microsoft.com/sqlserver/: SQL Server Web site
http://technet.microsoft.com/en-us/sqlserver/: SQL Server TechCenter
http://msdn.microsoft.com/en-us/sqlserver/: SQL Server DevCenter