locked
an error occurred during an attempt to establish a connection to the external data source RRS feed

  • 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.

    Tuesday, June 3, 2014 8:39 AM

Answers

All replies

  • Hi Rameshwar,

    PowerPivot workbooks contain embedded data connections. To support workbook interaction through slicers and filters, Excel Services must be configured to allow external data access through embedded connection information. External data access is required for retrieving PowerPivot data that is loaded on PowerPivot servers in the farm. Please refer to the steps below to solve this issue:

    1. In Central Administration, in Application Management, click Manage service applications.
    2. Click Excel Services Application.
    3. Click Trusted File Location.
    4. Click http:// or the location you want to configure.
    5. In External Data, in Allow External Data, click Trusted data connection libraries and embedded.
    6. Click OK.

    For more information, please see:
    Create a trusted location for PowerPivot sites in Central Administration: http://msdn.microsoft.com/en-us/library/ee637428.aspx

    Another reason is Excel Services returns this error when you query PowerPivot data in an Excel workbook that is published to SharePoint, and the SharePoint environment does not have a PowerPivot for SharePoint server, or the SQL Server Analysis Services (PowerPivot) service is stopped. Please check this document:
    http://technet.microsoft.com/en-us/library/ff487858(v=sql.110).aspx

    Finally, here is a good article regarding how to troubleshoot PowerPivot data refresh for your reference. Please see:
    Troubleshooting PowerPivot Data Refresh: http://social.technet.microsoft.com/wiki/contents/articles/3870.troubleshooting-powerpivot-data-refresh.aspx

    Hope this helps.


    Elvis Long
    TechNet Community Support

    Thursday, June 5, 2014 9:50 AM
  • Hi Elvis,

    Thanks for the reply.

    I have already selected the following option in Central Administrator. still NO LUCK.

    Also, The PowerPivot for SharePoint is installed & SQL Server Analysis Services (PowerPivot) service is also running.still NO Luck.

    One thing which is worked is, when I directly connect to Analysis Services Instance from Excel (Data Tab-> From Other Source -> Analysis Service) then it gets refreshed on Browser.

    But when I  connect from BISM connection file to connect to Analysis Service, then it doesn't refresh on Browser.

    But the thing is in both Cases it gets refreshed In Office Excel, but it encounters issue on Browser i.e. Excel Services.

    So there must be permission issue on Excel service.

    Also I have tried to Use EffectiveUserName Property still doesn't work.(Central Administrator -> Excel Service Application -> Global Settings)

    Hope you got my point.


    • Edited by Rameshwar Pawale Thursday, June 5, 2014 10:30 AM Images were not attached
    Thursday, June 5, 2014 10:25 AM
  • Hello,

    Have you configure credential for PowerPivot for SharePoint data refresh? Please refer to the following articles:
    Configure Stored Credentials for PowerPivot Data Refresh (PowerPivot for SharePoint): http://msdn.microsoft.com/en-us/library/ee210671(v=sql.110).aspx
    Configure the PowerPivot Unattended Data Refresh Account (PowerPivot for SharePoint): http://msdn.microsoft.com/en-us/library/ff773327(v=sql.110).aspx

    I would strongly recommend you to collect Usage data history, which is stored for events (connections, load, unload, and on-demand query processing) and data refresh (scheduled data processing). It's helpful for us to troubleshoot this issue.
    Configure Usage Data Collection: http://msdn.microsoft.com/en-us/library/ee210657.aspx
    Configure and View SharePoint Log Files and Diagnostic Logging (PowerPivot for SharePoint): http://msdn.microsoft.com/en-us/library/ee210652(v=sql.110).aspx


    Elvis Long
    TechNet Community Support

    • Proposed as answer by Elvis Long Thursday, June 12, 2014 2:18 AM
    • Marked as answer by Elvis Long Thursday, June 19, 2014 3:16 AM
    Thursday, June 5, 2014 10:43 AM
  • Hi Elvis,

    I will get back to you after trying out Secure Store Service.

    Also I will set the logs on Power Pivot Service(SharePoint) in order to get the exact Issue.

    Thanks!

    Thursday, June 5, 2014 1:31 PM