Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide

Posted by on May 31, 2012 in SharePoint, SharePoint 2010

Microsoft SQL Server PowerPivot for SharePoint extends SharePoint 2010 and Excel Services out of the box capabilities  to provide you with the ability to add server-side processing for PowerPivot Excel Workbooks that are residing in SharePoint.  As I have been doing some PowerPivot installations for SharePoint 2010 in recent times, I thought it would be a good idea to prepare this step by step guide, helping my readers with the process of getting it up and running smoothly.

In summary, PowerPivot allows you to store massive amounts of data which can then be displayed as a powerful data mashup.  By turning your workbooks into shared applications on SharePoint 2010, you gain the collaborative and document management  features that are provided out of the box such as versioning and workflows.

The Installation

We begin by installing SQL PowerPivot for SharePoint on our Application Server or dedicated PowerPivot Server with SharePoint running.

Launch the SQL Server Installation Centre and Select Installation on the left navigation pane and then click on New Installation or add features to an existing installation.

clip image002 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Enter the product key at the Product Key screen and click Next.

Accept the License Terms and then click Next.

Click Install to install the Setup Support Files.

The setup support rules are then run.  At this stage you should rectify any errors if present.

clip image004 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

You will now be greeted with the Setup Role Screen identical to the below.  Select SQL Server PowerPivot for SharePoint and select Add PowerPivot for SharePoint to Existing Farm.

clip image006 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

The next screen will automatically select the Analysis Services for SharePoint Integration feature.

clip image008 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

The installation rules are run.  If you receive any failures, you will need to address these before you can continue.

clip image010 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

You will then be greeted with the Instance Configuration screen in which you will accept the default named instance and Instance ID.

clip image012 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

The wizard will then check the Disk Space Requirements and provide you with a green tick to continue.

Click Next.

Here we will use a dedicated domain account for SQL Server Analysis Services.  (Please note that the installation wizard will not accept any built in machine accounts.  This could be your existing SharePoint SQL account that may already be setup in your environment or a dedicated SharePoint SQL Server Analysis Services Account.  This account will be a member of the SQLServerMSASUser$<server>$PowerPivot security group and the WSS_WPG security groups on the local computer.  This will occur automatically.

clip image014 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

The Analysis Services Configuration screen will then appear in which you will specify and add your Analysis Services administrators.  I would add the current user as a minimum and your SQL Server Analysis Services Account. Click on Data Directories and make any necessary drive changes in there.  Note that Analysis Services administrators will have unrestricted access to Analysis Services.

clip image016 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

The Error Reporting screen appears.  Click Next.

The Installation Configuration Rules screen appears.  Address any Errors before you proceed.

clip image018 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next.

Review the Summary and click on Install.

The installation will now proceed.

You may receive a warning about restarting your computer at the completion.  Please restart your computer to complete the installation process.

In order to check whether the installation was successful, venture into SharePoint Central Administration > System Settings > Manage services on server.  The SQL Server Analysis Services and SQL Server PowerPivot System Service should be set to started.

clip image020 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Deploying the PowerPivot Solution Package

Now that we have confirmed that our PowerPivot services are up and running in Central Administration, we will now proceed to deploy the PowerPivot Solution Package.

Launch Central Administration and navigate to System Settings > Farm Management > Manage farm solutions.

You will notice there are two power pivot Solutions listed as below.

clip image022 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

You will notice that the powerpivotfarm.wsp is already Globally deployed.  This is automatically deployed when you install the first PowerPivot for SharePoint instance in your SharePoint farm.  You will not need to deploy this ever again.  The powerpivotwebapp.wsp on the other hand is only deployed to SharePoint Central Administration Web Application and you must manually deploy it to each Web Application that will require PowerPivot data access.  In order to do so;

Click on powerpivotwebapp.wsp.

clip image024 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click on Deploy Solution.

clip image026 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Specify your time and select your SharePoint Web Application and then click OK.

If you need to deploy the solution to other SharePoint Web Applications, repeat the above process each time.

We now need to ensure that the necessary pre-requisite services are started.  These are Excel Calculation Services, Secure Store Service and Claims to Windows token service.

Launch Central Administration and navigate to System Settings > Manage Services on server.  In my instance, the Claims to Windows Token Service and Secure Store Service were stopped.  Click on Start and ensure their status is set to started.

Creating the PowerPivot Service Application

We can now proceed and create our first PowerPivot Service Application.  Launch Central Administration and navigate to Application Management > Service Applications > Manage service applications.

Click on New and select SQL Server PowerPivot Service Application.

Enter the details or the Service Application Pool as follows;

Name: PowerPivotServiceApplication1

Application Pool: Create new application pool; SharePoint-PowerPivotApplication1

Select your registered managed account.  This account must have Analysis Services system administrator permissions on the local Analysis Services service that is installed on the same computer.  This was set during the installation of SQL in our earlier steps above.

In my case, I am using the account sp_sql.

Enter your Database Server and enter a Database Name and select Windows Authentication.

clip image028 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

clip image030 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Also ensure the that the “Add the proxy for this PowerPivot service application to the default proxy group” is checked.

Click OK.

You should hopefully receive a window stating that the PowerPivot service application has been successfully created.

clip image032 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click OK.

Activating the PowerPivot Feature Integration for your Site Collections

We now need to activate the PowerPivot Feature activation to make application pages available to your SharePoint sites.

Click on Site Actions from within your SharePoint Site and select Site Settings > Site Collection Administration > Site Collection Features.

Navigate down the page until you reach PowerPivot Feature Integration for Site Collections and click on Activate.

clip image034 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

You will need to do this for all other site collections where you would like to run PowerPivot on.

Configuring the Excel Services Application

PowerPivot relies heavily on Excel Services so we need to ensure that our Excel Services Application is configured.

  • Launch Central Administration and navigate to Application Management > Manage Service Applications.
  • Click New and select Excel Services Application
  • Enter a Name, and Create a new application pool as per the below screen capture
  • Ensure Add to default proxy list is checked.

clip image036 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click OK

Your Excel Services Application should now be listed under Service Applications in Central Administration and Started.

clip image038 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click on the newly created Excel Services Application and click on Trusted File Locations.  This is the area where we need to specify our SharePoint Locations.  Please note that PowerPivot cannot access any workbook that is stored outside SharePoint.

  • Click on the existing http entry and make any modifications such as increasing the Maximum Workbook size.
  • Under External Data, select Trusted data connection libraries and embedded for the PowerPivot access.
  • Deselect the Warn on Refresh checkbox

clip image040 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Configuring the Secure Store Service Application

We need to ensure that we provision the Secure Store Service Application in order to allow PowerPivot to store its credentials.  Essentially we are configuring a PowerPivot unattended account for data refresh to work.

  • Launch Central Administration and navigate to Application Management > Manage Service Applications.
  • Click New and select  Secure Store Service
  • Enter a name for the service application, specify your database settings and create a new application pool similar to the below screen captures

clip image042 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

clip image044 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click OK.

You should receive confirmation that the Secure Store Service Application was successfully created.

The Secure Store Service will now be listed with the other service applications.

We will now  need to generate the master key.  Click on the Secure Store Service application.  This will direct you to a page with the below warning

“Before creating a new Secure Store Target Application, you must first generate a new key for this Secure Store Service Application from the ribbon”

clip image046 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click on Generate New Key from the Ribbon.

You will be asked to enter a Pass Phrase.  You will need to keep this pass phrase in a secure location as it will be required to be entered when adding a new secure store service server or when restoring.

After you have entered and confirmed your pass phrase, click OK.

clip image048 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Configuring the Unattended PowerPivot Data Refresh Account

Now that we have configured the Secure Store Service, we can proceed to configure our PowerPivot unattended data refresh account.  This account is used when you schedule PowerPivot data refresh jobs and allows workbook authors to schedule data refreshes without using their own credentials.  Because we are setting up PowerPivot on an existing farm, this is a manual step configure all in Central Administration.

  • Launch Central Administration and navigate to Application Management > Service Applications > Manage Service Applications and select the Secure Store Service Application that we created earlier.
  • Click New which will bring us to the Target Application Settings Window.
  • Enter a name for your Target Application ID such as PowerPivotUnattendedAccount and a Display Name and Contact Email Address.
  • Leave Target Application Type set to Individual and select Use default Page

clip image050 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click Next

The Specify the credential fields for your Secure Store Target Application page is displayed as per the below screen capture.

clip image052 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Accept the default values and click Next.

The Target Application Administrators page is displayed next.  Here we need to specify the account used for the PowerPivot Service Application Pool.  You should also add any other Windows domain account account which requires Full control permissions.

clip image054 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click OK.

Your Target Application ID should now be listed under the Secure Store Service Application.

clip image056 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click on the checkbox beside the newly created Target Application ID and click on Set Credentials from the Ribbon interface.

Here we need to specify the Credential Owner first which is the Account we specified right at the start when we ran the Analysis Services Setup.   In my instance I had used the account sp_sql.

You will also be required to enter the Windows User Name and password for an account that we specified above under Target Application Administrators.

clip image058 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

Click OK

Navigate back to Central Administration > Application Management > Manage Service Applications.

Click on the PowerPivot Service Application.  This will now load the PowerPivot Management Dashboard.  Under Actions, click on Configure service application settings.

Scroll down until you reach the Data Refresh section and enter the Target Application ID that we created earlier; PowerPivotUnattendedAccount.

clip image060 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

We now must grant the contribute permissions to the PowerPivot Unattended data refresh account.  This needs to be applied to any PowerPivot workbook for which it will be utilised.

So how do we know if this is all working?!  There are a number of steps, but a quick indication that we are pretty much there is to navigate to the PowerPivot Management Dashboard.  We do so via Central Administration > General Application Settings > PowerPivot Management Dashboard.  We should receive something similar to the below without any errors or warnings.

clip image062 thumb Installing SQL Server PowerPivot for SharePoint 2010–Step by Step Guide sharepoint 2010 sharepoint

 

As you can see from the above there are a number of steps involved but hopefully this guide will provide with a good basis.

If you require any assistance with your SharePoint or other IT needs, the team at GKM2 are happy to assist.  You can contact us via info@gkm2.com.au or 1300 797 288 within Australia.

  • Sergey

    This is a really great post and great explained each step, good work.

    I’m having an error while accessing PowerPivot service application.
    “The data connection uses windows authentication and user credentials could not be delegated …”
    I saw it’s a very common error in forums, I can’t find a solution to it (Claims to windows token is running).
    Can you advise me how to fix the problem?

  • Khaled Khelawy

    Great Article :)

  • Deepak Ratnani

    Thank you very much.. it really help me alot…

  • Wendy

    Great article! This was a huge help to me.

  • chirag

    fantastic keep it up

  • Gaurav Goyal

    Good Solution