Forum Discussion

kmd's avatar
kmd
Contributor II
10 days ago
Solved

Excel Optimization for XFGETCELL() / XFSETCELL()

Hi all,

Not sure if I'm in the right spot to post this question but here goes.

I recently downloaded a word document off the OneStream site (I think from Knowledge Base) called "Optimizing Excel for Quick Views and XFGETCELL()".

In that document there was a recommendation to change the default value from 2 to 4 in the Environment / Configuration Tab/ MultithreadingSettings property / "NumThreadsForGetDataCells" and "NumThreadsForSetDataCells".

Has anyone else tried this yet and did you realize any performance improvement in large Excel XFGETCELL() workbooks?

On a related note, does anyone have any other recommendations for optimization of Excel beyond the usual that has been presented here (i.e. Manual Calculation = on, Various preference settings, Refreshing in the Spreadsheet tool instead of in Excel, etc. etc.).

I feel like we've tried everything suggested at this point, except for the NumThreadsForGetDataCells configuration.

Thanks in advance for any advice.

Excel Add In
  • MarcR's avatar
    MarcR
    9 days ago

    Hi kmd, i havent tried this setting but i do have experience that switching from XFGetCell to XFGetCellUsingScript improves performance on sheets with many retrieve cells.
    With this formula you put your main script in a single cell like A#123:S#Actual:T#2025M1 etc and then only do an override for the row/ column that you are on. 

  • kmd's avatar
    kmd
    Contributor II
    9 days ago

    Hi MarcR,  that's really interesting.  I've not used that function previously but it makes sense as this allows a POV like we have with cube views.  I'll give that a try - thanks for the tip.  Curious to know the number of cells with XFGETCEL() functions you're using as a comparative to the sizes of our own big workbooks.

    As an FYI on the NumThreadsForGetDataCells - we've kind of looked into on our side and it appears that this is something we'll need to get OneStream to modify for us.

    Thanks again for this tip.  Looking forward to testing this

  • MarcR's avatar
    MarcR
    VIP
    9 days ago

    Hi kmd, i havent tried this setting but i do have experience that switching from XFGetCell to XFGetCellUsingScript improves performance on sheets with many retrieve cells.
    With this formula you put your main script in a single cell like A#123:S#Actual:T#2025M1 etc and then only do an override for the row/ column that you are on.