Lots of Excel Pivot Tables, one data source and File size - Best solution?
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
The problem
I need the most efficient solution fulfilling the following constraints:
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?
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
Hi Simon,
I'm not sure how exactly you think this will work. I have 1xx different pivot tables summarizing data differently - would you suggest I create 1xx different summary-queries in Access to be used in excel instead of excel pivots? I'm sure this is not what you mean, since this will remove the flexibility of pivot tables in excel, but could you please elaborate?
I'm not sure how exactly you think this will work. I have 1xx different pivot tables summarizing data differently - would you suggest I create 1xx different summary-queries in Access to be used in excel instead of excel pivots? I'm sure this is not what you mean, since this will remove the flexibility of pivot tables in excel, but could you please elaborate?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Rory!
Thanks for your information. I am now trying to link the pivots, and it works great. Although one problem and therefore question has arisen, and that is it seams that when done this way, the excel file establishes some sort of connection to the access db, so that I am not able to open the access file in unshared mode - which is a necessity since I need to perform various updates along the way in the db - and there is always someone having the excel dashboard open.
Isn't it possible in excel to have the data refreshed once by pressing "Refresh All", and then cut the DB connection?
I guess there might be a setting somewhere?
Regards
Thanks for your information. I am now trying to link the pivots, and it works great. Although one problem and therefore question has arisen, and that is it seams that when done this way, the excel file establishes some sort of connection to the access db, so that I am not able to open the access file in unshared mode - which is a necessity since I need to perform various updates along the way in the db - and there is always someone having the excel dashboard open.
Isn't it possible in excel to have the data refreshed once by pressing "Refresh All", and then cut the DB connection?
I guess there might be a setting somewhere?
Regards
ASKER
Confirmed me in my thoughts.
The solution is running completely as expected today with no problems what so ever. Perfect!
The solution is running completely as expected today with no problems what so ever. Perfect!
Could you use that to summarise the data and then connect from excel to the pivot query?