question

KishoreAdichanSukumaran-0120 avatar image
0 Votes"
KishoreAdichanSukumaran-0120 asked KishoreAdichanSukumaran-0120 edited

Excel to SSAS connectivity for Pivot table fails

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.

office-excel-itprosql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered KishoreAdichanSukumaran-0120 edited

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks @LukasYu-msft, It seems first time the refresh is successful and then we add a field it shows error. We did try to create a new pivot table in a new excel and each time it shows the error "The current operation was cancelled because another operation in the transaction failed."

Note:- We are using Windows authentication with the AD logins

0 Votes 0 ·