we are facing a strange behaviour of MS Excel files with SSAS 2005.
We have some xls files with cube connections with some macros. we have recently updated our office pack from 2003 to 2010(32 bit) and also our operating systems
from Win XP 32 bit to Win 7 64 bit.
from past few days we have noticed that our xls files are taking long time in cube refresh(sheet refresh) and also expanding the hierarchy columns to the
lowest level with data.
while doing some analysis we founf that the connection string which xls file was using is a bit different to xlsx or xlsm.
We created a new sheet in same file with a new connection to cubes(same way and same
structure of cube like old sheet) and its was work as expected but dont know what was the issue with the other sheet
in same file.
We also saved the xls file in xlsx and xlsm and tried to do a cube refresh on xlsx, this works better then xls but still not upto expectations.
We changed the connection string of old sheet with a new sheet connection string and now the cube refresh in sheet is much better but still not upto expactation.
are there any issues with old format(xls) used in new office pack(2010). I dont know what the issue is from from where to start digging.