SQL 2012, PowerPivot Management Dashboard Processing Timer Job fails


Issue:

The PowerPivot management dashboard does not show all usage data like it is suppose to and you see that the PowerPivot Management Dashboard Processing Timer Job fails.

You may see errors like:

"Call to Excel Services returned an error"

“Cannot query internal supporting structures for column 'Date'[Rolling52Weeks] because they depend on a column, relationship, or measure that is not processed. Please refresh or recalculate the model”

Verify Issue:

The error information can be generic and not very useful for this issue.  You can verify this specific issue by starting SQL Server Management Studio and pointing it to your PowerPivot Service Application database, it is likely on the same SQL Server as you SharePoint 2013 content databases.  The database will likely be named something like this "DefaultPowerPivotServiceApplicationDB-57bc03ae-bd41-4102-ab6f-f03201e3b583"

Expand the database in the tree and expand the Views folder.

Right click on Usage.vDate and pick "Select top 1000 rows"

Scroll all the way to the right in the results grid and look at the values for the "Today" column.

If the "Today" column has NULL in it this is our problem (the column should have a valid date in it)

 

Workaround:

Open a new query window to the PowerPivot Service application database (same as referenced in the above steps)

Run this command:

EXECUTE sp_refreshview N'Usage.vDate';

 

More Info:

This issue was caused because the table the view references has been modified and the view was put into an invalid state.

I am working with the product group to have SQL updates refresh these views automatically.

Comments (4)

  1. Lee Drinkwater says:

    Thank you for posting this article. I hope this helps someone else but this same procedure fixed a similar issue that generated the following error in ULS:

    Hit ECS exception name: ‘ExternalDataRefreshFailed’; message:’Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed.

    AND

    Medium Result from usage code processing: Calculation error in column ‘Date'[]: Cannot convert.

    In my example the PowerPivotDB was upgraded from 2010 to a 2013 farm and the NULL value in the view was preventing the Management Dashboard timer job from running successfully.

    Lee

  2. Andrew E. says:

    Thanks for this Warren, what if I see the Call to Excel Services returned an error but I do see valid dates in the Today column?

  3. Andrew E. says:

    To add to my previous comment, I am on SQL 2014 RTM CU8 though….

    Thanks

  4. SeanFr says:

    I have the same question as Andrew E. I’m seeing the "Call to Excel Services" error. ULS logs show the exception:

    Hit ECS exception name: ‘ExternalDataRefreshFailed’; message:’Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. Unable to get the required information about
    this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed.
    Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. Unable to get the required information about this cube. The cube might have been reorganized or changed
    on the server. The PivotTable was not refreshed. Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. Unable to get the required information about this cube.
    The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. Unable to get the required information about this cube. The cube might have been reorganized or changed on the server. The PivotTable was not refreshed. We were
    unable to refresh one or more data connections in this workbook. The following connections failed to refresh: Data ‘

    We use SQL 2012 R2 SP1.

    same thing too, I see valid dates in the Today column

Skip to main content