none
Issue with On-Premise Sharepoint List Data Refresh RRS feed

  • General discussion

  • I'm running into an issue while trying to build  a report from SharePoint List onto PowerPivot Model, so that I can build report using PowerPivot Gallery in SharePoint (On Premise). Theoretically, it should be possible, but not sure why/what issue I'm running into. Whenever I schedule Refresh, it gives me this message 

    8/19/2014 1:57:37 PM 00:00:03 Call to Excel Services returned an error.

    Error Log gives me this details:

    System.InvalidOperationException: Call to Excel Services returned an error. ---> Microsoft.AnalysisServices.SPClient.Interfaces.ExcelServicesException: We're sorry. We can't open the workbook in the browser because it uses these unsupported features:

    • SharePoint lists ---> Microsoft.Office.Excel.Server.WebServices.ExcelServerApiException: We're sorry. We can't open the workbook in the browser because it uses these unsupported features:

    • SharePoint lists
       at Microsoft.Office.Excel.Server.WebServices.ApiShared.ExecuteServerSessionMethod(Boolean hasSessionId, String sessionId, CoreServerSessionMethod coreWebMethod, String name, Boolean skipFeatureCheck)

    PPDT file gives me some additional details:

    PowerPivot Service Applications

    Name = Default PowerPivot Service Application
    Id = b08fded8-af7e-4826-897b-224c1719888f
    --------------------------------------------------------------------------------
    PowerPivot Service Application Database
    Connect to database: done
    Table [DataRefresh].[Enqueue] fails: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'DataRefresh.Enqueue'.
       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 Microsoft.AnalysisServices.SPAddin.DiagnosticTrace.WriteDatabaseStatus(StreamWriter w, GeminiServiceApplication serviceApplication)
    ClientConnectionId:7e31c77c-9e5b-4ea3-a07d-40e3da3f114d
    Table [DataRefresh].[Frequency] fails: System.Data.SqlClient.SqlException (0x80131904): Invalid object name 'DataRefresh.Frequency'.
       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)

    Let me know if any of you have faced this issue and how to fix this?

    -Shri


    Wednesday, August 20, 2014 3:59 PM

All replies

  • I initially used PowerQuery and saw in another thread, Refresh isn't supported with power Query and hence I followed these steps

    • Export the SP list to Excel (atomsvc)
    • Added the data into Model (PowerPivot)
    • Enabled Refresh under Connections
    • Uploaded the Excel Document to PowerPivot Gallery
    • Scheduled Refresh in SharePoint (under account configured by Admin)
    • Created a report against Model Excel (_layouts/15/ReportServer/AdHocReportDesigner.aspx?RelativeReportUrl=)

    I get the initial data (as the data was already loaded into data model) but I dont get any new data.

    Also, the simple Refresh All against Excel doesn't work either.

    -Shri

    Wednesday, August 20, 2014 4:26 PM
  • Shri, is this still a problem?

    Thanks!


    Ed Price, Azure & Power BI Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Thursday, October 2, 2014 12:01 AM
    Owner