Was this page helpful?
Your feedback about this content is important. Let us know what you think.
Additional feedback?
1500 characters remaining
Export (0) Print
Expand All

Install SQL Server BI Features with SharePoint (Power Pivot and Reporting Services)

Analysis Services and Reporting Services can be integrated with a Microsoft SharePoint farm to enable Business Intelligence (BI) features in SharePoint. The features include Power Pivot for SharePoint, Power View, and Reporting Services. Power Pivot for SharePoint is used for Power Pivot data access in a SharePoint farm. Power Pivot for SharePoint is the data engine for workbooks created in Power Pivot for Excel and accessed from a SharePoint library. Once you save a Power Pivot workbook to SharePoint, you can use it as a data source for Power View reports.

Some of the installation and configuration steps required for SharePoint 2010 are different than the steps required for SharePoint 2013. Some of the topics in this section apply to both versions of SharePoint.

Applies to: SharePoint 2013 | SharePoint 2010

 

note For the current release notes, see SQL Server 2016 Release Notes.

In this topic:

This section summarizes the different levels of BI features you can choose to install and configure.

Excel Services in SharePoint 2013 includes data model functionality to enable interaction with a Power Pivot workbook in the browser. For basic data model functionality you do not need to deploy the Power Pivot for SharePoint 2013 add-in into the farm. You only need to install an Analysis Services server in SharePoint mode and register the server within the Excel Services Data Model settings.

Deploying the Power Pivot for SharePoint 2013 add-in enables additional functionality and features in your SharePoint farm. The additional features include Power Pivot Gallery, Schedule Data Refresh, and the Power Pivot Management Dashboard. See the table for additional information.

Level

Features

Install or Configure

1

SharePoint Only

Native Excel Services Features

Excel Services and other services included with SharePoint Server 2013.

2

SharePoint with Analysis Services in SharePoint Mode

Interactive Power Pivot workbooks in the browser

  • Install Analysis Services in SharePoint mode.

  • Register Analysis Services Server in Excel Services.

3

SharePoint with Reporting Services in SharePoint Mode

Power View

4

All Power Pivot Features

  • Access to workbooks as a data source from outside the farm.

  • Schedule Data refresh.

  • Power Pivot Gallery.

  • Management Dashboard.

  • BISM link file content type.

Deploy Power Pivot for SharePoint 2013 add-in (spPowerPivot.msi). For more information, see the following:

For additional information on enabling Analysis Services features, see The SQL Server BI Light-Up Story for SharePoint 2013 (http://blogs.msdn.com/b/analysisservices/archive/2012/07/27/introducing-the-bi-light-up-story-for-sharepoint-2013.aspx).

Top Arrow icon used with Back to Top link

If you want to use both Power Pivot for SharePoint and Reporting Services, run SQL Server Installation Wizard twice. Reporting Services and Power Pivot are separate choices on the Setup Role page of the SQL Server setup wizard.

Power Pivot for SharePoint supports both SharePoint 2010 and SharePoint 2013; however a different architecture and installation process is used depending on the version of SharePoint.

The following is a summary of the installation steps to deploy SQL Server 2016 BI Features on a single server:

Power Pivot for SharePoint 2013

For SharePoint 2013, the Power Pivot for SharePoint installation can be run on a server that does not have a SharePoint product installed. The Power Pivot architecture used for SharePoint 2013, runs outside the SharePoint farm and can either be installed on a server that also contains a SharePoint installation or it can be installed a server that does NOT contains a SharePoint installation.

  1. Install SharePoint Server 2013 and enable Excel Services.

  2. Install Analysis Services in SharePoint mode, and grant the SharePoint farm and services accounts server administrator rights in Analysis Services.

    For both versions of SharePoint, the Power Pivot installation process starts by selecting the setup role of SQL Server Power Pivot for SharePoint in the SQL Server Installation wizard or use a SQL Server command prompt installation.

      

    Setup Role

      

  3. For SharePoint 2013, you can extend the Power Pivot features and experience. Download and run spPowerPivot.msi to add server-side data refresh processing, collaboration, and management support for Power Pivot workbook. For more information, see Microsoft SQL Server 2014 Power Pivot for Microsoft® SharePoint.

    Run the Power Pivot for SharePoint 2013 installation package spPowerPivot.msi on each server in the SharePoint farm to ensure the correct version of the data providers are installed.

  4. To configure Power Pivot for SharePoint 2013, use Power Pivot for SharePoint 2013 Configuration tool.

    The SQL Server installation wizard installs two Power Pivot Configurations tools. One of the configuration tools supports SharePoint 2013 and the other tool supports SharePoint 2010.

    two powerpivot configuratoin tools
  5. Configure Excel Services in SharePoint Server 2013 to use the Analysis Services instance. For more information, see the section “Configure Basic Analysis Services SharePoint Integration” in Install Power Pivot for SharePoint 2013.and Manage Excel Services data model settings (SharePoint Server 2013) (http://technet.microsoft.com/library/jj219780.aspx).

  6. For more information, see Install Power Pivot for SharePoint 2013.

Top Arrow icon used with Back to Top link

Power Pivot for SharePoint 2010

For SharePoint 2010, it is required that the Power Pivot for SharePoint Installation is run on a server that already has SharePoint 2010 installed or will be installed. The Power Pivot architecture for SharePoint 2010 runs inside the farm and requires SharePoint on the server that Power Pivot for SharePoint is installed.

  1. Install Analysis Services in SharePoint mode, and grant the SharePoint farm and services accounts server administrator rights in Analysis Services.

    A SharePoint 2010 deployment does not support spPowerPivot.msi, and the .msi is not required with SharePoint 2010.

    For both versions of SharePoint, the Power Pivot installation process starts by selecting the setup role of SQL Server Power Pivot for SharePoint in the SQL Server Installation wizard or use a SQL Server command prompt installation.

  2. The SQL Server installation wizard installs two Power Pivot Configurations tools. One of the configuration tools supports SharePoint 2013 and the other tool supports SharePoint 2010.

    To configure Power Pivot for SharePoint 2010, use the Power Pivot Configuration Tool .

  3. For more information, see Power Pivot for SharePoint 2010 Installation.

Top Arrow icon used with Back to Top link

Reporting Services for SharePoint 2010 and 2013

  1. The installation for Reporting Services in SharePoint mode is unchanged from the previous release.

    The Reporting Services installation steps for SharePoint 2010 and SharePoint 2013 are very similar. Important notes regarding SharePoint versions are:

    1. Install Reporting Services in SharePoint mode. Install Reporting Services SharePoint Mode (SharePoint 2013) and Install Reporting Services SharePoint Mode in a Single Server Farm (SharePoint 2010).

    2. Install the Reporting Services add-in for SharePoint products (rsSharePoint.msi). See Install or Uninstall the Reporting Services Add-in for SharePoint. For the current version of the Reporting Services add-in for SharePoint, see Where to find the Reporting Services add-in for SharePoint Products.

    3. Configure the Reporting Services SharePoint service and at least one Reporting Services service application. For more information, see the section “Create a Reporting Services Service Application” in Install Reporting Services SharePoint Mode for SharePoint 2013.

Arrow icon used with Back to Top linkTop

SharePoint 2013 does not support in-place upgrade. However database-attach upgrade is supported.

If you have an existing Power Pivot installation integrated with SharePoint 2010, you cannot in-place upgrade the SharePoint server. However, you can complete the following steps as part of a SharePoint database-attach upgrade:

  1. Install a new SharePoint Server 2013 farm.

  2. Complete a SharePoint database-attach upgrade, and migrate your Power Pivot related content databases to the SharePoint 2013 farm.

  3. Install an instance of SQL Server Analysis Services in SharePoint mode and grant the SharePoint farm and services accounts, server administrator rights in Analysis Services.

  4. Install the Power Pivot for SharePoint 2013 installation package spPowerPivot.msi on each server in the SharePoint farm.

  5. In SharePoint 2013 Central Administration, configure Excel Services to use the Analysis Services server running in SharePoint mode created in step 3.

    To migrate refresh schedules, configure the Power Pivot service application.

NoteNote

For more information on Power Pivot and SharePoint database-attach upgrade, see the following:

Arrow icon used with Back to Top linkTop

Community Additions

ADD
Show:
© 2015 Microsoft