none
Refresh freeze after upgrading from Powerpivot 2010 > 2013

    Question

  • While upgrading powerpivot sheets from 2010 --> 2013, I run in the following problem: on hitting the refresh button, PP starts retrieving records, but freezes after retrieving an x number of rows, for instance 50.000 from 54.000

    I found someone else with the same problem (but with no solution), the problem seems to be in the calculated columns: in an example sheet, we also make use of one calculated column. If I remove this column, the refresh succeeds, and if I remove all Excel-sheets, the refresh succeeds as well.

    the formula is not that tough:

    "if(OR(OR([AttachedProductCrossSellType] = "Not available";[AttachedProductCrossSellType] = "Same Products on Invoice");OR([AttachedProductCrossSellType] ="Insurance";[AttachedProductCrossSellType] ="Other Attached"));"Other";[AttachedProductCrossSellType])"

    any idea's?

    Wednesday, July 17, 2013 9:13 AM

Answers

  • For anyone who has the same problem:

    Powerpivot 2013, compared to 2010, now refreshes both the powerpivot data and the corresponding pivots in Excel in the same refresh instance, re-calculating all metrics (calculated fields) every time and thus slowing down.

    Our main fix was to remove all instances of divisions in the metrics, especially in combinations with iferror() or if([value]=null;blank();false).

    After a long search, we found out that for instance a metric like

    =iferror([value_a];[value_b]/[value_a];blank())

    Has a bad performance. Therefore we used the new divide() function and rewrote this to

    =divide([value_a];[value_b])

    This gained us an enormous boost in performance, now allowing powerpivots to refresh in a few seconds or minutes rather than 30 minutes or longer.

    We also removed all non-relevant data connections, and changed all date calculations from dateadd() to parallelperiod()

    All works like a charm now, even faster than in 2010.

    But we have a new problem: after uploading to sharepoint 2013, and setting a scheduled refresh, this refresh disappears after uploading a new version of the document. For more info, search this forums for "could-not-find-the-schedule-for-this-work-item-powerpivot-scheduled-refresh-on-sharepoint-upon" (cant add a link)

    • Marked as answer by Jasper Kock Friday, August 23, 2013 1:26 PM
    Friday, August 23, 2013 7:25 AM

All replies

  • we have had a similar issue, upon investigation this appears to be where a pivot table linked to the PP model is empty. If anyone has a solution I would be very interested to here it.
    Saturday, July 20, 2013 4:08 PM
  • No solution at the moment.

    Things I tried this far:

    - Changed the calculated column to query case-statement in 2010 then update to 2013 (failed)

    - Rebuild powerpivot data in 2013 (success, but time consuming to update pivots, so no option)

    - Build a new workbook in 2010 based on the same queries/connections, but with a simple test pivot (succes, but no solution)

    So it seems like it is something in the workbook that fails at the update, and it is not simply the calculated column

    Monday, July 22, 2013 12:04 PM
  • Has anybody else seen this?

    Thanks!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

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

    Thursday, August 22, 2013 6:33 PM
    Owner
  • For anyone who has the same problem:

    Powerpivot 2013, compared to 2010, now refreshes both the powerpivot data and the corresponding pivots in Excel in the same refresh instance, re-calculating all metrics (calculated fields) every time and thus slowing down.

    Our main fix was to remove all instances of divisions in the metrics, especially in combinations with iferror() or if([value]=null;blank();false).

    After a long search, we found out that for instance a metric like

    =iferror([value_a];[value_b]/[value_a];blank())

    Has a bad performance. Therefore we used the new divide() function and rewrote this to

    =divide([value_a];[value_b])

    This gained us an enormous boost in performance, now allowing powerpivots to refresh in a few seconds or minutes rather than 30 minutes or longer.

    We also removed all non-relevant data connections, and changed all date calculations from dateadd() to parallelperiod()

    All works like a charm now, even faster than in 2010.

    But we have a new problem: after uploading to sharepoint 2013, and setting a scheduled refresh, this refresh disappears after uploading a new version of the document. For more info, search this forums for "could-not-find-the-schedule-for-this-work-item-powerpivot-scheduled-refresh-on-sharepoint-upon" (cant add a link)

    • Marked as answer by Jasper Kock Friday, August 23, 2013 1:26 PM
    Friday, August 23, 2013 7:25 AM