Wednesday, May 16, 2012

Installing and setting up PowerPivot for SharePoint 2010

  1. 1. Log into the SharePoint 2010 central admin server as a farm administrator (whatever server Central Admin is installed on)
    2. Install PowerPivot from the SQL 2008 R2 Enterprise disk:

    2.1.  Click the Installation option on the left had side and then select New installation or add features to an existing installation
    2.2.  At the Setup Role screen select SQL Server PowerPivot for SharePoint and select your existing farm installation and click Next
    2.3.  Click Next on the Feature Selection screen (this is read only and for information only)
    2.4.  Setup will now run a rule check, if you get any errors you need to resolve these before you can continue – this should be bypassed now if logged in as farm admin and on the CA machine
    2.5.  Leave the name as is: “POWERPIVOT”
    2.6.  Select an account to run the Analysis Services and click Next. Type in the domain account you used as the app pool service account when you installed the DB engine of the instance that runs SharePoint. (say you have a sql instance that is the sql used by SP, go to configuration manager of that sql instance and look at the service account that was used for the DB engine)
    2.7.  On the summary screen click Install. Please check the summary screen first. It should display the correct Central Administration port. If not, you need to go to the installation configuration file (in the textbox at the bottom of the summary screen), open with Notepad and edit to the right port.
    2.8.  Before hitting install, please check this article. If you install on a CA server without a previous SQL installation, you should not run into errors, but check the 3 issues out of this article anyway before proceeding.
            Look at the red sections HACK #1, 2 and 3.

    3. Configure the farm for PowerPivot:
    3.1.  If the install has succeeded and it says so at the end of it, if you navigate to Central Administration, under ‘Manage Farm Features’, you should see :

    3.2.  Under System Settings, farm solutions you should see the following solutions automatically deployed:

    3.3.  Deploy the powerpivotwebapp.wsp to your web application.
    3.4.  System Settings | Manage Services on Server start the required services:
    ·         Excel Calculation Services 
    ·         Secure Store Service 
    ·         Claims to Windows token Service 

    3.5.  Under “Manage service applications”, create a new service application of type “SQL Server PowerPivot”, like in the screen below. Use all defaults.

    3.6.   Check to see if you have Excel Services enabled by seeing if it is listed in the Service Applications section of central admin. If not enabled, enable it by creating one. Use all defaults.
    3.7.   If there is no listing for a secure store then click on New and select Secure Store Service. Use defaults. Click on Generate New Key and complete the details (I this you should be all set on this one, we generated the key for other purposes in our last session)
    4.     Enable PowerPivot for the site
    4.1.  Site collection features->activate powerpivot:

    4.2.  This should deploy the new document library template:

    4.3.  When creating a PP library instance, you might get this:

    To resolve this, you need to add 
    <SafeControl Src=”~/_layouts/powerpivot/*” IncludeSubFolders=”True” Safe=”True” AllowRemoteDesigner=”True” SafeAgainstScript=”True” />” 
    to the site collection web config manually.

    5.     Additional  setup you need to perform:
    5.1.  Go to your site collection, try to export a list as a datafeed, and if you get this error:
    For security reasons DTD is prohibited in this XML document. To enable DTD processing set the ProhibitDtd property on XmlReaderSettings to false and pass the settings into XmlReader.Create method.
                                    It means you do not have ADO.NET for 3.5 (really just KB 982307) installed. You need to follow this article on MSDN (below) and install on the web server that has the web app that serves your site collection. If farm, on all web servers.

    5.2.  Double-hop issue: you get prompted on data refresh of the powerpivot charts; you need to follow the steps from this article:

1 comment:

  1. Thanks for this post.
    I foolow every step, now i am trying to export the data feed to PowerPivot.
    When i export the Data Feed, the PowerPivot try to import the data for some time (+20min), than show me this error message: “The remote server returned an error: (503) Server Unavailable”
    Appreciate if you could help.

    BR Marcelo F