Providing large Excel Spreadsheets to the End User

[MPA331]

Hello! First time poster, long time reader.

First, I apologize if this is not the right location for this post. I wasn't completely sure where to post this question.

My role has required me to move away from working with simple Excel files to working with large sets of metadata. I've been learning incrementally through various online resources. I am currently approaching my project using Excel along with Power Query/Power Pivot, but I suspect my problem is actually part of a bigger issue.

Here's my immediate issue:
I have a folder containing excel workbooks with raw data, one workbook for each months data, roughly 2mb per file. I am using Power Query to pull from this folder and shape my data. The issue I am having is that once I have pulled the data, I am left with roughly 3.5mm rows of data and Excel runs out of memory and cannot process.

The finished report will be used by roughly 80 different account managers to track sales. Each account manager does not necessarily need all of data for an account that isn't theirs. So, I thought a possible solution might be to create a form through VBA where the Account Manager could select just the information that was relevant to them and refresh the query that way? The end result would be a report that the end user would input their dimension criteria to refresh the query which would feed into my Power Pivot Data model and give them insights through the Pivot Tables I've created.

I really don't know if this is the best approach to my situation, but I'm at a loss here with how to approach this project. I've been learning database structure and design in bits and pieces online, but before I spend any more time spinning my wheels, I wanted to reach out to the community and get some feedback on my situation.

Any feedback is appreciated!

Thank you!

MPA

 

[mole999]

what is your data source ?

 

[SpillerBD]

At that scale of data you should be using 64-bit versions, which just making it to that type of result suggest you already are. However the memory available on your machine and consumers of your PP may also need to increase unless you go to a hosted webpage solution (see PowerBI.com and PowerPivotPro.com)
Because the number of columns matters so much its hard to say just how much memory would be needed, but my 8GB seems to run out kinda early on what I consider small projects.

You might set up the PQ for each account/manager so that unneeded data isn't loaded.

Rob Collie (and Avi Singh) have decent blog posts on several strategies to use in general. Avi post may help sort some of the efficiencies and they have few other blog post on the subject.

I too am getting into PQ and PP, but am a little hamperned by IT keeping us in a 32-bit world. (Also watch on the POWER BI forum.)