When you first install PowerPivot for SharePoint, there are some configuration tasks that need to be completed to get basic functionality working for the PowerPivot Management Dashboard, as well as to get the groundwork laid out for data refresh and slicing inside of workbooks.

 

While not difficult to do, these steps are often missed and we see quite a few support cases opened up for these simple configuration issues.

 

These items are not in any particular order, but all of them are relevant to every installation of PowerPivot for SharePoint 2010 and 2013 (Unless otherwise noted). All of these tasks should be completed after the successful installation of the PowerPivot Analysis Services SQL instance as well as the running of the PowerPivot Configuration Tool on at least one server in the farm successfully.

 

For this example, I will use screenshots from one of my isolated test farms, so please do not take the names of servers or service accounts literally. They will be different for every scenario depending on your configuration.

 

Also, I will not be covering Kerberos or any data refresh configuration as part of this article. Nor will I be covering any troubleshooting. This will only cover getting the PowerPivot Management Dashboard into a working state after a fresh install.

 

 

  • Define your PowerPivot instance (SharePoint 2013 ONLY)
    • We need to tell SharePoint which PowerPivot AS instance we are using to host data models. In Central Administration, navigate to your Excel Services service application and click on "Data Model Settings".
      • Add your PowerPivot instance here in the "servername\PowerPivot" format:
        In this example, my PowerPivot instance is hosted on a server named "Reporting", therefore my PowerPivot AS instance is denoted as such: "        servername\PowerPivot"

 

  • Define your unattended account for PowerPivot.
    • During the running of the configuration tool, it creates an unattended data refresh account inside of the Secure Store service. Within the scope of PowerPivot, this account is used to populate the information inside of the PowerPivot Management Dashboard. As this ID is already configured, all we need to do is define a service account to be stored in the ID. This service account must have at least "db_reader" permissions on the PowerPivot Service Application database. This database is located with the rest of your service application databases on whatever instance stores your SharePoint databases. Unless you explicitly changed its name during configuration, it can usually be identified by the service application name followed by a GUID just like in the screenshot below:

      • To define this account, navigate to your Secure Store Service application in Central Administration.
        • Find the Secure Store ID labeled "PowerPivotUnattendedAccount"
        • Hover your mouse over the ID and click the dropdown menu.
        • Click "Set Credentials"
        • Enter a farm account for a Credential Owner if there isn't one present.
        • Enter the desired data access account in the "Windows User Name" field in a "domain\username" format.
        • Enter and confirm the password.
        • Click "OK"
      • Navigate to your PowerPivot Service application and click "Configure service application settings"
      • Find the "PowerPivot Unattended Data Refresh Account" field, and ensure that the "PowerPivotUnattendedAccount" is listed. If it isn't, enter it here, and then click "OK"

 

  • If you are using different service accounts for all of your services, you may need to run the following PowerShell commands to give your Excel Services account access to Central Admin. This will allow the service to render workbooks in the browser.
    • Run the following from an administrator enabled SharePoint 2013 Management Shell:
      • $w = Get-SPWebApplication –Identity <URL of the Central Administration>
      • $w.GrantAccessToProcessIdentity("<insert Excel Services service account here>")

  • Make the Excel Services and PowerPivot service accounts Administrators on the PowerPivot Analysis Services instance. This is required for the appropriate services to create and manage data models on the instance.
    • Open up SQL management Studio and connect to your PowerPivot AS instance.
    • Right click on the root node and select "Properties"

    • Click "Security"
    • In this section, ensure that your accounts that run the PowerPivot Service application and Excel Services are listed. If they are not, add them.
      Note that my screenshot has a few more accounts added…..ignore them.

  • After the accounts are added, click "OK" and close out of SQL management Studio.

 

 

After all of this is done, you can test to see if the dashboard will retrieve data when it is available by force running the PowerPivot Management Dashboard Processing Timer Job.

  • Navigate to your PowerPivot Service Application and click "Review Timer Job Definitions".
  • Click "PowerPivot Management Dashboard Processing Timer Job".
  • Click "Run Now", then "Job History" once the page loads.
  • Refresh this page until you see the timer job near or at the top of the list. If the status is succeeded, you can go back to the Management Dashboard, though it is unlikely to populate any usable data as no workbook activity has been recorded yet.
    • If this timer job fails, you will need to troubleshoot the error. Most of the steps I went through above cover most of the troubleshooting needed to resolve this. 9 times out of 10, the unattended account either isn't properly set or does not have the correct permissions on the PowerPivot Service Application database. You may have to dig into the ULS logs to uncover the error if it is not obvious.

 

Barring any other unforeseen issues, after a few days of using PowerPivot in your environment, you should start seeing data populated inside of the dashboard. Note that this data is not updated in real time. It utilizes the SharePoint Web Analytics to populate data, so at a minimum you will see updates every 24-48 hours.

 

Interested in other post configuration tasks? Check out our blog for more!

http://blogs.technet.com/b/excel_services__powerpivot_for_sharepoint_support_blog/