Configure PowerPivot for SharePoint for a BI test environment

 

Applies to: SharePoint Server 2010 Enterprise

This article is part of the Configuring a BI infrastructure: Hands-on labs series. To complete the steps in this article, you must first have completed:

• Create a baseline environment for a BI test environment

• Configure Excel Services for a BI test environment

Microsoft SQL Server PowerPivot for Microsoft SharePoint supports query processing and data refresh for published Excel workbooks that contain PowerPivot data. This article describes how to configure PowerPivot for SharePoint 2010 in a virtual environment that already has SharePoint Server 2010 and SQL Server 2008 R2 installed and configured.

Configuring PowerPivot for SharePoint consists of the following steps:

• Create a managed account for SQL Server Analysis Services

• Deploy PowerPivot for SharePoint

• Configure a PowerPivot service application

• Configure data refresh for PowerPivot for SharePoint

• Create a Secure Store service application for PowerPivot for SharePoint

• Verify that PowerPivot for SharePoint is configured correctly

• Testing data refresh

Create a managed account for SQL Server Analysis Services

To configure PowerPivot for SharePoint, you must have a service account for the SQL Server Analysis Services instance that is installed with PowerPivot for SharePoint. Use the following procedure to create an Active Directory account for Analysis Services.

To create an Active Directory account for Analysis Services

1. Log on to Contoso-DC as Contoso\Administrator.

2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

3. Expand the contoso.local node.

4. Right-click Users, click New, and then click User.

5. In the Full name and User logon name boxes, type PowerPivotSSAS.

6. Click Next.

7. Type and confirm a password for the account.

8. Clear the User must change password at next logon check box.

9. Select the Password never expires check box.

10. Click Next, and then click Finish.

Once the account is created, the next step is to deploy PowerPivot for SharePoint.

Deploy PowerPivot for SharePoint

Deploying PowerPivot for SharePoint consists of the following steps:

• Installing PowerPivot for SharePoint on a SharePoint Server farm

• Deploying the PowerPivot solution package to a SharePoint Web Application

• Activating PowerPivot feature integration for the Business Intelligence Center

The first step is to install PowerPivot for SharePoint on the Contoso-AppSrv computer.

To install PowerPivot for SharePoint 2010 on an existing SharePoint Server 2010 farm

1. Attach the SQL Server 2008 R2 DVD to the DVD drive for the Contoso-AppSrv virtual machine.

2. Log on to Contoso-AppSrv by using the Contoso\FarmAdmin account.

3. On the SQL Server DVD, double-click setup.exe.

   The SQL Server Installation Center opens.

4. Click Installation, and then click New installation or add features to an existing installation.

   The SQL Server setup window opens.

5. On the Setup Support Rules page, click OK.

6. On the Product Key page, type the product key for your version of SQL Server, and then click Next.

7. On the License Terms page, select I accept the license terms, and then click Next.

8. On the Setup Support Files page, click Install.

9. On the Setup Support Files page, click Next.

10. On the Setup Role page, select SQL Server PowerPivot for SharePoint, and in the Add PowerPivot for SharePoint to list, select Existing Farm. Then click Next.

11. On the Feature Selection page, click Next.

12. On the Installation Rules page, click Next.

13. On the Instance Configuration page, in the Instance ID box, select the default PowerPivot text, and type ContosoAppSrv, and then click Next.

14. On the Disk Space Requirements page, click Next.

15. On the Server Configuration page, in the SQL Server Analysis Services row, specify the Contoso\PowerPivotSSAS account and password. Then click Next.

16. On the Analysis Services Configuration, click Add Current User, and then click OK.

17. On the Analysis Services Configuration page, click Next.

18. On the Error Reporting page, click Next.

19. On the Installation Configuration Rules page, click Next.

20. On the Ready to Install page, click Install.

    The installation process runs.

    Tip

    It might take several minutes for the installation process to finish.

21. After the installation process is complete, you must restart the computer. On the Complete page, click Close. Then restart the Contoso-AppSrv computer.

The next step is to deploy a PowerPivot solution to the SharePoint Web application that you created when you set up the baseline environment.

To deploy the PowerPivot solution package

1. Log into the Contoso-AppSrv computer by using the Contoso\FarmAdmin account.

2. Click the Start button, select All Programs, select Microsoft SharePoint 2010 Products, and then select SharePoint 2010 Central Administration.

3. In SharePoint 2010 Central Administration, click System Settings, and then click Manage farm solutions.

   Note

   There are two separate solution packages visible: powerpivotfarm.wsp and powerpivotwebapp.wsp. The first solution, powerpivotfarm.wsp, is deployed automatically when you install the first PowerPivot for SharePoint instance. You do not have to deploy this again. The second solution, powerpivotwebapp.wsp, is deployed automatically for Central Administration. However, you must deploy this solution manually for each SharePoint Web application that supports PowerPivot for SharePoint data access.
   In this case, we have one Web application. In a production environment, there are likely more than one Web application.

4. Click powerpivotwebapp.wsp, and then click Deploy Solution.

5. In the Deploy To? section, use the Choose a Web application to deploy this solution list to select http://contoso-appsrv/.

6. Click OK.

The next step is to configure the Business Intelligence Center to work with PowerPivot data. To do this, follow these steps.

To enable PowerPivot feature integration with the Business Intelligence Center

1. Browse to the Business Intelligence Center (http://contoso-appsrv/sites/bicenter).

2. Click Site Actions, and then click Site Settings.

3. In the Site Collection Administration section, click Site Collection Features.

4. Next to PowerPivot Feature Integration for Site Collections, click Activate.

5. Close the browser window.

Now that PowerPivot for SharePoint is installed, the next step is to configure a PowerPivot service application.

Configure a PowerPivot service application

Configuring PowerPivot for SharePoint consists of the following steps:

• Starting the Claims to Windows Token Service

• Creating an Active Directory account for the application pool under which the PowerPivot service application will run

• Registering that account as a managed account in SharePoint Server 2010

• Creating a PowerPivot service application

• Configuring Excel Services to support PowerPivot workbooks

Begin by starting the Claims to Windows Token Service, which is used to enable access to data that is stored on another computer. In this case, we will be accessing data that is stored on the Contoso-SQL computer.

To start the Claims to Windows Token Service

1. Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

2. In Central Administration, click System Settings, and then click Manage services on server.

3. Start the Claims to Windows Token Service.

4. Verify that both SQL Server Analysis Services and SQL Server PowerPivot System Service are started.

The next step is to create an Active Directory account. This account will be used for the PowerPivot for SharePoint Service application.

To create an Active Directory account for PowerPivot for SharePoint

1. Log on to Contoso-DC as Contoso\Administrator.

2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

3. Expand the contoso.local node.

4. Right-click Users, click New, and then click User.

5. In the Full name and User logon name boxes, type PowerPivotAppPool.

6. Click Next.

7. Type and confirm a password for the account.

8. Clear the User must change password at next logon check box.

9. Select the Password never expires check box.

10. Click Next, and then click Finish.

The next step is to register the managed account in SharePoint Server.

To register the PowerPivotAppPool account in SharePoint Server

1. Log on to Contoso-AppSrv by using the Contoso\FarmAdmin account.

2. Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

3. On the SharePoint Central Administration Web site home page, in the left navigation, click Security.

4. On the Security page, under General Security, click Configure managed accounts.

5. On the Managed Accounts page, click Register Managed Account.

6. In the User name box, type Contoso\PowerPivotAppPool.

7. In the Password box, type the password for the Contoso\PowerPivotAppPool account.

8. Click OK.

The next step is to create a PowerPivot service application.

To create a PowerPivot service application

1. In Central Administration, click Application Management, and then click Manage service applications.

2. In the Service Applications ribbon, click New, and then click SQL Server PowerPivot Service Application.

   The Create New PowerPivot Service Application dialog box appears.

3. In the Name box, type PowerPivotServiceApplication1.

4. In the Application Pool section, select Create a new application pool, and in the Application pool name box, type PowerPivotServAppPool.

5. Use the Configurable list to select Contoso\PowerPivotAppPool.

6. In the Database Server list, specify Contoso-SQL, and keep the default database name.

7. In the Database authentication section, keep the default setting, Windows Authentication.

8. In the Default section, select the check box for Add the proxy for this PowerPivot service application to the default proxy group, and then click OK.

9. After your service application is successfully created, click OK in the confirmation page.

The service application appears in the Contoso farm service application list together with the other service applications. You might have to refresh the page to see it.

The next step is to configure Excel Services to support PowerPivot workbooks. To do this, you increase the maximum file size for Excel Services and edit external data settings.

To configure Excel Services to support PowerPivot workbooks

1. In Central Administration, click Application Management, and then click Manage service applications.

2. Click Excel Services.

3. Click Trusted File Locations.

4. Click http://contoso-appsrv/sites/bicenter/documents.

5. In the Workbook Properties section, in the Maximum Workbook Size box, type 50.

   Increasing the workbook size makes it possible to work with more data in PowerPivot for Excel.

6. In the External Data section, under Allow External Data, select Trusted data connection libraries and embedded.

7. In the External Data section, clear the Refresh warning enabled check box.

8. Click OK.

Now that you have installed and configured PowerPivot for SharePoint, your next step is to configure data refresh for PowerPivot for SharePoint.

Configure data refresh for PowerPivot for SharePoint

Configuring data refresh for PowerPivot data in Excel workbooks consists of the following steps:

• Creating an Active Directory account for the PowerPivot unattended data refresh account

• Granting Read permissions to access the data during data refresh

The first step is to create an Active Directory account.

To create an Active Directory account for PowerPivot data refresh

1. Log on to Contoso-DC by using the Contoso\Administrator account.

2. Click Start, click Administrative Tools, and then click Active Directory Users and Computers.

3. Expand the contoso.local node.

4. Right-click Users, click New, and then click New User.

5. In the Full name and User logon name boxes, type PowerPivotUnattended, and then click Next.

6. Type and confirm a password for the account.

7. Clear the User must change password at next logon check box.

8. Select the Password never expires check box.

9. Click Next, and then click Finish.

Once the Active Directory account is created, the next step is to grant Read permissions to that account in SQL Server.

To grant Read permissions to the PowerPivot unattended data refresh account in SQL Server

1. Log on to the Contoso-SQL computer by using the Contoso\SQLAdmin account.

2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

3. In the Connect to Server dialog box, specify the following settings:

   • In the Server type list, select Database Engine.

   • In the Server name box, type Contoso-SQL.

   Then click Connect.

4. In the Object Explorer pane, expand Security.

5. Right-click Logins, and then click New Login.

6. In the Login name box, type Contoso\PowerPivotUnattended.

7. In the Select a Page section, click User Mapping.

8. Select the Map check box for the ContosoRetailDW database, and then, under Database role membership for: ContosoRetailDW, select the db_datareader check box.

9. Click OK.

10. Close SQL Server Management Studio, and then log off the Contoso-SQL computer.

The next step is to create a Secure Store target application.

Configure Secure Store service for PowerPivot for SharePoint

Configuring Secure Store Service for PowerPivot for SharePoint consists of the following steps:

• Creating a PowerPivot target application for the Secure Store Service

• Specifying the target application identity for the PowerPivot unattended data refresh account

• Granting Contribute permissions to the PowerPivot unattended data refresh account in SharePoint Server

The first step is to create a PowerPivot target application for Secure Store Service.

To create a PowerPivot target application for Secure Store Service

1. Log on to Contoso-AppSrv by using the Contoso\FarmAdmin account.

2. Click Start, click All Programs, click Microsoft SharePoint 2010 Products, and then click SharePoint 2010 Central Administration.

3. On the Central Administration home page, in the Application Management section, click Manage service applications.

4. In the list of service applications, click Secure Store Service.

5. In the ribbon, on the Edit tab, click New.

6. On the Target Application Settings page, specify the following settings:

   • In the Target Application ID box, type PowerPivotDataRefresh.

   • In the Display Name box, type PowerPivotDataRefresh.

   • In the Contact E-mail box, type an e-mail address. For our example, we use farmadmin@contoso.local.

   • In the Target Application Type list, select Group.

   • In the Target Application Page URL section, select None.

   Then click Next.

7. On the Specify the credential fields for your Secure Store Target Application page, keep the default values, and then click Next.

8. On the Create New Secure Store Service Application page, specify the following settings

   • In the Target Application Administrators box, type Contoso\FarmAdmin.

   • In the Members box, type the name of the application pool identity for the PowerPivot service application. For our example, we use PowerPivotAppPool.

   Then click OK.

   The list of Secure Store Service target applications is displayed. Do not close this window.

The next step is to set credentials for the target application.

To set credentials for the PowerPivot target application

1. Begin with the list of Secure Store Service target applications open. Select the check box next to PowerPivotDataRefresh.

2. In the ribbon, on the Edit tab, in the Credentials section, click Set.

3. On the Set Credentials for Secure Store Target Application (Group) page, specify the following settings:

   • In the Windows User Name box, type Contoso\PowerPivotUnattended.

   • In the Windows Password and the Confirm Windows Password boxes, type the password for the Contoso\PowerPivotUnattended account.

   Then click OK.

   The list of Secure Store Service target applications is displayed. Do not close this window.

The next step is to specify a PowerPivot unattended data refresh account.

To specify a target application identity for the PowerPivot unattended data refresh account

1. In Central Administration, in the Application Management section, click Manage service applications.

2. In the list of service applications, click the name of the PowerPivot service application. For our example, we click PowerPivotServiceApplication1.

   The PowerPivot Management Dashboard opens.

3. In the Actions section, click Configure service application settings.

   The PowerPivot Settings page opens.

4. In the Data Refresh section, in the PowerPivot Unattended Data Refresh Account box, type the name of the target application identity. For our example, we use PowerPivotDataRefresh.

   Then click OK.

5. Close Central Administration, but remain logged in to the Contoso\FarmAdmin account.

The next step is to grant Contribute permissions to the PowerPivotUnattended account in SharePoint Server. This enables users to open PowerPivot workbooks from a SharePoint library and save it after refreshing data.

To grant Contribute permissions to the PowerPivot unattended data refresh account in SharePoint Server

1. Open a Web browser and navigate to the Contoso team site. For our example, the Web site address (URL) is http://contoso-appsrv.

2. Click BI Center to open the Business Intelligence Center.

3. Click Site Actions, and then click Site Permissions.

4. In the ribbon, on the Edit tab, click Grant Permissions.

5. In the Grant Permissions dialog box, specify the following settings:

   • In the Users/Groups box, type the name of the PowerPivot unattended data refresh account. For our example we use Contoso\PowerPivotUnattended.

   • In the Grant Permissions section, select Add users to a SharePoint group (recommended), and then select Business Intelligence Center Members [Contribute].

   Then click OK.

6. Close the Web browser, and then log off the Contoso-AppSrv computer.

Now that PowerPivot for SharePoint is deployed, the next step is to verify that PowerPivot for SharePoint is configured correctly.

Verify that PowerPivot for SharePoint is configured correctly

Verifying that PowerPivot for SharePoint is configured correctly consists of the following steps:

• Installing the PowerPivot add-in for Excel on a client computer

• Creating and publishing a PowerPivot workbook to SharePoint Server

Before you can install the PowerPivot add-in for Excel, you must verify that Microsoft .NET Framework 3.5.1 is installed on the client computer.

To install the Microsoft .NET Framework 3.5.1

1. Log on to Contoso-Client by using the Contoso\Administrator account.

2. Click Start, and then click Control Panel.

3. Click Programs.

4. In the Programs and Features section, click Turn Windows features on or off.

   The Windows Features dialog box appears.

5. Select the Microsoft .NET Framework 3.5.1 check box, and then click OK.

6. After Microsoft .NET Framework is installed, close the Control Panel. Do not log off the computer.

The next step is to download and install PowerPivot for Excel.

To install PowerPivot for Excel

1. Open a web browser.

2. In the address line, type https://go.microsoft.com/fwlink/?LinkID=224183.

   The Microsoft Download Center page for Microsoft SQL Server 2008 R2 - PowerPivot for Microsoft Excel 2010 – RTM opens.

3. Click the Download button for PowerPivot for Excel.

4. If a dialog box appears that prompts you to run or save the application, click Run.

   The PowerPivot for Excel Setup dialog box appears.

5. On the welcome page, click Next.

6. On the License Agreement page, select I accept the terms in the license agreement, and then click Next.

7. On the Registration Information page, in the Name box, type your name, and then click Next.

8. On the Ready to Install the Program page, click Install.

9. On the Completing the PowerPivot for Excel installation page, click Finish.

10. Close the web browser. Log off the computer.

The next step is to open Microsoft Excel and create a PowerPivot workbook.

To create a PowerPivot workbook

1. Log on to the Contoso-Client computer by using the Contoso\Susan.Burk account.

2. Click Start, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010.

3. If Excel opens and displays a Microsoft Office Customization Installer dialog box, click Install.

   PowerPivot for Excel adds a PowerPivot tab to the Excel workbook.

4. In Excel, on the PowerPivot tab, in the Launch group, click PowerPivot Window.

   The PowerPivot window opens.

5. On the Home tab, click From Database, and then click From SQL Server.

   The Table Import Wizard dialog box appears.

6. On the Connect to a Microsoft SQL Server Database page, in the Server name box, type Contoso-SQL.

7. Use the Database name list to select ContosoRetailDW, and then click Test Connection.

8. If your connection is successful, click Next.

9. On the Choose How to Import the Data page, select the Select from a list of tables and views to choose the data to import option, and then click Next.

10. In the Select Tables and Views page, select the check box next to the DimPromotion table, and then click Finish.

    The Importing page indicates that 28 rows of data were transferred.

11. Click Close to close the Table Importing Wizard.

    The data is displayed in the PowerPivot window.

12. On the Home tab, in the Reports group, click PivotTable down arrow, and then click PivotChart.

    The Create PivotChart dialog box appears.

13. Select Existing Worksheet, and then click OK.

    An empty chart opens in Excel.

14. In the PowerPivot Field List pane, select the check boxes for PromotionDescription and DiscountPercent.

    The chart updates to display data.

15. From the PowerPivot Field List pane, drag StartDate to the Slicers Horizontal box.

    A box that contains slicers is added to the page, over the PivotChart report.

    Do not close the workbook.

The next step is to publish the workbook to SharePoint Server.

To publish the workbook

1. In Excel, click the File tab, and then click Save & Send.

2. Click Save to SharePoint.

3. Under Locations, click Browse for a location, and then click Save As.

   The Save As dialog box appears.

4. In the location box at the top of the window, type http://contoso-appsrv/sites/BICenter/Documents.

   Note

   It might take some time for the Save As dialog box to refresh and show the site content.

5. In the File name box, type ContosoPromotions.xlsx.

6. Select the Open with Excel in the browser check box, and then click Save.

   A browser window opens and displays a cached version of the ContosoPromotions workbook.

7. In the browser window, click a slicer, such as one that has an August date. Notice that the word “school” is misspelled as “scholl.” This is an error in the underlying database that we will correct, and then you can see how data refresh works.

   Close the browser window.

The next step is to configure data refresh settings for the PowerPivot workbook.

To configure data refresh settings for the PowerPivot workbook

1. Open a web browser and navigate to the Business Intelligence Center Documents library. For example, we use http://contoso-appsrv/sites/bicenter/documents.

2. Point to the ContosoPromotions file, click the down arrow that appears, and then click Manage PowerPivot Data Refresh.

3. On the Manage Data Refresh page, select the Enable check box, and then click OK.

You have successfully installed and configured PowerPivot for SharePoint.

To see how the slicers work, click different slicers. The PivotChart report is updated to display information for that particular Start Date. To apply more than one slicer, hold down the Ctrl key, click the slicers that you want to use, and then release the Ctrl key and the mouse.

The next step is to verify that data refresh is working correctly.

Test data refresh

To confirm that data refresh works for PowerPivot workbooks, we follow these steps:

• Refresh the data currently displayed in the browser on the Contoso-Client computer

• Change a value in the ContosoRetailDW database

• Refresh the data displayed in the browser on Contoso-Client again and confirm that the new information is displayed

First, we refresh the existing data. Use the following procedure to refresh the data in the browser.

To refresh the ContosoPromotions workbook in a browser window

1. Begin with the ContosoPromotions workbook open in a browser window.

   In the browser, on the Data drop-down list, click Refresh All Connections.

   Note

   If a warning message appears, click OK.

If PowerPivot for SharePoint is configured correctly, the data will refresh without error, although we will not see any changes in data values at this point. The next step is to change a value in the ContosoRetailDW database and confirm that the changes appear in the workbook.

To update data in the ContosoRetailDW database

1. Log on to Contoso-SQL by using the Contoso\SQLAdmin account.

2. Click Start, click All Programs, click Microsoft SQL Server 2008 R2, and then click SQL Server Management Studio.

3. On the Connect to Server dialog box, select Database Engine for the Server type, and then click Connect.

4. In the toolbar, click New Query.

5. Copy the following query to the query window:

   USE [ContosoRetailDW]
   GO
   UPDATE dbo.DimPromotion set dbo.DimPromotion.PromotionName = 'European Back-to-School Promotion' where dbo.DimPromotion.PromotionName = 'European Back-to-Scholl Promotion'
   GO
   UPDATE dbo.DimPromotion set dbo.DimPromotion.PromotionDescription = 'European Back-to-School Promotion' where dbo.DimPromotion.PromotionDescription = 'European Back-to-Scholl Promotion'
   GO
   

6. In the toolbar, click Execute.

The next step is to return to the Contoso-Client virtual machine and refresh ContosoPromotions.xlsx in the browser window.

To refresh the ContosoPromotions workbook

1. Log on to Contoso-Client computer by using the Contoso\Susan.Burk account.

2. Open a Web browser, navigate to the Business Intelligence Center Documents library, and open the ContosoPromotions.xlsx file.

   For example, we use the Web site address http://contoso-appsrv/sites/bicenter/documents/contosopromotions.xlsx.

   The workbook opens in a browser window.

3. In the browser, on the Data drop-down list, click Refresh All Connections.

   Note

   If a warning message appears, click OK.

4. In the slicer section, click an August date.

   Notice that the report now displays the correct spelling of “school.”

See Also

Concepts

Configuring a BI infrastructure: Hands-on labs

Other Resources

PowerPivot for SharePoint (https://go.microsoft.com/fwlink/p/?LinkId=226555)