Excel to SSAS connectivity for Pivot table fails

Kishore S 1 Reputation point
2021-06-23T18:56:47.313+00:00

We have an excel spreadsheet (Office 365) connecting to an SQL Server Analysis Service (SSAS) cube. The data is being pulled to a Pivot table in the excel spreadsheet. The spreadsheet used to work fine in the past and suddenly having the following Errors\Issues.

1) We couldn't get data from an external data source. Please try again Later.

If the problem persists, please contact the administrator for the external data source.

Here is the error message that was returned from the analysis server named <XXXXXXX>

Server: The current operation was cancelled because another operation in the transaction failed.

2) We couldn't get data from an external data source. Please try again Later.
If the problem persists, please contact the administrator for the external data source.

Here is the error message that was returned from the analysis server named <XXXXX>

An Error was encountered in the transport layer

This is not allowing the pivot table to refresh.

Any help would be appreciated.

SQL Server Analysis Services
SQL Server Analysis Services
A Microsoft online analytical data engine used in decision support and business analytics, providing the analytical data for business reports and client applications such as Power BI, Excel, Reporting Services reports, and other data visualization tools.
1,055 questions
Excel Management
Excel Management
Excel: A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.Management: The act or process of organizing, handling, directing or controlling something.
1,363 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Lukas Yu -MSFT 5,796 Reputation points
    2021-06-24T02:16:48.963+00:00

    Hi,

    Was this error continuously occurring ?

    You could first the check the connection, see if the connection is still successful to the cube. If yes, you could try create new pivot table in an new excel see if this error pops up.

    The error could suggesting some setting issue , you could read this thread see if it helps your case : Excel Connectivity with SSAS

    Regards,
    Lukas