Forum Discussion

kmd's avatar
kmd
Contributor II
4 days ago

XFGETCELL(), XFSETCELL() PERFORMANCE

Hi all,

Every forecast cycle we get the same performance complaints from the same region group of users.  They are always uploading massive amounts of data and refreshing massive amounts of data in the same workbooks using the XF functionality in Excel.

To be clear, we have tried multiple times to convince them that they should be importing that data via CSV and advising that they will not get the same performance using the XF functions.  

We have also tried having OneStream re-index the databases before these cycles and we have regular daily app pool recycles.

The OneStream data logs really don't show any significant refresh issues (less than 5 minutes usually) but the one user doing all this work is insisting that it's taking hours and hours to finish refreshing and they often have to close and reopen Excel and re-launch OneStream.

We have raised multiple tickets and OneStream does not see any back end issues.

We have looked at their Excel preferences and tried different options there but no improvement.

I am 100% out of ideas so if anyone out there has any thoughts, would love to hear what you've done to resolve these issues.

I'm going to post this as well somewhere in the community where Excel discussions happen.

Thanks for any advice.

Data Integration
Data Sources
Import

1 Reply

  • T_Kress's avatar
    T_Kress
    Icon for OneStream Employee rankOneStream Employee
    4 hours ago

    You have probably already heard or considered this before, but have you tried setting up cube views that you pull into Excel for review and the data submission?  The problem with XFSET cells is the following:

    • Each XFSET and XFGET acts as an individual call to the data base, cell by cell so for large retrieves and submissions it can be less performant
    • XFSET for data submission is less controlled (frankly scary) as it is the wild west.  If a user has a base white intersection, they can effectively submit data "anywhere" which makes it far less controlled than using prescribed cube views and forms, and very challenging to audit
    • I wonder if it would perform any better from the Spreadsheet page within OS as opposed to their native Excel?

    Cube views can use sparse row suppression which can be more formant than individual XFGET and XFSET cells.  And if a user does not want to log in to OS, you can pull cube views in to Excel for users to both retrieve and submit data.

    Other than that, I am not sure of ways to improve performance for huge spreadsheets with lots of XFGET and XFSET functions.