Excel to SSAS connectivity for Pivot table fails

Question

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.

Answer 1

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