Best Practices for SAP Analytics Cloud CSV and Excel export performance

As a SAP”s customer, I see these products have many feature but also limitation. In this blog I will  share best practices for performance improvement for CSV and Excel export from SAP Analytics Cloud.

Export types

SAP Analytics Cloud supports export to CSV or Excel. CSV can be exported with “All” or “Point of View” options.

 

	CSV / All	CSV / Point of View		Excel
Functionality	Exports all the data, not just the currently visible data. For example, if your chart has a hierarchy, all the nodes of the hierarchy will be exported, even if you have not drilled down on the data in your chart.	Exports what you see in the table grid (all the visible rows and columns), including comment columns, calculations, and hierarchy levels in separate columns. If table has multiple pages then it also exports downloaded data from those pages, limited by drill limitation.
Component	server-side export	browser-side export
Max	3,000,000 cells 60 columns	no documented limit*		500,000 cells
Notes	Measure and dimension names from the data source (not from story) are exported. If you have renamed a measure or dimension, you won’t see new names in the exported data. The measure and dimension names from the data source are exported.	* User’s computer / browser memory limits number of cells which can be downloaded into the browser for export
		For a complete dataset export, user first to download all data into the browser by increasing table’s drill limitation
		For optimal performance 30,000 cells is a recommended limit, downloading more cells is supported but can be slower
		Default drill limitation is 500 rows x 60 columns. If it is not increased then only first 500 rows (up to 60 columns) are exported
Help	help	help	help

 

Restrictions for all export types

• Formatting (such as cell color, font styles, etc.) will not be exported.
• Hyperlinks are removed.
• Hierarchies are flattened.
• For tables, only the table data region is exported. Custom cells outside the data region (for example, on a grid page) are not exported.
• Custom cells and calculations created from story formula bar won’t be exported.
• The export option is disabled if any of the following are valid:
  • there is no data in the chart or
  • the model used in this story is in Edit mode.

 

General Best Practices

1. Before exporting data, make sure that the number formats are the same for the model and your local machine. For example, if the data you want to export uses a comma as a decimal separator and a period as a thousands separator (123.456,78), you will need to verify that your machine’s number format is the same. On a PC, in the Control Panel, go to Clock, Language, and Region >  Region > Additional Settings and verify the separator formats.
2. If you want to include custom cells (that are outside the table data region) in your export, you need to select all the cells, copy them, and then paste them into Excel.
3. Use Include Number Formatting when you want the formatting to exactly match what is in the chart or table. For example, if the table shows $12.04 Million, that is how the value will be exported, even if that is a rounded value.
4. Don’t use Include Number Formatting if the actual data values are different from those displayed in the chart or table (for example, the actual data has more decimal places).
5. Before opening the exported CSV file in Excel, verify that your Excel list separator settings are set correctly.

 

Performance Best Practices

1. If exporting large volume of data in CSV or Excel for the purpose of data analysis in Excel then consider performing as much analysis as possible in SAP Analytics Cloud, so the need for a large volume export is either reduced or eliminated.
2. If existing stories don’t provide data which you need and you preform additional analysis in Excel to get that data then consider redesigning stories such that new stories provide the data you need. This way additional analysis in Excel is no longer necessary and with it export to Excel may be no longer needed.
3. When exporting, consider exporting only data which you need, don’t export unnecessary data. This can be achieved by selecting story filter values for data you are interested in. Exporting unnecessary data may increase the export time.
4. Connecting your laptop to power outlet / power supply and setting power plan to high performance may improve performance.
5. Make sure to use a fast network with wide bandwidth as slow network can negatively impact high volume export performance.
6. Large amount of data are transferred over the network during export. Enabling gzip compression on the backend side significantly reduces the amount of data to transfer over the network. It can very significantly improve export performance. For example, in case of on-premise data sources a gzip compression can be enabled in on-premise backend server.
   • You can check if gzip is enabled by inspecting (in Google Developer Tools, Network tab) response headers from your on-premise backend data sources, for example by inspecting response headers of GetResponse requests. When gzip is enabled the Response Header contains “Content-Encoding: gzip”.
7. Consider using “CSV / All” export type everywhere where it is possible as it uses the power of the backend server more than other export types, and loads browser and user’s laptop less than other export types. Be aware that there are limitations as described in help. For example:
   • Export of max 3,000,000 cells is supported.
   • Export of max 60 columns is supported.
   • The measure and dimension names from the data source (not from story) are exported. If you have renamed a measure or dimension, you won’t see new names in the exported data. The measure and dimension names from the data source are exported.

 

CSV / Point of View or Excel Export Performance Best Practices

1. If due to CSV / All export type limitations you cannot use it and you decided to use CSV Point of View or Excel exports and if you are planning to export larger dataset then be aware that CSV Point of View and Excel exports will not be performed at the server, but rather export will be done in the browser after all story data is downloaded into the browser first. Therefore ensure that your computer has lots of free memory to load all the cells which you want to export. Using computers with insufficient memory can impact performance and can limit number of cells you can download and export.
2. For optimal performance it is not recommended to save story with a very large drill limitation. Otherwise the user would be forced to wait for the entire dataset download into the browser first (which may take a while for large data sets), before the user even gets a chance to specify desired / reduced drill limitation or to set filters. Instead it is recommended to set a small default drill limitation so initial story loads faster and user quickly gets ability to set desired filters and to configure drill limitation as desired.
3. CSV Point of View and Excel :

• For optimal performance 30,000 cells is a recommended limit for drill limitation, downloading more cells is supported but can be slower.
• For Excel 500,000 cells is maximum supported cell limit.
• No documented max cell limit for CSV Point of View export. It is limited by laptop memory / power.
• Default drill limitation is 500 rows x 60 columns. If it is not increased then only first 500 rows and up to 60 columns are exported.

4. To improve performance of the export consider unchecking “Repetitive Member Names” option on the table:

1. 1. Click on the table
   2. Click on “…”
   3. Select “Show/Hide”
   4. Uncheck “Repetitive Member Names”

 

5. Be aware that CSV Point of View and Excel exports will not be performed at the server, but rather export will be done in the browser after all story data is downloaded into the browser first. CSV Point of View and Excel large volume exports are two step process:

• Step 1: download all data into the browser by increasing table’s drill limitation
• Step 2: trigger export

 

Step 1: download all data into the browser by increasing table’s drill limitation

1. Download data into the browser by increasing table’s drill limitation.

a) Click on exclamation mark, on top left side of the table

b) Click on Edit Drill Limitations

c) Either select “Custom”, uncheck “Preserve recommended cell limit” and specify “Rows” and “Columns”, click OK.

d) Or select “Unlimited”, click OK.

 

2. Wait for data to load. When complete, running three blue dots will disappear.

3. Check that all data was successfully loaded by checking the drill limitations icon changed

 

Step 2: trigger export

4. Click export
5. In case of huge exports on underpowered computer the browser can be less responsive, if prompted, click “wait”.

 

Determining drill limitation needed for entire dataset export

Either:

• Use an Unlimited setting or Custom setting with a large value which is most likely to be sufficiently large for entire dataset, for example 25,000 rows x 25 rows, etc. Based on your knowledge of data and computers specs, over time you can decide on a large value to use which will be larger than the largest dataset in a table and which computers can handle. OR
• Improve stories by adding an Indicator widget which shows how many rows are in the table. Users can enter this number of rows in the table’s drill limitation.

The Indicator widget is to use COUNT formula, to include the same dimensions as table and to apply the same filters as the table.

The original source is from SAP blog.