The world of analytics has evolved enormously in recent years. AI-powered analytics are revolutionising the way we work with data. SAP Business Data Cloud offers seamless integration with Databricks and opens up new possibilities for machine learning. Yet, amidst all these innovations, one constant remains stubbornly persistent - Microsoft Excel.
Whether it's financial planning, analysis or calculations, Excel is and remains the preferred tool for millions of business users worldwide. The familiar interface and the flexibility of the spreadsheet are not easy to replace.
SAP has recognised this need. With BEx Analyzer, Analysis for Office and now SAP Analytics Cloud, Add-in for Microsoft Excel, SAP has released solutions that enable users to access a consolidated database directly within their familiar Excel interface. This ensures a “single version of the truth”.
Yet, the add-in reached its limits with large amounts of data, around 1.000.000 cells. As soon as reports reached this size, the add-in restarted. It was simply not designed for massive data exports.
SAP has recently released high-volume mode, which enables analyses with up to 10 million cells. In this article, I will show you how to use high-volume mode and also address the current limitations.
You can easily switch to high-volume mode using the table's context menu.
When high-volume mode is active, a note is displayed in brackets below the table name. You can switch between standard and high-volume mode at any time.
High-volume mode generates a performance-optimised table specifically for large amounts of data and is limited to the essential analysis functions. The following table shows a comparison of standard mode and high-volume mode.
|
Standard Mode |
High-Volume Mode |
|
|
Configured data record limitation |
1 Mio |
10 Mio (backend maximum) |
|
Maximum table size |
ca. 1 Mio cells |
ca. 10 Mio cells |
|
Maximum workbook size |
ca. 1 Mio cells |
ca. 12 Mio cells |
Thus, the SAC Add-in can keep up with Analysis for Office. While Analysis for Office has a standard limit of 500.000 cells, you can set the parameter ResultSetSizeLimit to -1 in order to use the values defined in the BW system under BICS_DA_RESULT_SET_LIMIT_MAX.
Every time you change the layout of the table, the display is refreshed and the values are recalculated. With large amounts of data, this process can take some time. If you add multiple dimensions to the layout one after another, it is advisable to disable automatic refresh. This will help you avoid unnecessary loading times.
Alternatively, you can pause refresh before inserting the table, switch to high-volume mode and define the desired breakdown. When you subsequently refresh the table, all required data is loaded at once.
High-volume mode is designed for higher performance. But, the difference to normal mode only becomes significant with several hundred thousand cells. To measure and compare performance, you can activate performance profiling.
To do this, go to Workbook in the design panel and select Preferences. Then select Enable option under Performance Profiling. An additional tab will now be displayed containing the performance information.
There are also some limitations that must be considered. In high-volume mode, the functionality is reduced to the essentials. For example, planning and data entry are not possible. Planning processes with millions of cells rarely occur in practice though. Normally, planning takes place at an aggregated level.
There are also compromises in terms of appearance. Formatting options such as number formats or conditional formatting are not available. Additionally, you have to do without data point comments and calculated measures. The Add rows/columns function is also not available.
Furthermore, it is currently not possible to display properties or attributes of dimensions in high-volume mode. However, this limitation may be resolved in the future, as it is a very important feature in everyday work with data. As a workaround, you can first display the attributes and then switch to high-volume mode.
The high-volume mode in the SAP Analytics Cloud Add-in for Microsoft Excel closes an important gap. Where previously the limit was one million cells, analyses with up to 10 million cells are now possible. This is a real benefit for users who regularly work with large datasets.
We recommend using high-volume mode specifically for large data exports and comprehensive reports. For interactive analyses, planning or formatted reports, standard mode remains the better choice. The ability to switch between the two modes gives you the flexibility you need.
Are you contemplating how to use the SAC features in your organisation? Reach out for a free, individual consultation with one of our experts.