Overview
A Velixo report's refresh speed depends on how much data it requests, how Velixo communicates with your ERP, and how often Excel recalculates the workbook. This article covers the settings and report-design techniques you can use to make reports refresh faster.
This article covers:
-
Report-design techniques that reduce how much Velixo and Excel have to process
-
Refresh settings that control how much data is retrieved
-
Connection and parallelism settings that control how Velixo communicates with your ERP
-
Additional settings for large Sage Intacct databases
Performance also depends on factors outside Velixo, including your network connection to the ERP, the ERP server's capacity, your version and edition of Excel, the amount of available RAM, and how many workbooks are open at once. The techniques below address what you can control from within Velixo and Excel.
Designing efficient reports
Use EXPAND functions instead of listing values individually
The Velixo EXPAND functions return many values from a single formula. For example, instead of entering one formula per account to list a 150-account chart of accounts, a single SI.EXPANDACCOUNTRANGE formula returns the entire range as a spill. Fewer formulas mean a smaller workbook that refreshes faster.
Use spill-aware functions
Velixo GL functions are spill-aware: they accept an Excel spill range (for example, C3#) as an argument and return a result that spills to match. Building on the chart-of-accounts example, after SI.EXPANDACCOUNTRANGE produces the list of accounts in a spill, a single SI.ACCOUNTNAME formula referencing that spill returns the name of every account at once. Two formulas replace hundreds, which reduces the processing load on both Excel and Velixo. See .
Avoid volatile Excel functions
Volatile Excel functions force Excel to recalculate the workbook more often than necessary, and each recalculation can trigger data retrieval from the ERP. Avoid these functions in reports where possible. The volatile Excel functions are:
-
NOW -
TODAY -
RAND -
RANDBETWEEN -
OFFSET -
INDIRECT -
INFO(depending on its arguments) -
CELL(depending on its arguments)
For TODAY and NOW, use the non-volatile Velixo equivalents TODAYNV and NOWNV instead.
To learn more, see the Microsoft documentation on volatile functions.
Limit the number of open workbooks
When Excel recalculates, it recalculates all open workbooks. Keeping several reports open at once can substantially slow down a refresh. Close reports you are not actively using.
Choosing the right refresh option
Use Smart Refresh
Smart Refresh retrieves only data that is new or changed in the ERP since the last refresh, so it is substantially faster than a full reload. The Refresh menu options Current Sheet, Entire Workbook, and All Open Workbooks all use Smart Refresh, and Velixo recommends them by default. Use Full Refresh (Slow) only when Smart Refresh cannot detect a change, for example after records are hard-deleted from the ERP or its data structure changes. See Refresh features (NX).
Refresh only what you need
In Velixo NX, prefer Refresh → Current Sheet or Entire Workbook over All Open Workbooks. Refreshing a narrower scope retrieves and recalculates less data.
Refresh on open instead of reloading
In Velixo NX → Options → Refresh Behavior, enabling Refresh data when opening the file performs a Smart Refresh when the workbook opens, pulling in only what is new rather than reloading everything.
Tuning how Velixo communicates with your ERP
The following settings are in Velixo NX → Options → Performance. The defaults suit most workbooks; adjust them when a report makes many data requests, or when you encounter timeouts or throttling. See Options for the full settings reference.
Maximum number of simultaneous REST API requests in Sage Intacct (default 4, range 1–32). Raising this lets Velixo send more independent requests to Sage Intacct in parallel, which can speed up refreshes. Setting it too high can trigger rate limiting from the Sage Intacct API.
Maximum number of operations per API request in Sage Intacct (default 16, range 1–32). A higher value packs more operations into each request and reduces the total number of API calls. Very large batches can make the Sage Intacct server time out on complex operations; lower this value if you see timeout errors during refresh.
Maximum number of simultaneous functions to process. Controls how many functions Velixo evaluates at once. Adjust this to match the resources available on your computer.
Options for large Sage Intacct databases
For large Sage Intacct datasets, Velixo NX → Options → Performance includes additional controls.
Use asynchronous data retrieval. Retrieves data in smaller subsets rather than all at once. This takes slightly longer but prevents the Sage Intacct server from cutting off very large requests.
Only load balances for accounts specified in the workbook. By default, Velixo retrieves and caches all account balances, which speeds up later lookups but is slow for very large charts of accounts. Enabling this option retrieves balances only for the accounts actually referenced in the workbook.
Granular Balance Loading. Restricts balance queries to specific dimensions, reducing refresh time when a single dimension is used across many balances in a workbook. It is limited to 2 dimensions, because each added dimension sharply increases the number of ERP requests Velixo makes. See Granular Balance Loading (Sage Intacct).
Global Object Filters. Applies filters to all queries Velixo makes for a given Sage Intacct object, reducing the volume of data returned. See Global Object Filters (Sage Intacct).
Clearing the cache
Velixo caches retrieved ERP data to speed up subsequent refreshes. If a report behaves unexpectedly or refreshes slowly after ERP-side changes, select Velixo NX → Options → Clear all Velixo data caches to force the next refresh to rebuild the cache from scratch.