• Article

How to: Install PowerPivot for SharePoint from the Command Prompt

You can run Setup from the command line to install SQL Server PowerPivot for SharePoint. You must include the /ROLE parameter in your command and exclude the /FEATURES parameter. Depending on how you specify the /ROLE parameter, SQL Server Setup will either install PowerPivot for SharePoint in an existing SharePoint 2010 farm or as a new server that will be optimized for PowerPivot deployment. You must have the Microsoft SharePoint 2010 installation media to create a new server.

This topic contains the following sections:

Prerequisites

/ROLE based installation options

Existing SharePoint farm installation

New SharePoint server and Analysis Services installation

Verify Installation

Next Steps: Install SQL Server PowerPivot for Excel and build a PowerPivot data source

Prerequisites

Installation requirements vary depending on whether you install PowerPivot for SharePoint on a new or existing farm.

General Requirements

You must have Windows Server 2008 (64-bit).

You must have .NET Framework 3.5 SP1. This version of the .NET Framework is available through Windows Update.

You must have SQL Server 2008 R2 Enterprise, Developer, Evaluation, or Data Center edition.

You must be a local administrator to install SQL Server.

You should know in advance how you want to specify the service accounts. For more information about account requirements, see Plan a PowerPivot Deployment in a SharePoint Farm.

SPI_AS_NewFarm Requirements

You must have an installed but un-configured deployment of Microsoft SharePoint 2010 that includes Excel Services.

SPI_AS_ExistingFarm Requirements

You must have an operational farm or standalone server of Microsoft SharePoint 2010. The edition must include Excel Services.

You must be a farm administrator to add PowerPivot for SharePoint to the farm. If the farm administrator account is not a member of the local Administrator group, add it to Administrators so that you can install PowerPivot for SharePoint. You can remove the account from the Administrator group after Setup is finished.

If the farm consists of multiple servers, you must run SQL Server 2008 R2 Setup on an application server that is already joined to the farm.

/ROLE based installation options

For PowerPivot for SharePoint deployments, the /ROLE parameter determines how Setup checks for prerequisites and what to include in the current installation. Valid values for SharePoint integration include:

  • SPI_AS_ExistingFarm

  • SPI_AS_NewFarm

Both roles install application, configuration, and deployment files that enable a PowerPivot for SharePoint to run in a SharePoint farm. Specifying either role will cause Setup to check for hardware and software requirements necessary for SharePoint integration.

SPI_AS_ExistingFarm checks for a local installation of SharePoint 2010. It uses existing farm connectivity on the local server to join PowerPivot for SharePoint to the farm. It also deploys two solution packages and enables the feature for the farm.

SPI_AS_NewFarm checks for an un-configured SharePoint server, installs a Database Engine instance, installs PowerPivot for SharePoint, configures the SQL Server components, configures the farm by creating a configuration database and Central Administration web application, creates a default web application and site collection, and then deploys the PowerPivot solution packages in the farm and enables PowerPivot for the Web application. Because it is a new farm, configuration is optimized for self-service business intelligence scenarios. Specifically, services that are not used for large scale data analytics are turned off.

If you specify SPI_AS_NewFarm, you must enter parameter values used to configure the farm. These include /farmaccount, /farmaccountpassword, /passphrase, and /farmadminport. For more information about these commands, see Setup Command Reference (PowerPivot for SharePoint).

Existing SharePoint farm installation

If you want to install PowerPivot for SharePoint on a computer that already has SharePoint installed and configured, you can perform the installation as a command line operation.

  1. Copy the following command into a text editor:

    Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /ROLE=SPI_AS_ExistingFarm /INSTANCENAME=PowerPivot /INDICATEPROGRESS
/ASSVCACCOUNT=<DomainName\UserName> /ASSVCPASSWORD=<StrongPassword> /ASSYSADMINACCOUNTS=<DomainName\UserName>

    The /q parameter runs Setup in quiet mode, which suppresses the user interface.

    The /IAcceptSQLServerLicenseTerms is required when the /q or /qs parameter is specified for un-attended installations.

    The /action parameter instructs Setup to perform an installation.

    The /role parameter instructs Setup to install the Analysis Services program and configuration files required for SharePoint integration. This role also detects and uses the existing farm connectivity information to access the SharePoint configuration database. This parameter is required. Use it instead of the /features parameter to specify the components to install.

    The /instancename parameter specifies 'PowerPivot' as a named instance. This value is hard-coded and cannot be changed. It is specified in the command for educational purposes so that you know how the service is installed.

    The /indicateprogress parameter allows you to monitor progress in the command prompt window.

  2. The PID parameter is omitted from the command, which causes the Evaluation edition to be installed. If you want to install the Enterprise edition, add the PID to your Setup command and provide a valid product key.

    /PID=<product key for an Enterprise installation>

  3. Replace the placeholders for <domain\username> and <StrongPassword>with valid user accounts and passwords.

    The /assvaccount and /assvcpassword parameters are used to configure the Analysis Services service instance on the application server. Replace these placeholders with valid account information.

    The /assysadminaccounts parameter must be set to the identity of the user who is running SQL Server Setup. You must specify at least one system administrator. Note that SQL Server Setup no long grants automatic sysadmin permissions to members of the built-in Administrators group.

  4. Remove line breaks.

  5. Select the entire command and then click Copy on the Edit menu.

  6. Open an administrator command prompt. To do this, click Start, right-click the command prompt, and select Run as administrator.

  7. Navigate to the drive or shared folder that contains the SQL Server 2008 R2 installation media.

  8. Paste the revised command into the command line. To do this, click the icon in the top left corner of the command prompt window, point to Edit, and then click Paste.

  9. Press Enter to run the command. Wait for setup to complete. You can monitor Setup's progress in the command prompt window.

  10. To verify installation, check the summary.txt file at \Program Files\SQL Server\100\Setup Bootstrap\Log. Final result should say "Passed" if the server installed without errors.

  11. Configure the server. At a minimum, you must create a service application and enable the feature for each site collection. For more information, see Configuration (PowerPivot for SharePoint).

New SharePoint server and Analysis Services installation

If you have installation media for both SharePoint 2010 and SQL Server 2008 R2, you can run the setup programs in sequence to create either a new SharePoint server or the first server in a multi-server farm that includes PowerPivot. When server installation is complete, you will have an operational SharePoint server that can host large scale PowerPivot data in Excel workbooks.

Install SharePoint 2010

  • Start with a new, un-configured installation of SharePoint 2010. This is a new installation for which you have not yet run the psconfig.exe program. If you already configured the farm, skip this section and go to Existing SharePoint farm installation.

    You can run SharePoint server installation from the command line. You can also configure operating system features and roles via the command line. For a complete reference to Windows Server 2008 command line configuration, see Server Manager Technical Overview Appendix.

Install SQL Server Database Engine and PowerPivot for SharePoint

  1. Copy the following command into a text editor:

    Setup.exe /q /IAcceptSQLServerLicenseTerms /ACTION=install /ROLE=SPI_AS_NewFarm /INSTANCENAME=PowerPivot /INDICATEPROGRESS
/SQLSVCACCOUNT=<DomainName\UserName> /SQLSVCPASSWORD=<StrongPassword> /SQLSYSADMINACCOUNTS=<DomainName\UserName> 
/AGTSVCACCOUNT=<DomainName\UserName> /AGTSVCPASSWORD=<StrongPassword> 
/ASSVCACCOUNT=<DomainName\UserName> /ASSVCPASSWORD=<StrongPassword> /ASSYSADMINACCOUNTS=<DomainName\UserName> 
/FARMACCOUNT=<DomainName\UserName> /FARMPASSWORD=<StrongPassword> /PASSPHRASE=<StrongPassword> /FARMADMINPORT=<availablePort>

    The /q parameter runs Setup in quiet mode, which suppresses the user interface.

    The /IAcceptSQLServerLicenseTerms is required when the /q or /qs parameter is specified for un-attended installations.

    The /action parameter instructs Setup to perform an installation.

    The /role parameter instructs Setup to install the Analysis Services program and configuration files required for SharePoint integration. This role also detects and uses the existing farm connectivity information to access the SharePoint configuration database. This parameter is required. Use it instead of the /features parameter to specify the components to install.

    The /instancename parameter specifies 'PowerPivot' as a named instance. This value is hard-coded and cannot be changed. It is specified in the command for educational purposes so that you know how the service is installed.

    The /indicateprogress parameter allows you to monitor progress in the command prompt window.

  2. The PID parameter is omitted from the command, which causes the Evaluation edition to be installed. If you want to install the Enterprise edition, add the PID to your Setup command and provide a valid product key.

    /PID=<product key for an Enterprise installation>

  3. Replace the placeholders for <domain\username> and <StrongPassword>with valid user accounts and passwords for running SQL Server services.

    1. /sqlsvcaccount, /sqlsvcpassword, /agtsvcaccount, and /agtsvcpassword are used to configure the database server for the farm.

    2. The /assvaccount and /assvcpassword are used to configure the Analysis Services service instance on the application server. Replace these placeholders with valid account information.

    3. The /assysadminaccounts parameter must be set to the identity of the person who is running SQL Server Setup. You must specify at least one system administrator. Note that SQL Server Setup no long grants automatic sysadmin permissions to members of the built-in Administrators group.

  4. Replace the placeholders for <domain\username> and <StrongPassword> for /farmaccount and /farmpassword with a valid user account and password for accessing the database server. Initially, this account will be used to provision essential services used in the farm. It is also used by the SharePoint server to connect to its configuration database. It must be a domain account (it cannot be a local Windows account, nor can it be a built-in account). For installation purposes, the account must be a member of the Administrators group on the local computer.

  5. Replace the placeholders for <StrongPassword> for /passphrase with a password that includes a combination of upper and lower case letters, numbers, and symbols. This phrase will be used by farm administrators to add new application servers and Web front end servers to the farm.

  6. Replace <AvailablePort> for /farmadminport with an unused port number in the range of 1024 through 49151. Alternatively, remove the parameter from the command to allow setup to choose a random port that is not in use. To determine an available port, type the following command at command prompt to return a list of TCP ports that are being used:netstat –a –n -p tcp

    Review the Microsoft Support article, Information about TCP/IP port assignments, to read about TCP port assignments and the differences between Well Known Ports (0 through 1023), Registered Ports (1024 through 49151), and Dynamic or Private Ports (49152 through 65535).

  7. Remove line breaks.

  8. Select the entire command and then click Copy on the Edit menu.

  9. Open an administrator command prompt. To do this, click Start, right-click the command prompt, and select Run as administrator.

  10. Navigate to the drive or shared folder that you are using to access the SQL Server installation media.

  11. Paste the revised command into the command line. To do this, click the icon in the top left corner of the command prompt window, point to Edit, and then click Paste.

  12. Press Enter to run the command. Wait for setup to complete. You can monitor the progress of Setup in the command prompt window.

  13. To verify installation, check the summary.txt file at \Program Files\SQL Server\100\Setup Bootstrap\Log. Final result should say "Passed" if the server installed without errors.

  14. For further verification, open the SQL Server Configuration Manager to confirm the services you installed are configured and in a running state. To do this, click Start, click All Programs, click Microsoft SQL Server, click Configuration Tools, and then click SQL Server Configuration Manager. View the SQL Server Services page to verify that SQL Server Browser and SQL Server are running. You should also see SQL Server Agent installed in Manual start mode, in a stopped state.

After Setup is finished, you will be a farm administrator in the SharePoint farm, a system administrator for the Database Engine, and a site collection administrator for the default SharePoint Web application. For instructions on how to change administrators for your deployment or delegate administration to others, see SharePoint product documentation.

Verify Installation

At a minimum, you can check pages in Central Administration and on SharePoint sites to verify that PowerPivot server components and features are available. However, to fully verify an installation, you must have a PowerPivot workbook that you can publish to SharePoint and access from a library. For testing purposes, you can publish a sample workbook that already contains PowerPivot data and use it to confirm that SharePoint integration is correctly configured.

To verify PowerPivot integration with Central Administration, do the following:

  1. In SharePoint Central Administration, on the Home page, in System Settings, click Manage farm features.

  2. Verify that PowerPivot Integration Feature is Active.

  3. On the Home page, in System Settings, click Manage services on server.

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

  5. On the Home page, in Application Management, click Manage service applications.

  6. Click PowerPivot Service Application to open PowerPivot Management Dashboard for this application. Or, select PowerPivot Service Application and click Properties to view the configuration settings. You can modify these settings later if you encounter connection timeout errors. For more information about these settings, see Create and Configure a PowerPivot Service Application.

To verify PowerPivot integration with a SharePoint site, do the following:

  1. In a browser, open the Web application you created. If you used default values, you can specify http://<your computer name> in the URL address.

  2. Verify that PowerPivot data access and processing features are available in the application. You can do this by verifying the presence of PowerPivot-provided library templates:

    1. On Site Actions, click More Options...

    2. In Libraries, you should see Data Feed Library and PowerPivot Gallery. These library templates are provided by the PowerPivot feature and will be visible in the Libraries list if the feature is integrated correctly.

To verify PowerPivot data access on the server, do the following:

  1. Upload a PowerPivot workbook to PowerPivot Gallery or any SharePoint library. For more information about how to access sample files, see Roadmap to Creating PowerPivot Workbooks in Excel.

  2. Click on the document to open it from the library.

  3. Click on a slicer or pivot the data. The server will load PowerPivot data in the background. In the next step, you will connect to the server to verify the data is loaded and cached.

  4. Start SQL Server Management Studio from the Microsoft SQL Server 2008 R2 program group in the Start menu. If this tool is not installed on your server, you can skip to the last step to confirm the presence of cached files.

  5. In Server Type, select Analysis Services.

  6. In Server Name, enter <server-name>\powerpivot, where <server-name> is the name of the computer that has the PowerPivot for SharePoint installation.

  7. Click Connect.

  8. In Object Explorer, click Databases to view the list of PowerPivot data files that are loaded.

  9. On the computer file system, check the following folder to determine whether files are cached to disk. The presence of cached files is further verification that your deployment is operational. To view the file cache, go to the \Program Files\Microsoft SQL Server\MSAS10_50.POWERPIVOT\OLAP\Backup folder.

Next Steps: Install SQL Server PowerPivot for Excel and build a PowerPivot data source

After you have the server components installed in a farm, you must have an installation of Excel 2010 with the PowerPivot client application to create or view a PowerPivot data source.

For instructions on how to install the add-in and create data sources, see Install PowerPivot for Excel (SQL Server Books Online) and Roadmap to Creating PowerPivot Workbooks in Excel.