Excel Add-in Optimization

The first thing to do when facing an Excel Add-in performance issue when using data models is to determine which data model is at fault and optimize it as explained in Performance.

You can see the SQL query behind the Excel Add-in Report in the Nectari log file (refer to Nectari Logs to determine where the log file is located for the Excel Add-in).

You should first optimize the data modeland test it within Nectari before trying it again on the Excel Add-in.

Use the following guidelines if the data model related to an Excel Add-in report is already optimized and there are performance issues.

1. Check the number of Excel formulas for the report.
   Note

   Most of the time, poor Excel Add-in performance is related to too many formulas in a single Excel file, sometimes on too many sheets.

   A good practice is to use parameters that the user can enter (for example, a combo box) not only to filter the data but also to reduce the number of formulas of the Excel file and simplify its use.

2. Check the Excel Add-in Cache Parameter to make sure it is properly set.
   Important

   The Excel Cache setting is the source of the performance issue in many cases.

   It is strongly recommended to double check this and seek additional training to perform an appropriate setup.

To learn how to get reference and formula optimization advice from Nectari, see the Cache Optimizer page.