Solved

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

Posted on 2014-01-02
5
246 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

739 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