Solved

Pivot Table Using 1.6Gigs of RAM When Connected To Access

Posted on 2011-02-14
7
468 Views
Last Modified: 2012-08-14
I'm having performance issues with Excel Pivot Tables connected to a large Access table.

I manage two Access databases of ledger details that are used by 15 people both in the US and India. The first database processes the data (Extract, Transform, Load), and then outputs a final table to a second standalone  database for users to connect to with Excel Pivot Tables over the network. The final table currently has 508,000 records with 42 columns, and with about 50,000 records added each month, I have 2 more months to go in this fiscal year (3/31 year end).

When the Pivot Table connects to the data, it scans the entire table and loads it into the Pivot Cache. Looking at the memory usage for the Excel Instance in Task Manager, it is exceeding a whopping 1.6 gigabytes.

To reduce the size of the Pivot Cache, i'm considering:
1. Creating queries based on the Output Table so users can connect to smaller data sets, or
2. Allow users to choose dimension elements in either a Form, List Box, or Combo Box that would then use VBA to limit the data returned to the Pivot Cache.

How should I handle it?

0
Comment
Question by:Davisron867
  • 3
  • 3
7 Comments
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 400 total points
Comment Utility
Both of your suggestions appear to do the same thing:
Filter (limit) the records before being feed to the Pivot table.
(Although I am not quite sure what you mean by "dimension elements" in this context...)

The simpler test to try first would be the query/filter.

Can I ask why you can't do the Pivot in Access?
This may save resources involved with "Bridging" the data from Access to Excel...
Since Access 2003 Pivots in Excel and Access are just about the same. (in terms of final output)

Finally, you can also look into "Crosstab Queries" in Access.
They are what was used before Pivot Tables, and present similar outputs.
http://www.databasedev.co.uk/crosstab_queries.html
http://support.microsoft.com/kb/304348

JeffCoachman
0
 
LVL 45

Assisted Solution

by:patrickab
patrickab earned 100 total points
Comment Utility
Davisron867,

That is a very broad question. Perhaps you need to go back a step and ask for what purpose someone needs a Pivot Table. Having determined that then it would make sense to limit the data that is scanned to that which fits the criteria specified by the Pivot Table requester. At 1.6Gb it is massive and pretty well unusable at that size. Apart from which how does anyone make sense of so much data? Thus the need to whittle it down to only what is really needed.

Patrick

0
 

Author Comment

by:Davisron867
Comment Utility
boaq, patrick:

Thanks for your replies.

I currently use Excel pivot tables for the flexible and fast drill-down feature, essentially producing a cube that behaves like Essbase. The Pivot Tables drive all of the user's reports, which they update by refreshing each month.

So, for example, an accrual team scans ledger balances firm-wide to determine if accruals are missing or wrong. They may need to investigate the details of 20 balances in 20 minutes. This tool makes it happen.

Some people write commentary on material month-to-month variances, wherever they appear. Often, the delta can only be understood by reviewing the transaction details. Others look at only individual businesses or account areas.

If I was just managing the process for myself, I'd just filter the data as needed when creating the pivot table. An ideal fix would be to detect the parameters selected in the Pivot Table and shape the pivot cache to contain only that data. So for example, if the Pivot Table had [Region] = "US" as the page field, an event would capture that choice and cache only those records.

Another idea i had was to see if a pivot table can use an unbound recordset that uses ADO to feed the cache every time the parameters of the Pivot Table are chosen. I want to make it simple if I can for the non-techies, but, not being a professional programmer, I lack the experience to know if this or that will work well.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 74

Assisted Solution

by:Jeffrey Coachman
Jeffrey Coachman earned 400 total points
Comment Utility
Again, why not use Access Pivot tables?
0
 

Author Comment

by:Davisron867
Comment Utility
Access pivot tables only allow up to 3 row fields which is wholly inadequate for my purposes.

Everyone uses Excel exclusively to review, manage, report, and share information. I'll use Access for its efficient query engine and data storage, but all in the service of extending Excel's abilities. We've no functional reason to use it as a front-end.  

I parsed the data into queries that return smaller, more manageable record sets and instructed people to use the queries instead. Its an easy fix. I am also going to pursue a strategy of using a control sheet in our Excel workbooks to allow users to choose parameters to filter the data on the workbook level, and I'll use code to insert the parameters into the connection string.
0
 

Author Comment

by:Davisron867
Comment Utility
Actually I mis-spoke.

For simplicity's sake, I should always consider the existing tools in an application before developing a custom solution. I need to look at the functioning of Access pivot tables more closely.

Thanks for the tip Boaq2000.
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
Comment Utility
ok
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…

772 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

11 Experts available now in Live!

Get 1:1 Help Now