Too much data for Power Pivot

[czarna dziura]

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]

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]

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]

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

 

[czarna dziura]

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]

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]

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]

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]

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]

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