none
an error occurred during an attempt to establish a connection to the external data source

    Question

  • Hi All,

    I have one Tabular SSAS project & I have deployed it on SQL Server Analysis Service 2012.

    Then I have created a BISM connection to that deployed project as in following way.

    Then I have created one PowerPivot excel report out of that BISM workbook using its connection string & it is working correctly in Excel.

    But when I upload it to the SharePoint, it give me following error while refreshing the Data from BISM workbook.

    this error is observed only when I open it on browser i.e. Excel Web Access.

    but the same error is not observed when I refresh the report in Microsoft Excel 2013.

    why is it giving error on only SharePoint i.e. Excel Services. due to this I am not able to change Slicer's either.

    One of the solution I found was to use some Hot Fix of SQL Server. but I haven't tried this. Plz refer the below link

    http://support.microsoft.com/kb/2854370/en-us

    Please help!

    Thanks in advance.


    Wednesday, May 28, 2014 9:25 AM

All replies

  • Is it not possible to refresh the workbook which uses BISM connection on Browser (Excel Web Access)?

    Thursday, May 29, 2014 9:42 AM
  • Hi Rameshwar,

    For the error
    "An error occurred during an attempt to establish a connection to the external data source. The following connections failed to refresh: PowerPivot Data"
    It occurs if you query PowerPivot data on a server that does not have PowerPivot for SharePoint installed. It also occurs if the SQL Server Analysis Services (PowerPivot) service is stopped. For the detail information about it, please refer to the link below.
    http://technet.microsoft.com/en-us/library/ff487858(v=sql.110).aspx

    Regards,


    Charlie Liao
    TechNet Community Support

    Tuesday, June 03, 2014 9:52 AM
    Moderator
  • Hi Charlie,

    Thanks for your reply.

     Unfortunately, PowerPivot for SharePoint is installed & SQL Server Analysis Services (PowerPivot) service is also running.

    I think it is the Permission level Issue of Excel Services on SharePoint.

    I have marked the EffectiveUserName Property also in Global Setting from Central Administrator. but still no Luck.

    PowerView is working fine. PowerPivot is working only when I open it in Office Excel. only problem remains when I view it in Browser i.e. thru Excel Services.

    Hope this helps u to investigate further.

    Tuesday, June 03, 2014 10:06 AM