locked
Preventing PowerPivot Pivot Tables from Updating when clicking outside of PowerPivot/Excel RRS feed

  • Question

  • This has been frustrating me greatly. 

    If I work in PowerPivot and make some changes, as soon as I click one of my Excel worksheets or click outside of Excel to use another application (e.g. browser) it automatically triggers an update of ALL of the Pivot Tables in my workbook which are connected to my data model. 

    Given the number I have this can take over a minute and I don't want this to happen.

    I have set Calculation Options to Manual in both PowerPivot and under the 'Formulas' section in the ribbon but this has made no difference - can anyone help?

    Thanks.

    Tuesday, July 14, 2015 6:00 AM

Answers

  • So, what I do is always have a "blank" worksheet called "Title" where I put information about the workbook and this does not have any pivot tables and it is not a power view sheet. I always navigate to this tab when saving the sheet and if I am working in Power Pivot, make sure I am on that sheet so that when I click back to the Excel file I do not get the problem you are experiencing. Pain, but prevents the issue.
    Tuesday, July 14, 2015 2:35 PM
  • Hi Maracles,

    According to your description, you need to prevent PowerPivot from updating when making any changes on the PowerPivot data model, right?

    When design a PowerPivot data model, any changes will be updated automatically, such as create calculated column, create measure or build relationship between two tables. This the default setting. What we can do is setting calculation options to Manual Calculation Mode. Based on my test, under this model, the result for this formula will not be calculated automatically. However, the formula itself will be updated. So it will take a time to update this formula.
    https://msdn.microsoft.com/en-us/library/gg413421%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 15, 2015 2:19 AM
  • Maracles,

    Please send a Frown to the team or use Power BI User Voice to submit your feedback!

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, July 29, 2016 8:53 PM

All replies

  • So, what I do is always have a "blank" worksheet called "Title" where I put information about the workbook and this does not have any pivot tables and it is not a power view sheet. I always navigate to this tab when saving the sheet and if I am working in Power Pivot, make sure I am on that sheet so that when I click back to the Excel file I do not get the problem you are experiencing. Pain, but prevents the issue.
    Tuesday, July 14, 2015 2:35 PM
  • OK, so this works for Power View and even if you are on the Power View sheet you can click the "X" to not update it. I can't see any similar option for pivot tables, although ESC might work, that tends to kill processes in Excel.
    Tuesday, July 14, 2015 2:43 PM
  • Thanks Seth - I have been hitting Escape to prevent the update, it's just annoying as I have to hammer away at Escape for about 15 sec before it settles down and lets me use Excel again! 

    Hopefully this problem is fixed in the 2016 update, I have seen numerous other improvements. 
    Tuesday, July 14, 2015 4:33 PM
  • Hi Maracles,

    According to your description, you need to prevent PowerPivot from updating when making any changes on the PowerPivot data model, right?

    When design a PowerPivot data model, any changes will be updated automatically, such as create calculated column, create measure or build relationship between two tables. This the default setting. What we can do is setting calculation options to Manual Calculation Mode. Based on my test, under this model, the result for this formula will not be calculated automatically. However, the formula itself will be updated. So it will take a time to update this formula.
    https://msdn.microsoft.com/en-us/library/gg413421%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396

    If you have any concern about this behavior, you can submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it is resolved in the next release of service pack or product. Your feedback enables Microsoft to make software and services the best that they can be, Microsoft might consider to add this feature in the following release after official confirmation.

    Thank you for your understanding.

    Regards,


    Charlie Liao
    TechNet Community Support

    Wednesday, July 15, 2015 2:19 AM
  • Hi Charlie,

    Thanks for the feedback but possibly I didn't explain very well.

    When I'm within the PowerPivot window itself (Excel Ribbon > PowerPivot > Manage) I don't have any performance issues creating measures, calculated columns or filtering the data. 

    My issue arises when I make changes to my data model and then click from that PowerPivot window into one of my Excel worksheets on which I have a Pivot Table that is linked to my data model.

    My workbook is made up of 9-10 worksheets each with Pivot Tables linked to my data model, as soon as I enter any of these sheets having made a change to a measure or calculated column in my data model then every pivot table tries to update simultaneously (over 50+) causing me to have to wait 2-3 minutes. At the time it is doing it I get a flashing "(Reading Data)" in the bottom right corner of excel. 

    It is this I don't want to happen. Does this make more sense? 

    Thanks. 

    Monday, July 20, 2015 7:32 PM
  • Maracles,

    Please send a Frown to the team or use Power BI User Voice to submit your feedback!

    Thanks!


    Ed Price, Azure Development Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    Friday, July 29, 2016 8:53 PM