# Too much data for Power Pivot



## czarna dziura (Jun 3, 2015)

Hi All,

I've been tasked with creating a Power Pivot spreadsheet for my boss to summarize data, from a MS SQL server DB, at the end of each month.
However, PowerPivot doesn't seem to be able to handle the amount of data I need it to handle, and suggests upgrading to 64 bit excel or upgrading my machines memory.
64 bit excel isn't an option (strict corporate rules about the applications we can use) and, I have 16 GB of RAM in my machine, and so far Excel hasn't gotten close to any limit of available physical memory. 
I was wondering if you guys have any suggestions for how to deal with this. I was thinking, that perhaps it would be fine (i.e. Excel could handle it) if the data was pulled live from the DB instead of being imported first, but haven't found a way to do so.

Thanks in advanced!


----------



## Kyle123 (Jun 3, 2015)

Sounds like you need to do your summarising in SQL first before trying to cram everything into power pivot.

As an aside, excel can only use up to about 4gb of ram without it being 64bit


----------



## scottsen (Jun 3, 2015)

32bit is actually limited to 2gb, not 4.  There is some magic work around to increase that to 3gb -- Rob blogged that somewhere on powerpivotpro.com

If you can fight corp IT to give you a VM with 64 bit... that would be best.  The only other options are (as Kyle suggest), pre-aggregate some stuff in SQL.... or be really really careful with the "shape" of your data as it comes in.

How much data are we talking (in terms of rows, ... and columns) ?


----------



## Kyle123 (Jun 3, 2015)

Hmmm, think I knew that, dunno where I got 4gb from, probably thinking about windows


----------



## czarna dziura (Jun 3, 2015)

I'm trying to convince IT to let me use 2013 64 bit because it looks like it's limited only by the specs of the machine. 
With regards to working the data before import, unfortunately, I have no permissions aside from reading the data, so no views, tables, what have you. I was thinking, I might possibly be able to import the data in chunks (month by month) and then within excel aggregate it but when I attempt to import the data in smaller chunks it just hangs on "Import in progress"
In terms of data, I'm with a bank, so we're looking at 6 tables (minimum) for the data I need each with about 10-30 columns and 5+million rows per table.


----------



## Kyle123 (Jun 3, 2015)

Can't you just write better SQL for importing the data? You don't need views etc to summarise data, you can summarise with just SQL


----------



## czarna dziura (Jun 3, 2015)

By better SQL I'm assuming you mean the query pulls exactly the data I need. Under that assumption, no I can't, as my initial query attempt did just that, but it still was too much.
For reference it's choking at ~ 3 million rows.

@scottsen - Do you happen to know the post where that's covered? I haven't been able to find it


----------



## Kyle123 (Jun 3, 2015)

You must be able to aggregate more than that, no one is going to look through 3million records for a monthly summary. You should be able to do 90%+ of the summarising and calculations directly in SQL before it even hits Excel - you'd have to be doing something really fruity not to be able and Then you probably wouldn't be using excel


----------



## czarna dziura (Jun 3, 2015)

Part of the work is to provide the ability to change how the data is categorized easily.
Is there perhaps a way to have a base SQL query, that hasn't been executed, then when the categories are selected it updates and runs the query?


----------



## scottsen (Jun 3, 2015)

Ya, with that volume of data, I think you are just REALLY in trouble without 64 bit.  Even if you get it "working" its going to **** you off with how often it crashes.

Only other solution I can think of is to skip power pivot, and use SQL Server Analysis Services in Tabular mode.  It is effectively the same thing, and if your business already has SQL Server, they might be able to get some SSAS going.  I feel your pain with the IT fight, but... you are in trouble.  Breaking it into multiple tables won't help -- that would typically use MORE memory (based on the way power pivot compression works)

You could bring in just a subset of the data... build out your model and reports, show epic value... then use it to say "look, if you buy me a real machine... I can bring in the rest of the data..." ?

Rob's 3gb fix:  Grab Bag of Fun - PowerPivotPro PowerPivotPro   Though, seriously, you have a "real" amount of data.  You are going to want to optimize your model (some tips here Performance Archives | Tiny Lizard) to have it perform well, but... without 64 bit you are just in trouble


----------



## czarna dziura (Jun 3, 2015)

Hi All,

I've been tasked with creating a Power Pivot spreadsheet for my boss to summarize data, from a MS SQL server DB, at the end of each month.
However, PowerPivot doesn't seem to be able to handle the amount of data I need it to handle, and suggests upgrading to 64 bit excel or upgrading my machines memory.
64 bit excel isn't an option (strict corporate rules about the applications we can use) and, I have 16 GB of RAM in my machine, and so far Excel hasn't gotten close to any limit of available physical memory. 
I was wondering if you guys have any suggestions for how to deal with this. I was thinking, that perhaps it would be fine (i.e. Excel could handle it) if the data was pulled live from the DB instead of being imported first, but haven't found a way to do so.

Thanks in advanced!


----------



## czarna dziura (Jun 3, 2015)

Thanks for your help!
My fight is also hampered by the fact that I'm a summer student so I have even less bargaining power than your AVG(Joe).
I'll talk to them about using SSAS, but word on the street is that the department maintaining the SQL server is working on putting it on the main DB server (which is ~60 times slower) so I'm doubtful they'll play ball.
Thanks again both of you for your advice.

Edit: 
Just tried your tinylizard link and our overzealous filter blocked it... I can't win...


----------



## Matt Allington (Jun 8, 2015)

What I have seen work in the past with other clients is this.

1.  Get IT to agree to give you a desktop PC that is outside of corporate policy and is not part of the SOE.
2.  Load this baby up with Excel 64 bit and 16GB of memory, Quad Core processor.
3.  Build outstanding, mind blowing reports on this PC, and show them to your boss.
4.  Your boss says "please give me these reports on my PC".
5.  You say, it is technically possible but IT has put a speed limiter on my car to stop me going more than 5km per hour.  If you can help me get the speed limiter removed, I can be a super hero.
6.  You boss helps you become a super hero.


The trick is not to let IT cotton on to steps 3 - 6 when you are asking for step 1.

PS, I regularly develop workbooks with 50+ million rows of data for one of my clients.  The workbooks are normally 150 - 250 MB and work like a dream on Excel 64 bit.  Power Pivot isn't your problem, it is your IT department.  My best advice is to demonstrate the value and the rest will come.  In the absence of clear benefits, you will never beat IT.  Just show the value and the rest will flow.


----------



## czarna dziura (Jun 9, 2015)

Step 1 is a minor problem, as in IT is so strict that you can't change your desktop background without admin access. Unfortunately there's no chance they'd ever agree to give me a desktop outside of their system, as doing anything with them requires so many levels of approval, that even if I convince them to let me start the process it will be a year before it's completed, by which time I won't be with them.


----------

