Over the years, we have had customers engage us for PowerPivot configuration cases, but the issue lies in the PowerPivot install. But before I dive into errors, etc. I will first explain the PowerPivot Install (for SharePoint 2010 and SharePoint 2013).
There are two areas that need to be configured.
1. Analysis Services (AS) needs to be installed in PowerPivot Mode.
If you have an AS \POWERPIVOT Instance already, is it in PowerPivot Mode? The easiest way to check it to connect to the AS Server running the \POWERPIVOT Instance and check the icon. Does it look like the below?
Determine the Server Mode of an Analysis Services Instance
If the Icon does not look like the above, then you will not be able to load PowerPivot Data Models against it. You will need to install an AS Server in PowerPivot Mode and name the Instance POWERPIVOT.
2. SharePoint needs to have the sppowerpivot.msi installed which is the PowerPivot Configuration Tool. I wrote a blog on this, that you can reference:
Run the "PowerPivot Configuration Tool" after Upgrading the Analysis Services POWERPIVOT Instance.
Important Note: If you have an AS Server, in PowerPivot Mode, and you have that listed under Central Administration > Application Management > Manage Service Applications > Excel Services > Data Model Settings.
You will be able to upload a PowerPivot workbook to SharePoint, a Data Model will be created on the AS PowerPivot Server and you will be able to slice (browser refresh) that workbook. Below is a blog on the 3 types of refreshes in PowerPivot.
What is PowerPivot for SharePoint? Part 2.
If you do not have the PowerPivot Configuration Tool installed on SharePoint, you not be able to see the PowerPivot Management Dashboard or be able to Schedule an after hours Data Refresh. Once you install the PowerPivot Configuration Tool, you will need to run it.
This will then lay down the PowerPivot Solutions onto SharePoint.
However, things can awry and the install can become corrupt, not laid down to the incorrect directory, or unbeknownst to you, someone delete pieces of the install. If that is the case, you will see errors like the below when accessing the PowerPivot Management Dashboard, Scheduling a Data Refresh, etc.:
If you see the above errors, most likely the PowerPivot Configuration Tool needs to either be re-run or removed from the SharePoint Server and re-installed. Best practice is to locate the build of PowerPivot on your AS server, extract the PowerPivot Configuration Tool (sppowerpivot.mis) out of that CU and install it on the SharePoint Server. For instructions go here.
If you are still seeing issues, you most likely will need to engage an Microsoft Technical Support Expert to evaluate the install. But, before you do that, it might be wise to check the Server Configuration to ensure PowerPivot is passing all 4 health checks. To do this:
1. Enable ULS logging and make sure Excel Services is set to Verbose.
2. Then Stop and Start Excel Services on all machines running Excel Services.
3. Merge the ULS logs.
4. Open the ULS log in ULS Viewer click Ctrl + F and search for “check server configuration”
In my below screenshot you will see “Fail”:
In the above screenshot, you will see examples of a bad environment.
The message “Check Administrator Access (Server\POWERPIVOT): Fail.” means that the Excel Services service account does not have the required permissions on the Analysis Services instance <SERVER>\POWERPIVOT. If you grant the permissions in SQL Server Management Studio (SSMS), the administrator access check will pass, as hopefully will do all the other Analysis Services server checks that Excel Services performs at regular intervals.
The message “Check Deployment Mode (Server\POWERPIVOT): Fail. (Expected: SharePoint, Actual: Multidimensional).” Means that the Analysis Services Server you are pointing to under Excel Services > Data Model Settings is not in PowerPivot Mode.
Here’s a successful sequence of all the server checks taken from the ULS log on a properly configured and functioning SharePoint 2013 application server:
Checking Server Configuration (Server\POWERPIVOT) ...
1. Check Administrator Access (Server\POWERPIVOT): Pass.
2. Check Server Version (Server\POWERPIVOT): Pass (12.0.2000.8 >= 11.0.2800.0).
3. Check Deployment Mode (Server\POWERPIVOT): Pass.
4. Check Server Configuration (Server\POWERPIVOT): Pass.
If all of these are passing and you re-ran/re-installed and re-ran the PowerPivot Configuration Tool you will need to work with a Microsoft Technical Support expert from our Analysis Services Team.