Say you have a excel file containing a cube(referencing a Multidimensional cube on a SSAS server) configured such that you have a hierarchy in the rows:
And this hierarchy has been expanded so that certain nodes are visible within this hierarchy(Such as "Retained Earnings")
If you edit this cube on the server and remove one of the leaves or nodes references in your expanded excel file, and refresh your cube within your excel file you would expect that both the node that has been removed from the cube and the expansion of said node would have been removed from your excel file.
This is the case in version 2108 of excel and prior versions as well. However in version 2109 - Build 14430.20306 in certain cases this is no longer the case. In several(6+) files that I have referencing multiple Multidimensional cubes if you attempt to refresh or in any way alter these pivot tables(containing the condition described above) in excel you will be prevented from doing so without any error, the OLAP pivot table will attempt to refresh with your change, fail, and any change you've made to the pivot table will be undone.
If you attempt to click the "Refresh All" you will be presented with the following error:
A PivotTable, cube function or filter control using the connection X didn't refresh. Continue to refresh all?
Examining the issue deeper, if you set up SQL profiler pointed at the server and attempt to refresh a excel file with this condition in version 2108 you will see something on along the following lines:
(Does some discovery then executes the main query)
(Errors out but keeps going, re-enumerates many sections of the cubes and is able to recover, excel successfully refreshes cube)
If you attempt to refresh the same exact file in version 2109 you will see the following:
(Does some discovery then executes the main query)
(Errors out and this is the end of the profile trace, no other commands are logged and excel “halts”, the cube is "frozen" and is not refreshed)
I'm not quite sure what change has been applied to excel to triggers this behavior, and I'm not able to reproduce it using a newly built cube, but this issue has affected a good number of existing files I have pointing at many multidimensional cubes I've generated over the years.
I'm not able to post any of my affected files as they contain proprietary information however this issue is wide reaching and I would be surprised if it hasn't affected others.
Has anyone else experienced this issue? Is there any work around that anyone is aware of, what I've been doing is refreshing the cubes in 2108 and sending them to whoever is trying to use them in 2109, this is only a short term solution, is there any other solution to this other than having all users rollback to a older version of excel?
Note: Cross Posted on the Microsoft Forum
Update 1
Not alone on this one, issue seems to be widespread in the community, here's the main post.
For us the issue is still in place for me in build 14430.20306, I've had other users report issues all the way back to build 14430.20234. If I revert back to 14326.20404, it goes away.
I've looked into using MS Diagnostic Data Viewer to see if I can gather additional information on the issue, without luck, no events are recorded when the cube refresh occurs.
