none
Sharepoint 2013 - We cannot locate a server to load the workbook Data Model when trying to create powerview report over powerpivot workbook

    Question

  • Hi all,

    i'm trying to set up a proof of concept BI rig on azure

    I have sharepoint 2013 and sql server 2012 sp1 cu6 installed & powerpivot for sharepoint 2013 & ssrs integrated mode installed on the same server.

    I've created a powerpivot gallery and uploaded a powerpivot workbook.

    I've configured excel services and run the powerpivot config tool

    but i get the following error if i try to create a powerview report off the workbook. The workbook is fine and working in an existing powerpivot gallery on a different server.

    <detail><ErrorCode xmlns="http://www.microsoft.com/sql/reportingservices">rsCannotRetrieveModel</ErrorCode><HttpStatus xmlns="http://www.microsoft.com/sql/reportingservices">400</HttpStatus><Message xmlns="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://demo/PowerPivot%20Gallery/demo.xlsx'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><HelpLink xmlns="http://www.microsoft.com/sql/reportingservices">http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3381.0</HelpLink><ProductName xmlns="http://www.microsoft.com/sql/reportingservices">Microsoft SQL Server Reporting Services</ProductName><ProductVersion xmlns="http://www.microsoft.com/sql/reportingservices">11.0.3381.0</ProductVersion><ProductLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">127</ProductLocaleId><OperatingSystem xmlns="http://www.microsoft.com/sql/reportingservices">OsIndependent</OperatingSystem><CountryLocaleId xmlns="http://www.microsoft.com/sql/reportingservices">1033</CountryLocaleId><MoreInformation xmlns="http://www.microsoft.com/sql/reportingservices"><Source>ReportingServicesLibrary</Source><Message msrs:ErrorCode="rsCannotRetrieveModel" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsCannotRetrieveModel&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3381.0" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">An error occurred while loading the model for the item or data source 'http://demo/PowerPivot%20Gallery/demo.xlsx'. Verify that the connection information is correct and that you have permissions to access the data source.</Message><MoreInformation><Source>Microsoft.ReportingServices.ProcessingCore</Source><Message msrs:ErrorCode="rsErrorOpeningConnection" msrs:HelpLink="http://go.microsoft.com/fwlink/?LinkId=20476&amp;EvtSrc=Microsoft.ReportingServices.Diagnostics.Utilities.ErrorStrings&amp;EvtID=rsErrorOpeningConnection&amp;ProdName=Microsoft%20SQL%20Server%20Reporting%20Services&amp;ProdVer=11.0.3381.0" xmlns:msrs="http://www.microsoft.com/sql/reportingservices">Cannot create a connection to data source 'TemporaryDataSource'.</Message><MoreInformation><Source>Microsoft.AnalysisServices.SPClient</Source><Message>We cannot locate a server to load the workbook Data Model.</Message><MoreInformation><Source></Source><Message>We cannot locate a server to load the workbook Data Model.</Message><MoreInformation><Source>Microsoft.Office.Excel.Server.WebServices</Source><Message>We cannot locate a server to load the workbook Data Model.</Message></MoreInformation></MoreInformation></MoreInformation></MoreInformation></MoreInformation><Warnings xmlns="http://www.microsoft.com/sql/reportingservices" /></detail>

    The error pops up immediately (so it's not a server timeout issue)

    There are no failed connection attempts to the SSAS\POWERPIVOT instance. I've tried adding every single sharepoint service acc and even the Everyone group as an admin of the POWERPIVOT ssas instance but it made no difference so it doesn't look like an SSAS permissions issue.


    Adelaide, Australia

    Tuesday, October 22, 2013 1:06 AM

Answers

  • There are no failed connection attempts to the SSAS\POWERPIVOT instance. I've tried adding every single sharepoint service acc and even the Everyone group as an admin of the POWERPIVOT ssas instance but it made no difference so it doesn't look like an SSAS permissions issue

    the permissions were fine. iis/sharepoint wasn't picking up the new version of the permissions even after a few hrs. I had to restart iis for a different reason & it started working afterwards. Whether it was the reset or one of 10001 things i tried in between i dont know.


    Adelaide, Australia

    • Marked as answer by jakubk Thursday, October 24, 2013 11:06 PM
    Thursday, October 24, 2013 11:06 PM

All replies

  • Hi Adelaide,

    Could you please help to check the Excel Services account have sufficient permission to the PowerPivot instance. We need to grant Excel Services account Server Administration Rights on Analysis Services.

    For detail steps, please refer to the article below:
    Configure Basic Analysis Services SharePoint Integration:http://technet.microsoft.com/en-us/library/jj219067.aspx#bkmk_config

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    Thursday, October 24, 2013 12:07 PM
    Moderator
  • There are no failed connection attempts to the SSAS\POWERPIVOT instance. I've tried adding every single sharepoint service acc and even the Everyone group as an admin of the POWERPIVOT ssas instance but it made no difference so it doesn't look like an SSAS permissions issue

    the permissions were fine. iis/sharepoint wasn't picking up the new version of the permissions even after a few hrs. I had to restart iis for a different reason & it started working afterwards. Whether it was the reset or one of 10001 things i tried in between i dont know.


    Adelaide, Australia

    • Marked as answer by jakubk Thursday, October 24, 2013 11:06 PM
    Thursday, October 24, 2013 11:06 PM
  • When you say Excel Services Account, you mean the account running the Application Pool where Excel Services is listed under Configure Service Accounts? Or the account set under Credentials of the Secure Store Service Application?

    Thanks.


    Radu P.

    Thursday, November 14, 2013 6:37 PM
  • I received the same error message when working with Excel Workbooks.  I could load the workbook in Excel Services, but nothing creating a Power View generated the same error message that started this thread.  Working in Excel Services generated the Error: "Cannot Find a server to load data model".  From the hints above, I checked the Excel Service Application in Central Admin.  It seems PowerPivot Configuration set the Server as just "PowerPivot" instead of "<Server>\PowerPivot".  Maybe I set it wrong to begin with???

    Making that change fixed my problem.

    Thanks


    Mario


    • Edited by Mario5280 Saturday, November 30, 2013 5:04 PM
    Saturday, November 30, 2013 5:01 PM