SAP Analytics Cloud, add-in for Microsoft Excel - New high-volume mode

[alexander_peter]

Since the first release in 2020, SAP Analytics Cloud, add-in for Microsoft Excel has matured to a powerful analytics and planning client embedded in Microsoft Excel.

After adding features like data point commenting, restricted measures or VBA support, we see a lot of customers using the add-in for highly critical business processes such as planning.

However, extracting a high number of cells (>500.000) has led to some challenges. The main challenge is a memory limitation that browsers support for one instance: 4GB. And note: even within Excel desktop, the add-in is running in a browser runtime (i.e., WebView2 or WebKit2). With the given architecture, the add-in could hit this limit easily with about 500.000 to 1.000.000 cells. When this limit was hit, Excel restarts the add-in.

There are scenarios where you need to combine detailed information and the flexibility of Excel calculations. We have been approached by many customers to increase the number of cells that can be displayed in SAC add-in. After a thorough analysis, we chose the way to bypass the SAP Analytics Cloud table (used in the story), which is currently the basis to render the table in Excel. It provides lots of features like number formatting, frontend calculations (insert new rows/columns including formulas), planning and many more. The disadvantage is that this SAC table consumes more memory than necessary for Excel use cases. In the SAC story, this is not so much of a problem as the tables can be read in paging. In Excel, however, the entire table needs to be loaded to support calculations on it.

Our approach is to optimize memory usage by directly reading the pure data information and adding some features on top which were previously offered by SAC table. We decided to have an option to switch from the standard table (using SAC table API) to a memory- and performance-optimized version of it (and back).

In the first version we limited the features for the high-volume mode to the essential ones. For example, planning is not supported – as we assume no planner works on input sheets with millions of cells. Also frontend calculations and comments are not supported in this mode. There are also some limitations that we plan to fix soon such as properties.

In our internal test scenarios we have seen memory consumption reduced – in comparison to the standard mode – by up to factor 10. We could also see performance improvements (end to end) by up to 30% (note that backend and rendering time stay the same, only calculation time is reduced). Of course, for smaller result sets, the difference between the mode is not significant.

In MDS (HANA multi-dimensional services), you will also hit some default values such as the maximum result set size which is set to 1 million cells (result_set_size_max_default). In order to support larger datasets, SAC add-in overrides this value in high-volume mode with the absolute maximum of 10 million cells (result_set_size_max_limit, see SAP note 2770570). The other value for cell size (only needed for internal server calculations) is not changed. As the memory allocation has been optimized, you can also add several tables with altogether more than 10 million cells into the workbook, while for one table the maximum stays with 10 million cells.

In order to activate the high-volume mode, you can use the context menu of the table.

You can switch back and forth between the modes. The default for new tables is the standard mode. You can use the pause/refresh feature for adding a new table and switch them to high-volume mode before the table is rendered.

When comparing performance, you can use the profiling information that you can activate in the workbook preferences.

This generates an additional sheet that provides information how much time is spent in different layers and how much memory is used.