Excel Services & PowerPivot for SharePoint

Troubleshooting Excel Services and PowerPivot for SharePoint rendering and refreshing in the browser.

"PowerPivot Management Dashboard ProcessingTimer Job" throws error "User cannot be found”.

"PowerPivot Management Dashboard ProcessingTimer Job" throws error "User cannot be found”.

  • Comments 2
  • Likes

Issue Overview:

Post migration of a working PowerPivot system from Windows Authentication to Claims Authentication sometimes not all workbook files author property is updated to the new format, this causes the "PowerPivot Management Dashboard Processing Timer Job" to fail with error:

“Microsoft.SharePoint.SPException: User cannot be found” at Microsoft.AnalysisServices.SPAddin.UsageProcessingTimerJob.PrepareUsageData

Some or all of your PowerPivot files like have their author property set in the old Windows style name like “MYDOMAIN\someuser”

If the author property was set to a claims format the error would go away, like “i:0#.w|MYDOMAIN\someuser”

The workaround for this issue is to delete and re-upload the PowerPivot workbooks affected.

The author field can not be fully examined from the SharePoint UI, so it can be difficult to track down all of these workbooks.

Attached to this post (very bottom) is sample code that shows one way to list all of the workbooks for a PowerPivot service application that have an author property in the Windows style format (these files will throw and error when the "PowerPivot Management Dashboard Processing Timer Job" runs).

** THIS SAMPLE CODE IS PROVIED AS IS, WITH NO WARRANTEES OR SUPPORT OF ANY KIND, USE AT YOUR OWN RISK **

Rename PPTimerUserError.bak to PPTimerUserError.exe so you can run the program (.bak so zip scanners don't block the file)

The .exe must be ran from one of the farm SharePoint 2013 servers

The output is tab delimited so you can open with Excel to sort and filter as needed

useage: PPTimerUserError <DB Server> <PowerPiviot DB Name>

example: PPTimerUserError warrenr-ws1 DefaultPowerPivotServiceApplicationDB-57bc03ae-bd41-4102-ab6f-f03201e3b583

output at C:\temp\PPTimerAuthorReport.txt  and PPTimerAuthorReportDetails.txt

Update

This PowerShell code is known to update the author field if you do not want to remove and re-add the affected files,

**USE AT YOUR OWN RISK **

$web = get-spweb http://reports2013/analytics

$list = $web.lists['Reports']

$user = get-spuser -Web $web -limit all | ? {$_.userlogin -match "SPAdmin"}

$sUser = $user.id.ToString() + ';#' + $user.DisplayName

$listItems = $list.Items

write-host $listItems[0]["Author"]

write-host $listItems[1]["Author"]

write-host $listItems[2]["Author"]

$li = $listItems[1] 

$li["Author"] = $sUser

$li.properties["vti_author"] = $user.loginname

$li["Editor"] = $sUser 

$li.UpdateOverwriteVersion()

write-host $li["Author"]

Attachment: PPTimerUserErrorv1.zip
Comments
  • Thanks for sharing.

  • When trying to run PPTimerUserError on a SharePoint farm that has SSL enabled the log file comes back with the following error. Is there something that has to be done differently to get this too run in this type of environment?

    Starting run at : 7/20/2015 3:23:53 PM
    Unhandled exception
    System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'Usage.Documents'.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
    at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
    at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
    at System.Data.SqlClient.SqlDataReader.get_MetaData()
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
    at System.Data.SqlClient.SqlCommand.ExecuteReader()
    at PPTimerUserError.Program.Main(String[] args)
    ClientConnectionId:71622cb4-30e4-4da9-be98-82ad2d88c3bf
    Error Number:208,State:1,Class:16

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment