Solved

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

Posted on 2014-01-02
5
203 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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

757 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

24 Experts available now in Live!

Get 1:1 Help Now