Deployment Checklist: Reporting Services, Power View, and PowerPivot for SharePoint

  • Article

Use the following checklist to install these BI features in the same SharePoint farm: PowerPivot for SharePoint, Report Builder, and Power View. Although this checklist recommends a specific installation order, in practice you can install these features in almost any order. This checklist assumes installation of the following products or features:

  1. SharePoint Server 2010 with Service Pack 1 (SP1)

  2. SQL Server 2012 Database Engine

  3. SQL Server 2012 Reporting Services and Reporting Services Add-in

  4. SQL Server 2012 PowerPivot for SharePoint

After you install these features, you will be able to do the following.

  • Access the PowerPivot workbooks that you created in PowerPivot for Excel from SharePoint sites.

  • Build interactive Power View reports based on PowerPivot workbooks in SharePoint.

  • Create Report Builder reports when you launch Report Builder in SharePoint.

Note

PowerPivot for SharePoint requires that you install SharePoint 2010 Service Pack 1 (SP1) on the SharePoint farm. If you are installing the software for evaluation purposes, consider starting with a clean server so that you can avoid the overhead of a farm upgrade step. Upgrading a farm is impactful to SharePoint operations and typically requires careful planning. If your goal is to install PowerPivot for SharePoint as quickly as possible, follow this approach: install SharePoint 2010, install SharePoint 2010 SP1, then configure the farm in a later step. Upgrade is avoided because the farm is not yet configured when you install SharePoint 2010 SP1.

In this checklist, the farm configuration step is assumed during PowerPivot for SharePoint configuration, using the PowerPivot Configuration Tool. Alternatively, you can use the SharePoint Product Configuration wizard if you prefer that approach. Both approaches result in an operational farm that supports PowerPivot for SharePoint.

Prerequisites

You must be a local administrator to run SQL Server Setup.

SharePoint Server 2010 enterprise edition is required for PowerPivot for SharePoint. You can also use the evaluation enterprise edition.

SharePoint Server 2010 SP1 must be installed. Without it, you cannot configure the farm to use SQL Server 2012 features.

The computer must be joined to a domain.

You must have one or more domain user accounts to provision the services. You will need domain user accounts for the following services: SharePoint web services and administrative services, Reporting Services, Analysis Services, Excel Services, Secure Store Services, and PowerPivot System Service. Domain accounts are required by the managed accounts feature in SharePoint. The Database Engine can be provisioned using a virtual account, but all other services should run as a domain user.

The PowerPivot instance name must be available. You cannot have an existing PowerPivot named instance on the computer on which you are installing PowerPivot for SharePoint.

If you are installing PowerPivot for SharePoint on an existing farm, you must have one or more SharePoint web applications that are configured for classic mode authentication. PowerPivot data access will only work if the web application supports classic mode authentication. For more information about classic mode requirements, see PowerPivot Authentication and Authorization.

Review the following additional topics to understand system and version requirements:

Steps

The following steps assume that an administrator is installing and configuring the server. The Setup user in SharePoint is also a farm administrator and often the primary site administrator for the default site collection. If you are dividing the following steps among several people, additional permissions might be required in order for the following steps to work.

Step

Link

Run the SharePoint 2010 Products Preparation Tool

You must have the SharePoint 2010 installation media. The preparation tool is PrerequisiteInstaller.exe on the installation media.

Install SharePoint Server 2010 enterprise or enterprise evaluation edition.

When installing SharePoint, you can choose to configure the farm later by not running the SharePoint 2010 Product Configuration Wizard after Setup is finished. Waiting to configure the farm will allow you to use a SQL Server 2012 Database Engine instance, which is installed in a later step, as the farm’s database server. To configure the farm, you will use the PowerPivot Configuration Tool. It includes actions for provisioning the farm if the farm is not yet configured.

Install SharePoint Server 2010 SP1.

Download SP1 from https://support.microsoft.com/kb/2460045.

Run SQL Server 2012 Setup to install the Database Engine and PowerPivot for SharePoint.

Install PowerPivot for SharePoint

Step 1 explains how to install PowerPivot for SharePoint. In this step, be sure to click the checkbox on the Setup Role page that adds the Database Engine to the role. Doing so adds the Database Engine to your installation so that you can use it as the farm’s database server when you configure the farm in the next step. However, if the farm is already configured, you can skip this step.

Step 2 asks you to configure the server. For this step, choose the PowerPivot Configuration tool. Although several approaches are available, using the configuration tool is the most efficient approach for a standalone installation.

If SharePoint 2010 is installed but not configured, the tool pre-selects actions that will create the farm, a default web application, and a root site collection. Be sure to leave these options selected so that the farm will be created. If you already configured the farm, the tool will omit these actions, and offer just the actions that are necessary for configuring PowerPivot for SharePoint.

Step 3 instructs you to install the SQL Server 2008 R2 version of the Analysis Services OLE DB Provider. This step is important for supporting versions of a workbook that were created in the 2008 R2 version of PowerPivot for Excel.

Verify the farm is operational.

First, start Central Administration and confirm that it is available. Next, open the team site by entering https://localhost. You should see a SharePoint team site.

Verify that PowerPivot for SharePoint is operational.

Verify a PowerPivot for SharePoint Installation

This task confirms PowerPivot data access using a sample workbook that you upload.

Run SQL Server 2012 Setup to install and configure Reporting Services and the Reporting Services Add-in.

Install Reporting Services SharePoint Mode as a Single Server Farm

Optionally, while installing Reporting Services, you can add an additional Analysis Services instance to the Setup feature tree if you want a second resource for hosting tabular data. The additional Analysis Services instance would be used to host tabular model databases that you create in SQL Server Data Tools (SSDT). Tabular databases are a valid data source for Power View reports.

Install Analysis Services in Tabular Mode

Verify that Reporting Services is operational.

Verify a Reporting Services Installation

(Site Administrators) Configure SharePoint permissions.

Contribute permissions are required to add, edit, or delete items in SharePoint libraries. View permission level is sufficient for read-only access to reports and PowerPivot workbooks that present embedded data.

PowerPivot workbooks that are accessed as external data sources (where the workbook URL is a connection string in another workbook or report) require Read permissions, which is higher than View permissions.

BI semantic model connections also required Read permissions. You might need to create new permission levels or SharePoint groups to get the correct permissions in place.

(Site Administrators) Extend document libraries

Extend document libraries to use BI content types: BI semantic model connections, Reporting Services Shared Data Sources, Report Builder reports:

  1. Enable content type management. In Shared Documents or another document library, in the Library tab, click Library Settings. Under General Settings click Advanced settings. In Content Types, select Yes to allow management of content types, and then click OK.

  2. Select the BI content types. In the Library tab, click Library Settings. Under Content Types, click Add from existing site content types. From the Business Intelligence content type group, add BI Semantic Model Connection File and Report Data Source. Optionally, you can also add other Reporting Services content types, such as Report Model, to enable additional report building scenarios.

For more information, see Add a BI Semantic Model Connection Content Type to a Library (PowerPivot for SharePoint) and Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode).

(Site Administrators) Create data connection files that are used to launch Power View.

You must create a BI semantic model connection (.bism) or a Reporting Services shared data source (.rsds) as a data source for Power View. After you create a data connection file, you can launch Power View using the data connection as its data source.

Create a BI Semantic Model Connection to a PowerPivot Workbook

Create a BI Semantic Model Connection to a Tabular Model Database

Create a Shared Data Source for a Data Model (SSRS)

Note: Power View is available because you installed the SQL Server 2012 version of Reporting Services and configured the server as a shared service. If you installed Reporting Services and configured it for the SQL Server 2008 level of integration, Power View is not available.

See Also

Other Resources

Features Supported by the Editions of SQL Server 2012