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.
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
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.
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?
Could you use that to summarise the data and then connect from excel to the pivot query?