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!

 

[czarna dziura]

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]

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]

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.