Compartir a través de

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

  • Artículo

 

Applies To: SQL Server 2016 Preview

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:

  • SQL Server BI Scenarios and SharePoint 2013

  • Overview of Installation

In This Section

In addition to the information in this topic, the following related topics are in this section of content.

Deployment Topologies for SQL Server BI Features in SharePoint

Guidance for Using SQL Server BI Features in a SharePoint 2010 Farm

Checklists for Installing BI Features with SharePoint

Install Reporting Services SharePoint Mode

Install Analysis Services in Power Pivot Mode

Power Pivot for SharePoint 2010 Installation

SQL Server BI Scenarios and SharePoint 2013

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

-Install Reporting Services in SharePoint mode.

-Install Reporting Services add-in (rsSharePoint.msi) for SharePoint. For more information, see Install or Uninstall the Reporting Services Add-in for SharePoint

4

All Power Pivot Features

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

-Schedule Data refresh.

-Power Pivot Gallery.

-BISM link file content type.

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

Install or Uninstall the Power Pivot for SharePoint Add-in (SharePoint 2013)

 Download SQL Server 2014 Power Pivot for SharePoint.

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

Overview of Installation

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 Analysis Services in Power Pivot Mode.and Manage Excel Services data model settings (SharePoint Server 2013) (https://technet.microsoft.com/library/jj219780.aspx).

  6. For more information, see Install Analysis Services in Power Pivot Mode.

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.

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 and Install Reporting Services SharePoint Mode for 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 The First Report Server in SharePoint Mode.

Overview of Database-attach Upgrade and SharePoint 2013

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.

Note

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

See Also

Where to find the Reporting Services add-in for SharePoint Products
Supported Combinations of SharePoint and Reporting Services Server and Add-in (SQL Server 2016)
Install or Uninstall the Reporting Services Add-in for SharePoint