PowerPivot Installation for SharePoint 2010

The Business Intelligence ContinuumSQL PowerPivot adds some impressive SharePoint 2010 business intelligence capabilities for large multi-sourced datasets.  When you build these BI applications within Microsoft Excel 2010, you can now publish them to SharePoint 2010 for collaboration and visibility throughout your organization.

PowerPivot workbooks are Microsoft Excel files that contain large, related datasets that you build in a separate PowerPivot window in Excel, and use with PivotTables and PivotCharts in a worksheet. The “window” is provided through the PowerPivot for Excel add-in that you can download from the Web.

What can SQL Server PowerPivot Do?

SQL Server PowerPivot for SharePoint Example
According to Microsoft, SQL Server PowerPivot for SharePoint extends SharePoint 2010 and Excel Services to add server-side processing, collaboration, and document management support for the PowerPivot workbooks that you publish to SharePoint.

You use the “window” to import data from a wide range of external data sources, including corporate relational or multidimensional databases, public data feeds, SQL Reporting Services reports, or MS Office documents. Once you import the data, you can build relationships between disparate data so that you can work with the data as a whole.

In addition to the separate work area, the SQL PowerPivot for Excel add-in also provides a local data processor that performs rapid calculations on large amounts of compressed data. Datasets that you import into the PowerPivot window can exceed the one million row limit in Excel worksheets as long as the overall file size does not exceed 2GB.

Pre-requisites & Assumptions for SQL Server PowerPivot Installation

  1. A configured SharePoint 2010 Server Standard or Enterprise licensed farm with a dedicated application server
    1. A hosted SharePoint 2010 Server “Treo” plan (or above) server farm from Fpweb.net would be the minimum recommendation for this BI scenario
  2. Licensing and installation media for SQL Server 2008 R2 Enterprise, Developer, or Datacenter edition
  3. You are a local administrator on the SharePoint 2010 application server
  4. The application server has the SharePoint bits installed and is joined to the farm
  5. You are a SharePoint farm administrator
  6. SQL PowerPivot for SharePoint can be installed on any server in the farm as long as it is not a dedicated web server
  7. For multi-server environments that have dedicated server for only running the Central Admin web application or Excel services roles\services, you have to install the Analysis Services OLE DB Provider
  8. PowerPivot caches the workbooks on disk, so be sure to have plenty of disk space available

PowerPivot Server Installation Procedure

  1. Click New Installation or Add Features Launch the SQL Server 2008 R2 Enterprise Installation
  2. Log into the SharePoint 2010 application server as a local admin.
  3. Launch the SQL Server 2008 R2 Enterprise install (pictured »)
  4. Click New Installation or Add Features
  5. For the installation type, select New installation or Add Features and click Next
  6. Click Install on the Setup Support Files screen
  7. Click Next
  8. Enter your SQL 2008 R2 Enterprise, Developer, or Datacenter product Key and click Next
  9. Click Next
  10. Click the I accept check box and click Next
  11. Select SQL Server PowerPivot for SharePoint as the role
  12. Select Existing Server from the drop down and Click Next (pictured below)
    1. Select Existing Server Selecting New Server here has some other implications which I’ll explain below in the Gotchas section
  13. Click Next on the Feature Selection screen
  14. Click Next again
  15. On the Instance configuration screen accept the default “PowerPivot” ID and click Next
  16. Assuming you have plenty of disk space, click Next on the disk space requirements screen
  17. Enter in a Domain service account to run SSAS, click Next
    1. The installation will block the use of a built in machine account
  18. Add your current user account and any other domain accounts that may administer SSAS, click Next
  19. Click Next through the verification screens and then Install
  20. Click Close after the installation is successfully completed

Some “Gotchas”

  • During the installation if you select deploy PowerPivot to a “New Server”  vs. “Existing Server”,  a new SharePoint farm is setup and the current application server will host all of the farm’s databases.  This is not what we wanted when we were initially testing this.  We tried to run the SharePoint products and technologies configuration wizard to remove the server from the farm and relocate the databases and encountered a nasty bug.
    • This bug is also encountered if you try and uninstall PowerPivot.  Here is a describing the issue.  Essentially, a required assembly gets removed from the Global Assembly Cache and not having this prohibits running the configuration wizard again.  In our case we had to completely rebuild the server.
  • You will also need to set up Excel Services to render the published PowerPivot workbooks.

References









2011-04-07T15:58:42+00:00 April 7th, 2011|

7 Comments

  1. Mohd April 9, 2012 at 4:41 am - Reply

    Hi Andy. I have encounter errors when in stage “Installation Rules” which is i told that my current sharepoint was sharepoint foundation 2010 server.
    But the thing is, my current sharepoint is sharepoint server 2010. How to solve this problem. Hope you can help.
    Thanks.

    Mohd.

  2. […] support for the PowerPivot workbooks that you publish to … … Read more here: SQL Server PowerPivot for SharePoint 2010 Installation Guide … ← Getting access to the best Sharepoint […]

  3. […] SQL Server PowerPivot for SharePoint 2010 Installation Guide … […]

  4. […] all over the place in recent Microsoft products like SharePoint Server 2010, Excel 2010 (using SQL PowerPivot), Windows Azure Storage, SQL Server 2008 R2 and […]

  5. […] reporting and analysis capabilities for large datasets stored within PowerPivot Excel Workbooks. Installing SQL PowerPivot for SharePoint 2010 extends the native Excel Services features of SharePoint. The combination of SharePoint Server and […]

  6. Moss (2010) Comunipedia September 3, 2015 at 12:28 am - Reply

    […] Read Sources […]

Leave A Comment