none
Persistent out of memory errors...have I exhausted the capcity at 55,000 rows???

    Pertanyaan

  • I have a calculated column as follows

    =CALCULATE(countrows(distinct(users[User ID])),filter(transactions,transactions[Date]>DateKey[Day]-179 && transactions[Date]<DateKey[Day]+1 && transactions[Transaction]="spend"))

    DateKey[Day] is daily dates from Jan2011 to now so 800 rows. The transactions table is being filtered for each day of DateKey to a range of 180 days before and the unique users who have a "spend" transaction counted.  This works fine.  If I create a new column and put a similar formula in I get out of memory errors. If I reduce the day adjustment ot 100 it works, make it 120 it fails.  If I close down excel and reopen I might eventually get the next column to work but then fails on the next column.

    The tables I have are 55,000 rows of transactions, but do have 4 other tables with lots of other calculated columns.  I am using 32-bit office on  64-bit Win 7 Pro and 16GB of RAM. Is this to be expect for this sort of calculation? Is memory being used up by other calculated columns or not relevant how much other stuff there is?  I am trying to get 64-bit office running on a VM, but having real problems installing the OS on the VM so for time being that is out. i recently uninstaleld powerpivot and reinstalled on the advice of one of the techs due to frequent crashes on my pivot tables. Thanks for any advice.

    The error I get is

    ============================
    Error Message:
    ============================

    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
    The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
    The current operation was cancelled because another operation in the transaction failed.
    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.

    ----------------------------
    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.
    The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation.
    The current operation was cancelled because another operation in the transaction failed.
    The operation has been cancelled because there is not enough memory available for the application. If using a 32-bit version of the product, consider upgrading to the 64-bit version or increasing the amount of memory available on the machine.


    ============================
    Call Stack:
    ============================

       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteCaptureLogAndProcessResults(OperationType type, Boolean cancellable, Boolean raw, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.ExecuteAMOCode(OperationType type, OperationCancellability cancellable, AMOCode code, Boolean raiseEvents)
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
    ----------------------------
       at Microsoft.AnalysisServices.BackEnd.SandboxTransaction.CommitInternal(Boolean finalCommit)
       at Microsoft.AnalysisServices.Common.SandboxEditor.ChangeFormula(TableWidgetPanel currentTable, IList`1 colIndices, IList`1 names, IList`1 expressions, Boolean doFormulaBarCommit, IList`1 displayIndices)

    ============================

    10 Mei 2012 12:38

Jawaban

Semua Balasan

  • How large is your workbook? With 32-bit Excel, the largest workbook is around 500M or so.

    -- This posting is provided "AS IS" with no warranties, and confers no rights

    10 Mei 2012 22:23
  • 22Mb. Did seem that big givne this was designed for millions of rows and presumably hundreds of measures.
    11 Mei 2012 8:17
  • from your other post, it could be caused by your newly created named sets.

    -- This posting is provided "AS IS" with no warranties, and confers no rights

    13 Mei 2012 15:42
  • Hi Chu. The history is as follows in full

    1. started building in 32-bit
    2. Created name sets as wanted my dates in reverse order
    3. As workbook got larger kept getting this random adding of name set to pivots on opening. Usually wind back a few versions and started working again.
    4. At point above i started getting the out of memory error consitently, but had had odd times all the way along even when book was only 10Mb.
    5. Installed a 64-bit Office on a VM. Forgot to change date format. started working in that and seemed fine with no out of memory errors. 
    6. Started getting name set issue again this time with no way out until I wound back 20 versions to the previous day.

    So my issues with the name sets started really early on in this process beofre I started on 64-bit.  I'm still surprised I'm having to work in 64-bit as I would not have thought my workbook was particularly big given the scale this product was designed for, but could be wrong. I'm suprised my pivot tables change without me touching anything. I'm surpised I can't fix it by rolling back one version.

    Maybe somehwere I confused it, but that makes it pretty unreliable.  The recovery is getting more and more painful each time. Despite aligning my 64-bit and 32-bit versions I expect this to keep happening.

    13 Mei 2012 16:07
  • Masplin,

    Is this still an issue?

    Thank you!


    Ed Price, SQL Server Customer Program Manager (Blog, Small Basic, Wiki Ninjas, Wiki)

    Answer an interesting question? Create a wiki article about it!

    04 Nopember 2013 0:50
  • so long ago i guess not!!!
    04 Nopember 2013 8:39