Results 1 to 9 of 9
  1. #1
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    'Problems obtaining data' error in pivtbl (XP (SP2))

    I have an extremely large workbook with tables and charts being refreshed programmatically from SQL Server 2000 (via the PivotTableWizard method). The amount of data being pulled back grows each day and, in the last few days, the PivotTableWizard call to refresh one of the pivottables sporadically began raising a "Problems obtaining data" error (very informative). The query used to refresh the table returns a ton of data (18 columns and over 65000 rows); note: the fact that it returns over 65000 rows has not caused problems in the past. Anyway, when I reduce the amount of data being pulled back the query (by reducing the time span over which data is drawn), the error stops being raised. Thus, the problem does SEEM to be related to the amount of data being retrieved--but I'd like to have a better idea what exactly is going on. Does anybody know whether there's a limit to the size of queries used to refresh pivot tables? Or, does anyone have any idea what might be the specific cause of this murky error message?

    Thanks...

    Dan

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    Are you, by chance, over 65,536 rows of data?
    This is all the rows in excel and if you get to the 65,537th piece of data you will get an error.

    You might want to separate and put them into separate sheets or use something like access, which (I do not think) has this row limitation.
    Steve

  3. #3
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    Yes, we are over 65536 rows. However, we had been over that for quite a while without receiving an error. What would explain that? Also--does that mean that, even if we didn't get an error, the pivot table was not reflecting the information past the 65536th row?

  4. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    You can have millions of rows of data in your source for the pivot table e.g. customer orders.
    But if you are displaying the pivot table in Excel, you can't have more than 65536 unique e.g. customers in a row field.

    zeddy

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    That is incorrect. You can ONLY have 8000 unique rows in a pivot table. This might be part of the issue, though I thought the error was different than the one that was listed.

    Steve

  6. #6
    2 Star Lounger
    Join Date
    Sep 2002
    Posts
    180
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    Thank you for the input. Here is some more information on the problem.

    Based on several experiments, I don't think the problem is caused by the number of items in the row fields, nor even, strictly speaking, in the number of rows of raw data being retrieved. The sequence of events I'm seeing is the following:

    1. After retrieving data over a smaller time span, the workbook size is approximately 80 MB.
    2. I then lengthen the time interval to its original value, and run the refresh successfully. At the end of this, the size is about 92 MB.
    3. I then run the refresh. All of the tables appear to refresh successfully. However, at the end of the refresh, the code loops through all the pivottables in
    the workbook and replaces "Grand Total" with "Total". On certain of those replaces, Excel raises an "Out of Memory" error (despite the fact that the
    the task manager shows 500 MB physical memory still available). When I look at the Excel process at that point, it shows that it's got about 297 MB in memory.
    4. Despite the "Out of Memory" errors, at the end of the second rerun, the tables appear to be refreshed. However, the workbook has now grown to 127 MB!
    5. If I run the refresh one more time, that's when I get the "Problems Obtaining Data" error.

    Any thoughts would be greatly appreciated.

    Dan

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    Sounds like a memory issue and microsofts inability to clean up memory. You might try importing into another program that can handle the large datasizes first and then possible use in excel or jsut use a good database (access for example) should be abel to handle it and (I think) can do cross-tab queries which are similar to pivots.

    I don't use access so my knowlege is sparse on this.

    Steve

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    If your data is coming from SQL Server2000 then this is an OLAP data source.
    You can create an OLAP cube to 'pre-process' your data before you analyse in Excel.
    (Use the OLAP Cube Wizard in Microsoft Query)
    To avoid memory problems you can use page fields to limit the amount of data retrieved at anyone time.

    zeddy

  9. #9
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,820
    Thanks
    133
    Thanked 481 Times in 458 Posts

    Re: 'Problems obtaining data' error in pivtbl (XP (SP2))

    Ooops! Steve you are correct.
    I should've said you can only have a maximum of 8000 items in a single pivot table.
    You can have many as pivot tables on a sheet as memory permits.
    Pivot tables can be based on the same source etc.

    zeddy

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •