locked
"The operation is cancelled" on Data Refresh all RRS feed

  • Question

  • I would love a bit of help with a data refresh issue.

    I am using Powerpivot 2013, feeding from SQL Server 2012

    I had a Powerpivot model with about 20 tables, all working fine.

    Now, an evolution of that spreadsheet has a weird problem where PowerPivot <Refresh All> stops after about 15 seconds with no data refreshed whatsoever and ALL tables showing Status "Cancelled". None of the tables show anything in the Message column.

    However, if I try to refresh each table one by one (which is tedious...), then each one refreshes properly

    Unfortunately, it took us quite a while to notice the issue and we have many modifications in the spreadsheet since the last one that refreshed properly.

    I tried to reapply all DAX/Powerpivot changes since the last good version and this shows no problem.

    The boss (my wife...) would prefer to fix the latest version of the spreadsheet though.

    I tried setting up Powerpivot traces but I can't see anything obvious... Although I can only find an error message "The Operation is cancelled" in the trace for the file that does not have a problem!!!
    I don't think I confused the files but that certainly confuses me!

    Any idea how to progress this, please?


    Eric Mamet

    Friday, May 5, 2017 7:30 AM

Answers

  • Eric, any progress on this?

    Thanks!

    I did not find out the underlying issue and I gave up on that file.

    I know it's not too glamorous but it worked...

    Thanks


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

    Wednesday, October 11, 2017 12:09 PM

All replies

  • Hi Eric,

    After research, I wonder if you have a timeout when you load the data from SQL Server to Power BI. Please check it based on the following screenshot.

    For another reason, if your SQL Server is locking because of lots of operation when you refresh. You can check it using sys.dm_os_waiting_tasks, which returns information about blocked and blocking processes. 

    Best Regards,
    Angelia


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Sunday, May 7, 2017 7:36 AM
  • Hi Angelia,

    Thank you for your help. I really appreciate.

    I use PowerPivot, not Power BI, and I am not able to find a setting for the command timeout.

    There is a Connection Timeout and it is left to the default of 30 seconds.

    When I try <Refresh All> in PowerPivot, it takes 15 seconds before the "Cancelled" error message and none of the tables seem to update.

    If I retry immediately after, the delay becomes very short (1 second or less).

    Because the default command time out is also 30 seconds usually, I assume this is not the issue.

    Because I have 19 tables, I increased Max Pool Size from 200 (default) to 400 but did not spot a difference.

    Any other idea?

    Thanks

    Eric


    Eric Mamet

    Monday, May 8, 2017 9:46 PM
  • Hi Eric,

    I really understand what you confused. After research, I am unable to reproduce your issue. I just doubt the computer has a thread conflict because of your large amount of tables. I will post the update if I have new idea.

    Thanks,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, May 18, 2017 9:08 AM
  • Eric,

    I hope you were able to solve your issue by now. Needless to say, we need to keep our wives and bosses happy, in your case it's all in one. 

    My hunch is that this is some kind of remote query timeout limitation set up on your server. I would look into that if I were you.

    I will leave you with two tests:

    • Test if you can refresh your model from another PC ?
    • Connect to another DB on your SQL Server and see if you can refresh ?

    Good Luck

    N -

    Friday, June 2, 2017 3:06 PM
  • Hi Nick,

    Thanks for the ideas.

    Yes, I tried to refresh for a copy of the database held on a decent desktop and the issue arises there too.

    In my last test of <Refresh All>, the cancellation happened within two or three seconds, which seems to rule out timeout issues.

    We have kind of given up on getting this working and are now restarting from an older version of that file on which I try to reproduce the DAX measures (and changes) as well as worksheets. Painful but that's the best I can offer the boss...

    Thanks for your help


    Eric


    Eric Mamet

    Friday, June 2, 2017 9:06 PM
  • Eric, any progress on this?

    Thanks!


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

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

    Monday, October 9, 2017 8:49 PM
  • Eric, any progress on this?

    Thanks!

    I did not find out the underlying issue and I gave up on that file.

    I know it's not too glamorous but it worked...

    Thanks


    Eric Mamet _ MCDBA, SQL Server 2005 MCTS, MCAD .Net

    Wednesday, October 11, 2017 12:09 PM