Excel Add-in Optimization

The first thing to do when facing an Excel Add-in performance issue when using SEI Data Models is: to determine which Data Model is at fault and optimize it as explained in Data Model Optimization.

You can see the SQL Query behind the Excel Add-in Report in the SEI Log file (refer to Traces and Logs to determine where the SEI Log file is located for the Excel Add-in).

You should first optimize the Data Model and test it within SEI 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, bad 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’s strongly recommended to double check this and seek additional training to perform an appropriate setup.