Oct 20, 2010

Debug PowerPivot Errors

1) "Unknown Error" on Server Health:

This is a excel calculation service problem: its service account is not granted a db_owner role in Central Admin Content Database.

 See this post for explanation why this needs to be done.

2) Window Authentication Error:

This error occurs randomly after Excel Server reboot. It is because Claim to Window Token Service is not running, starting it from Service Management Console will make this error go away.

3) Red X Error in the bubble chart web part
check the version of Microsoft® SQL Server® 2008 R2 ADOMD.NET

notice: sometimes the version from control panel is misleading, so it is better to check physical file's version:

To get this version, download SQL 2008 R2 Feature Pack.

4) Red X error on PowerPivot Workbook in PowerPivot Gallery
Disabling loopback on the WFE should resolve this problem, this is best available fix so far. See this KB for details.

5) Window Event Log error on Orphan database associated with PowerPivot Application
This one happens if you created a powerpivot application and deleted it later. Even though the database was deleted, but somehow it still stays in sharepoint metadata. Use PowerShell get-spdatabase to verify and delete it.

6)PowerPivot Workbook Data Refresh "Access Denied" error
this one occurs when
  • Unattended Account is not granted "contributor" permission from sharepoint site, or
  • SSS application's owner is not set as Farm Account (don't know why....)

Data Refresh is triggered by Timer Job, which first fire request to SSS for unattended account credential, and on success, use the unattended account to open data source. In case datasource is sharepoint resource such as a integrated mode report, unattended account need sharepoint permissoin to do that. Notice, all those happen without PowerPivot service getting involved at all at this point.

7) Data Refresh Error when use SSRS report as datasource:
Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: The remote server returned an error: (403) Forbidden..
This error occurs when powerpivot Analysis Service try to access embedded SSRS report data. This call is made directly to SSRS by PowerPivot Analysis Service. As SSRS needs to check sharepoint permission of caller credential (which credential? when kerberos is implemented, it is PowerPivot Analysis Service Account. see this post for details), it throws the above error when the PowerPivot AS service account doesn't have sharepoint read permission. Notice: in this case, it is PowerPivot Analysis Service Account that will be ultimately passed to SSRS reporting data, not unattended account.