locked
Excel pivot bug after the cube structure is changed RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    Hi, I searched the whole forum , but couldn't find the solution to this quit big problem. As probably some of you noticed, when a cube structure changes, an excel pivot table gives an error on refresh:
     "Excel cannot find OLAP cube 'name'. Either the OLAP database has been changed or you don't have permissions to connect to the cube."
    The only solution I know by now is to recreate the connection for Analysis Services. But this is not acceptable as it would be very inconvienient for the users. Is there a fix or workaround for that?

    I'm using AS2005 and excel 2007.

    Wednesday, October 14, 2009 10:27 AM

Answers

  • Question
    Sign in to vote
    0
    Sign in to vote

    Tautvis,
    Based on my test, you do not need to recreate the connection for SSAS after the cube structure changed. Instead, you just need to right click your PivotTable in Excel and click Refresh. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 20, 2009 8:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    After you remove a dimension or changing a hierarchy of the dimension, did you process your cube? In my test, I could reproduce your issue if I do not process the cube but this isse will not happen if I process the cube. So if you did not process your cube after made those changes, please process your cube than you can use Refresh in Excel.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 27, 2009 11:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Prodnik, thanks for the info.

    Actually, I used that hotfix last week, and it aparently solves the problem. What's more, due to my laptop configuration, I couldn't install the hotfix (I hope CIO is working on it...). I could, however, install it in a client's PC. That PC handles perfectly the Excels where I experienced the "Excel found unreadable content in.." error.

    I'll try adding a new hierarchy to an existing dimension just to see if the problem persists in that case.

    I also tried the workaround you explained ("remove the product dimension from rows/columns and re-add it before saving the workbook"). It has worked in some of my files, but not in all... good luck with it.

    I'll be back whith news.

    Regards
    Thursday, December 17, 2009 12:27 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Were you able to sort out this problem? I have the same exact problem.

    In my case I changed some dimension attributes in the OLAP Design (renamed them and hid some). After fully processing the cube, In Excel 2007 SP2, whenever I want to add that dimension to an existing Excel file I get the error  "Excel cannot find OLAP cube 'name'. Either the OLAP database has been changed or you don't have permissions to connect to the cube.". I also tried removing all dimensions and refreshing. As soon as I select the previously modified dimension I get the error. I am able to select other dimensions wothout any problem.

    As a workaround, If I go the the Pivot Table Options in Excel - Display Tab, I uncheck the 3rd option "Show Properties in Tootlips" and after that, it worked. The problem is that if you try to add properties to the report it fails again.

    I also tried downloading the patch mentioned above but I also get the message that it is already installed.

    If I create a new workbook to the cube, it works well...

    Any ideas?

    Rodrigo
    Wednesday, May 26, 2010 4:14 PM

All replies

  • Question
    Sign in to vote
    0
    Sign in to vote

    Tautvis,
    Based on my test, you do not need to recreate the connection for SSAS after the cube structure changed. Instead, you just need to right click your PivotTable in Excel and click Refresh. 

    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 20, 2009 8:34 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Refresh does not work. Try to change for exmaple a hierarchy of the dimension, or remove a dimension in the cube and the excel will give an error. This is a known error, but i'm suprised as there is no solution to it.
    Friday, October 23, 2009 7:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    After you remove a dimension or changing a hierarchy of the dimension, did you process your cube? In my test, I could reproduce your issue if I do not process the cube but this isse will not happen if I process the cube. So if you did not process your cube after made those changes, please process your cube than you can use Refresh in Excel.
    Please remember to mark the replies as answers if they help and unmark them if they provide no help
    Tuesday, October 27, 2009 11:20 AM
  • Question
    Sign in to vote
    0
    Sign in to vote

    The cube is processed before I try to use excel. Maybe you are trying to use excel with an empty pivot. Try this scenario:
    Browse the cube using a pivot in excel and leave columns, rows and values in the pivot, save and close the excel. Then change the cube structure, process the cube, and open the same excel pivot that was saved. With already present data in the pivot, the excel will give and error if tried to refresh or move/remove any of the columns or rows. This problem it's not only to me, I know dozens of guys with the same problem.

    I'm using Excel 2007, and SSAS 2005. And use FULL DATABASE PROCESS. Maybe another kind of cube process is required?

    Tuesday, October 27, 2009 11:42 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi,

    I think I'm having the same or similar problem in Excel Pivot Table after updating the cube.

    I had the Excel file in sharepoint (actually various files), and I recently changed a couple of dimensions. In the example, the pivot table has a time hierarchy in rows.

    Now, I try to open one of those files, and it warns me (you have to refresh data). After refreshing, if I try to drill down in a time hierarcy member, Excel crashes ("Microsost Office Excel has encountered a problem and needs to close....").

    I found a workaround. After refreshing, I take out the time hierarchy, and then put it back. After that 'move', I am able to drill down, etc... BUT, after saving the file, some of the pivot tables get wrong (after a message saying 'Excel found unreadable content in '...

    ¿Do we have to re-do all the excel files after cube structure updates?!?!?!

    Thanks in advance
    Thursday, December 10, 2009 11:39 AM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Raul,

    We are having the same problem here.  After adding a new product hierarchy to the cube all existing spreadsheets experience the "Excel found unreadable content in.." error after users had refreshed the pivot table, saved, closed and reopened their workbooks. 

    We have Office 2007 SP2 installed and so tried the hotfix related to KB 973932 but that didn't work for us.  At present  the only resolution is to remove the product dimension from rows/columns and re-add it before saving the workbook.  Obviously this doesn't work for those corrupted workbooks where a restore from backup was needed...
    Thursday, December 17, 2009 12:06 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Hi Prodnik, thanks for the info.

    Actually, I used that hotfix last week, and it aparently solves the problem. What's more, due to my laptop configuration, I couldn't install the hotfix (I hope CIO is working on it...). I could, however, install it in a client's PC. That PC handles perfectly the Excels where I experienced the "Excel found unreadable content in.." error.

    I'll try adding a new hierarchy to an existing dimension just to see if the problem persists in that case.

    I also tried the workaround you explained ("remove the product dimension from rows/columns and re-add it before saving the workbook"). It has worked in some of my files, but not in all... good luck with it.

    I'll be back whith news.

    Regards
    Thursday, December 17, 2009 12:27 PM
  • Question
    Sign in to vote
    0
    Sign in to vote
    Did anyone find a more elegant solution to this problem?  I have about 3000 pivot tables and I really do not want to manually update all the dimension.  

    I did try the hotfix and it says it was installed.  
    Monday, March 8, 2010 1:49 PM
  • Question
    Sign in to vote
    0
    Sign in to vote

    Were you able to sort out this problem? I have the same exact problem.

    In my case I changed some dimension attributes in the OLAP Design (renamed them and hid some). After fully processing the cube, In Excel 2007 SP2, whenever I want to add that dimension to an existing Excel file I get the error  "Excel cannot find OLAP cube 'name'. Either the OLAP database has been changed or you don't have permissions to connect to the cube.". I also tried removing all dimensions and refreshing. As soon as I select the previously modified dimension I get the error. I am able to select other dimensions wothout any problem.

    As a workaround, If I go the the Pivot Table Options in Excel - Display Tab, I uncheck the 3rd option "Show Properties in Tootlips" and after that, it worked. The problem is that if you try to add properties to the report it fails again.

    I also tried downloading the patch mentioned above but I also get the message that it is already installed.

    If I create a new workbook to the cube, it works well...

    Any ideas?

    Rodrigo
    Wednesday, May 26, 2010 4:14 PM