Solved

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

Posted on 2014-01-02
5
213 Views
Last Modified: 2014-10-02
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?
0
Comment
Question by:Raahaugen
  • 3
5 Comments
 
LVL 15

Expert Comment

by:Simon Ball
ID: 39750978
quick thought.. there is a pivot / crosstab query function in Access.

Could you use that to summarise the data and then connect from excel to the pivot query?
0
 

Author Comment

by:Raahaugen
ID: 39751029
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?
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 39751189
You can link the pivot tables to the Access queries directly. Tables using the same query should share the same cache automatically.
0
 

Author Comment

by:Raahaugen
ID: 39764582
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
0
 

Author Closing Comment

by:Raahaugen
ID: 40356512
Confirmed me in my thoughts.

The solution is running completely as expected today with no problems what so ever. Perfect!
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

943 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now