troubleshooting Question

Lots of Excel Pivot Tables, one data source and File size - Best solution?

Avatar of Raahaugen
RaahaugenFlag for Denmark asked on
Microsoft AccessMicrosoft Excel
5 Comments1 Solution295 ViewsLast Modified:
Hi Experts,

I have a setup with Access 2007, Excel 2007 and 1xx pivot tables. I need an efficient solution minimizing network-transfer for the 100 users while maximizing flexibility.

The setup
Excel 2007 as dashboard currently having 1xx different pivot tables based on just 3-4 different datasources
Access 2007 Datasource - data is updated daily. 3-4 queries are used as datasources for the pivot tables, row and column count varies between (rows*columns) 100*50 and 100.000*300
100 different users of the dashboard on external locations - most using broadband connections to open the dashboard

The problem
I need the most efficient solution fulfilling the following constraints:
...to keep the network-transfer requirement down (small file-size etc.),
...to let users download (or update) the file once and afterwards be able to use it offline (including "playing" with the pivot tables),
...and I need to be able to run the daily update using a windows scheduled batch.
Although the users does not necessarily have a good network connection, the computer updating the datasources (and currently also the excel file) has a fast connection to the network. So the more data processing etc. that can be done beforehand, the better.

What I do today:
Using VBA I copy data from the access database into local excel tables using "copyfromrecordset" procedure and afterwards refresh the pivot tables, whose datasources is the local excel tables. Finally I delete all the rows in the tables and save the excel file (method to lower the file size - the pivot tables keep the data in the shared cache). The users then accesses the file through a shortcut opening excel in read-only mode.

My Thoughts
Of course I would like you to think out of the box not getting biased by what I do already, but either way my thoughts are that I would like to kick the local excel tables and let the pivot tables use access directly as data source. It seams inefficient to have data copied into local tables first - also it is not efficient if I need to add or remove columns from some data sources. But letting every pivot table use their own data cache storing a copy of the data source, this would create a giant file - although I have not yet tried this!

What are your thoughts?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros