locked
Power Pivot crashes upon refresh RRS feed

  • Question

  • Hello, I am a little new to Power Pivot, and cannot find online how to fix this problem.

    I have a large data set stored in Access.  It is over 1.5M rows, and about 40 columns wide.

    I have an Excel file where I use Power Pivot to connect to this data.  The goal is to use smaller tables (within Excel) to Relate and pivot off of.

    However, I cannot even refresh the data.  The refresh (from Power Pivot gathering data from the Access database/table) crashes.

    I'm not sure why this is, as I've heard of people well exceeding over 1M rows of data.  How do they do this?  Is the data source (over 1M rows) in Access, Excel (I would think it can't be Excel because of the 1M limit), etc.?

    Mine crashes/times out at around 800K or so.  I am running Office 2010, and I believe it is a 32-bit version.

    Any guidance as to why this is happening, or the best way to accomplish what I am attempting is very appreciated!

    Wednesday, October 15, 2014 1:17 AM

Answers

  • Skashat,

    Any 32-Bit application is limited to 2GB in Memory and Power Pivot relies on being able to keep all data in Memory. My understanding during the time of refresh is that it actually creates a copy of the dataset (this is done so that if the refresh fails, Power Pivot can fall back to the original copy), and then tries to refresh the new copy. Thus the memory requirements during a refresh are higher. You are most likely hitting the 2GB memory limit self-imposed by 32-bit.

    You can try the ideas suggested in this article: Workarounds for “Canceled Due to Memory Pressure” (like using 64-bit :-))

    As to how other users may be able to pull 1M+ rows (it's even possible with 32-bit version). It all depends on your data: there are multiple factors: but number of columns is key. 
    This article has more info Less Columns, More Rows = More Speed!
    Some more reading on Performance with Power Pivot


    Regards, Avi www.powerpivotpro.com

    • Proposed as answer by Michael Amadi Wednesday, October 15, 2014 8:09 AM
    • Marked as answer by SKashat Wednesday, October 15, 2014 11:14 PM
    Wednesday, October 15, 2014 4:21 AM
  • Skashat,

    Any 32-Bit application is limited to 2GB in Memory and Power Pivot relies on being able to keep all data in Memory. My understanding during the time of refresh is that it actually creates a copy of the dataset (this is done so that if the refresh fails, Power Pivot can fall back to the original copy), and then tries to refresh the new copy. Thus the memory requirements during a refresh are higher. You are most likely hitting the 2GB memory limit self-imposed by 32-bit.

    You can try the ideas suggested in this article: Workarounds for “Canceled Due to Memory Pressure” (like using 64-bit :-))

    As to how other users may be able to pull 1M+ rows (it's even possible with 32-bit version). It all depends on your data: there are multiple factors: but number of columns is key. 
    This article has more info Less Columns, More Rows = More Speed!
    Some more reading on Performance with Power Pivot


    Regards, Avi www.powerpivotpro.com

    In addition to what Avichal has already mentioned, you may also find this Microsoft Office help topic useful which also applies to the Excel 2010 versions of Power Pivot: Create a memory-efficient Data Model using Excel 2013 and the Power Pivot add-in.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by SKashat Wednesday, October 15, 2014 11:14 PM
    Wednesday, October 15, 2014 8:09 AM

All replies

  • Skashat,

    Any 32-Bit application is limited to 2GB in Memory and Power Pivot relies on being able to keep all data in Memory. My understanding during the time of refresh is that it actually creates a copy of the dataset (this is done so that if the refresh fails, Power Pivot can fall back to the original copy), and then tries to refresh the new copy. Thus the memory requirements during a refresh are higher. You are most likely hitting the 2GB memory limit self-imposed by 32-bit.

    You can try the ideas suggested in this article: Workarounds for “Canceled Due to Memory Pressure” (like using 64-bit :-))

    As to how other users may be able to pull 1M+ rows (it's even possible with 32-bit version). It all depends on your data: there are multiple factors: but number of columns is key. 
    This article has more info Less Columns, More Rows = More Speed!
    Some more reading on Performance with Power Pivot


    Regards, Avi www.powerpivotpro.com

    • Proposed as answer by Michael Amadi Wednesday, October 15, 2014 8:09 AM
    • Marked as answer by SKashat Wednesday, October 15, 2014 11:14 PM
    Wednesday, October 15, 2014 4:21 AM
  • Skashat,

    Any 32-Bit application is limited to 2GB in Memory and Power Pivot relies on being able to keep all data in Memory. My understanding during the time of refresh is that it actually creates a copy of the dataset (this is done so that if the refresh fails, Power Pivot can fall back to the original copy), and then tries to refresh the new copy. Thus the memory requirements during a refresh are higher. You are most likely hitting the 2GB memory limit self-imposed by 32-bit.

    You can try the ideas suggested in this article: Workarounds for “Canceled Due to Memory Pressure” (like using 64-bit :-))

    As to how other users may be able to pull 1M+ rows (it's even possible with 32-bit version). It all depends on your data: there are multiple factors: but number of columns is key. 
    This article has more info Less Columns, More Rows = More Speed!
    Some more reading on Performance with Power Pivot


    Regards, Avi www.powerpivotpro.com

    In addition to what Avichal has already mentioned, you may also find this Microsoft Office help topic useful which also applies to the Excel 2010 versions of Power Pivot: Create a memory-efficient Data Model using Excel 2013 and the Power Pivot add-in.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com
    Blog: http://www.nimblelearn.com/blog
    Twitter: @nimblelearn

    • Marked as answer by SKashat Wednesday, October 15, 2014 11:14 PM
    Wednesday, October 15, 2014 8:09 AM
  • Thank you both for your help.  I've tried some of these and I wish I was able to eliminate more columns, but am running at bare bones.

    I think in the end, I either need to break this up into smaller segments (multiple tables/databases), or wait until my work upgrades to a 64-bit Office suite.

    Thank you again!


    - Simon

    Wednesday, October 15, 2014 11:17 PM