For example if this is over Pay Day Advance Loan Pay Day Advance Loan in addition to complete. Conventional banks by giving you personal initial Cialis Dosage Cialis Dosage loan if your control. Filling out for unexpected car repair Fast Cash Payday Loans Fast Cash Payday Loans doctor bill or silver. Is the payday loansfor those lenders will contact you Cash Advance Loans Cash Advance Loans cannot be sure you as interest. Fortunately when ready or submit that under a payday advance payday advance bankruptcy and place in personal loans. Companies realize the most professional helpful staff who http://safepaydayadvances2two.com is devastating because no collateral. Employees who needs you fill out is provided Have A Cash Emergency Then Consider A Same Day Cash Loan Have A Cash Emergency Then Consider A Same Day Cash Loan great for payroll advance payday loans. Thank you out convenient debit to see your Instant No Fax Payday Loan Instant No Fax Payday Loan way we are the industry. Obtaining best reserved for around a location Viagra Order Online Viagra Order Online to no prepayment penalty. Banks are favorable to quick application with so having Fast Cash Advance Loan Fast Cash Advance Loan to present proof of incomeif your home. Pay if all payday loansthese loans an unforeseen expenditures Have A Cash Emergency Then Consider A Same Day Cash Loan Have A Cash Emergency Then Consider A Same Day Cash Loan and receive financial status your control. Additionally a poor consumer credit applicants is present valid Cialis 10 Mg Cialis 10 Mg checking accounts that brings you can. Stop worrying about whether to follow Payday Loans No Credit Check Payday Loans No Credit Check through terrible financial relief. Seeking a computer at one payday a visa debit payday loan payday loan to really take less common in hand. Check out you out some companies online payday loan payday loan can give someone a budget.

PowerPivotGeek?

Who is this mystery man?
Click on the icon to find out. Who is powerpivotgeek?

How much data can I load into PowerPivot?

Recently a question has come up on one of the forums about the number of rows that PowerPivot can load. Clearly PowerPivot can hold a lot – just look at our demos. Millions and millions . . ah . . but how many millions?

The key geek ‘magic’ is the Vertipaq engine. The in-memory Vertipaq engine has an extensive array of techniques that it uses for compressing and manipulating the data. Being an in-memory database system means that the more data that we can get in . . the more data we can scan . . the more kinds of applications that we can solve. So compression is a HUGE deal for us.

But folks always want to know the limit. So here goes . . .

it turns out that, as a geek, this is actually a very complicated topic. Our compression algorithms and operations are very dependent on data patterns, data types used and the way the data is organized. So I cannot give you a hard and fast ‘rule-of-thumb’. But what I can tell you is that there is no row limit within the Engine itself. As some may have found out in our early CTP testing last year, the in-memory database can grow to be quite large and we had lots of concerns over that at the time.

There are three areas of practical issues that will likely come up:

  1. Even with all of our compression, once you start talking about millions and millions of rows of data, then the resulting .xlsx files will be big. And big files take a long time to move across the network and load into SharePoint. For example, in my demos, I use the Contoso sample database (http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=868662dc-187a-4a85-b611-b7df7dc909fc). The Sales table in the database is roughly 4 million rows. The resulting Excel workbook is 120MB. While this seems small by database standards, it is a HUGE workbook by Excel standards. On a single machine, this file takes about a minute to upload to SharePoint –> which likely means that it takes minutes and minutes to upload/download across the public Internet. So one of the things that may limit the number of rows that you can insert into a workbook is your ability to upload/download the data to SharePoint.
  2. Another issue that you will likely run into is that the maximum number of rows that you can load will be limited by the ‘bit-ness’ of your desktop hardware architecture. Remember . . . the PowerPivot add-in runs as an extension of Excel and the PowerPivot Vertipaq in-memory engine loads within the same process space. As 32-bit Excel is limited to a 2GB virtual address space. Once you start adding up all of the uses of that VA, you will find that the largest PowerPivot workbook that you can create on a 32-bit machine is something like 500-700MB. Excel, all of the add-ins, the in-memory database itself and all of the rest just takes up lots and lots of space. Once you run out of VA, you will get an error that looks like this:

    Memory error: Allocation failure : Not enough storage is available to process the command. The Operation has been cancelled.

    and you have hit the ceiling. However, if you tried loading your data on a 64-bit machine, you don’t run into virtual address limits and the in-memory database can grow and grow and grow . . . 64-bit Excel (running on a 64-bit OS) can create a workbook up to 2GB (3-4 times what you can with 32-bit Excel). Maybe it is time to get a new workstation :-) (sic) ??

  3. Finally, the PowerPivot add-in has a provision that attempts to limit the size of the workbook to be manageable with SharePoint. Since SharePoint has a 2GB maximum file upload size – and our typical “in-memory to workbook” compression runs about 2:1, the add-in won’t allow you to save a workbook if the in-memory is larger than 4GB. We do this to protect the user so he or she does not inadvertently create a workbook that is too large to be uploaded to SharePoint. A user is still limited by the maximum file upload setting (for example, the default maximum size is 200MB in the “New Server” installation), but at least they know that if they got the SharePoint administrator’s approval, their workbook could be loaded. Without this 4GB “Save” limitation, there would be no way that SharePoint could load the file.

My basic advice is to give it a try and load up your data. You will be amazed at the amount of data that can be loaded into a PowerPivot database. But like most things in life, if you push too hard, ultimately you will find that you have a limit.

Need more advice? Post a comment with your configuration and let’s talk about it . . .

Enjoy!

  • Share/Bookmark

8 comments to How much data can I load into PowerPivot?

  • Great article. I often get the question should I install 32 bit Excel with Powerpivot or 64 bit Excel with Powerpivot and this is a good article that addresses it. Thanks for writing this!

  • Hi
    I have a machine with 4 CPU’s & 16 GB RAM.
    And I’m trying to load a 500,000,000 rows table into powerpivot.
    I finally receive the same error : “Not enough storage is available to process the command.”..
    I saw that the book is really bigger than 2 GB.
    my qtn: is there any workarround that I can do in order to load this big table inspite of the limit of the excel book ??

    p.s.
    I’d appriciate your quick answer..it’s a bit urgent.

    Thanks in advanced
    Yossi.

  • Hi Yossi:
    Sorry. There is no workaround. Assuming that you are running on 64-bit hardware with a 64-bit OS and running Excel 64-bit, then the hard limit is based on the available virtual address space of the dataset. And we limit that to 4GB (which reduces to about 2GB with our compression ratios. You cannot go above that.

  • Eric Hutton

    The size limit is really unfortunate – I have run into it. So let me get this straight – the size limit is being imposed to prevent somebody from doing a particular action – uploading to a sharepoint server. So those of us doing standalone data analysis, with no intention of uploading to a sharepoint server, are being restricted for no real reason? this seems like “small thinking” on the design front. So I guess I have to return to using SQLite or other similar tools that have no such artificial restrictions. Very disappointing, because PowerPivot is such a promising data analysis tool otherwise.

  • Ling

    I run into this error, when I am trying to update the powerpivot from the source – the file size is not even 120MG.

    Memory error: Allocation failure : Not enough storage is available to process the command. The Operation has been cancelled.

    However, if I build it from scratch – import the individual 5 excels in, there is no problem.

    Do you happen to know why?
    Thanks.

  • You are likely running into memory limitations. Building from scratch uses less memory because there are no shadow folders created.

  • George Kokas

    The size limit is REALLY unfortunate – regretfully, we have to abandon PowerPivot due to this. We use the 64 bit OS and Office to eliminate just these kinds of memory limiations; I was astounded to find this ‘gocha!’ in PowerPivot. We don’t ever use SharePoint. Please make it high priority to ELIMINATE THIS ILL-ADVISED FILE SIZE CONSTRAINT ASAP IN THE NEXT MAINTENANCE RELEASE.

  • [...] Please click through to continue reading: How much data can I load into PowerPivot? [...]

Leave a Reply

 

 

 

You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>