Monday, August 9, 2010

Activate PowerPivot Feature Integration for Site Collections

Feature activation at the site collection level makes application pages and templates available to your sites, including configuration pages for scheduled data refresh and application pages for PowerPivot Gallery and Data Feed libraries.
  1. On a SharePoint site, click Site Actions.
    By default, SharePoint web applications are accessed through port 80. This means that you can often access a SharePoint site by entering http:// to open the root site collection.
  2. Click Site Settings.
  3. In Site Collection Administration, click Site collection features.
  4. Scroll down the page until you find PowerPivot Integration Site Collection Feature.
  5. Click Activate.
  6. Repeat for additional site collections by opening each site and clicking Site Actions.
Activate Online Help

PowerPivot for SharePoint includes online help that you can access from pages in your SharePoint site. The online help provides information about PowerPivot application pages, plus links to updated content on the web. The online help is installed but not activated. For instructions, see Activate the SQLPowerPivot Help Collection.
Verify Installation

PowerPivot query processing in the farm occurs when a user or application opens an Excel workbook that contains PowerPivot data. At a minimum, you can check pages on SharePoint sites to verify that PowerPivot features are available. However, to fully verify an installation, you must have a PowerPivot workbook that you can publish to SharePoint and access from a library. For testing purposes, you can publish a sample workbook that already contains PowerPivot data and use it to confirm that SharePoint integration is correctly configured.
To verify PowerPivot integration with a SharePoint site, do the following:
  1. In a browser, open the Web application you created. If you used default values, you can specify http:// in the URL address.
  2. Verify that PowerPivot data access and processing features are available in the application. You can do this by verifying the presence of PowerPivot-provided library templates:
    1. On Site Actions, click More Options...
    2. In Libraries, you should see Data Feed Library and PowerPivot Gallery. These library templates are provided by the PowerPivot feature and will be visible in the Libraries list if the feature is integrated correctly.
To verify PowerPivot data access on the server, do the following:
  1. Upload a PowerPivot workbook to PowerPivot Gallery or any SharePoint library. For more information about how to access sample files, see Roadmap to Creating PowerPivot Workbooks in Excel.
  2. Click on the document to open it from the library.
  3. Click on a slicer or filter the data to start a PowerPivot query. The server will load PowerPivot data in the background and return the results. In the next step, you will connect to the server to verify the data is loaded and cached.
  4. Start SQL Server Management Studio from the Microsoft SQL Server 2008 R2 program group in the Start menu. If this tool is not installed on your server, you can skip to the last step to confirm the presence of cached files.
  5. In Server Type, select Analysis Services.
  6. In Server Name, enter \powerpivot, where  is the name of the computer that has the PowerPivot for SharePoint installation.
  7. Click Connect.
  8. In Object Explorer, click Databases to view the list of PowerPivot data files that are loaded.
  9. On the computer file system, check the following folder to determine whether files are cached to disk. The presence of cached files is further verification that your deployment is operational. To view the file cache, go to the \Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup folder.
Post-Installation Steps

After you verify the installation, finish service configuration by creating a PowerPivot Gallery or tuning individual configuration settings. To make full use of the server components you just installed, you can download PowerPivot for Excel to create and then publish your first PowerPivot workbook.

Create a PowerPivot Gallery

PowerPivot Gallery is a library that includes preview and presentation options for viewing PowerPivot workbooks on a SharePoint site. Using PowerPivot Gallery to publish and view PowerPivot workbooks is recommended for its preview capability. In addition, if you also deployed Reporting Services to the same SharePoint server, a PowerPivot Gallery provides ease of use in creating reports. You can launch Report Builder from within PowerPivot Gallery to base a new report on a published PowerPivot workbook. For more information about creating and using the library, see How to: Create and Customize PowerPivot Gallery and How to: Use PowerPivot Gallery.

Create Additional Trusted Sites in Excel Services

You can add trusted sites in Excel Services to vary permissions and configuration settings on sites that provide Excel workbooks and PowerPivot data. For more information, see How to: Create a trusted location for PowerPivot sites.

Tune Configuration Settings

A PowerPivot service application is created using default properties and values. You can modify configuration settings for individual service applications to change the methodology by which requests are allocated, set server timeouts, change the thresholds for query response report events, or specify how long usage data is retained. For more information about configuration in Central Administration or about using PowerPivot features in SharePoint Web applications, see Configuration (PowerPivot for SharePoint) and Operations (PowerPivot for SharePoint).

Install PowerPivot for Excel and Build a PowerPivot Workbook

After you have the server components installed in a farm, you can create your first Excel 2010 workbook that uses embedded PowerPivot data, and then publish it to a SharePoint library in a Web application. Before you can build Excel workbooks that include PowerPivot data, you must start with an installation of Excel 2010, followed by the PowerPivot for Excel add-in that extends Excel to support PowerPivot data import and enrichment. For instructions on how to install PowerPivot for Excel and create PowerPivot data, see How to: Install PowerPivot for Exceland Roadmap to Creating PowerPivot Workbooks in Excel.

Add Servers or Applications

When you deploy the PowerPivot solution, feature integration is activated at the site collection level for all site collections in the web application. As you create new Web applications over time, you must deploy the powerpivotwebapp solution to each one. For instructions, see How to: Deploy PowerPivot Solutions.
Depending on how you configure the PowerPivot service application, the PowerPivot System Service will be added to the default connection group, making it available to all web applications that use default connections. However, if you configured your Web applications to use custom service application connection lists, you will need to add the PowerPivot service application to each SharePoint web application for which you want to enable PowerPivot data processing. For more information, see How to: Connect a PowerPivot Service Application to a SharePoint Web Application.
Over time, if you determine that additional data storage and processing capability is needed, you can add a second PowerPivot for SharePoint server instance to the farm. The installation process is almost identical to the steps you followed to add the first server, except for requirements in how you specify instance names and service account information. For instructions, see How to: Add PowerPivot Servers to a SharePoint Farm.

1 comment:

  1. Hi Rahul,
    Thanks for detail and clear explanation.
    Issue I am facing is : Under
    Site Collection Administration--->Site collection features...PowerPivot Integration Site Collection Feature is not available/visible.

    1.Sharepoint 2010 already installed (not sure did the person installed powerpivot while SP installation)
    2.I have admin rights.

    Is the anyway to enable this feature without reinstalling SP2010, if powerpivot was not installed during SP 2010 installation.