• Article

PowerShell Configuration Script (PowerPivot for SharePoint)

This topic is for SharePoint farm or service administrators who deploy or maintain servers through PowerShell script. For instructions on how to use SharePoint 2010 Central Administration to perform these same tasks, see Configuration (PowerPivot for SharePoint).

If you installed SQL Server PowerPivot for SharePoint on an existing SharePoint server that is already configured or already a member of a farm, SQL Server Setup will not create or modify SharePoint services or applications to work with your PowerPivot for SharePoint deployment. To complete the deployment, you must perform the configuration tasks yourself. All post-installation tasks can be done via script. This topic provides a template and instructions for using and running the script.

This topic contains the following sections:

Prerequisites

How to Run the Commands

Deploy the PowerPivot Solution

Activate the PowerPivot Feature

Create a PowerPivot Service Application

Start the Claims to Windows Token Service

Enable and Configure Excel Services

Enable Secure Store Service and Configure Data Refresh

Increase the Maximum Upload Size for a Web Application

Prerequisites

You must be a local administrator and a farm administrator to run the commands in this topic.

How to Run the Commands

The PowerShell commands in this topic are arranged into sections for configuring specific aspects of a PowerPivot for SharePoint deployment. You can run each of the command blocks individually in the SharePoint 2010 Management Shell, or you can place them in .ps1 files.

To run each command individually

  1. In the Microsoft SharePoint 2010 Products program group, right-click SharePoint 2010 Management Shell and select Run as administrator.

  2. Check for sufficient permissions by entering the following command in the shell. The command should return a list of PowerShell commands supported by Excel Services:

    get-help *SPExcel*

To run a .ps1 file

Alternatively, you can combine the scripts into a single .ps1 file and configure all settings in a single operation. If you do this, you must temporarily change the execution policy to run unsigned scripts. The following steps explain how to do this.

  1. Open the SharePoint 2010 Management Console with administrator permissions.

  2. Run the following command to determine the execution policy. Later, when you have completed the PowerPivot for SharePoint deployment, you will reset the execution policy back to this value.

    Get-ExecutionPolicy

  3. Run the following command to temporarily change the execution policy to allow scripts that are unsigned. Type Y to confirm the action.

    Set-ExecutionPolicy Unrestricted

  4. Enter the fully-qualified file name to run the script. For example, if you saved your script to a file named PowerPivotConfig.ps1, you would enter the following line and then press Enter to run the script.

    C:\Scripts-ps1\PowerPivotConfig.ps1

  5. Reset the execution policy to the original value in step 2. Type Y to confirm the action.

    Set-ExecutionPolicy restricted

To copy values from the PowerShell window

Some of the procedures in this topic ask you to copy IDs from the PowerShell window. If you do not already know how to do this use the following instructions to learn how.

  1. Open the SharePoint 2010 Management Console with administrator permissions.

  2. Enter a command that returns a GUID (for example, Get-SPServiceInstance)

  3. Click the PowerShell icon on the top left corner of the window.

  4. Point to Edit, and click Mark.

  5. With the cursor, highlight an ID.

  6. Click the PowerShell icon again, point to Edit, and click Copy. The ID is now stored in the clipboard. You can paste it into other command statements that use the ID as a parameter value.

Deploy PowerPivot Solution

PowerPivot for SharePoint includes one global and one application-level solution that must be deployed before you create a PowerPivot service application. The global solution is deployed by SQL Server Setup. The application-level solution must be deployed after Setup. You must run this script for each SharePoint web application that will support PowerPivot feature integration.

An alternative to this script is to use the instructions in the following topic: Deploy PowerPivot Solutions.

Install-SPSolution -Identity PowerPivotWebApp.wsp -AllWebApplications -GACDeployment

Activate the feature

PowerPivot feature activation is required for each site collection. This script specifies the root site collection of a SharePoint web application named "somesite". Replace "somesite" with the actual name of your web application. Note that if you created additional site collections (for example, a site named PowerPivot), the URL would be http://somesite/PowerPivot.

An alternative to this script is to use the instructions in the following topic: Activate PowerPivot Feature Integration for Site Collections.

Enable-SPFeature -Identity "PowerPivotSite" -URL http://somesite/

Note

You can use the Get-SPFeature command to view a list of all farm, web, and site-scoped features. Running this command shows you the name and ID of both the PowerPivot farm feature and the PowerPivot site collection feature.

Create a PowerPivot Service Application

Creating a PowerPivot service application provides an HTTP endpoint to the PowerPivot System Service. You must have at least one PowerPivot service application in the farm, and it must be a member of the default proxy group.

Note that this script uses the default service application pool as the service identity for the PowerPivot service application. Notice also that this script includes a placeholder for the farm database server. Be sure to replace the string 'Your database server name goes here' with the name of the SQL Server database engine instance that hosts the configuration database for the farm.

Before you run the script, verify that the database server is configured to allow remote TCP/IP connections. You can use the SQL Server Configuration tool to enable TCP/IP if you have not already done so.

An alternative to this script is to use the instructions in the following topic: Create and Configure a PowerPivot Service Application.

New-PowerPivotServiceApplication -ServiceApplicationName "Default PowerPivot Service Application" -DatabaseServerName "your database server name goes here" -DatabaseName DefaultPowerPivotServiceApplicationDB -AddToDefaultProxyGroup

Start the Claims to Windows Token Service

The Claims to Windows Token service must be running on each SharePoint server that has PowerPivot for SharePoint installed.

An alternative to this script is to use the instructions in the following topic: Install PowerPivot for SharePoint on an Existing SharePoint Server.

  1. Run the following command to get the ID of the Claims to Windows Token Service:

    Get-SPServiceInstance

  2. Copy the ID that is returned for the service.

  3. Enter the following command, using the ID to specify the service.

    Start-SPServiceInstance <ID>

  4. Run Get-SPServiceInstance again to verify Claims to Windows Token Service is online.

  5. Repeat on each SharePoint server that has PowerPivot for SharePoint installed.

Enable and Configure Excel Services

Excel Services performs server-side rendering of Excel workbooks that you publish to SharePoint. PowerPivot for SharePoint depends on Excel Services to trigger requests for PowerPivot data and to render PowerPivot data as PivotTables or other data objects in a worksheet.

An alternative to this script is to use the instructions in the following topic: Install PowerPivot for SharePoint on an Existing SharePoint Server.

Gather information and verify prerequisites

  1. Run the following command to check whether Excel Calculation Services is online. If it is not online, start the service using the same approach you used for starting the Claims to Windows Token Service.

    Get-SPServiceInstance

  2. Run the following command to check whether an Excel Services service application is already enabled. If a service application already exists, skip the commands to create it, but run the commands that enable external data access, increase the maximum workbooks size, and turn off data refresh warnings.

    Get-SPExcelServiceApplication

  3. Get the name of the application pool that you will use to create the service application. In the script that follows, you will use the SharePoint Web Services Default application pool. However, you can run the following command to determine whether other application pools are more suitable.

    Get-SPServiceApplicationPool

Create the Service Application and Service Application Proxy

New-SPExcelServiceApplication -name "Excel Services Application" –ApplicationPool "SharePoint Web Services Default"
New-SPExcelServiceApplicationProxy -name "Excel Services Application Proxy" –ServiceApplication "Excel Services Application" -DefaultProxyGroup

Configure the Service Application

This script increases the maximum workbook size, enables external data access, and turns off data refresh warnings.

Set-SPExcelFileLocation -Identity 'http://' -ExcelServiceApplication "Excel Services Application" -ExternalDataAllowed 2 -WorkbookSizeMax 2000 -WarnOnDataRefresh:$false

Add the Service Application to the default service application proxy group

  1. Get the service application proxy ID for the Excel Services application and copy the ID.

    Get-SPServiceApplicationProxy

  2. Add the service application proxy ID to the default proxy group, replacing "serviceAppId" with the actual ID that you copied from the PowerShell window.

    $ProxyGroup = Get-SPServiceApplicationProxygroup -default
Add-SPServiceApplicationProxyGroupMember -Identity $ProxyGroup -Member "serviceAppId"

Enable Secure Store Service and Configure Data Refresh

Secure Store Service is a SharePoint resource that is used to store credentials. PowerPivot for SharePoint uses Secure Store Service to support data refresh operations that are based on stored or referenced credentials.

Important

Running the commands in this section is sufficient for enabling some PowerPivot data refresh options. Specifically, people can enter their own credentials to run the data refresh job or type the SSS ID of a predefined target application that provides the credentials. This script does not create or set the PowerPivot unattended data refresh account. You can either develop that script yourself, or configure the account in Central Administration. For more information, see Configure and Use Stored Credentials for PowerPivot Data Refresh.

Gather information and verify prerequisites

Run the following command to check whether Secure Store Service is online. If it is not online, start the service using the same approach you used for starting the Claims to Windows Token Service.

Get-SPServiceInstance

Create the Service Application

Be sure to replace the placeholder value for the database server.

New-SPSecureStoreServiceapplication -Name "Secure Store Service" -partitionmode:$false -sharing:$false -databaseserver "Your database server" -databasename "SecureStoreServiceAppDB" -ApplicationPool "SharePoint Web Services Default" -auditingEnabled:$true -auditlogmaxsize 30

Create the Service Application Proxy and add it to the default proxy group

  1. Get the service application ID for Secure Store Services and copy the ID.

    Get-SPServiceApplication

  2. Run the following command, replacing the <ID> with the service application ID you copied in the previous step:

    New-SPSecureStoreServiceApplicationProxy -name "Secure Store Service Proxy" -ServiceApplication <ID> -defaultproxygroup

Generate the master key

  1. Set a variable for the master key.

    $newPassPhrase='type a strong password here'

  2. Copy the ID of the Secure Store Service application proxy. You will use it in the next step.

    Get-SPServiceApplicationProxy

  3. Set a variable for the service application proxy.

    $proxy='paste the ID for the secure store service application proxy here'

  4. Run the following commands in sequential order:

    Update-SpSecureStoreMasterKey -ServiceApplicationProxy $proxy -PassPhrase $newPassPhrase
Update-SpSecureStoreApplicationServerKey -ServiceApplicationProxy $proxy -PassPhrase $newPassPhrase

Configure usage and health data collection

This script enables usage and server health data collection that gathers information about how the system is used. It specifies a data collection interval of 5 minutes, which is more frequent than the default value of 30 minutes. Enabling usage data collection provides information that is used in built-in reports, including those in PowerPivot Management Dashboard.

An alternative to this script is to use the instructions in the following topic: Configure Usage Data Collection (PowerPivot for SharePoint).

Set-SPUsageService -UsageLogCutTime 5

Reconfigure Maximum Upload Size for the Default Web Application

By default, SharePoint allows you to upload files up to 50 megabytes in size. This script changes the maximum file upload size for the SharePoint web application to 2047 megabytes. 2047 is the maximum value supported by SharePoint.

Enter each of the following lines in sequential order. The first line gets the web application, the second line sets the property, and the third line updates the property on the server. Be sure to replace the placeholder value of "somesite" with the actual name of your application. You can use Get-SPWebApplication to return a list of applications that are defined in your farm.

An alternative to this script is to use the instructions in the following topic: Configure Maximum File Upload Size (PowerPivot for SharePoint).

$webapplication=Get-SPWebApplication http://somesite/
$webapplication.MaximumFileSize=2047
$webapplication.Update()