Ways to Improve Performance

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 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 EXPANDACCOUNTRANGE produces the list of accounts in a spill, a single 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).

For generic inquiries, Smart Refresh must be configured per inquiry: each one needs key columns defined and has its own Refresh Mode setting in Velixo NX → Options → Generic inquiries options.

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.

Filtering generic inquiries before download

Generic inquiries can return very large result sets. Filtering the data down to only what the report needs significantly reduces load time, because the filter is applied by Acumatica before the data is downloaded. Even though Acumatica's OData feed is fast, retrieving tens or hundreds of thousands of records is slow.

If you experience long load times for inquiries with a very large result set (for example, GL transactions or invoices), always supply a filter using the GIFILTER function in your GI or GILOOKUPF formulas. See GIFILTER and Filtering Generic Inquiries.

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 functions to process. Controls how many functions Velixo evaluates at once. Adjust this to match the resources available on your computer.

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.