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...
Data Integration
Data Sources
Import
T_Kress's avatar
T_Kress
Icon for OneStream Employee rankOneStream Employee
5 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.