Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Pivot Table data does not refresh, even though refresh completes successfully

Answered Pivot Table data does not refresh, even though refresh completes successfully

  • Wednesday, February 20, 2013 8:29 PM
     
     
    Sign In to Vote
    0

    Hi all,

    I received a workbook from a user which has a pivot table in which the data is not refreshing. If you click on refresh, it takes a long time (maybe 20 seconds) to perform the refresh.  It says that it finishes successfully, but the data is not current.  If I create a new pivot table off the same connection in the same workbook, this connection brings in fresh data. 

    Copying the pivot table to another sheet does not fix the issue.  The data still does not refresh.

    In profiler, the query is successfully starting and ending and there are no errors in the application or system logs. 

    This appears to be the same exact issue as this (with the exception that there are no background colors being formatted): http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/fa5d3a39-cc1d-4137-adb2-40b599908d20

    For the environment, this is excel 2010, 64 bit (14.0.6123.5001) accessing SQL 2008 R2, sp2.

    Ok, one last bit of major weirdness -- we have detected this issue because a dimension attribute was renamed, but is not getting updated.  If I add a label filter "Begins With..." and type in the name that I want to appear, the old name shows up in the pivot table with the correct amount next to it.  i.e.:

    I type in "Ameripris" in the filter, and the customer that appears in the pivot table is "AMERICAN ENTERPRISE".  It seems as though the data made it into Excel, but the pivot table is having trouble displaying it.

    I would hate to tell my customer to recreate all of their reports - or worse, you can never refresh them.  Any thoughts on troubleshooting this would be greatly appreciated.

    Mark

     

All Replies

  • Thursday, February 21, 2013 4:30 PM
    Moderator
     
     Answered
    Sign In to Vote
    0

    Two suggestions:

    1. Use the Reset Captions feature of this add-in. What can happen is that a user can type over the caption of a dimension member and rename it to something of their choosing. Then when that dimension member gets renamed in the cube, their rename in the PivotTable prevents it from showing the caption from the cube:

    http://www.contextures.com/xlPivotAddIn.html

    2. Use Profiler to check the MDX query sent from refreshing their PivotTable and compare it to the MDX query sent when you create a new PivotTable. There could be minor differences like the user may have filtered out all but a few members whereas a new PivotTable may include all but a few members. Then when new dimension members show up, the two PivotTables behave differently. The MDX should show you that.

    http://artisconsulting.com/Blogs/GregGalloway

     
  • Thursday, February 21, 2013 5:56 PM
     
     
    Sign In to Vote
    0

    The issue is definitely #1.  The user appears to have renamed the field.  Thank you!

    Do you know of any free ways to get the pivot table refreshed?  On the office site, they suggest the following:

    http://office.microsoft.com/en-us/excel-help/rename-a-pivottable-field-or-item-HP005202103.aspx

    If you hide and then redisplay levels in PivotTable reports based on OLAP source data, any renamed fields or items revert to their original names.

    I'm not sure what they mean by "levels."  I tried adding another field, collapsing and expanding, but that did not work.  Or perhaps this means the field gets reset if it is part of a hierarchy?

    Mark

     
  • Thursday, February 21, 2013 6:26 PM
    Moderator
     
     
    Sign In to Vote
    0
    I thought the Reset Captions feature was in the free version of the PivotPower add-in I mentioned above. It used to be, though Debra has made some changes to that website, I see. See the bottom of the page for the free version. If Reset Captions isn't in there, please let me know.

    http://artisconsulting.com/Blogs/GregGalloway

     
  • Thursday, February 21, 2013 6:58 PM
     
     
    Sign In to Vote
    0

    Oh, I'm sorry.  I did not see the link for the free version.  I did download it and it  does include the reset captions feature.  The only downside is that it reset the labels to the member unique name.  Which is odd for the user to look at, but at least I can see what the values were supposed to be. 

    This is not so handy on key column, where the name is text value.  Then the member unique name is meaningless. 

    Thanks for the tip, it at least exposes the underlying values.