Pivot Table Prb: "not enough memory"
Stratis Bahaveolos
I have a pivot table that uses an MS Access DB as it's data source. The
table it reads in from is approx 350,000 records long. The query brings in
about 20 variables. The workbook the pivot table is in has been used for
the past 10 months with little trouble. There are several sheets with pivot
tables in them that are all based off of the master pivot table (the data
source is "another pivot table" for all 'linked' sheets). There are charts
and different ways to look at the date. The file is roughly 15mb. I save
the data with the table (users need to drill down). File is in MS Excel 97
format, company standard.
Problem:
The table is growing over time. Over the past 2 weeks, I have gotten "Not
Enough Memory" Continue without undo? errors 4 times. I have fixed them by
dropping variables/columns in the query that I bring forward in the master
pivot table. I'm worried cause I'm out of variable I can drop and I suspect
this will happen again soon. Does anyone have any idea how to get around
this problem?
I have tried running it on different machines (some have 256 mb of RAM). No
effect.
Different OS/s 95, NT 4.0, NT 3.5. No effect.
I can't use server page fields cause of the linked sheets.
Any ideas?
thanks in advance.
stratis bahaveolos
Dir, Info systems
MacNeal Health Network
sbah...@macneal.com
TroyWxyz
on the size of pivot tables. I copied this from their knowledge base.
Troy
------------------------------------------------
DOCUMENT:Q157486 15-JAN-1998 [excel]
TITLE :XL97: Limits of PivotTables in Microsoft Excel 97
PRODUCT :Microsoft Excel
PROD/VER:WINDOWS:97
OPER/SYS:WINDOWS
KEYWORDS:xlpivot kbtool
======================================================================
--------------------------------------------------------------------
The information in this article applies to:
- Microsoft Excel 97 for Windows
--------------------------------------------------------------------
SUMMARY
=======
This article discusses some of the limitations of PivotTables in Microsoft
Excel 97.
MORE INFORMATION
================
In Microsoft Excel, PivotTables are interactive tables that quickly
summarize, or cross-tabulate, large amounts of data. Within a PivotTable,
you can rotate its rows and columns to see different summaries of the
source data, filter the data by displaying different pages, or display the
details for areas of interest.
What Are the Limits of PivotTables That I Should Know About?
------------------------------------------------------------
The following limits apply to PivotTables in Microsoft Excel:
Maximum Size
------------
In PivotTables in Microsoft Excel, there is no fixed maximum size. The
maximum size is usually limited only by the amount of available memory
in your computer.
Column Fields
-------------
The product of the number of items in all column fields within a
PivotTable cannot exceed 32768.
For example, suppose you create a PivotTable that contains five column
fields. The fields contain 10, 5, 2, 40, and 3 items respectively. The
product of these values is 10 x 5 x 2 x 40 x 3, or 12000.
If you tried to add one more field that contained 3 items, the product
would be 12000 x 3, or 36000. Since this exceeds the maximum product,
you would receive the error message:
Not enough memory to completely display PivotTable.
Note that worksheets in Microsoft Excel are limited to 256 columns.
Because of this, even if you are successful in creating a PivotTable
that contains a large number of column fields, you may not be able to
display the entire expanded PivotTable.
Row Fields
----------
The product of the number of items in all row fields within a
PivotTable cannot exceed 2^31 (2 raised to the 31st power), or
approximately 2.1 billion. The same logic that applies to column
fields also applies to row fields.
Maximum Number of Records
-------------------------
There is no fixed maximum number of records that can be used when
creating a PivotTable.
In practice, creating a PivotTable from an external database that
contains a very large number of records can be a great strain on
the workstation where Microsoft Excel is running, and can take a
very long time to complete.
If you are creating a PivotTable from a very large database, you may
want to use server page fields in your PivotTable. See the next section
for information on how to use server page fields in PivotTables.
How Can I Keep from Running into These Limits?
----------------------------------------------
One way to avoid running into these limits is to use page fields in
PivotTables, especially if fields contain more than 40 unique items. Using
page fields makes your PivotTable more memory-efficient and reduces the
size (in cells) of the PivotTable, making it easier to read.
Another way to optimize your PivotTable is to use server page fields.
For additional information on how to use server page fields in Microsoft
Excel, please see the following article in the Microsoft Knowledge Base:
ARTICLE-ID: Q157488
TITLE : XL97: Using Server Page Fields in PivotTables
REFERENCES
==========
For more information about PivotTables in Microsoft Excel, click the Index
tab in Microsoft Excel Help, type the following text
PivotTables, overview
click Display, click "PivotTables: Analyzing data interactively", and then
click Display.
Additional query words: 8.00 XL97 pivot tables table
======================================================================
Keywords : xlpivot kbtool
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbprb
=============================================================================
Copyright Microsoft Corporation 1998.
Deb McCleary
Wow, you have some kind of user systems if a 15mb file hasn't been crashing
all over the place!! <g>. I am a diehard Excel user, but it sounds to me
like you have reached the limits - you need to use Access for your
reporting! Alternatively, you could switch to some specialized tools -
Cognos has some Data Warehouse user interface software (Powerplay,
Impromptu), that I believe would do what you are describing (charts,
drilldown, etc).
Alternatively, you will need to split your pivot table into a couple of
pieces (assuming the data lends itself to this), and have more than 1 file.
Deb McCleary
The Report Builder Service
http://home.istar.ca/~debast/TRBSpage/
Stratis Bahaveolos wrote in message <6qfem9$s1f$1...@news1.anet.com>...
>Background:
>I have a pivot table that uses an MS Access DB as it's data source. The
>table it reads in from is approx 350,000 records long. The query brings in
>about 20 variables. The workbook the pivot table is in has been used for
>the past 10 months with little trouble. There are several sheets with
pivot
>tables in them that are all based off of the master pivot table (the data
>source is "another pivot table" for all 'linked' sheets). There are charts
>and different ways to look at the date. The file is roughly 15mb. I save
>the data with the table (users need to drill down). File is in MS Excel 97
>format, company standard.
>
>Problem:
>The table is growing over time. Over the past 2 weeks, I have gotten "Not
>Enough Memory" Continue without undo? errors 4 times. I have fixed them by
>dropping variables/columns in the query that I bring forward in the master
>pivot table. I'm worried cause I'm out of variable I can drop and I
suspect
>this will happen again soon. Does anyone have any idea how to get around
>this problem?
>
>I have tried running it on different machines (some have 256 mb of RAM).
No
>effect.
>Different OS/s 95, NT 4.0, NT 3.5. No effect.
>I can't use server page fields cause of the linked sheets.
>
Ogilvy, Thomas, W., Mr., ODCSLOG
Server Page Fields is the only solution I could think of - if the table
is too big, it is too big. But you said you could not use server page
fields. The only other alternative might be to write VBA code to
dynamically create your pivot tables so that you can use server page
fields.
The following article references might give you some insight. It sounds
like you have done your research, so I apologize if you are already
familiar with these - just in case you are not.
http://support.microsoft.com/support/kb/articles/q162/4/76.asp
XL97: Purpose of the PivotTable Optimize Memory Option
http://support.microsoft.com/support/kb/articles/Q157/4/86.asp
XL97: Limits of PivotTables in Microsoft Excel 97
http://support.microsoft.com/support/kb/articles/Q157/4/88.asp
XL97: Using Server Page Fields in PivotTables
http://support.microsoft.com/support/kb/articles/q104/3/08.asp
XL: Optimizing PivotTable Performance
This wasn't working today - but perhaps it will be fixed:
http://support.microsoft.com/support/tshoot/pivot97.asp
Resolve Unusual Behavior in a Microsoft Excel PivotTable
Regards,
Tom Ogilvy
> ----------
> From: Stratis Bahaveolos[SMTP:sbah...@macneal.com]
> Posted At: Friday, August 07, 1998 1:48 PM
> Posted To: misc
> Conversation: Pivot Table Prb: "not enough memory"
> Subject: Pivot Table Prb: "not enough memory"