Install SQL Server BI Features with SharePoint 2013 (SQL Server 2012 SP1)

SQL Server 2012 Service Pack 1 (SP1) is a version of Microsoft SQL Server 2012 that supports Microsoft SharePoint 2013 Excel Services usage of Excel workbooks containing data models and Reporting Services Power View reports.

Excel Services in SharePoint 2013 includes data model functionality to enable interaction with a PowerPivot workbook in the browser. You do not need to deploy the PowerPivot 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 PowerPivot for SharePoint 2013 add-in enables additional functionality and features in your SharePoint farm. The additional features include PowerPivot Gallery, Schedule Data Refresh, and the PowerPivot Management Dashboard. See the table for additional information.

The basic steps to deploy the PowerPivot for SharePoint 2013 add-in are the following:

1. Verify Excel Services is configured to use an in SharePoint mode. For more information, see Manage Excel Services data model settings (SharePoint Server 2013) (https://technet.microsoft.com/en-us/library/jj219780.aspx).

2. Run spPowerPivot.msi to install the PowerPivot for SharePoint 2013 Configuration tool, SharePoint solution files, and data providers.

3. Run the PowerPivot for SharePoint 2013 Configuration tool to deploy and configure the PowerPivot features in SharePoint.

For information on how to download spPowerPivot.msi, see Microsoft® SQL Server® 2012 SP1 PowerPivot® for Microsoft® SharePoint® (https://www.microsoft.com/en-us/download/details.aspx?id=35577).

Microsoft® SQL Server® 2012 Service Pack 1 (SP1) (https://www.microsoft.com/en-us/download/details.aspx?id=35575).

The following table summarizes the different levels of BI features you can choose to install and configure.

Level	Features	Install or Configure
SharePoint Only	Native Excel Services Features	Excel Services and other services included with SharePoint Server 2013.
SharePoint with Analysis Services in SharePoint Mode	Interactive PowerPivot workbooks in the browser	Install Analysis Services in SharePoint mode. Register Analysis Services Server in Excel Services.
SharePoint with Reporting Services in SharePoint Mode	Power View	Install Reporting Services in SharePoint mode. Install Reporting Services add-in for SharePoint.
All PowerPivot Features	Access to workbooks as a data source from outside the farm. Schedule Data refresh. PowerPivot Gallery. Management Dashboard. BISM link file content type.	Deploy PowerPivot for SharePoint 2013 add-in.

For additional information on enabling Analysis Services features, see Introducing the 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).

In this topic:

• What’s New in SQL Server 2012 SP1 Installation for Business Intelligence

• Known Issues

• Installation Overview

• Overview of database-attach upgrade and SharePoint 2013

• Example Deployment Topologies

What’s New in SQL Server 2012 SP1 Installation for Business Intelligence

• PowerPivot for SharePoint: Analysis Services provides a backend service for Excel Services to load, query, and refresh PowerPivot data models so that users can interact with Excel workbooks that contain data models in the browser. Analysis Services in SharePoint mode is fully independent of and external to SharePoint, although the server running Analysis Services must be on the same network and Active Directory Domains Services (AD DS) forest as the SharePoint farm.

  You install and manage Analysis Services using SQL Server installation media and tools. Choose the setup option PowerPivot for SharePoint. After you install Analysis Services in SharePoint mode, the only additional configuration tasks are to grant the SharePoint services accounts server administrator permissions in Analysis Services and to configure Excel Services to point to the Analysis Services instance.

  For detailed steps on installing a single server, see. Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).

• spPowerpivot.msi: A Windows Installer package available with the SQL Server 2012 SP1 Feature Pack. The installer enhances the PowerPivot for SharePoint experience with more features such as PowerPivot Gallery and Schedule Data Refresh. The .msi deploys Analysis Services client libraries, the PowerPivot for SharePoint 2013 Configuration tool, and copies PowerPivot for SharePoint 2013 installation files to SharePoint servers.

  After you run the .msi, you need to run PowerPivot for SharePoint 2013 Configuration to deploy the solutions and configure the farm. For more information see:

  • Install or Uninstall the PowerPivot for SharePoint Add-in

  • Configure PowerPivot and Deploy Solutions (SQL Server 2012 SP1).

• Reporting Services: The overall Reporting Services installation remains the same with SQL Server 2012 SP1 as it was with SQL Server 2012. There is an updated Reporting Services for SharePoint add-in that supports SharePoint 2013. For more information, see Supported Combinations of SharePoint and Reporting Services Components.

Top

Known Issues

Review the release notes at SQL Server 2012 Service Pack 1 Release Notes (https://go.microsoft.com/fwlink/?LinkID=248389).

Top

Overview of Installation with SharePoint 2013

The following is a summary of the installation steps to deploy SQL Server 2012 SP1 BI Features on a single server.

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 more information, see Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).

3. Configure Excel Services in SharePoint Server 2013 to use the Analysis Services instance you installed in step 2.

   See the section “Configure Basic Analysis Services SharePoint Integration” in Install Analysis Services Server in SharePoint Mode for SharePoint 2013 (SQL Server 2012 SP1).

4. Install Reporting Services in SharePoint mode and the Reporting Services add-in for SharePoint products.

   For more information, see Install Reporting Services SharePoint Mode for SharePoint 2013.

5. 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.

Note

If you want to use both Analysis Services and Reporting Services, run SQL Server Installation Wizard twice because the PowerPivot for SharePoint option does not offer choices on the Setup Role page.

Top

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 PowerPivot 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 PowerPivot 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 PowerPivot 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 PowerPivot service application.

Note

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

• Migrate PowerPivot to SharePoint 2013

• Overview of the upgrade process to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256688).

• Clean up preparations before an upgrade to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256689).

• Upgrade databases from SharePoint 2010 to SharePoint 2013 (https://go.microsoft.com/fwlink/p/?LinkId=256690).

Top

Example Deployment Topologies with SharePoint 2013

The SQL Server setup option PowerPivot for SharePoint has no dependencies on SharePoint. It does not use the SharePoint object model or interfaces to support integration. Therefore, Analysis Services can be installed on any computer running Windows Server 2008 R2 or later version. It can be but does not have to be an application server in a SharePoint farm. One of the configuration steps is to point Excel Services to the server running Analysis Services. For load balancing and fault tolerance, it is recommended to install and register multiple Analysis Services servers running in SharePoint mode.

Reporting Services SharePoint mode requires SharePoint server 2013 and utilizes the SharePoint Service Application architecture.

The following sections illustrate typical deployment topologies:

PowerPivot for SharePoint 2013 Single Server Deployment

A single server deployment is useful for testing purposes but it is not recommended for production deployments.

The following diagram illustrates the components that are part of a single server Analysis Services deployment.

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	The Sharepoint content, configuration, and service application databases.
(4)	An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.

Top

PowerPivot for SharePoint 2013 Two Server Deployment

In the following two-server deployment, the SQL Server Database Engine and Analysis Services in SharePoint mode run on a separate server than SharePoint. In SQL Server 2012 SP1 the Microsoft SQL Server PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) is installed on the SharePoint server.

PowerPivot for SharePoint 2013 extends SharePoint Server 2013 to add server-side data refresh processing, data providers, PowerPivot Gallery, and management support for PowerPivot workbooks and Excel workbooks with advanced data models.

The installer package is available as part of the SQL Server 2012 SP1 feature pack. The feature pack can be downloaded from the Microsoft download center at SQL Server 2012 SP1 Feature Pack (https://go.microsoft.com/fwlink/p/?LinkID=263965).

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
(4)	An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
(5)	The SharePoint content, configuration, and service application databases.

Top

PowerPivot for SharePoint 2013 Three Server Deployment

In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. In SQL Server 2012 SP1 the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
(4)	An Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
(5)	The SharePoint content, configuration, and service application databases.

Top

PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Single Server Deployment

A single server deployment is useful for testing purposes but it is not recommended for production deployments.

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	Reporting Services service application.
(4)	Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack.
(5)	The SharePoint content, configuration, and service application databases.
(6)	Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.

Top

PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Two Server Deployment

In the following two-server deployment, the SQL Server Database Engine and Analysis Services server running in SharePoint mode run on a separate server from SharePoint. In SQL Server 2012 SP1 the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	Reporting Services service application.
(4)	Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack.
(5)	RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
(6)	Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
(7)	The SharePoint content, configuration, and service application databases.

Top

PowerPivot for SharePoint 2013 and Reporting Services in SharePoint mode Three Server Deployment

In the following three-server deployment, the SQL Server Database Engine, Analysis Services server running in SharePoint mode, and SharePoint, each run on a separate server. In SQL Server 2012 SP1 the PowerPivot for SharePoint 2013 installer package (spPowerPivot.msi) must be installed on the SharePoint server.

(1)	Excel Service Application. The service application is created as part of the SharePoint installation.
(2)	PowerPivot Service Application. Default name is Default PowerPivot Service Application.
(3)	Reporting Services service application.
(4)	Install the reporting services add-in for SharePoint from either the SQL Server 2012 SP1 installation media or the SQL Server 2012 SP1 feature pack.
(5)	RUN the spPowerPivot.msi to install data providers, the PowerPivot configuration tool, PowerPivot Gallery, and schedule data refresh.
(6)	Analysis Services Server in SharePoint Mode. Configure the Excel Services Application Data Model Settings to use this server.
(7)	The SharePoint content, configuration, and service application databases.

Top

Submit feedback and contact information through Microsoft SQL Server Connect (https://connect.microsoft.com/SQLServer/Feedback).